The Spreadsheet Asset Allocation Tool

Every so often, someone asks me to help with their finances. Usually, it has to do with asset allocation. I'm not an expert, but one of the things I know how to do is to use a spreadsheet. I usually open up a spreadsheet, and then perform a copy/paste from Vanguard's home screen, then open a second worksheet in the same file, and use that to compute asset allocation, and to see how far off things are.

I recently switched from a PC-based spreadsheet to one based on Google docs and spreadsheets. There are two reasons for this: first is that I ended up with so many different versions of the spreadsheet that I couldn't keep them straight between all the computers and files floating around. It doesn't help that I decided not to bring my Infrant RAID file system with me when I came to Germany, so I can't retrace some of my thinking from before, easily.

The second reason is the GoogleFinance function. This handy little feature lets me use one spreadsheet (instead of creating one per asset allocation period, which was what I was doing), only update the number of shares I have, and then have the asset allocation spreadsheet automatically recalculate. That way, like the big boys, I can rebalance every day if I wanted to. (NOTE: doing so in a taxable account is a bad idea!)

Unlike other financial bloggers, I don't blog anonymously, so I'm not going to show everyone my spreadsheet. However, I have constructed a hypothetical spreadsheet around a simple $100k portfolio with a 75% equity/25% fixed income allocation. The only unfortunate thing about this sample is that you can't download it or copy it or do anything interesting with it, but it gives you an idea of what I go through every asset re-allocation period. There are a few interesting things about this asset allocation, even so:

1. The portfolio isn't as tax efficient as it could be. For the purposes of capturing the foreign tax credit, it is best to hold the components of the Total International Index Fund rather than the Total fund-of-funds itself. Note that for domestic funds, holding the Total Stock Market Index is the right thing to do tax-wise, since as companies grow or shrink you don't pay the transaction fees for them moving from one market segment size to the next. (Unlike Small/Medium/Large cap categories, stocks don't usually move between countries)
   
2. The only alternative type investments is the REIT. I personally use a more complex mix to gain more diversity. However, for a portfolio of this size, if you break things up too much you lose efficiency --- you don't reach the Admiral shares level as quickly as you otherwise might.
   
3. Adding a value tilt is as easy as adding an appropriate value fund and adjusting the numbers in the asset allocation worksheet. I consider this superior to holding each of the components separately because you reach the Admiral shares faster (get lower costs), and it's simpler to track. Note that Bernstein recommends holding the components, but he normally deals with such large sums of money that getting the lowest cost share class is a given. The rest of us just aren't that lucky.

Disclaimer: The example spreadsheets and asset allocation are meant for informational purposes only. They are not meant as an endorsement of a particular fund, or to suggest that a particular asset allocation is the right one for you. Please do your own research before embarking on an investment program.