August 8, 2011
Just thought I’d post a link to a rebalancing spreadsheet I’ve used for some time now from www.flexibleretirementplanner.com:
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.