Figuring Out Asset Allocation Across Accounts

Figuring out our desired asset allocation was only the first step – the next was figuring out what trades I needed to make to get that allocation.  Enter Google Spreadsheet – which can pull financial information from Google Finance.  I’ve made it available with somewhat dummy data (The funds we have are accurate, but the number of shares is not), so you too can play with it.  It’s not likely to be immediately useful to you, but you can see it as an example of how we allocated across accounts, and create one for yourself.  The important page is the first one – you just want to categorize your holdings according to the asset classes you’ve defined (which may not be all that easy), then fill in your current amounts.

I used the second sheet for data – I keep track of the number of shares I have, and Google Finance pulls in the current prices and then updates the first sheet.   I have conditional formatting enabled on the second row to show me when my allocation is out of whack by +/- 1%.  If you change your desired allocations, you’ll need to change the conditional formatting – Google won’t let me use a cell as a starting point.

There is no magic linking the first sheet and the data sheet – it’s all manual linking in the formulas.

If you do happen to have more Google Spreadsheet-fu than I do and update it, please let me know so that I can link folks to an updated version.

5 thoughts on “Figuring Out Asset Allocation Across Accounts

  1. cashrebel

    That sounds like a better way to do it. Since I only re-balance my portfolio every year or so, I just log into Mint to see how much each account is worth and then build a spreadsheet to tell me what needs to change.

    1. Mom Post author

      Our plan is to only rebalance about once per year, but I’ve seen pros and cons to rebalancing based on how far “off” you are. So I’m going to keep track of the data about once per month and decide over the next few months how I want to handle the rebalancing. In all accounts except one, there are virtually no fees to buy/sell, so I could theoretically rebalance every day if I wanted.

    1. Mom Post author

      Hopefully, the spreadsheet is useful to you. I’d love to make it a bit more automated, but my spreadsheet-fu is decidedly lacking. I can get the information I want after some time, but I’d love to be able to learn how to say “Only sum the cells in column J where the value of column K is something”. My brain tells me this should be possible, but I haven’t figured it out yet.

  2. Pingback: To rollover or not - the importance of fees

Tell me your thoughts