Asset Allocation Speadsheet - suggestions, please

[Htown Harry]

Quote:

Originally Posted by sengsational The bigger challenge for me, that a lot of the stock templates don't cover, is that I'm balancing over so many accounts that can't exchange with each other. I've got 9 groupings now, and that's down from what it used to be. When I say 'groupings', it's money in a place that can't mix with another place. ... It's quite the puzzle to get allocated!

Quote:

Originally Posted by target2019 To the OP, it helped me quite a bit to limit the funds in a space. We have 3-5 funds in each space (403b, 401k, Roths, Sep-IRA, T-IRA, and taxable). So, in 7 spaces we are still under 20 funds total. There is some overlap, but the simplification of all is most necessary at this time.

You both bring up good points. Sengsational, your graphic helps a lot.

I have these nine "spaces", not counting 529 plans:

Me: 401(a), 457(b), IRA#1, IRA#2, Inherited IRA, Pension account, taxable

DW: IRA, 403(b)

I can consolidate to eight for sure by consolidating my two IRAs, maybe seven if DW's former employer's 403(b) provider will allow a transfer of her very small balance account to her IRA.

The 457(b) could be rolled to the IRA, but I choose not to do that so I can preserve the more favorable ER withdrawal terms. (Taxable, penalty free withdrawals at any age since I have separated from service at that employer.)

One constraint with the 457 account: there's only one stock or bond fund offered with less than a 0.5% ER, a small cap fund at 0.2%. I'll keep all of the funds in the 457(b) invested in small caps. Fortunately, the value of the account is a match to a small cap stock allocation I am comfortable with.

I do have the ability and need to reduce the number of holdings in some of the other accounts. I'm thinking 3-5 in the largest accounts should be preserve the flexibility for rebalancing without creating an overly large number of different fund name holdings.

Of course, I'll want tax efficiency in the taxable account...

A puzzle indeed.

[LOL!]

Most of our accounts have a single fund in them that basically never gets touched. For example, 3 IRAs are 100% invested in a bond fund. The taxable account doesn't really need any sells for a gain (which would generate taxes). All the necessary rebalancing occurs in a 401(k) plan which has all the index funds (US, int'l, bond, small) available.

Thus, one can look and solve the puzzle in about a minute.

[Htown Harry]

Here's feedback on some of your suggestions. I do appreciate the ideas.:

The Youtube video for an Excel add-in that I found was a bust. I spent 30 minutes figuring out where on my computer to download the file to, apparently because Windows 7 stores add-ins in a different directory than the one used in the 2009 code. Once I had that figured out, the download of the data created error messages, something about the number of columns. I aborted the mission after an hour.

I also tried the Data / Connections method in Excel, making it as far as a selected Google Finance stock page with arrows indicating fields I could link. It went nowhere meaningful from there. The bugler sounded a retreat after 20 minutes.

Conclusion: automatic price downloads are beyond my skills and / or patience. I'll start with a spreadsheet that requires manual copying of dollar values for each holding over from my MSMoney holdings screen.

(If I do move to a price x share count method in the future version 2 of my spreadsheet, there is hope. I already have a listing of all of my fund holdings in a Yahoo portfolio, using dummy share counts. I use it for manual price updates in MSMoney. I downloaded the CSV file of the portfolio into a temporary spreadsheet, with good results. If I pay attention to keeping the fund list order consistent, copying current prices over to my asset allocation spreadsheet should be pretty quick and straightforward.)

I have an old account at Fidelity with a token amount in a money fund, so I looked at that site. I didn't find a way to add outside account data after some searching. My patience was thin since I was nervous I would have the same problem with incorrect allocations of a closed end fund that I do with Vanguard. Aborted after 20 minutes.

LOL!, I gave the TRP to Morningstar method a go. I had flashbacks to a bad experience trying to create a user name and account back when you ran the asset allocation tutorial a few years ago. Back then I believe I eventually made it through to the Morningstar X-ray, but this time I aborted when the "Call a TRP representative for assistance" message came up after several attempts to re-use my old user name.

An adaptation of Financial Ramblings' spreadsheet has the lead. It has enough features to beat some of the most basic examples I have found, yet the page setup and formulas are a good match to my low-to-intermediate Excel skills. I'll spend more time with it next weekend.

[Htown Harry]

Quote:

Originally Posted by LOL! Most of our accounts have a single fund in them that basically never gets touched. For example, 3 IRAs are 100% invested in a bond fund. The taxable account doesn't really need any sells for a gain (which would generate taxes). All the necessary rebalancing occurs in a 401(k) plan which has all the index funds (US, int'l, bond, small) available. Thus, one can look and solve the puzzle in about a minute.

Very helpful, LOL.

[target2019]

Quote:

Originally Posted by Htown Harry Very helpful, LOL.

In this link you'll see a graphic of part of my summary sheet. I think it explains what I've done, but without formulas.
https://docs.google.com/file/d/0B0p8...it?usp=sharing

What I am after each month is the total in a fund. That is off screen. In the top half is summary by space. Then I have a summary by type of asset in lower half, and calculate how far I am off target.

[Htown Harry]

Quote:

Originally Posted by LOL! All the necessary rebalancing occurs in a 401(k) plan which has all the index funds (US, int'l, bond, small) available. Thus, one can look and solve the puzzle in about a minute.

Quote:

Originally Posted by target2019 Then I have a summary by type of asset in lower half, and calculate how far I am off target.

Seeing another example is a big help. Thanks target2019.

One difference in your spreadsheet from some is it doesn't include fields and formulas to recommend specific rebalancing dollar amounts by account or holding. Instead, it appears you prefer to just use the deviation from the target percentages as a filter, then figure a set of rebalancing transactions off-spreadsheet. (Or maybe you just play with the dollar value or share data to try what-ifs that produce revised holding percentages that are in balance with your target allocation percentages?)

So let's see if I really understand LOL's "don't sweat the pennies" rebalancing approach correctly, as applied to target 2019's spreadsheet...

If today was rebalancing day, LOL! would sell about 1.7% portfolio value of the overweighted small / midcap fund in the 401k account to get that holding and the U.S. stock category back to target. The proceeds would then be spent on the fund in the 401k that is rough equivalent of Vanguard Total Bond, which gets the bond category to target. Then you would make a slight bookkeeping adjustment to add a 1.7% target weighting to your 21st holding and a similar target reduction to Vanguard Total Bond.

Everything else is within 1%, so you are done.

[target2019]

Quote:

Originally Posted by Htown Harry Seeing another example is a big help. Thanks target2019. One difference in your spreadsheet from some is it doesn't include fields and formulas to recommend specific rebalancing dollar amounts by account or holding. Instead, it appears you prefer to just use the deviation from the target percentages as a filter, then figure a set of rebalancing transactions off-spreadsheet. (Or maybe you just play with the dollar value or share data to try what-ifs that produce revised holding percentages that are in balance with your target allocation percentages?) So let's see if I really understand LOL's "don't sweat the pennies" rebalancing approach correctly, as applied to target 2019's spreadsheet... If today was rebalancing day, LOL! would sell about 1.7% portfolio value of the overweighted small / midcap fund in the 401k account to get that holding and the U.S. stock category back to target. The proceeds would then be spent on the fund in the 401k that is rough equivalent of Vanguard Total Bond, which gets the bond category to target. Then you would make a slight bookkeeping adjustment to add a 1.7% target weighting to your 21st holding and a similar target reduction to Vanguard Total Bond. Everything else is within 1%, so you are done.

You nailed it. I do have dollar amounts in there. The columns are hidden. No peeking...

Also have two additional columns. One is for whatif/ -- I add $5K to Roth fund a, what does that do to allocation..not much.

Also have a column that I can toggle on and off that shows the effect of new contributions through the end of this year.

In each space there is a fixed fund that I can easily rebalance to - except the 401k. I might add a stable value or similar fund there, and move 1% of the small/mid into that. I might also direct 100% of new contributions to the stable value.

I could do similar in the 403b, and forego mucking with the 401k.

Notice I used might or could, as I know this situation could easily take care of itself before August is over.

[Htown Harry]

Quote:

Originally Posted by target2019 You nailed it.

I believe I now have a plan. Thanks for the help, everyone.

One last question for LOL! Does your spreadsheet have fields for tracking r-squared and alpha on the holdings reported in the LOL! Market Timing Newsletter?

[LOL!]

Quote:

Originally Posted by Htown Harry I believe I now have a plan. Thanks for the help, everyone. One last question for LOL! Does your spreadsheet have fields for tracking r-squared and alpha on the holdings reported in the LOL! Market Timing Newsletter?

The answer is YES. This is provided by the M* Portfolio Manager. Here's s snapshot of part of the portfolio: