Google Sheets is my primary tool for managing a large index fund portfolio spanning multiple accounts at several institutions.
An early version of my investment spreadsheet is available for feedback as a free template. This is the first post in a series about the spreadsheet and its development.
The Spreadsheet
My investment philosophy is simple, but it requires a global view of my portfolio spanning all accounts.
I use the spreadsheet to answer several important questions:
- What funds comprise my entire portfolio spanning all of my accounts? In a simple format, what funds are in my IRA, my spouse’s 401(k), our HSA, a taxable account, etc.?
- What is my actual asset allocation spanning all of my accounts?1
- How does my actual asset allocation compare to my target (desired) allocation?
And how do I fix things if actual diverges too much from target?
The spreadsheet does not need much information. For Alice and Bob’s example portfolio in this post, I have entered:
- Account names and types.
- Fund symbols and the asset class(es) they represent.
- Holdings/transactions: a fund and the number of shares owned per account (e.g., Roth IRA, 936 shares of VTI2).
With this data filled in, the next tab called Account Holdings automatically generates a report.
The spreadsheet uses the GOOGLEFINANCE function to dynamically recalculate current values as share prices change over time:
I use this report to cross-check against my online statements. If any value is too different, I probably missed entering a transaction into the spreadsheet.
I also print this report to include with my estate plan. It might aid my spouse or executor when they need to inventory accounts when I am incapacitated or die.
Asset Allocation
The next tab in the spreadsheet determines target asset allocation. Asset allocation is one of the most important decisions an investor makes.
In this example, Alice and Bob have used formulas to show the thinking behind the ultimately calculated percentages:
Their example asset allocation:
- Reserves $100,000 as a cash buffer off the top.
- Invests the rest in 65% stocks (and 35% in bonds by subtraction).
- Invests 60% of the stock allocation in US stocks (and 40% in non-US stocks by subtraction).
- Invests 30% of the bond allocation in TIPS3 (and 70% in nominal4/nom. bonds by subtraction).
In cell B9-C9, $100,000 is automatically calculated as 4.57% of their roughly $2.2 million portfolio. The allocations to all other asset classes are also automatically adjusted downward slightly to account for the cash buffer.
A cash buffer is optional, but I find it important to support in a simple way.
The variable names and values on the right are both editable. In other experiments, I have replaced them with an entirely different set as a way to explore different portfolio constructions.
Rebalance Matrix
With the target asset allocation in place, the next tab called Rebalance Matrix generates a report.
This report places accounts vertically on the left and asset classes horizontally across the top.
Each cell in the inner matrix pairs a fund with account and asset class.
In cell B12, $257,250 of VTI (a US stock fund) is owned in the Roth IRA (Alice) account and is in the US Stocks asset class. It is the only fund owned in that account.
In cell C14, $310,500 of VXUS (an international stock fund) is owned in the Roth IRA (Bob) account and is in the Int’l Stocks asset class. Similarly, it is the only fund owned in its account.
Different funds in the same asset class are “stacked” vertically. Cells B20-B23 show 65% of VT and 100% of VTI allocated to US Stocks within the Brokerage (Joint) account. 35% of VT, its non-US allocation, is shown in cell C20 under the Int’l Stocks asset class. In total, the Brokerage (Joint) account owns VT, VTI, SCHP and VMFXX.
Looking back toward the top in rows 4-10, Target (% and $) and Actual show target and actual asset class allocations in percent and dollars, summed by column, representing the allocations summed across all accounts.
If target and actual deviate too far, the Difference cells will automatically become more red, indicating that money should ideally start moving from well-performing asset classes to relatively underperforming ones (“sell high, buy low!”).
Alice and Bob probably do not need to rebalance in this example. They are within 1 percentage point of their desired asset allocation in every asset class (row 7). The largest deviation is shown in cell D7 and D10 but is small compared to their portfolio.
A Different Scenario
If I were Alice and Bob, I would open the spreadsheet every few months to add new transactions and check for growing deviations in the Rebalance Matrix. Close monitoring would not be required.
But what if Alice and Bob want to experiment with raising their stock allocation to 75% (from 65%)? In the Asset Allocation sheet:
The Rebalance Matrix sheet automatically regenerates5:
The story here is much more, er, red.
As expected, Alice and Bob would need to start moving money out of Nom. Bonds and TIPS and into US Stocks and Int’l Stocks.
For example, US stocks are now 5.24 percentage points (cell B7) from target. $114,579 (cell B10) needs to flow from other asset classes into US Stocks to rebalance exactly.
Rebalancing can be a complicated exercise involving personalized circumstances and tax considerations. The spreadsheet lets me quickly experiment with several ideas before executing any trades.
For this example, I will show one possible transaction in what might need to be multiple to fully complete the rebalance:
These rows represent an exchange Alice could consider to rebalance from Nom. Bonds and into US Stocks in her Example Corp 401(k):
- A sale (negative number) of 11,068.335 shares of FXNAX, a fund in the Nom. Bonds category that is currently overweight by $133,486 (cell D10).
- Using the sale proceeds, a purchase (positive number) of $115,000 of FSKAX, a US stock fund. $115,000 is a rounded off version of the $114,579 inflow (cell B10) needed to reach the target allocation of US Stocks.
I used =115000 / GOOGLEFINANCE("SYMBOL") to get the rough number of shares for $115,000 of each fund at today’s prices. For the sale, I used -115000, a negative number.
If Alice actually executed this transaction, she would later change the values to the exact share counts when the trade settled.
Adding these two transactions causes the Rebalance Matrix to automatically regenerate:
In cells B16-D17, the exchange is shown as $115,000 of newly purchased FSKAX (the stock fund) while FXNAX (the bond fund) shows a balance reduced by $115,000 compared to the prior screenshot.
Total balances have not changed because money is only moving from the FXNAX fund to FSKAX, netting to zero in total.
The Difference columns for US Stocks and Nom. Bonds (cells B10 and D10) are noticeably more green.
Although there is more to do with Int’l Stocks and TIPS, this exchange would help get the portfolio closer to alignment with its new target. If it hadn’t or if had I spotted a better sequence of transactions, I could quickly delete the two mock transactions and try something else.
Summary
Google Sheets helps me be a better long-term investor. To me that means:
- Creating an simple investment plan based on sensible principles.
- Consistently following the plan and rebalancing occasionally, regardless of the current news or politics.
- Ignoring noise from investors playing a different (short-term or irrational) game.
The spreadsheet is my attempt to give myself actionable information for my style of investing, without the noise that most commercial tools spam at me when I login.
An early version of the spreadsheet is available as a Google Sheets template here: Investment Spreadsheet v0.1 (Google Sheets template).
I welcome your feedback. Please reach out with your thoughts: andy@dollar42.com
Footnotes
For example, what percentage is invested in US stocks relative to other asset classes across all of my accounts?↩︎
VTI and other funds are used as examples only. Any fund supported by GOOGLEFINANCE can be used. Or worst case, the share price can be provided manually for custom funds or assets.↩︎
Treasury Inflation Protected Securities: a type of government bond that has some inflation protection features↩︎
Nominal bonds are not automatically adjusted for inflation. For this example portfolio, nominal bonds mean US bonds excluding TIPS.↩︎
Sometimes it does not automatically regenerate, but a value in the orange cell B1 can be changed to force it to recalculate↩︎