- Joined
- Oct 13, 2010
- Messages
- 10,763
I am sharing an Excel sheet that calculates an asset allocation across multiple accounts and investment vehicles. It also provides a section where proposed rebalancing actions can be entered, and the resulting new allocation is presented.
https://drive.google.com/file/d/0B6X74x23Hx7DeEg4el94WXgyOUk/view?usp=sharing
The sheet has some formatting that doesn't come through on Google docs, so downloading and running in your spreadsheet software might be advised if you plan to get serious about using the sheet. Running directly on Google is probably good enough for window shopping.
Just for a quick once-over, if you open the sheet you can see the positions within accounts. Each position is split into it's allocation based on the percentages to the right.
You can show rows 40 through 78 and see that US Equities is high by $128K, Hard Assets is $133K low, etc. Yellow signifies numbers out of range. Looking at row 78, those numbers are all in range (not highlighted in color). Now find 30 and -30 in column S. Change those to 20 and -20. That would signify a transfer plan of 10K less from VFINX to EPP. Notice that the asset allocation plan of the sample was originally in range, and now there is one value that's out of range. Rather arbitrarily, I've set this sheet to highlight cells that are more than $12K out of whack.
My motivation for this spreadsheet was how very simple some of the asset allocation spreadsheets available on the Internet seemed to be (not that I looked real hard or anything). What I'm sharing here is just a simplified version of what I really use. This one has 9 asset classes and 29 positions. The asset classes are not exactly what I use. The positions, target asset allocation percents, accounts, investment vehicle allocations, balances, everything is just made-up as an example.
If there's an easier way to get all this done, I'd like to hear about it. But for now, this is pretty much how I do my AA. This is not "automated", so it's a trial and error thing. And each positive entry must have a negative entry of the same amount in order to give the output any meaning. What makes the problem harder is that one can't transfer from any account to any other account since that would have serious tax consequences and might not even be possible/legal. So you must stay in the bounds of your account, and that account may or may not have an investment vehicle that has what you need to get in balance.
https://drive.google.com/file/d/0B6X74x23Hx7DeEg4el94WXgyOUk/view?usp=sharing
The sheet has some formatting that doesn't come through on Google docs, so downloading and running in your spreadsheet software might be advised if you plan to get serious about using the sheet. Running directly on Google is probably good enough for window shopping.
Just for a quick once-over, if you open the sheet you can see the positions within accounts. Each position is split into it's allocation based on the percentages to the right.
You can show rows 40 through 78 and see that US Equities is high by $128K, Hard Assets is $133K low, etc. Yellow signifies numbers out of range. Looking at row 78, those numbers are all in range (not highlighted in color). Now find 30 and -30 in column S. Change those to 20 and -20. That would signify a transfer plan of 10K less from VFINX to EPP. Notice that the asset allocation plan of the sample was originally in range, and now there is one value that's out of range. Rather arbitrarily, I've set this sheet to highlight cells that are more than $12K out of whack.
My motivation for this spreadsheet was how very simple some of the asset allocation spreadsheets available on the Internet seemed to be (not that I looked real hard or anything). What I'm sharing here is just a simplified version of what I really use. This one has 9 asset classes and 29 positions. The asset classes are not exactly what I use. The positions, target asset allocation percents, accounts, investment vehicle allocations, balances, everything is just made-up as an example.
If there's an easier way to get all this done, I'd like to hear about it. But for now, this is pretty much how I do my AA. This is not "automated", so it's a trial and error thing. And each positive entry must have a negative entry of the same amount in order to give the output any meaning. What makes the problem harder is that one can't transfer from any account to any other account since that would have serious tax consequences and might not even be possible/legal. So you must stay in the bounds of your account, and that account may or may not have an investment vehicle that has what you need to get in balance.
