Join Early Retirement Today
Reply
 
Thread Tools Search this Thread Display Modes
Asset Allocation Speadsheet - suggestions, please
Old 08-17-2013, 10:32 AM   #1
Thinks s/he gets paid by the post
Htown Harry's Avatar
 
Join Date: May 2007
Posts: 1,516
Asset Allocation Speadsheet - suggestions, please

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.
__________________

__________________
No doubt a continuous prosperity, though spendthrift, is preferable to an economy thriftily moral, though lean. Nevertheless, that prosperity would seem more soundly shored if, by a saving grace, more of us had the grace to save.

Life Magazine editorial, 1956
Htown Harry is offline   Reply With Quote
Join the #1 Early Retirement and Financial Independence Forum Today - It's Totally Free!

Are you planning to be financially independent as early as possible so you can live life on your own terms? Discuss successful investing strategies, asset allocation models, tax strategies and other related topics in our online forum community. Our members range from young folks just starting their journey to financial independence, military retirees and even multimillionaires. No matter where you fit in you'll find that Early-Retirement.org is a great community to join. Best of all it's totally FREE!

You are currently viewing our boards as a guest so you have limited access to our community. Please take the time to register and you will gain a lot of great new features including; the ability to participate in discussions, network with our members, see fewer ads, upload photographs, create a retirement blog, send private messages and so much, much more!

Old 08-17-2013, 10:46 AM   #2
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
pb4uski's Avatar
 
Join Date: Nov 2010
Location: Vermont & Sarasota, FL
Posts: 16,422
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.
__________________

__________________
If something cannot endure laughter.... it cannot endure.
Patience is the art of concealing your impatience.
Slow and steady wins the race.
pb4uski is online now   Reply With Quote
Old 08-17-2013, 10:56 AM   #3
Thinks s/he gets paid by the post
 
Join Date: Jul 2005
Posts: 3,862
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.
__________________
Animorph is offline   Reply With Quote
Old 08-17-2013, 10:58 AM   #4
Thinks s/he gets paid by the post
photoguy's Avatar
 
Join Date: Jun 2010
Posts: 2,301
Quote:
Originally Posted by Htown Harry View Post
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).
__________________
photoguy is offline   Reply With Quote
Old 08-17-2013, 11:19 AM   #5
Thinks s/he gets paid by the post
Htown Harry's Avatar
 
Join Date: May 2007
Posts: 1,516
Quote:
Originally Posted by Animorph View Post
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.
Quote:
Originally Posted by photoguy View Post
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?

__________________
No doubt a continuous prosperity, though spendthrift, is preferable to an economy thriftily moral, though lean. Nevertheless, that prosperity would seem more soundly shored if, by a saving grace, more of us had the grace to save.

Life Magazine editorial, 1956
Htown Harry is offline   Reply With Quote
Old 08-17-2013, 12:45 PM   #6
Thinks s/he gets paid by the post
target2019's Avatar
 
Join Date: Dec 2008
Posts: 3,705
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.
__________________
target2019 is offline   Reply With Quote
Old 08-17-2013, 01:57 PM   #7
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Jun 2005
Posts: 8,616
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: Asset allocation tutorial?

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.
__________________
LOL! is offline   Reply With Quote
Old 08-17-2013, 02:42 PM   #8
Full time employment: Posting here.
 
Join Date: Aug 2007
Posts: 892
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.
__________________
tulak is offline   Reply With Quote
Old 08-17-2013, 04:46 PM   #9
Thinks s/he gets paid by the post
 
Join Date: Jul 2005
Posts: 3,862
Quote:
Originally Posted by Htown Harry View Post
I'm sure there are a number of variations, but is this method of accessing prices from Yahoo! typical?

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.
__________________
Animorph is offline   Reply With Quote
Old 08-17-2013, 04:52 PM   #10
Confused about dryer sheets
mdb's Avatar
 
Join Date: Aug 2013
Posts: 1
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!
__________________
mdb is offline   Reply With Quote
Old 08-17-2013, 06:23 PM   #11
Thinks s/he gets paid by the post
Htown Harry's Avatar
 
Join Date: May 2007
Posts: 1,516
Quote:
Originally Posted by mdb View Post
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.

Quote:
(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...
Quote:
Before going too far, make a list of what you want this spreadsheet to do.
I appreciate the input. Keep it coming.
__________________
No doubt a continuous prosperity, though spendthrift, is preferable to an economy thriftily moral, though lean. Nevertheless, that prosperity would seem more soundly shored if, by a saving grace, more of us had the grace to save.

Life Magazine editorial, 1956
Htown Harry is offline   Reply With Quote
Old 08-17-2013, 06:45 PM   #12
Give me a museum and I'll fill it. (Picasso)
Give me a forum ...
 
Join Date: Jun 2005
Posts: 8,616
Quote:
Originally Posted by Htown Harry View Post
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.
__________________
LOL! is offline   Reply With Quote
Old 08-17-2013, 07:38 PM   #13
Thinks s/he gets paid by the post
sengsational's Avatar
 
Join Date: Oct 2010
Posts: 3,832
Here's a video I made so you can keep from re-typing your portfolio with Morningstar instant x-ray:

__________________
sengsational is offline   Reply With Quote
Old 08-17-2013, 08:59 PM   #14
Thinks s/he gets paid by the post
 
Join Date: Jul 2005
Posts: 3,862
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.
__________________
Animorph is offline   Reply With Quote
Old 08-17-2013, 10:20 PM   #15
Thinks s/he gets paid by the post
gauss's Avatar
 
Join Date: Aug 2011
Posts: 1,708
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
__________________
gauss is offline   Reply With Quote
Old 08-18-2013, 11:57 AM   #16
Thinks s/he gets paid by the post
photoguy's Avatar
 
Join Date: Jun 2010
Posts: 2,301
Quote:
Originally Posted by Htown Harry View Post
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.
__________________
photoguy is offline   Reply With Quote
Old 08-18-2013, 01:14 PM   #17
Full time employment: Posting here.
 
Join Date: Apr 2006
Posts: 925
Quote:
Originally Posted by kiki View Post
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).
__________________
If there's one thing in my life that's missing; It's the time I spend alone
Sailing on the cool and bright clear waters; There's lots of those friendly people
Showin me ways to go; And I never want to lose your inspiration
CoolChange is offline   Reply With Quote
Old 08-18-2013, 02:15 PM   #18
Thinks s/he gets paid by the post
sengsational's Avatar
 
Join Date: Oct 2010
Posts: 3,832
Quote:
Originally Posted by Htown Harry View Post
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.
__________________
sengsational is offline   Reply With Quote
Old 08-18-2013, 02:56 PM   #19
Full time employment: Posting here.
 
Join Date: Aug 2007
Posts: 892
Quote:
Originally Posted by CoolChange View Post
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.
__________________
tulak is offline   Reply With Quote
Old 08-18-2013, 05:26 PM   #20
Thinks s/he gets paid by the post
target2019's Avatar
 
Join Date: Dec 2008
Posts: 3,705
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.
__________________

__________________
target2019 is offline   Reply With Quote
Reply


Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off


 

 
All times are GMT -6. The time now is 09:09 PM.
 
Powered by vBulletin® Version 3.8.8 Beta 1
Copyright ©2000 - 2017, vBulletin Solutions, Inc.