Rebalancing w/ a Spreadsheet

by Tim Isbell, September 2013

On another web page, I outline how to use the Asset Allocation Rebalancing capabilities within Quicken. This work great for the set of asset classes that Quicken built into their system, but there's no way to customize the Quicken asset classes such as those in the chart on Asset Allocation Basics.

So I generated a spreadsheet tool to help me rebalance. You can see the spreadsheet below on this page. Notice that it is wider than the web page, so you'll need to use the horizontal scroll bar at the bottom of the spreadsheet. It's a Google Sheet that you can copy to your Google Drive or download into Excel by just clicking here: Rebalancing Spreadsheet. Then you can change the numbers or completely re-program it for your asset classes. If you improve on the spreadsheet (and I know some of you can) or find a better solution, PLEASE let me know!

Asset Allocation Rebalancing Spreadsheet



Spreadsheet Instructions

Notice that the spreadsheet is wider than most browsers. To see all the columns, A through H, you will probably need to use the slider bar at the bottom of the spreadsheet.

Also, notice at the bottom of the chart is an option allowing you to download the spreadsheet to your computer. There you can play with it all you want.

Blue fields in columns A through E are to insert your asset allocation percentages, consistent with your risk tolerance. I pre-filled these for someone who has $100,000 to invest and wants to implement the asset allocation in the chart on the Asset Allocation Basics page of this website. 

Black fields in columns A through E are calculated fields. These distribute the number you put in A3 throughout all the levels. 

Blue fields in column F are to insert your current holdings.

Black fields in Column G are calculated fields, showing you how close your actual allocation is to your target location for that particular asset class.

Black fields in Column H are calculated fields, indicating how many dollars need to move in or out of that asset class in order to reach your target allocation. 

The Totals Check (last line) is so you can easily see that things add up. Each of the numbers on this line should be the same as the number you put into cell A3. If not, go back and find your error. 

If you don't have $100,000 to invest, simply put 0% in the fields of those small columns. When your investments grow to where these numbers meet the minimums, you can put the percentages back in.


All the best,

Tim


ċ
Asset Allocation Rebalancing Spreadsheet.gsheet
(0k)
Tim Isbell,
Mar 27, 2014, 10:18 PM
Comments