A critical task for a do it yourself investment manager is to implement one's asset allocation across their investing accounts. This is generally done to maximize the asset location (tax location) aspects of the portfolio. It's really just a spreadsheet problem and one of the reasons I've said if you can't use basic spreadsheet functions you really have no business managing your own money. Over the years, I've published multiple blog posts about implementing an asset allocation, including how I do my own.
- How To Do It (2012)
- How I Do Mine (2012)
- How I Do Mine (2016)
- Rules for Asset Allocation Implementation (2019)
- Six Principles of Asset Location (2019)
However, a lot of people are still struggling with this concept. You can learn more about it by purchasing our online Fire Your Financial Advisor course or by hiring a good financial advisor at a fair price to help you set up your initial investing plan. I use an asset allocation spreadsheet and I'll explain how to do this, with a step-by-step guide using my own portfolio. It will also serve as an update to those 2012 and 2016 posts, since my implementation looks very different now than it did back then due to having a much larger taxable account.
How to Build an Asset Allocation Spreadsheet
Step # 1 Open a Spreadsheet
You can do this using the ubiquitous Microsoft Excel or the free Google Sheets. There are other spreadsheet programs out there (Apple Numbers) and you can even build one by hand if you really want to. But most people use one of the two options above. I prefer Excel just because I'm used to it. If I didn't already have the old downloadable version of Microsoft Office, I'd probably just use Sheets. It works fine for these purposes. But I'm going to use Excel for the demonstration.
Step # 2 List Your Desired Asset Allocation
Obviously, you cannot implement your desired asset allocation until you actually have an asset allocation. If you need help with that, consider these posts:
Again, if you find that task overwhelming, consider taking Fire Your Financial Advisor or hiring a recommended pro (or both).
In my case, my asset allocation looks like this:
- Stocks (60%)
- US Stocks (25%)
- Small Value Stocks (15%)
- International Stocks (15%)
- Small International Stocks (5%)
- Bonds (20%)
- Nominal Bonds (10%)
- Inflation-indexed Bonds (10%)
- Real Estate (20%)
- Publicly traded REITs (5%)
- Private Equity (10%)
- Private Debt (5%)
This is a 60/20/20 portfolio, with 1/3 of my stocks being international. I know I tolerate it well since I have now invested through 4 bear markets with it (2008, 2011, 2018, 2020). I know it takes enough risk to meet my primary financial goals since I've already met them.
So let's put that into our asset allocation spreadsheet, just for easy reference. So far the spreadsheet looks like this:
Step # 3 List Accounts
Next, you need to list all of your assets by account type. So you look up all of your accounts and write down how much money is in them.
I have included our six retirement accounts and our taxable account. Some might include their HSA as well, particularly if they do not plan to spend it any time soon. We don't, nor do we include my Cash Balance Plan. In both cases, the amounts in there are relatively trivial in comparison to our overall balance so it really would not affect much. In the total boxes, you can use a “SUM” equation to get your totals.
For the dollars column, it would look like this: =SUM(B14:B20)
For the percentage column, it would look like this: =SUM(C14:C20)
For each box in the percentage column, use this formula: =B14/$B$21
The $ signs in that formula keep the B21 factor from changing as you copy and paste the formula from C14 into C15-20.
For additional style points, select the dollars column and hit the “$” sign and select the percentage column and hit the “%” sign. You can add as many decimal places as you like. You can also center any columns you like by hitting the appropriate button.
# 4 Look Up Your Information
Then you have to go do some work. Log into all of your accounts and get the account totals. Your asset allocation spreadsheet should now look like this.
Note that while the ratios are actually correct, I used a factor to adjust the dollar amounts to keep them more relatable to my typical reader and hidden from the typical kidnapper.
# 5 Place Asset Classes Into Accounts
The next step may be the trickiest. You want to place assets into the account according to availability and also in accordance with the principles of asset location. In my case, at the current time, this is fairly easy due to the relative size of our taxable account in that almost everything goes into taxable. The easiest way to do this on the asset allocation spreadsheet is to add additional columns and rows as you go using the insert function.
The formatting is relatively easy and similar to what was done previously.
E36 =SUM(E14:E35)
F36 =SUM(F14:F35)
B36 and C36 adjust automatically as you insert rows and columns.
F15 =E15/$E$36
You can copy and paste that to the rest of the F column.
The E column is really where the work is at when building an asset allocation spreadsheet. For something like E18, that column just equals what is in the account (B17). For something like E30, it is equal to 25% * B36. For something like E33 it might be B36*10%-E20-E26. For something like E23 it might be B36*15%-E16-E18-E25. But you get the picture. You'll have to adjust every cell in this column individually. You will notice that the percentages in each asset class in the entire portfolio are now equal to the desired asset allocation. But this is dramatically simpler than if I tried to recreate my asset allocation in every account as discussed in this post. The more complex your asset allocation, the more complicated it gets to try to recreate it in every account in the portfolio.
So now you know the HOW to do this step. You might still be a little confused about the WHY I chose to place those particular assets classes into those particular accounts. So I'll make a few brief comments about each asset class.
US Stocks
This is a very tax-efficient asset class and one of the first to consider moving to taxable if you must invest in a taxable account.
International Stocks
Again, this is a very tax-efficient asset class and one of the first to consider moving to taxable. Is it more tax-efficient than US stocks? It's hard to say. The yield is higher (which makes it less tax-efficient) but you also get the foreign tax credit (which makes it more tax-efficient) to help. It honestly doesn't matter much which one goes to taxable first, they're both very tax-efficient. In my case, my taxable account is bigger than my allocation to both of these asset classes so I can just stick both of them in there.
Small International Stocks
While not as tax-efficient as the total market funds, this is still a pretty tax-efficient asset class so long as you are using an index fund or ETF to invest in it. You do get the foreign tax credit. It seemed wiser to move this to taxable before small value stocks.
Equity Real Estate
Given my limited tax-protected space, I see no reason whatsoever to try to put private equity real estate into an IRA or 401(k). Most of my tax-protected accounts wouldn't allow it anyway. The tax-efficiency of equity real estate due to depreciation sheltering the income makes it tax-efficient enough to have in taxable in my situation and in that of most real estate investors.
Inflation-Indexed Bonds
We're talking about Treasury Inflation Protected Securities (TIPS) here. Although the income from TIPS, like that of other treasury bonds, is not subject to state income taxes, they're still a pretty tax-inefficient asset class for someone like me in the top tax bracket. Plus, there is the phantom tax issue, where you could be taxed on income you didn't actually receive. This is just a great asset class to have in a tax-protected account to avoid all that hassle. So mine is in a 401(k) where I buy the Schwab TIPS ETF (SCHP). Lots of options there, but this one seems reasonable given the 401(k) is at Schwab and there are no transaction fees on this option.
REITs
Despite the new 199A deduction, REITS are still not very tax-efficient and are best placed into a tax-protected account. I have had them in my Roth IRA for over a decade.
Debt Real Estate
This is NOT a tax-efficient asset class. In fact, it may be my least tax-efficient asset class. Essentially all of the return is taxable every year at my very high ordinary income tax rates. I have long considered trying to get this asset class into a self-directed IRA or 401(k). I may still end up doing it eventually. Unfortunately, the WCI 401(k), which used to have a self-directed feature when it was an individual 401(k), no longer does now that we have employees. I just didn't want to take that fiduciary risk nor pay the fees necessary to have that feature in it. So that leaves just our IRAs. We would have to roll one or more away from Vanguard into a self-directed IRA. But that would only be a temporary solution. Since we're only able to put $6K a piece into our Roth IRAs every year, and we're adding a lot more than that to our taxable account each year, our Roth IRAs will soon be less than 5% of our portfolio. I suppose we could do some Roth conversions to boost that at some point, but for now we're just keeping it simple and leaving this in taxable. It is definitely the least ideal situation in our implementation and hopefully you won't have an issue like this when implementing yours.
Nominal Bonds
My nominal bond investment of choice is the TSP G Fund. For better or for worse, my entire TSP is less than the 10% portfolio allocation to nominal bonds. So nominal bonds also had to go somewhere else. I've chosen to just buy a Vanguard muni bond fund in taxable to take care of this. Now as you look at the spreadsheet above, you'll see a little bit of nominal bonds in my WCI 401(k). That's a lie to make the spreadsheet percentages come out perfectly. They're actually in taxable where I'm a little below my desired allocation in equity real estate. But we can talk about rebalancing issues in another post.
Small Value Stocks
These are all over the place in the portfolio. If you have a moderately complex asset allocation, you should also have one asset class that is all over the portfolio. As the account sizes change in relation to one another, this is the asset class that facilitates the rebalancing. Need more REITs? Sell small value in Jim's Roth and buy it in taxable. Need more TIPS? Sell small value in Jim's Doctor 401(k) and buy it in taxable. Run out of space for REITs in Jim's Roth IRA? Start exchanging small value stocks for REITs in Katie's Roth IRA and buying small value stocks in taxable. Small value stocks are not the most tax-efficient asset class in the portfolio, but I'd rather have them in taxable than REITs or TIPS.
Step # 6 Choose Investments
Now comes the easy and sometimes even the fun part. You simply choose investments in each account within the desired asset class for that account. Let's go through each of our accounts.
Jim's Roth IRA
The account is at Vanguard, so the Vanguard REIT Index Fund and the Vanguard Small Value Index Fund are no-brainers. I use the traditional mutual funds for ease of use.
Katie's Roth IRA
The account is at Vanguard, so the Vanguard Small Value Index Fund is a no-brainer.
Jim's TSP
It's all in the G Fund, my preferred nominal bond investment due to the “free lunch“, where you get treasury bond yields with money market risk.
Jim's Doctor 401(k)
This is almost all TIPS, in the Schwab TIPS ETF (SCHP). The small value stocks that are still there (but rapidly leaving) are in the Vanguard Small Value Index Fund. I use the ETF version here because it has lower transaction costs at Schwab than the Vanguard traditional mutual fund.
Jim's WCI 401(k)
This account at Fidelity is now all in the Vanguard Small Index Fund ETF due to lower transaction costs than the traditional mutual fund.
Katie's WCI 401(k)
Same as above.
Taxable Account
Our main taxable account is at Vanguard, so we tend to use Vanguard Funds or ETFs where possible. Our main holdings here include:
- Vanguard Total Stock Market Index ETF
- Vanguard Total International Stock Market Index ETF
- Vanguard FTSE All-World Ex-US Small Cap ETF
- Vanguard Intermediate Tax-Exempt Bond Fund
We also have a few oddball holdings there which represent tax-loss harvesting partners of the above funds that now have relatively low basis. These are flushed out of the account periodically as our charitable contributions (via our Vanguard Charitable DAF) to help simplify the portfolio. Obviously, the private real estate holdings are not held in the Vanguard brokerage account, but individually with each provider. That topic is complex enough that it deserves its own post which I have done in the past once or twice a year. But my preferred holdings are private funds. The upside is minimal hassle and broad diversification. The downsides include lack of control, high minimum investments, and lots of out-of-state tax returns. Ideally I'll have about three debt funds and three to ten equity funds. Three of each would be plenty for my taste, but due to the nature of equity funds (all invested up front, no ability to make additional contributions, and a long holding period) it is unlikely that I would ever be able to own just three, at least while I am still making sizable contributions to my portfolio.
This asset allocation spreadsheet can be modified to also function as your rebalancing spreadsheet, but that's a topic for another day. If you would like a copy of this spreadsheet to assist you in developing your own, download it here. If this all seems really complicated to you, you can simplify it a bit by using a less complex asset allocation. If you are only using three asset classes instead of nine, it's definitely an easier process. But if even that is overwhelming, do yourself a favor and hire someone to do this for you. Yes, it'll cost you a few thousand a year, but that's far better than not doing it right at all.
What do you think? What is your asset allocation and how do you implement it across your accounts? Comment below!
Awesome walkthrough!
I will be adjusting my spreadsheet based on a lot of the tips in here!
What happened to “Bonds go in Taxable?” I see you have some muni bonds in your taxable account, but with current interest rates so low, wouldn’t placing the highest growth assets into tax protected space give you the greatest efficiency?
What do you mean what happened to it? Did you never read beyond the headline of that post? Or any of the follow-up posts? Here’s the most recent one I’ve done on asset location:
https://www.whitecoatinvestor.com/asset-location/
At current low interest rates, bonds are likely fine in taxable (especially muni bonds for those in high brackets), but it varies on a lot of factors that are specific to you.
Devils advocate: Why the need to be so precise when the outcome of any given portfolio or allocation adjustment is almost as likely to move in one direction or another? If my target is, say, 70 percent stock 30 percent bonds, but I stay at 73% stocks for a while, does it really matter? I take on a BIT of extra risk, I likely get a BIT of extra return, maybe not. Is it really so critical?
No, your criticism is completely reasonable. A 70/30 portfolio doesn’t perform much differently from a 73/27 portfolio anyway.
My vanguard personal advisor services only re-balances when the allocation exceeds 5% plus or minus. Speaking of which, I should probably quit the service – I can do it myself now.
If you had a large amount of savings currently in a money market account, being that the stock market is up right now, would you wait to invest it in diversified low cost index funds?
Thank you so much.
The time to invest is when you have the money, and according to your written plan.
dude get it in there! time in the market better than timing the market. If at all time high valuations, market still has more chance of going up then down. Look at the last decade before the coronabear.
If it makes you feel better, dollar cost average, but studies have shown this still not as good on overall return as just putting it in ASAP.
I did that that last week as I was looking at purchasing a business. That deal didn’t go through, so I simply moved that money into my asset allocation. So no, I wouldn’t wait.
Thank you so much. Ok, so I did invest the money in the days following this chat in several low cost index funds, and now I’m down appox 5-10k. Ugh.
It feels like that happens to me about 1/3 of the time, which makes sense since the market goes up about 2 out of 3 years. Remember you’re not investing for just a few days. What do you think the odds are that you’ll be underwater in 30 years?
Yes, true, thank you. It’s so hard to watch it go down like that!
Hoping to retire in 10 years. 🙂
Stop watching! Look at it again in a few months if you really want.
Jim this is awesome. Thanks for diving into the weeds! Given the complexity of your portfolio though, do you think it even matters to have 5% in small international? Maybe just get rid of it and boost the total international to 20% for simplicity? Is there really strong evidence that 5% tilt in this asset class, especially when you might have some exposure in the total international fund, really moves the needle? also, is the small factor expected to have the same premium even when it’s international? Isn’t there a little more risk in small international given some of those companies might be in emerging markets, and is there appropriate increase in expected return for that additional risk?
It’s a fair criticism. Bear in mind I only have four stock funds in this portfolio- TSM, TISM, SV, and SI. Not exactly crazy complexity. The complexity in my portfolio comes more from the number of accounts than the number of investments.
But it’s like taxes, I’d rather have the money and pay more taxes than not have the money. I’d rather have all the accounts than not have the accounts, even if I have to deal with some complexity.
4 stock asset classes, 2-3 bond asset classes, 2-3 real estate asset classes. 8-10 total.
Nice idea and you did a lot of admiral work. Kudos to you for that!
However, nowadays the simple solution is to buy an index target retirement fund from vanguard and let them do the heavy lifting!
The costs are so low nowadays AND they take care of the rebalancing which is a nightmare if you have many accounts.
When looking at real estate as a portion of an asset allocation, how would you list that if investing outside of funds ie. residential or commercial as the property owner since these are often purchased with leverage? Your current equity in a property?
I would put it in the real estate allocation. I guess the equity is a reasonable way to do it, although you obviously own the whole thing and the mortgage is more like a negative bond than something decreasing your ownership. But since the equity is how you would treat a REIT index fund, syndication, or private fund, you probably ought to use that for your properties too.
Excellent post. I might have the opportunity to invest in two surgery centers. Assuming I go through with the investment, how would you suggest I categorize these shares? My AA is 60/20/20 (stocks/bonds/real estate). My instinct is to keep them separate from my AA; however, the total investment would be about 12% of the portfolio value, if included.
Thanks for all you do!
I’d keep it separate.
Great post Jim. I learned a couple tips on on Excel. I was encouraged to see my allocations and even my spreadsheet are very similar to yours. Must’ve learned something reading the blog over the last few years. The only surprise to me was small value and nominal bonds. Is there a reason you don’t put small value in taxable over bonds? My default is to put all my bonds in tax protected until I hit my allocation before doing taxable with bonds. If I had yours I would’ve put small value in taxable and nominal bonds (or Vanguard total Bond) in my retirement. Small point since overall was pretty close. Thanks.
Small value is my next asset class going into taxable as my taxable:tax protected ratio continues to grow. About 1/3 of my small value is in taxable now and I bet most of it will be in there by the end of the year. Happy? 🙂 Almost all of my stocks, all of my real estate except the Vanguard REIT index and about 1/3 of my bonds are now in taxable. First world problem for sure. The big question is what will go into taxable first when all that is left in tax protected is G fund, TIPS, and REITs? G fund can’t go there. TIPS have the phantom income issue. REITs are notoriously tax inefficient. Maybe I’ll buy TIPS at Treasury Direct or perhaps drop the REITs in favor of some more private real estate. We’ll see. But that moment may only be a year or two away for me.
What would everyone recommend putting in an HSA?
I guess you would treat it like a Roth IRA?
If you’re not planning to spend it for years, you can just add it in to your retirement allocation. Otherwise, perhaps a separate allocation. If you’re spending from it as you go, you probably ought to keep some in cash or cash-like investments. Perhaps enough to cover 1 year of max out of pocket.
I have been using the Personal Capital app to track my asset allocation, yearly return, and prediction for retirement using their Monte Carlo simulation. For this reason I have not kept a spread sheet. I don’t have any investment holdings that the app would not be able to incorporate. The asset allocation breakdown on the app is so specific that I wonder if I need to use the spread sheet approach. Amy I missing something? In fact, using the app, I think I am getting a more accurate picture of my asset allocation. For example, I may think I have only 5% in real estate through the Vanguard VNQ REIT fund (VNQ). But in reality I have more because a small part of a my Vanguard Small Cap index fund (VB) and Fidelity Total Market (FSKAX) actually invest in real estate according to the app. This actually adds a couple of percentage points of allocation.
Thank you.
Glad you’ve found something that works for you.
Great post Jim. Thanks for all that you do.
Your comments re: debt real estate hit home with me. I added equity real estate to my portfolio last year (via syndication). I would also like to get involved in debt real estate. However, I’m a W2 employee in the highest tax bracket with 5% state tax. My only options for investing in this asset class are to invest in a taxable account or via an SDIRA which I’d have to open via roll over from my current employer’s 457b. I’ve avoided investing via a taxable account because I’m not sure the tax issues, added portfolio complexity, and added state tax return costs and hassle are worth it. I’ve avoided investing via a SDIRA as I assume it would create pro rata issues with my backdoor ROTH IRA. I suppose I could stop doing my backdoor ROTH as it accounts for less than 1% of our portfolio.
You could have a SD Roth IRA by doing a big conversion. Or just invest in it in taxable. Or just skip it. It’s optional.
I realize, this is lazy but any chance someone has used google sheets, set it up like the excel downloadable spreadsheet in this post and is willing to share the template? no longer have excel access at work or at home. thanks!
Thank you so much for sharing this spreadsheet. I’ve been saving like crazy and following all of your advice for a few years and actually forming a plan and getting on top of asset allocation has always been a “I’ll get to that part” issue… this is very helpful!
Our pleasure.