Rebalancing Spreadsheet

Just thought I’d post a link to a rebalancing spreadsheet I’ve used for some time now from www.flexibleretirementplanner.com:

Rebalancing Spreadsheet

The spreadsheet needs a couple small things to get working. The main thing is to erase the data in the Imported Data tab. Then, fill out the Imported Data tab with your Permanent Portfolio asset class names along with the cost basis and current market value. For example:

Vanguard Total Stock Market  $xxxx   $yyyy

US Treasury Long Term Bonds $xxxx   $yyyy

Gold Bullion $xxxx $yyyy

Treasury Money Market $xxxx   $yyyy

Next up is to go to the Asset Class Info tab. On the bottom table you want to delete the security names that are listed under the Security Table. Then go into the Asset Class 1 column and blank out the asset class types. Next, put in the asset class names exactly as typed them on the Imported Data tab. In the Asset Class 1 column select the name of the asset class that best defines what you are using. For instance for my Vanguard Total Stock Market index I just selected “Lg Cap Blend.” For the bonds select “Domestic Bonds.” For your Treasury Money Market select “Cash.” For the Gold select “Gold.” If you did this right, the current value you typed into the previous tab will be copied over, if not you will get an Not Found message. Check for typos if this happens.

Then, go to the Rebalance tab. Type in “0″ in each column entry under Target Percent to blank everything out. Then go to each asset class label (Lg Cap Blend, Domestic Bonds, Gold, Cash) and put in your desired percent holding. In the case of the Permanent Portfolio you put in 25% next to Lg Cap Blend, Domestic Bonds, Cash and Gold. You will see the figures you entered into the Imported Tab be magically copied into the spreadsheet. The target amount figure should match the portfolio values you entered in the Imported Data tab.

Finally, go to the top left box and look for Trigger Factor. This is the value where if the asset class has shifted up or down too much the spreadsheet will tell you how much you need to buy or sell to bring it back into alignment. You can try setting it between 20-30% for your rebalancing bands.

Now when you need to see if and how much to rebalance you simply update the current market values in the Imported Data tab and the figures are done for you. You can also play around with the parameters on the spreadsheet to test out various doomsday scenarios for your allocation. But be careful not to alter the formulas.

Enjoy.

 

 

 

 

Craig Rowland

I own the place.

  • http://www.bravenewlife.com Brave New Life

    This spreadsheet is great. I have a spreadsheet, but it pales in comparison. And since I use Quicken, this works perfectly.

    Thanks for sharing.

  • fenhkg

    Question: If an individual is using the PP and investing on a monthly basis how do we handle rebalancing?
    Is the following approach valid?:
    1) $100 contributed/invested monthly
    2) Purchase $25 of each of the 4 asset classes each month
    3) Whenever one of the 4 asset classes goes out of balance by +/- 10% sell off (or buy more)

    I guess I am a little confused on how a person “capture gains” if instead of actually selling an asset that has appreciated they use a greater % of their $100 monthly contribution to buy more of the asset(s) that have “under performed” to bring the portfolio into balance.