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.

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:

asset allocation spreadsheet step 2

 

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.

asset allocation spreadsheet step 2

 

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.

Excel toolbar

 

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

Asset allocation spreadsheet step 3

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.

asset allocation spreadsheet step 4

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

asset allocation spreadsheet

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.

asset allocation spreadsheet step 5

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!