Rebalancing template/spreadsheet
- InvestorNewb
- Posts: 1663
- Joined: Mon Sep 03, 2012 11:27 am
Rebalancing template/spreadsheet
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!
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)
Re: Rebalancing template/spreadsheet
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.
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.
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.
- InvestorNewb
- Posts: 1663
- Joined: Mon Sep 03, 2012 11:27 am
Re: Rebalancing template/spreadsheet
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.
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)
-
- Posts: 11647
- Joined: Sat Oct 04, 2008 11:42 am
Re: Rebalancing template/spreadsheet
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
Here's a page describing the googlefinance() function:
http://support.google.com/drive/bin/ans ... wer=155178
Re: Rebalancing template/spreadsheet
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...
Re: Rebalancing template/spreadsheet
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!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.
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.
Re: Rebalancing template/spreadsheet
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
Re: Rebalancing template/spreadsheet
Awesome Google Docs suggestion for automatic price updates, thanks!
http://support.google.com/drive/bin/ans ... wer=155178
http://support.google.com/drive/bin/ans ... wer=155178
1Fi1LTvx8KmtotRqcha9mj5h43319mJvjY
-
- Posts: 1
- Joined: Tue Mar 04, 2014 7:10 pm
Re: Rebalancing template/spreadsheet
Hi SSSS, I'm also interested in your rebalancing Google Doc template. Can you send a link to me? Thanks!
-
- Posts: 625
- Joined: Mon Jan 07, 2013 10:02 am
Re: Rebalancing template/spreadsheet
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.
Re: Rebalancing template/spreadsheet
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.
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.
Re: Rebalancing template/spreadsheet
I have found this rebalancing template useful and relatively simple:
http://www.financialramblings.com/archi ... readsheet/
http://www.financialramblings.com/archi ... readsheet/
-
- Posts: 625
- Joined: Mon Jan 07, 2013 10:02 am
Re: Rebalancing template/spreadsheet
SSSS - thanks a lot for posting the link, just downloaded it.
Re: Rebalancing template/spreadsheet
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?
Are you placing your fixed income + REIT assets in your ROTH IRA while having the others in brokerage?
Re: Rebalancing template/spreadsheet
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:
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: