This entry is a little different from my others in that I’ll be laying out a step-by-step guide on how I actually rebalance my portfolio every quarter. For some of you, this may be pretty mundane – something you already know how to do – but perhaps for others, this could help build your confidence that you don’t need a financial advisor or a trading background to rebalance your portfolio. I will do my best not to get too technical, and I’ll use some simple examples to illustrate the math used.
To rebalance my portfolio, I use two online applications:
Pre-Work: Set up Google Sheets
I use only two tabs for my portfolio management: one to determine my buy/sell decisions (named ‘Portfolio’) and another to track my sales and offset capital gains with losses (named ‘Sales’).
Let’s set up the first tab ‘Portfolio’. This tab simply compares your target portfolio mix with your actual mix – in order to determine what buy/sell decisions to make. Read this post to learn more about setting a portfolio mix. For the purpose of this post, let’s say (hypothetically) you have a desired allocation mix of 40% domestic stocks, 30% bonds, 30% international. And that the index funds you chose are VOO, VEA, and VGLT, respectively. On Google Sheets, create a simple table that looks like the following:
| Balance | Percent | Target % | Buy (Sell) | |
| Cash | 0% | |||
| VOO | 40% | |||
| VEA | 30% | |||
| VGLT | 30% | |||
| Total | 100% |
Go ahead and input the Target % as shown above.
Let’s now set up the second tab ‘Sales’. This also has a simple table tracking all of your sales for the current tax year (since capital gains/losses are measured by tax year). For now, just set up the table like the following:
| Date | Win/Lose | Ticker | Cost Basis | Current Price | Shares | Cash | Gain/Loss |
That’s it for set up. Now let’s use these two tables while we simulate a hypothetical quarterly rebalancing for your portfolio.
Step 1: Get current market values of your portfolio
Log onto Fidelity. Click on your trading portfolio (for me, it’s tabled ‘Individual’) and navigate to the ‘Positions’ tab. This should show your current portfolio balances. All you need to do here is just (manually) type in your portfolio balances into your Google spreadsheet (‘Portfolio’ tab; first table) just as you see it in Fidelity. Let’s say hypothetically you see and type in your balances like the following:
| Balance | Percent | Target % | Buy (Sell) | |
| Cash | $5,899 | 0% | ||
| VOO | $287,352 | 40% | ||
| VEA | $176,199 | 30% | ||
| VGLT | $164,771 | 30% | ||
| Total | 100% |
Now calculate the Total for the Balance column using the =sum() function. Then calculate the Percent column by taking each row’s balance and dividing by the Total (e.g., Cash Percent = $5,899 / $644,221). This should generate a table that looks like the following:
| Balance | Percent | Target % | Buy (Sell) | |
| Cash | $5,899 | 2.47% | 0% | |
| VOO | $287,352 | 44.60% | 40% | |
| VEA | $176,199 | 27.35% | 30% | |
| VGLT | $164,771 | 25.58% | 30% | |
| Total | $644,221 | 100.00% | 100% |
That’s wraps up Step 1. Pretty simple so far, right?
Step 2: Calculate buy and sell amounts
Next, we need to do some basic arithmetic to figure out what buy and sell decisions to make. For the Buy (Sell) column of the ‘Portfolio’ table, you use a formula that subtracts Target % with Percent, and then multiples this difference with the Total Balance. For example, for VOO, you would calculate = (40% – 44.6%) * $644,221 = $(29,664). Do this for all the rows, and you should have a table that looks like the following:
| Balance | Percent | Target % | Buy (Sell) | |
| Cash | $5,899 | 2.47% | 0% | $ (15,899) |
| VOO | $287,352 | 44.60% | 40% | $ (29,664) |
| VEA | $176,199 | 27.35% | 30% | $ 17,607 |
| VGLT | $164,771 | 25.58% | 30% | $ 28,495 |
| Total | $644,221 | 100.00% | 100% |
Interpreting this table shows that since you last rebalanced your portfolio, the value of VOO has risen relative to VEA and VGLT. Plus, your cash balance has risen, potentially from dividends received from your funds or that you deposited some cash into your portfolio. Now, in order to rebalance, you need to sell VOO. And then use the proceeds along with your cash to purchase VEA and VGLT. This brings us to the next step…
Step 3: Sell specific lots to offset gains/losses
This step is important to manage your tax exposure. If you make sell decisions that continually generate a capital gain, then you might have to pay a tax bill the following year, especially if they are short-term capital gains. I personally only sell funds that are long-term, i.e., held over a year, to reduce my tax exposure. And as stated in this post, I try my best within a tax year to sell funds that generate losses to offset any gains received to-date.
Continuing with our hypothetical example, we know we need to sell $29,664 of VOO. But let’s try to choose a specific ‘lot’ (what Fidelity calls a specific purchase you made in the past) to make sure you are offsetting gains with losses. Let’s go back to Fidelity. Since you need to sell VOO, click on VOO and select the ‘Sell’ button. You’ll notice from the resulting pop-up window the current price (which we can assume to be $459.05); you can type that into the second table in the ‘Sales’ tab, along with today’s date and the VOO ticker symbol. Under the Sell drop-down menu in Fidelity (same pop-up window), you’ll notice an option that says ‘Sell Specific.’ Clicking on that shows you another pop-up window. Let’s say hypothetically you see a table that looks like the following:
| Quantity | Unrealized G/L | Holding Period | Date Acquired | Cost Basis / Share |
| 110 | $5,025.90 | Short | 7/10/23 | $413.36 |
| 398 | $15,159.82 | Long | 3/31/22 | $420.96 |
This shows that you purchased VOO two times in the past. The obvious lot to sell is the second one listed, because it’s classified as “Long” which qualifies as a long-term capital gain if you sold. You need to sell enough of this lot to generate $29,664 of cash (to purchase the other funds). This means you need to sell = $29,664 / $459.05 = about 65 shares (rounding up to get enough cash). If you sold 65 shares of the “Long” lot with a cost basis per share of $420.96, then you would generate an estimated gain of = 65 * ($459.05 – $420.96) = $2,475.85. This gives you all the information you need to complete the ‘Sales’ tab table in your Google spreadsheet, like the following:
| Date | Win/Lose | Ticker | Cost Basis | Current Price | Shares | Cash | Gain/Loss |
| 4/24/24 | Win | VOO | $420.96 | 459.05 | 65 | $ 29,838.25 | $ 2,475.85 |
What does this mean for tax purposes? It means that you will have to pay taxes in roughly $2,476 of capital gains, which is minimized because of the long-term status of these gains. Ideally, though, you would want to sell another fund at a loss (at a later point in the year) to offset this gain, which would allow you to minimize your taxes further.
Getting back to your sale, you return to Fidelity – to that pop-up window – type in 65 shares of the specific ‘Long’ lot that you want to sell. You confirm the information from the resulting screen, which should show you getting roughly $29,838 of cash. And then approve of the sale.
Step 4: Buy remaining funds after sales settle
It takes a few days for Fidelity to settle out the sale and deposit the cash into your account. There’s likely some variance given the specific price at which you sell VOO, but let’s say that it’s $30,000 of cash that gets added to your cash balance of $5,899. This gives you enough funds to purchase VEA and VGLT.
Some basic arithmetic is needed against to calculate how many shares you need of each fund. Let’s say the current price of VEA is $48.38. Since you need to purchase $17,607 (from your Portfolio tab), you need = $17,607 / $48.38 * 95% = 335 shares. Why do I multiply by 95%? You’ll find that to execute a market order, Fidelity requires that you have a buffer of 5% cash to ensure intra-day volatility. This makes sure you don’t run a negative cash balance after your buy orders clear.
Then executing in Fidelity is as simple as clicking the Buy button, entering the 335 shares, and approving of the market order.
Some Q&A
What if you have more than one portfolio?
Having more than one portfolio is common since most investors have a (pre-tax) 401(k) account and a (post-tax) Individual investment account. What I do to manage multiple accounts is just to create individual tables on the ‘Portfolio’ tab in my Google Sheets, that then roll up to a summarized table that compares the aggregated balances against your target allocation.
What if I have multiple funds that are categorized as the same allocation type (e.g., VOO and VTI are both Domestic funds)?
This is the case for me, since my 401(k) account was restricted to Fidelity funds, so my domestic funds included VOO and FXAIX, my international funds included VEA and FDIVX, etc. The solution for this is to flex your spreadsheet formula skills: add a column labeled ‘Category’ or ‘Type’ in your portfolio table, and then employ a ‘SUMIF’ function to add up all of the different funds based on type, and then summarize the totals in an aggregate table (that compares against your portfolio allocation %s).
What if I don’t have losses to offset any capital gains (in a tax year)?
I’m not going to pretend to be a tax advisor here, but the reality may be that you just have to cough up the 15% long-term cap gains rate for your gains in the year. Sure, there may be other ways on your return to offset this tax exposure – but I would consult your CPA for that kind of advice. It does help to know that paying tax on a gain is like the tail wagging the dog – hey, you’re still gaining!
Summary
I know this post might be a bit technical, reads like a textbook, and probably better served as a video tutorial. But I have had both family members and friends ask me about the ‘how to’ and ‘nuts and bolts’ questions about portfolio allocation, so hope that this post may help them see that 1) it’s quite easy to do it yourself, and 2) that you don’t need to pay a financial advisor a fee to do this for you. To paraphrase Hunger Games, may the odds be in your favor with investing!
~Lester T