Rebalancing template/spreadsheet

Have a question about your personal investments? No matter how simple or complex, you can ask it here.

Rebalancing template/spreadsheet

Postby InvestorNewb » Wed Feb 13, 2013 2:03 pm

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!
Current Holdings: VTI, VXUS, VNQ, VCE (largest to smallest)
User avatar
InvestorNewb
 
Posts: 1012
Joined: Mon Sep 03, 2012 12:27 pm

Re: Rebalancing template/spreadsheet

Postby SSSS » Wed Feb 13, 2013 2:27 pm

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
SSSS
 
Posts: 1865
Joined: Fri Jun 18, 2010 12:50 pm

Re: Rebalancing template/spreadsheet

Postby InvestorNewb » Wed Feb 13, 2013 3:03 pm

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.
Current Holdings: VTI, VXUS, VNQ, VCE (largest to smallest)
User avatar
InvestorNewb
 
Posts: 1012
Joined: Mon Sep 03, 2012 12:27 pm

Re: Rebalancing template/spreadsheet

Postby DSInvestor » Wed Feb 13, 2013 3:35 pm

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
DSInvestor
 
Posts: 6541
Joined: Sat Oct 04, 2008 12:42 pm

Re: Rebalancing template/spreadsheet

Postby nydad » Wed Feb 13, 2013 3:55 pm

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...
User avatar
nydad
 
Posts: 449
Joined: Wed Dec 01, 2010 12:10 pm

Re: Rebalancing template/spreadsheet

Postby Raptor » Wed Feb 13, 2013 8:36 pm

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! :)
Raptor
 
Posts: 1
Joined: Wed Feb 13, 2013 8:28 pm

Re: Rebalancing template/spreadsheet

Postby grabiner » Wed Feb 13, 2013 8:47 pm

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
David Grabiner
User avatar
grabiner
Advisory Board
 
Posts: 13303
Joined: Wed Feb 21, 2007 12:58 am
Location: Columbia, MD

Re: Rebalancing template/spreadsheet

Postby money » Thu Feb 14, 2013 12:31 pm

Awesome Google Docs suggestion for automatic price updates, thanks!

http://support.google.com/drive/bin/ans ... wer=155178
1Fi1LTvx8KmtotRqcha9mj5h43319mJvjY
money
 
Posts: 55
Joined: Sun Jan 20, 2013 6:31 pm
Location: California

Re: Rebalancing template/spreadsheet

Postby cykoballer » Tue Mar 04, 2014 9:13 pm

Hi SSSS, I'm also interested in your rebalancing Google Doc template. Can you send a link to me? Thanks!
cykoballer
 
Posts: 1
Joined: Tue Mar 04, 2014 9:10 pm

Re: Rebalancing template/spreadsheet

Postby michaelsieg » Wed Mar 05, 2014 12:21 am

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.
michaelsieg
 
Posts: 324
Joined: Mon Jan 07, 2013 12:02 pm

Re: Rebalancing template/spreadsheet

Postby SSSS » Wed Mar 05, 2014 4:16 am

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.
User avatar
SSSS
 
Posts: 1865
Joined: Fri Jun 18, 2010 12:50 pm

Re: Rebalancing template/spreadsheet

Postby bogle_rad » Wed Mar 05, 2014 6:15 am

I have found this rebalancing template useful and relatively simple:

http://www.financialramblings.com/archives/asset-allocation-spreadsheet/
bogle_rad
 
Posts: 4
Joined: Mon Jul 01, 2013 8:04 pm

Re: Rebalancing template/spreadsheet

Postby michaelsieg » Wed Mar 05, 2014 9:47 pm

SSSS - thanks a lot for posting the link, just downloaded it.
michaelsieg
 
Posts: 324
Joined: Mon Jan 07, 2013 12:02 pm


Return to Investing - Help with Personal Investments

Who is online

Users browsing this forum: bluejello, Google [Bot], jwtietz, qwerty123, reyarey, tomd37 and 89 guests