Our Asset Allocation Spreadsheet

by Michael on Aug 14, 2013 · 3 comments

Okay… As requested, I’ve pulled together a generic version of the asset allocation spreadsheet that I mentioned in my post about deciding when to rebalance.

While I keep mine in Google Docs, I discovered some limitations that impacted the flexibility of the spreadsheet when I was building the generic version as a Google Spreadsheet. Thus, I wound up making Excel and OpenOffice versions instead.

Note that I don’t expect this to be all things to all people. It’s quite simple and it was designed for our specific needs. But it works well, and should give you a starting point for rolling your own if you want something different.

Before I talk about how to use it, here are the download links:

I made the .xlsx version with Excel 2011 for Mac. Hopefully it works across platforms. As for the .ods version, I made that with the latest version of OpenOffice. If you’d prefer a Google Spreadsheet, you can simply import it.

If you do decide to import into Google Docs, you’ll need to fix the conditional formatting on the Overview sheet. Apparently Google doesn’t let you use variables in formatting rules so you’ll have to enter your tolerance bands manually.

As for using the spreadsheet to track your allocation…

How to use the spreadsheet

It probably makes the most sense to work through it from back to front. Starting on the Allocation sheet, set your desired asset allocation. This includes both an overall stock/bond breakdown as a breakdown within stocks and bonds. I’ve also included a field for the width of your tolerance band.

The current values reflect our preferred allocation, as well as the actual width of our tolerance bands (i.e., ±5%). That being said, you can easily edit anything in a beige-ish cell. The others are locked to prevent accidental changes, but…

There’s no password required to unlock the protected cells. To enable editing, just go to Tools > Protection in Excel or Tools > Protect Document in Open Office and toggle the protection. Thus, you can customize it to suit your needs.

Next up, the Data sheet… Since spreadsheets are made for doing math, I built in the ability to include balances for each asset class from up to eight accounts. Thus, you don’t have to sum things up yourself.

Just enter your holdings for each account (or leave as zero, as appropriate) and the spreadsheet will do everything for you. Note that you can also rename the accounts if you wish. They’re currently named Account1, Account2, and so on.

Note: There’s no need for Account1 for one holding to be the same actual account as Account1 for another holding. The names are just placeholders and the math is done on the sums for each asset type.

And finally… The Overview sheet. This entire sheet is locked, as it auto-updates based on the values entered on the other sheets. The values here reflect a perfectly balanced portfolio worth $100k (based on the default entries on the Data sheet).

The top section gives you the overall stock/bond breakdown. The next two sections give you the breakdown within stocks and bonds, respectively. And the last section gives provides a detailed look across all four asset classes.

In looking at the Overview, you’ll see that the cells in the rightmost column are colored green. That’s because everything is within the tolerance band specified on the Allocation sheet. Once things leave that range, affected cells will turn red and you’ll know that it’s time to rebalance your holdings.

That’s it. I hope this is helpful. 🙂

1 Harlan V. August 14, 2013 at 3:21 pm

Thanks for this! Very very useful!

2 Martin August 14, 2013 at 4:17 pm

Great work. I like using spreadsheets to review allocation, but for my 401k. In my other accounts I am still lenient to my allocation. I am 100% stocks anyway.

3 Little House August 15, 2013 at 9:46 am

I’m guessing I could use this for my mutual fund as well; break it into which companies they are investing in. Thanks for sharing!

Comments on this entry are closed.

Previous post:

Next post: