Rebalancing template/spreadsheet

Have a question about your personal investments? No matter how simple or complex, you can ask it here.
Post Reply
User avatar
Topic Author
InvestorNewb
Posts: 1663
Joined: Mon Sep 03, 2012 11:27 am

Rebalancing template/spreadsheet

Post by InvestorNewb »

Hello,

Can anyone recommend a free rebalancing template/spreadsheet download for Microsoft Excel?

I'm looking for one that shows current allocation and desired allocation, and how much money needs to be reinvested to reach the desired allocation.

I'm also looking for one that is easy on the eyes - so simple & aesthetic.

Thanks!
My Portfolio: VTI [US], VXUS [Int'l], VNQ [REIT], VCN [Canada] (largest to smallest)
User avatar
SSSS
Posts: 1914
Joined: Fri Jun 18, 2010 11:50 am

Re: Rebalancing template/spreadsheet

Post by SSSS »

I could send you a copy of mine if you like the look of it. It's a Google Docs spreadsheet so that it can update prices automatically -- it could be exported to Excel, but the automatic price updating would be lost. I've heard there are ways to do automatic price updating in Excel, but I haven't messed with it.

Image

Only the blue cells are edited directly; everything else is calculated from them. Column H has several levels of conditional formatting going from yellow to red as things dirft further out of balance. If you're not using ETFs and don't have to worry about rounding off to the nearest share, some of the columns could be deleted.
User avatar
Topic Author
InvestorNewb
Posts: 1663
Joined: Mon Sep 03, 2012 11:27 am

Re: Rebalancing template/spreadsheet

Post by InvestorNewb »

Thanks for the offer, SSSS. I'm also using ETFs, but I am looking for something a little simpler.

I also don't have Google Docs, so I would prefer to use a remote template.
My Portfolio: VTI [US], VXUS [Int'l], VNQ [REIT], VCN [Canada] (largest to smallest)
DSInvestor
Posts: 11647
Joined: Sat Oct 04, 2008 11:42 am

Re: Rebalancing template/spreadsheet

Post by DSInvestor »

I also like Google Docs which is free. If you have a gmail account, you have google docs. The GoogleFinance() function in Google Docs makes it really easy to get prices for US stocks, ETFs and mutual funds. Once you have that, you can design the spreadsheet yourself very easily.

Here's a page describing the googlefinance() function:
http://support.google.com/drive/bin/ans ... wer=155178
Wiki
User avatar
nydad
Posts: 453
Joined: Wed Dec 01, 2010 10:10 am

Re: Rebalancing template/spreadsheet

Post by nydad »

You can use the smf_addin (http://finance.groups.yahoo.com/group/smf_addin/) to pull stock quotes into excel. There are others as well...
Raptor
Posts: 1
Joined: Wed Feb 13, 2013 6:28 pm

Re: Rebalancing template/spreadsheet

Post by Raptor »

SSSS wrote:I could send you a copy of mine if you like the look of it. It's a Google Docs spreadsheet so that it can update prices automatically -- it could be exported to Excel, but the automatic price updating would be lost. I've heard there are ways to do automatic price updating in Excel, but I haven't messed with it.

Image

Only the blue cells are edited directly; everything else is calculated from them. Column H has several levels of conditional formatting going from yellow to red as things dirft further out of balance. If you're not using ETFs and don't have to worry about rounding off to the nearest share, some of the columns could be deleted.
SSSS, this is exactly what I've been looking for. I prefer to use Google docs for this - would love a copy / link, if you can make it available. Many thanks! :)
User avatar
grabiner
Advisory Board
Posts: 35307
Joined: Tue Feb 20, 2007 10:58 pm
Location: Columbia, MD

Re: Rebalancing template/spreadsheet

Post by grabiner »

There are links to several examples on the wiki, as well as a guide for how to make your own:Using a Spreadsheet to Maintain a Portfolio
Wiki David Grabiner
money
Posts: 57
Joined: Sun Jan 20, 2013 4:31 pm
Location: California

Re: Rebalancing template/spreadsheet

Post by money »

Awesome Google Docs suggestion for automatic price updates, thanks!

http://support.google.com/drive/bin/ans ... wer=155178
1Fi1LTvx8KmtotRqcha9mj5h43319mJvjY
cykoballer
Posts: 1
Joined: Tue Mar 04, 2014 7:10 pm

Re: Rebalancing template/spreadsheet

Post by cykoballer »

Hi SSSS, I'm also interested in your rebalancing Google Doc template. Can you send a link to me? Thanks!
michaelsieg
Posts: 625
Joined: Mon Jan 07, 2013 10:02 am

Re: Rebalancing template/spreadsheet

Post by michaelsieg »

SSSS - I like your rebalancing sheet a lot, it has a nice look to it and I hold almost the same asset classes - I saved a copy of the image you posted and will try to get it to work on an Excel file...the new Excel for mac apparently supports VBA - for some reason I am worried to upload my financial info to a google drive.
User avatar
SSSS
Posts: 1914
Joined: Fri Jun 18, 2010 11:50 am

Re: Rebalancing template/spreadsheet

Post by SSSS »

This link is a copy of my spreadsheet:

https://docs.google.com/spreadsheet/ccc ... sp=sharing

That will take you to a read-only copy. If you're signed into a Google account, you can "Make a Copy" from the File menu & will be able to edit it. Even without a Google account, you can export a copy for Excel, however the =googlefinance() function isn't supported in Excel so you'd need to find another way to fill in share prices (or do it manually).

Under normal circumstances only the blue cells need to be edited. Everything else is automatically calculated. If you decide whether to rebalance based on how far off-balance you've drifted, you might consider tweaking the conditional formatting rules in column H to your desired values. I currently have white/yellow/orange/red based on how far from 100% of the desired percentage they are.
bogle_rad
Posts: 8
Joined: Mon Jul 01, 2013 7:04 pm

Re: Rebalancing template/spreadsheet

Post by bogle_rad »

I have found this rebalancing template useful and relatively simple:

http://www.financialramblings.com/archi ... readsheet/
michaelsieg
Posts: 625
Joined: Mon Jan 07, 2013 10:02 am

Re: Rebalancing template/spreadsheet

Post by michaelsieg »

SSSS - thanks a lot for posting the link, just downloaded it.
SSSill
Posts: 2
Joined: Wed Jun 22, 2016 12:52 pm

Re: Rebalancing template/spreadsheet

Post by SSSill »

SSSS- Thank you for posting, you are making my life easier : )

Are you placing your fixed income + REIT assets in your ROTH IRA while having the others in brokerage?
User avatar
SSSS
Posts: 1914
Joined: Fri Jun 18, 2010 11:50 am

Re: Rebalancing template/spreadsheet

Post by SSSS »

There are apparently quite a few people using my spreadsheet:

https://docs.google.com/spreadsheet/ccc ... sp=sharing

That's great that people find it useful, but every week I've been receiving several requests for write access to my master copy, and it doesn't make sense to grant those requests. Anybody who wishes to use the spreadsheet can use the "Make a copy" functionality to copy it to their own Google Drive and then do whatever they want with it. Once you've made your own copy you should have full write access to your copy, and other people (including me) won't be able to see your copy unless you choose to reshare it.

Here's a screenshot in case anyone has trouble finding it:

Image
Post Reply