Asset Allocation Speadsheet - suggestions, please

[Htown Harry]

One of my 2013 new year's resolutions was to become more disciplined and systematic with our asset allocation and fund re-balancing. I still have 4-1/2 months to make it.

One obstacle has been my use of the My Money financial software for tracking my portfolio. When Microsoft stopped supporting MS Money, one of the features they disabled was an asset allocation tool. Even if it was still available, I believe the only categories available were "stock", "bond" and "mutual fund". I doubt I would find it particularly useful, even if the capabilities were still intact.

I am not interested in changing to another financial software or entering data in a web-based application.

So, I have been resigned to the fact that I would need to create my own spreadsheet from scratch. It's certainly doable, but it will potentially be quite an investment in time for my well-diversified portfolio of over a dozen different holdings spread over several providers and a half-dozen accounts.

Today I came across a blogger's downloadable spreadsheet that looks like a substantial head start on what I have in mind.
Our Asset Allocation Spreadsheet @ Financial Ramblings

I believe I would need to invest just a couple of hours in tweaking this for my own use. Quarterly updates would probably take about half an hour of cutting and pasting current fund values over from My Money.

I would appreciate comments and advice, perhaps with comparisons to other spreadsheets found on the web or your home-grown asset allocation spreadsheets.

 

[pb4uski]

I have a simple Excel spreadsheet with rows for asset classes and columns for the various accounts we have and I plot the holdings of each account to the appropriate rows and then total the rows and compare to my targets.

Most of my holding relate to a single asset class (I ignore cash held by, say, a domestic stock mutual fund) but for a few holdings that have more than on asset class I allocate based on the funds holdings as show on M*.

It takes me about 5 min to update it and one of the things I like about it is that it includes a comparison to target and how far out of balance I am.

 

[Animorph]

I use an Excel spreadsheet. It automatically downloads fund/ETF prices from Yahoo Finance, where I keep a simple "portfolio" of every fund and ETF I was ever interested in. A single table download gets me all the prices I need. I copy my portfolio shares from a single Quicken report. The spreadsheet grabs the number of shares and the latest price, totals everything up, and figures out the allocation percentages. I don't worry about allocations within an individual fund, like a Morningstar X-Ray, which would be a lot more complex.

 

[photoguy]

I believe I would need to invest just a couple of hours in tweaking this for my own use. Quarterly updates would probably take about half an hour of cutting and pasting current fund values over from My Money.

I think most spreadsheets have a way of directly accessing stock values so you don't need to manually cut and paste. Usually it's a function call based on the ticker name.

I started out with a simple sheet (fund name, shares, share price) broken into asset category (cash & equivalante: cash, mm, ibond, bonds: short, intermediate, tips, and various stock categories).

I now also track weighted expense ratios, dividends, taxable vs non-tax locations. etc.

Satisfies some of my OCD tendencies around my portfolio without giving me an urge to "tweak" holdings. I can spend my time tracking down missing pennies and differences between yahoo stock price quotes vs vanguard (for the same fund).

 

[Htown Harry]

I use an Excel spreadsheet. It automatically downloads fund/ETF prices from Yahoo Finance, where I keep a simple "portfolio" of every fund and ETF I was ever interested in. A single table download gets me all the prices I need.

I think most spreadsheets have a way of directly accessing stock values so you don't need to manually cut and paste. Usually it's a function call based on the ticker name.

I'm sure there are a number of variations, but is this method of accessing prices from Yahoo! typical?

how to download Yahoo Finance data into Microsoft Office Excel for free - YouTube

 

[target2019]

Before going too far, make a list of what you want this spreadsheet to do.
- Display current value of holdings
- Segment stocks - bonds - cash into subcategories called ...
- and so on
I only keep month-end totals of accounts. At end of each month I grab all fund totals and a macro parses the data to my allocations spreadsheet.
Everything we own - even external funds - I get these totals at end of month from Vanguard, and paste to Excel, then parse data as I need to.
If I had to start over, I would probably use Quicken.

 

[LOL!]

It will take one less than 15 minutes to enter their financial holdings into the Morningstar Portfolio Manager which gives one the Portfolio X-ray (or use Instant X-ray and save as Portfolio). That seems to be much less time than using a spreadsheet.

The asset allocation tutorial shows how to get free M* Portfolio Manager via TRowePrice: http://www.early-retirement.org/forums/f28/asset-allocation-tutorial-31324.html

A trick is not to go anal retentive on it because for asset allocation decisions
(a) you don't need share amounts to the thousandth decimal place
(b) you don't need real dates
(c) you don't need real transactions either
(d) you don't need to worry about tracking reinvested distributions in any real sense (you do that with MSMoney)

Plus the info is "in the cloud" so you don't have to worry about losing it, backing it up, etc.

So as long as you accept that the result will be within $10 to $1000 of what you actually have, it is a simple matter to just copy your 20 to 30 ticker symbols and dollar amounts from MSMoney and be done with it.

 

[tulak]

I transitioned from Excel to Google Docs a few months ago. It was always a pain to get stock quotes into Excel, but with Google Docs, you just use the GoogleFinance function.

I also modeled my entire AA, in addition to keeping track of it in MoneyDance. I prefer the spreadsheet to modeling my portfolio, since it gives me more concise information than I could get out of a personal finance app.

 

[Animorph]

I'm sure there are a number of variations, but is this method of accessing prices from Yahoo! typical?

how to download Yahoo Finance data into Microsoft Office Excel for free - YouTube

I use the Data/Connections selections, which are tougher to use in 2007 and later versions. But they don't require any add-ons. You eventually end up surfing the web in an Excel window. It lets you select which portions of a web page you want to download. Tables are nicely loaded into multiple spreadsheet cells. You can have Excel auto update the downloads every few minutes if you like to monitor during the day.

 

[mdb]

Hi all. I'm the guy who wrote the article linked by the OP. I appreciate the interest.

I agree that tracking shares #s and pulling in the prices helps to streamline the process. I didn't do that for a variety of reasons, mainly because I have some holdings without ticker symbols. While I could've used a hybrid approach, I didn't. Not sure why, I just didn't.

Like kiki, I keep an eye on the big picture using MoneyDance, but I prefer to use a simple spreadsheet for rebalancing decisions.

Other options for tracking your allocation (that haven't been mentioned above) include things like Vanguard's Portfolio Watch, Fidelity's Full View, or a service like Personal Capital. I recently reviewed the latter, but I'm not sure if it's kosher to link to my own stuff here, so I won't. But it's not hard to find.

Thanks!

 

[Htown Harry]

Hi all. I'm the guy who wrote the article linked by the OP. I appreciate the interest.

Other options for tracking your allocation (that haven't been mentioned above) include things like Vanguard's Portfolio Watch, Fidelity's Full View, or a service like Personal Capital. I recently reviewed the latter, but I'm not sure if it's kosher to link to my own stuff here, so I won't. But it's not hard to find.

Thanks!

Nice to meet you. I do like your spreadsheet.

I have substantial holdings at Vanguard, and I like the Portfolio Watch layout. Unfortunately, it gives me meaningless data for the asset allocations, which is one of the reasons I plan to create my own spreadsheet. The reason: several closed-end bond funds I hold are incorrectly classified as stocks by Vanguard. I may try the other services you mention to see if they have the same issue.

LOL!, you threw me off a little bit with the way you worded this point.

(d) you don't need to worry about tracking reinvested distributions in any real sense (you do that with MSMoney)

I think you mean I don't need to have the spreadsheet make any return calculations or track the cost basis, correct?

If I use share counts and uploaded (or copied) prices as the raw data for the spreadsheet, I believe part of the periodic rebalancing must include updating the share counts to account for reinvested shares. To keep using the original share counts would significantly undercount the allocation on high-dividend and high capital gains mutual funds over time.

I think avoiding the extra work of updated for reinvested shares is an argument in favor of the dollar-value data entry method mdb used in his spreadsheet. Just enter one dollar figure for each holding and let the calculations run...

Or maybe not. If I spend the time to get the share price updates working automatically, then the rebalance data entry might actually be less work, since not all of my stocks and mutual funds have a reinvestment of dividends every quarter.

I'm probably overthinking this. Target2019 makes a very good point...

Before going too far, make a list of what you want this spreadsheet to do.

I appreciate the input. Keep it coming.

 

[LOL!]

If I use share counts and uploaded (or copied) prices as the raw data for the spreadsheet, I believe part of the periodic rebalancing must include updating the share counts to account for reinvested shares. To keep using the original share counts would significantly undercount the allocation on high-dividend and high capital gains mutual funds over time.

What I meant is that with the M* portfolio tool, you don't even need to know share prices because it will look them up for you.

As for reinvested distributions, those are usually rather small (say less than 3% of the holding) and you can just manually increase the number of shares every few months or even at year-end and away you go.

I just went through the steps to create a new portfolio with the M* portfolio manager at TRowePrice. It asks me to enter the ticker, number of shares and purchase price. I just put $1 for the purchase price. The tool looks up the real share prices and only uses the purchase price if you ask it for performance which you don't need to do. Anyways, for 5 holdings it took about a minute to enter the data.

(in essence it is like the dollar-entry value of the other tool, but is share-entry value instead)

I think you should at least try it and see if you like it. Plus you can have multiple portfolios with the tool, so that you can test things.

 

[sengsational]

Here's a video I made so you can keep from re-typing your portfolio with Morningstar instant x-ray:

 

[Animorph]

But M* or Fidelity Full View won't help much, if like me you are tracking several equity funds that you want to hold at a target percentage of your portfolio. The custom spreadsheet does that, and tells me if any holding has exceeded my rebalancing trigger. Very easy to use, once it's been set up.

 

[gauss]

I use the spreadsheets offered at Rebalance your Couch Potato Portfolio with these free tools | Squawkfox for the purpose of adding to the investments to keep the AA where I want it to be. I just enter the value of each funds before the additional money goes in and the amount of money to go in. The desired AA is already entered into the spreadsheet. The result is the amount of money to send to each investment.

I specifically use the U.S. Margarita Couch Potato spreadsheet because it was setup with three funds, which was the amount that I needed. I just changed the labels to reflect my actual funds.

-gauss

 

[photoguy]

I'm sure there are a number of variations, but is this method of accessing prices from Yahoo! typical?

I don't know. On a mac (excel 2011), there is a function Data > Get External Data > Run Saved Query that lets you download stock info from MSN and it puts it right into a sheet. I actually don't use this and instead use yahoo's web api to get a csv file with all of my quotes.

 

[CoolChange]

I transitioned from Excel to Google Docs a few months ago. It was always a pain to get stock quotes into Excel, but with Google Docs, you just use the GoogleFinance function.

I also modeled my entire AA, in addition to keeping track of it in MoneyDance. I prefer the spreadsheet to modeling my portfolio, since it gives me more concise information than I could get out of a personal finance app.

Do manually update your Google Doc from actual holdings for modeling purposes; or, have you automated this?

I am also a MoneyDance user (very old version, but considering upgrading).

 

[sengsational]

I would appreciate comments and advice, perhaps with comparisons to other spreadsheets found on the web or your home-grown asset allocation spreadsheets.

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. So let's say I held 4 positions in my IRA, that would have 4 lines in the following snipit from my rebalancing spreadsheet:

So each line of the above is an account with a balance and I've keyed the allocations. Many of the allocations are 100% of one allocation category, but a balanced fund would be spread out (I use Morningstar to find those percentages).

The spreadsheet tells me "you're X high in this and Y low in that", easy enough, which is what most of the stock spreadsheets will calculate for you. But now that you know that, it's trial and error to work within the dotted lines to figure out the trades required to get back in balance. And then there's the considerations like you don't want your cash for spending in a tax advantaged account, and you're better off with X asset class in or out of a tax advantaged account, etc. It's quite the puzzle to get allocated! But at least recently I pulled the old 401k into the current 401k, so that's a few less accounts and one less grouping to work-in.

 

[tulak]

Do manually update your Google Doc from actual holdings for modeling purposes; or, have you automated this?

I am also a MoneyDance user (very old version, but considering upgrading).

Yes, I manually enter the number of shares for each asset and contributions to our portfolio. It's not too much work, only a couple of updates a month. This makes it a lot easier for determining when to rebalance and checking portfolio performance (using XIRR).

I use MoneyDance more for money management and tracking expenses. My portfolio is in MoneyDance, but I don't like how MoneyDance presents the portfolio in its reports (can't view it per asset), so I end up using a spreadsheet instead.

 

[target2019]

Google spreadsheet (all sheets in 1 doc) is limited to 500 googlefinance functions, I think.

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.

 

[Htown Harry]

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!

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]

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]

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/0B0p8BxaOOekUYWR2U3JwSEZPSTg/edit?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.