Cascading Asset Allocation Spreadsheet

Discuss all general (i.e. non-personal) investing questions and issues, investing news, and theory.
Post Reply
Topic Author
pradador
Posts: 156
Joined: Thu Jun 14, 2012 9:20 pm
Contact:

Cascading Asset Allocation Spreadsheet

Post by pradador » Wed Dec 05, 2012 2:49 pm

Hey guys, I've been cruising the Wiki these past few days learning as much as I can and have been putting together spreadsheets for myself to help with my investments. I thought I'd give back by sharing what I've created in case it is useful to anyone. Suggestions on how to improve the tools would be very welcome as well.

### Cascading Asset Allocation Worksheet ###

After reading Using a Spreadsheet to Maintain a Portfolio and the Cascading Asset Allocation Method, I put together a worksheet to calculate asset allocation by either starting from the top and moving down or starting from a sample detailed portfolio and working up to see what the breakdown is.

In the top down sheet, the most detailed percentages are calculated based on the input from left to right. In the bottom up sheet, the percentages are calculated right to left based on the input of the detailed percentages.

https://docs.google.com/spreadsheet/ccc ... nhwZHdqU3c

User avatar
NAVigator
Posts: 2457
Joined: Tue Feb 27, 2007 7:24 am
Location: Iowa

Re: Cascading Asset Allocation Spreadsheet

Post by NAVigator » Wed Dec 05, 2012 3:56 pm

I have been impressed with the cascading asset allocation format. You have done a nice implementation that I will now adapt to my portfolio. Thanks for posting this!

Jerry
"I was born with nothing and I have most of it left."

pascalwager
Posts: 1570
Joined: Mon Oct 31, 2011 8:36 pm

Re: Cascading Asset Allocation Spreadsheet

Post by pascalwager » Sun Dec 09, 2012 12:43 am

The colored blocks are a nice adaptation of the "Portfolio Tree".

I might just add this to my existing Google Drive spreadsheet; except, in my case, the percentages would be the actual calculated percentages for each asset class (equities/bonds, US/int'l, large cap, etc.) which already appear on my sheet.

User avatar
LadyGeek
Site Admin
Posts: 57879
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Cascading Asset Allocation Spreadsheet

Post by LadyGeek » Sun Jan 13, 2013 5:40 pm

Sorry for the late post, I just found this thread. This is an interesting way to work with asset allocation. It doesn't do a detailed portfolio analysis in terms of value, but it gets you to the percentages.

I made a few changes:
- Updated the cell borders to show the breakdown more clearly (at least to me). The gridlines are turned off.
- Added arrows at the bottom of each table (Top Down, Bottom Up) to indicate the direction. (Rant: Google spreadsheet doesn't handle spaces like Excel. I had to change the spaces to dashes. Not pretty. :annoyed)
- Added an error check. In cell L32 of each tab, an error is displayed if the percentages don't equal 100%.

On Google drive: Cascading Asset Allocation (view or download)

This spreadsheet is a transposed (vertical to horizontal) implementation of tfb's Cascading Asset Allocation Method:

Image

This is a proposed change to the OP to incorporate as you see fit. If you don't like what I did, feel free to reject. I think your spreadsheet would be a nice addition to the wiki: Using a Spreadsheet to Maintain a Portfolio

When you are ready, post here and I'll put your spreadsheet and a short description in the wiki. You are welcome to supply the description as well. (If not, I'll take a crack at it.)

(Spreadsheet updated 14-Jan-13)
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

Topic Author
pradador
Posts: 156
Joined: Thu Jun 14, 2012 9:20 pm
Contact:

Re: Cascading Asset Allocation Spreadsheet

Post by pradador » Sun Jan 13, 2013 9:44 pm

I like your additions LadyGeek. Removing the gridlines and adding borders makes it easier on the eyes.

One thing that I've still been wondering about is how to really define the target percentages for the Large/Mid/Small and Value/Blend/Growth segments once you have to pick out funds. I use Morningstar for guidance and it seems the differences in say an S&P small cap based fund vs an MSCI small cap one would mean your actual weights are different.

Specifically, let's say I want to put 80% of my U.S. stock allocation in a large cap blend fund, 15% of it in a mid cap blend fund, and 5% of it in a small cap blend fund. If I have S&P index funds available to me this might work out to 80% S&P 500, 15% S&P MidCap 400, and 5% SmallCap 600. Using IVV, IJH and IJR in Morningstar it produces the following style box.

Image

If instead I pick MSCI based funds, I would go with 80% MSCI Large Cap 300 (MGC), 15% MSCI Mid Cap 450 (VO), and 5% MSCI Small Cap (VB) which would look like this:

Image

The differences might not be enough to really make a big difference for most investors. However I do still wonder of the impact I'd have deciding on say Large Cap Value/Blend/Growth allocations down to the actual percentages. For example, if I want 23/24/23 large cap value/blend/growth then I might split my large cap percentages down to 33%/34%/33% instead of just 100% large cap blend. Then I'd pick the funds to use and try to match them to those percentages. If I wanted to use S&P funds I'd stick with the allocation described above, and if I only have MSCI funds available I'd use something like 70/20/10 instead.

Plugging these numbers into etfreplay.com suggests there is stronger correlation and predictability by using the adjusted MSCI weights and comparing those to the S&P weights.

User avatar
LadyGeek
Site Admin
Posts: 57879
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Cascading Asset Allocation Spreadsheet

Post by LadyGeek » Sun Jan 13, 2013 10:24 pm

OK, I forgot one of the more important visualization changes in my spreadsheet which relates to what you are asking: I removed all the decimal places, so you only resolve to the nearest 1%. Why? Because you only really need to track asset allocation to the nearest 5 %. Think about it.

I'm changing the numbers to prove my point, but I don't think I'm that far off:
- Portfolio rebalancing is done when the values move more than 5% away from the target.
- Indexes change their fund compositions when the underlying funds move more than 5% from a trigger band.

Now, to your question. You've got too many moving parts here and are over-analyzing. I think you're choosing funds on the basis of building a nice and clean total market composition, but can't decide on which index to use. Different indexes have:

- Different fund compositions
- Different methodologies on the criteria for index rebalancing (survivorship bias, etc.)

While you haven't indicated if you would mix between S&P and MSCI tracking, that's another option - but it would mess up your tracking. Why don't you pick the ones with the lowest cost and broadest diversification in the asset class of choice?

My take is that if you limit your allocations to the nearest 5%, a lot of the underlying churn will just disappear. So, there's no need to over-analyze anything and you can proceed.

Background info: Stock Market Indexing

As for your website analysis, that looks at historical performance (back testing). You are forming a pattern of past performance and extrapolating that to future performance. Of course you know this, but I want to put things in perspective.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

Topic Author
pradador
Posts: 156
Joined: Thu Jun 14, 2012 9:20 pm
Contact:

Re: Cascading Asset Allocation Spreadsheet

Post by pradador » Sun Jan 13, 2013 11:14 pm

LadyGeek wrote:Why don't you pick the ones with the lowest cost and broadest diversification in the asset class of choice?
LadyGeek wrote:My take is that if you limit your allocations to the nearest 5%, a lot of the underlying churn will just disappear. So, there's no need to over-analyze anything and you can proceed.
The 5% idea makes a lot of sense. It improves the signal-to-noise ratio quite a bit.

As for the index choice stuff, I think what I'm really trying to do in my head is understand the impact of fund choice and to make the portfolio fund agnostic. It makes more sense for me to pick a style composition and then match the fund choices to it based on cost and availability. This lets me get around the problem of having different funds available in my 401(k) and IRA accounts. In theory, I could expect similar performance from my 401(k)'s S&P based funds and say my Vanguard MSCI based funds assuming similar underlying composition.

Overall, I agree with you that some of this is over-analysis but it's interesting to use backtesting to put ideas to the test.

User avatar
LadyGeek
Site Admin
Posts: 57879
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Cascading Asset Allocation Spreadsheet

Post by LadyGeek » Mon Jan 14, 2013 5:47 pm

pradador wrote:It makes more sense for me to pick a style composition and then match the fund choices to it based on cost and availability. This lets me get around the problem of having different funds available in my 401(k) and IRA accounts. In theory, I could expect similar performance from my 401(k)'s S&P based funds and say my Vanguard MSCI based funds assuming similar underlying composition.

Overall, I agree with you that some of this is over-analysis but it's interesting to use backtesting to put ideas to the test.
So you're trying to match performance using different funds? I don't see the point, as you are compromising your overall return just to test a theory. First things first: You have a case of "analysis paralysis." Stop analyzing and get your portfolio going. Next...

I have a suspicion that you enjoy backtesting and would like to do more. Why don't you take this to the next level? Take a look at Multifactor investing - a comprehensive tutorial - finiki :) It's the ultimate slice-n-dice, as you do your own regression analysis. You'll need not only a spreadsheet, but R-Studio (free software) to do the factor analysis. If you want to go this route, take at most 5% of your portfolio (that you have just started..) and experiment.

If this looks appealing, be sure to read the references at the bottom of the article, especially: Collective thoughts by Robert T. This was a collaborative effort between the Bogleheads and Financial Webring Forums. ClosetIndexer (and myself) are members of both forums. The analysis process details are the wiki: Fama-French three-factor model analysis

To new investors: This is an advanced investing topic which intentionally deviates from the total market approach (the Bogleheads investment philosophy) and can safely be ignored.
================================================================

Let me know when you would like your spreadsheet put in the wiki (or, I can use mine and give credit to you. It doesn't matter to me.)
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

Topic Author
pradador
Posts: 156
Joined: Thu Jun 14, 2012 9:20 pm
Contact:

Re: Cascading Asset Allocation Spreadsheet

Post by pradador » Mon Jan 14, 2013 7:03 pm

LadyGeek wrote:I have a suspicion that you enjoy backtesting and would like to do more. Why don't you take this to the next level? Take a look at Multifactor investing - a comprehensive tutorial - finiki :) It's the ultimate slice-n-dice, as you do your own regression analysis. You'll need not only a spreadsheet, but R-Studio (free software) to do the factor analysis. If you want to go this route, take at most 5% of your portfolio (that you have just started..) and experiment.

If this looks appealing, be sure to read the references at the bottom of the article, especially: Collective thoughts by Robert T. This was a collaborative effort between the Bogleheads and Financial Webring Forums. ClosetIndexer (and myself) are members of both forums. The analysis process details are the wiki: Fama-French three-factor model analysis
Thanks for all the advice and for these links LadyGeek. I've definitely combed through Collective thoughts before which was a great read and source of valuable information.
LadyGeek wrote:Let me know when you would like your spreadsheet put in the wiki (or, I can use mine and give credit to you. It doesn't matter to me.)
You should go ahead and post the one with your edits. I like where it's at!

User avatar
serbeer
Posts: 1238
Joined: Fri Dec 28, 2007 2:09 pm

Re: Cascading Asset Allocation Spreadsheet

Post by serbeer » Mon Jan 14, 2013 7:21 pm

I really like the visualization and likely to adopt it for my Excell spreadsheet, as a separate tab showing cascade based on current AA caclculated by rebalancing engine.

One thing I am not clear about though is why both US and International REITs are placed under corresponding Small Cap umbrella??? They should be a totally separate category under Stocks category IMO...

Topic Author
pradador
Posts: 156
Joined: Thu Jun 14, 2012 9:20 pm
Contact:

Re: Cascading Asset Allocation Spreadsheet

Post by pradador » Mon Jan 14, 2013 7:54 pm

serbeer wrote:One thing I am not clear about though is why both US and International REITs are placed under corresponding Small Cap umbrella??? They should be a totally separate category under Stocks category IMO...
I agree. Originally, I was working with Schwab's portfolio allocation tool which placed REIT funds alongside small cap and thus placed it there. As I learn and understand more about asset classes, it makes more sense to break it off.

User avatar
LadyGeek
Site Admin
Posts: 57879
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Cascading Asset Allocation Spreadsheet

Post by LadyGeek » Mon Jan 14, 2013 10:37 pm

How's this look? Cascading Asset Allocation Give it a test run. If it's OK, I'll put it in the wiki.

REITs are now directly under stocks and I added International Bonds. For consistency, I realigned all the asset categories to have an extra blank row.

Numbers are intentionally displayed to the nearest percent, which will appear as a round-off error at first glance. The bottom right corner of the table (L32) will display "Problem!" if something is wrong - this is a great way to debug your entries.

Remember that the spreadsheet is a tool. I'm listing all the possibilities for ease of use, only enter what you want. It works just fine for a 3-fund portfolio.

(I didn't see the "Manage versions" anywhere, so I uploaded a new spreadsheet and fixed my previous post with the new link.)
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

User avatar
serbeer
Posts: 1238
Joined: Fri Dec 28, 2007 2:09 pm

Re: Cascading Asset Allocation Spreadsheet

Post by serbeer » Tue Jan 15, 2013 12:54 am

Looks great now, thank you. For my own spreadsheet, I'll have to add Mid Caps as well, since I keep track of it as separate class (while I am aware of most people on this board preference for mixing up Large and Small to get "implied" Midcaps).

Will make for a nice visualization tab in my spreadsheet, it is mostly tables otherwise...

LFKB
Posts: 590
Joined: Mon Dec 24, 2012 7:06 pm

Re: Cascading Asset Allocation Spreadsheet

Post by LFKB » Tue Jan 15, 2013 2:39 am

LadyGeek wrote:How's this look? Cascading Asset Allocation Give it a test run. If it's OK, I'll put it in the wiki.

REITs are now directly under stocks and I added International Bonds. For consistency, I realigned all the asset categories to have an extra blank row.

Numbers are intentionally displayed to the nearest percent, which will appear as a round-off error at first glance. The bottom right corner of the table (L32) will display "Problem!" if something is wrong - this is a great way to debug your entries.

Remember that the spreadsheet is a tool. I'm listing all the possibilities for ease of use, only enter what you want. It works just fine for a 3-fund portfolio.

(I didn't see the "Manage versions" anywhere, so I uploaded a new spreadsheet and fixed my previous post with the new link.)
Can you help me understand the point of ths spreadsheet thought? Are you going to buy a fund that corresponds with each % in the rightmost column and have a 9 or 10 fund portfolio? Or are you trying to buy less funds and get to a Morningstar box that aligns with the rightmost column?
Last edited by LFKB on Tue Jan 15, 2013 2:24 pm, edited 1 time in total.

User avatar
serbeer
Posts: 1238
Joined: Fri Dec 28, 2007 2:09 pm

Re: Cascading Asset Allocation Spreadsheet

Post by serbeer » Tue Jan 15, 2013 1:51 pm

The point is visualization of percent of a percent of a percent type of hierarchy. Eg, my spreadsheet calculates allocation based on most granular level and I can feed it into copy of reverse tab of this spreasheet to see what total stock/bonds or US/International stocks/REIT/Bonds it results in in visual format. So if someone asks me what AA I use, I can paste the whole image in response answering all AA questions at once.

delisim
Posts: 139
Joined: Wed Dec 05, 2007 10:24 am

Re: Cascading Asset Allocation Spreadsheet

Post by delisim » Tue Jan 15, 2013 4:14 pm

pradador wrote:One thing that I've still been wondering about is how to really define the target percentages for the Large/Mid/Small and Value/Blend/Growth segments once you have to pick out funds. I use Morningstar for guidance and it seems the differences in say an S&P small cap based fund vs an MSCI small cap one would mean your actual weights are different.

Specifically, let's say I want to put 80% of my U.S. stock allocation in a large cap blend fund, 15% of it in a mid cap blend fund, and 5% of it in a small cap blend fund. If I have S&P index funds available to me this might work out to 80% S&P 500, 15% S&P MidCap 400, and 5% SmallCap 600. Using IVV, IJH and IJR in Morningstar it produces the following style box.
I struggle with this. I'd love to use Vanguard's Voyager report to view my AA details since I already enter all my investments there, but it thinks that my SWTSX (Schwab Total Stock Market Index) is 100% large cap, which is simply not true.

This wiki page is helpful for figuring out the actual % of REITs you own, but its a pain to figure out the categories if you buy a fund of funds like a target retirement, or heaven forbid a fund without a ticker-symbol.

User avatar
LadyGeek
Site Admin
Posts: 57879
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Cascading Asset Allocation Spreadsheet

Post by LadyGeek » Tue Jan 15, 2013 9:39 pm

First, thanks to learning_head for finding an entry error in the spreadsheet (via PM). The percentages for nominal bonds didn't add to 100% and since I had no nominal bond funds, the error was missed. :oops: The result is a new row which checks for errors at every step.

I also made another change- short-term reserves has been renamed to Fixed income. This is a frequent topic of discussion; there is no consensus on the role of fixed income in a portfolio. It's dependent on your situation. If you want to include fixed income as a separate asset, that's fine. If not (enter as a bond), then set this value to 0.

The wiki is updated: Using a Spreadsheet to Maintain a Portfolio

There already was a spreadsheet (by Ducks) which used tfb's cascading asset allocation method, so I created a new section with a detailed explanation.

To the new investors: Unlike Multifactor investing, properly assigning asset allocation is something worthwhile to learn. Remember that the spreadsheet is a tool - you don't need to fill in all the entries. It works fine with a Three-fund portfolio or Lazy Portfolio.

Comments / questions / concerns are welcome.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

User avatar
Ducks
Posts: 515
Joined: Sun Apr 20, 2008 5:01 pm

Re: Cascading Asset Allocation Spreadsheet

Post by Ducks » Tue Jan 15, 2013 9:59 pm

Thanks for the props, LadyGeek. I made that spreadsheet a long time ago; I hope it helps somebody. Also, if anybody wants to save it as a Google doc somewhere, that'd be awesome.
Getting our Ducks in a row since 2008.

User avatar
LadyGeek
Site Admin
Posts: 57879
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Cascading Asset Allocation Spreadsheet

Post by LadyGeek » Tue Jan 15, 2013 10:11 pm

No problem. Actually, it would be better left as an Excel spreadsheet. Google Docs is not friendly when it comes to translating from different formats. :P

In case anyone is using your AssetLocation tab as a reference, it's been updated. See: Principles of Tax-Efficient Fund Placement
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

User avatar
Ducks
Posts: 515
Joined: Sun Apr 20, 2008 5:01 pm

Re: Cascading Asset Allocation Spreadsheet

Post by Ducks » Tue Jan 15, 2013 10:36 pm

Thanks. I'll remove the graphic in that page and put the link to the wiki instead. When I made the spreadsheet, I don't think that page of the wiki existed. :)
Getting our Ducks in a row since 2008.

User avatar
serbeer
Posts: 1238
Joined: Fri Dec 28, 2007 2:09 pm

Re: Cascading Asset Allocation Spreadsheet

Post by serbeer » Wed Jan 16, 2013 12:26 pm

Thank you LadyGeek. I adopted the latest revision for my spreasheets. You may consider making two more revisions:

1) Adding step-by-step error checking to Bottom-Up tab as well

2) The erorr-checking formula =IF(ROUND(SUM(I2:I41),3)<>1100%,"Problem!","")is not the best one. If one does not have one or more of the asset subclasses (for example, I do not have international bonds), then one or more of the 11 components is 0 and the whole is not 1100%.

A better check, the one I am using now, is to make sure that the total in this column is = N x 100% where N is integer number and that total is no more than 1100%: =IF(AND(INT(SUM(I2:I45)) = SUM(I2:I45),ROUND(SUM(I2:I45),3)<=1100%),"","Problem!")

User avatar
LadyGeek
Site Admin
Posts: 57879
Joined: Sat Dec 20, 2008 5:34 pm
Location: Philadelphia
Contact:

Re: Cascading Asset Allocation Spreadsheet

Post by LadyGeek » Wed Jan 16, 2013 9:27 pm

First, I updated the wiki to mention the Fixed Income asset category: Using a Spreadsheet to Maintain a Portfolio
serbeer wrote:1) Adding step-by-step error checking to Bottom-Up tab as well
I already had error checking in the Bottom-Up tab, but it was off to the right. I moved it in the same location as the Top-Down tab (under the 100%). This tab only has one column to check, the rest are formulas.
serbeer wrote:2) The erorr-checking formula =IF(ROUND(SUM(I2:I41),3)<>1100%,"Problem!","")is not the best one. If one does not have one or more of the asset subclasses (for example, I do not have international bonds), then one or more of the 11 components is 0 and the whole is not 1100%.

A better check, the one I am using now, is to make sure that the total in this column is = N x 100% where N is integer number and that total is no more than 1100%: =IF(AND(INT(SUM(I2:I45)) = SUM(I2:I45),ROUND(SUM(I2:I45),3)<=1100%),"","Problem!")
Your formula needs to point to an N in each column (new cell), which makes the spreadsheet a little more complicated to maintain. I also found a problem when using this in MS Excel: Try deleting one of the values in Column I. Not setting it to 0, but deleting the entry. No error is indicated.

I updated my formula to use N, but as a hard-coded number:

Code: Select all

=IF(ROUND(SUM(I2:I41),3)<>11*100%,"Problem!","")
In this manner, a user can see that the 11 means something. A comparison with the other formulas in that row should show how the modification is made.
Wiki To some, the glass is half full. To others, the glass is half empty. To an engineer, it's twice the size it needs to be.

Post Reply