Home › Templates › Portfolio rebalancing spreadsheet
Portfolio Rebalancing Spreadsheet
A free portfolio rebalancing spreadsheet for Excel and Google Sheets: enter up to 12 holdings with price, units and target %, and it shows each holding's drift from target, flags anything outside your tolerance band, and works out the buys and sells (or a new-cash-only plan with no selling) to get back on target, in whole units and after fees.
Use it in Google Sheets: choose File → Import → Upload in Sheets and pick the .xlsx, or upload it to Google Drive and open it with Google Sheets. No sign-up, no add-on, no live price feed: you type current prices yourself.
| Holding | Asset class | Value now | % now | Target | Drift (pts) | Band | Band check | Buy and sell $ | New cash only $ |
|---|---|---|---|---|---|---|---|---|---|
| FUND-A | US stocks | $42,000.00 | 46.15% | 40% | +6.15 | ±5.00 pts | Outside band | −$3,602.40 | – |
| FUND-B | International stocks | $15,000.00 | 16.48% | 20% | −3.52 | ±5.00 pts | Within band | +$4,198.80 | $2,332.44 |
| FUND-C | US bonds | $19,000.00 | 20.88% | 25% | −4.12 | ±5.00 pts | Within band | +$4,998.50 | $2,665.56 |
| FUND-D | Inflation-linked bonds | $6,000.00 | 6.59% | 5% | +1.59 | ±1.25 pts | Outside band | −$1,200.30 | – |
| FUND-E | Real estate (REIT) | $4,500.00 | 4.95% | 5% | −0.05 | ±1.25 pts | Within band | +$299.70 | – |
| FUND-F | Money market | $4,500.00 | 4.95% | 5% | −0.05 | ±1.25 pts | Within band | +$299.70 | – |
| Total | $91,000.00 | 100.00% | 100% | 2 outside | +$4,994.00 | $4,998.00 |
What's inside the spreadsheet
| Tab | What you type | What it calculates |
|---|---|---|
| Start here | Nothing | Steps, how the two modes work, colour key, limits, a note on taxes, disclaimer |
| Rebalance | Six settings, then up to 12 holdings: name, asset class, price, units, target % | Value and weight now, drift in points and as % of target, band check, buy-and-sell trades, new-cash-only buys, units to trade, Buy/Sell/Hold, fees, weight after, a summary and two charts |
| Example | Already filled | Six fictional funds, so you can see every formula working. Switch B4 to compare the two modes. |
The settings
| Setting | What it does |
|---|---|
| Mode | Buy and sell: trades every holding back to target. New cash only: spends new cash on underweight holdings and sells nothing. |
| New cash to add | Money you are adding now. In Buy and sell mode a negative amount is cash you want to take out. |
| Absolute band | Allowed drift in percentage points, e.g. 5% means a 40% target may sit between 35% and 45%. |
| Relative band | Allowed drift as a share of the target, e.g. 25% of a 5% target is ±1.25 points. The sheet uses the smaller band for each holding. Leave either blank to switch it off. |
| Fee per trade | A flat amount per buy or sell. The sheet holds back cash for the fees before sizing trades. |
| Whole units only? | Yes rounds buys down and sells up, so the plan never spends cash you do not have. No allows fractional shares. |
The default bands (5 points and 25% of target) are starting values to change, not a recommendation.
How to use it
- Open Rebalance. In B4 pick Buy and sell or New cash only.
- Type any new cash in B5, your bands in B6 and B7, a fee per trade in B8 (or 0), and Yes or No for whole units in B9.
- From row 14, add each holding: name, asset class (optional), today's price, units held and target %. Check that the summary says Target check: OK: 100%.
- Read the Band check column. If nothing is outside its band, you may decide no trade is needed.
- Read columns O to V: the planned trade, units to trade, Buy/Sell/Hold, fee, and the weight after the trades. The summary shows totals to buy and sell, fees and cash left over.
- Place the trades with your broker, then update units and prices in the sheet. Next time, start again from step 3.
Tolerance bands: when to rebalance
A portfolio drifts because its holdings earn different returns. Investor.gov's asset allocation guide puts it this way: "Over time, some investments will grow faster than others. This may push your holdings out of alignment with your investment goals and change the risk level of your portfolio." It adds that some experts rebalance "at regular intervals, such as every six or 12 months", while others rebalance "when your holdings of an asset class increase or decrease more than a pre-set percentage", and that "rebalancing tends to work best when done relatively infrequently."
The pre-set percentage is the tolerance band. Vanguard's 2010 research paper "Best practices for portfolio rebalancing" (Jaconetti, Kinniry and Zilbering; copy hosted by a third party) found "no optimal frequency or threshold", and concluded that for most broadly diversified stock and bond fund portfolios, "annual or semiannual monitoring, with rebalancing at 5% thresholds, is likely to produce a reasonable balance between risk control and cost minimization for most investors."
A fixed 5-point band works for large holdings but is very wide for a 5% holding, which could double before it is flagged. That is why the sheet also has a relative band and uses whichever is tighter. In the example, FUND-D is only +1.59 points over its 5% target, but that is 31.9% of the target, so it is outside its ±1.25-point band.
New cash only: rebalancing without selling
The same Vanguard paper notes that rebalancing "with dividends, interest payments, realized capital gains, or new contributions can help investors both exercise risk control and trim the costs of rebalancing." The New cash only mode does that with your contribution. It finds the holdings furthest below target, measured as value ÷ target, and raises them to one common level, in proportion to their targets, until the cash is used up. Overweight holdings are left alone.
The level is found with plain formulas, no solver or macro. Helper column X holds value ÷ target for each holding. Column Y holds the cash needed to lift every holding at or below that level up to it. The highest level the cash can reach sets the fill level, and each buy is target % × level − value now, or zero.
The formulas, explained
Exact formulas from row 14 of the Rebalance and Example tabs. Columns: C price, D units, E target %. Settings: B4 mode, B5 new cash, B6 absolute band, B7 relative band, B8 fee, B9 whole units. Row 26 holds the totals.
| Field | Formula |
|---|---|
| Value now (F) | =IF(OR(N(C14)<=0,AND(D14="",E14="")),"",C14*N(D14)) |
| % now (G) | =IF(F14="","",IF($F$26=0,0,F14/$F$26)) |
| Drift in points (H) | =IF(F14="","",G14-N(E14)) |
| Band (J): smaller of the two bands | =IF(F14="","",MIN(IF(N($B$6)>0,$B$6,1),IF(AND(N($B$7)>0,N(E14)>0),$B$7*E14,1))) |
| Band check (K) | =IF(F14="","",IF(ABS(H14)>J14+0.0000001,"Outside band","Within band")) |
| Target value, buy and sell (L) | =IF(F14="","",N(E14)*($F$26+N($B$5)-N($B$8)*$Y$29)) (Y29 = trades counted for fees) |
| Trade to target (M) | =IF(F14="","",L14-F14) |
| Value ÷ target, helper (X) | =IF(OR(F14="",N(E14)<=0),"",F14/E14) |
| Cash to lift to this level, helper (Y) | =IF(X14="","",X14*SUMIF($X$14:$X$25,"<="&(X14+ABS(X14)*0.000000001),$E$14:$E$25)-SUMIF($X$14:$X$25,"<="&(X14+ABS(X14)*0.000000001),$F$14:$F$25)) |
| Cash to allocate after fees (Y33) | =MAX(0,N($B$5)-N($B$8)*Y32) (Y32 = holdings that get cash in a first pass) |
| Fill level (Y35) | =IF(COUNT(AC14:AC25)=0,0,(Y33+SUMIF($X$14:$X$25,"<="&(Y34+ABS(Y34)*0.000000001),$F$14:$F$25))/SUMIF($X$14:$X$25,"<="&(Y34+ABS(Y34)*0.000000001),$E$14:$E$25)) (Y34 = highest value ÷ target the cash can reach) |
| Buy with new cash (N) | =IF(F14="","",IF(X14="",0,MAX(0,E14*$Y$35-F14))) |
| Planned trade by mode (O) | =IF(F14="","",IF($B$4="New cash only",N14,M14)) |
| Units to trade (P) | =IF(O14="","",IF(ABS(O14)<0.005,0,IF($B$9="Yes",IF(O14>0,ROUNDDOWN(O14/C14,0),-ROUNDUP(-O14/C14,0)),O14/C14))) |
| Fee (S) | =IF(R14="","",IF(R14="Hold",0,N($B$8))) |
| Cash left over (summary M8) | =N(B5)-Q26-S26 |
The tiny 0.000000001 margin in the SUMIF criteria stops rounding in text-to-number conversion from dropping a holding from its own level. Fees are handled in two passes: the sheet first counts how many holdings would get cash, then takes fee × that count off the cash before sizing the buys.
Worked example from the sample data
The Example tab holds six fictional funds worth $91,000.00, with $5,000.00 of new cash, a $1.00 fee per trade and whole units.
Step 1: drift and band check
FUND-A is worth $42,000.00, which is 46.15% of $91,000.00. Its target is 40%, so it has drifted +6.15 points, more than its 5-point band. FUND-D is 6.59% against 5%: only +1.59 points, but its band is the smaller of 5 points and 25% × 5% = 1.25 points, so it is also flagged. 2 of 6 holdings are outside their bands.
Step 2a: New cash only (the Example tab's setting)
Value ÷ target is lowest for FUND-B ($75,000.00) and FUND-C ($76,000.00), so the cash goes there. Two holdings get cash, so $2.00 is held back for fees and $4,998.00 is allocated. Both are lifted to a level of $86,662.22: FUND-B buys 20% × $86,662.22 − $15,000.00 = $2,332.44, FUND-C buys 25% × $86,662.22 − $19,000.00 = $2,665.56. Rounded down to whole units:
| Holding | Action | Units | Trade $ | % after | Drift after (pts) |
|---|---|---|---|---|---|
| FUND-A | Hold | 0 | $0.00 | 43.78% | +3.78 |
| FUND-B | Buy | 38 | +$2,280.00 | 18.01% | −1.99 |
| FUND-C | Buy | 53 | +$2,650.00 | 22.57% | −2.43 |
| FUND-D | Hold | 0 | $0.00 | 6.25% | +1.25 |
| FUND-E | Hold | 0 | $0.00 | 4.69% | −0.31 |
| FUND-F | Hold | 0 | $0.00 | 4.69% | −0.31 |
New cash alone narrows the gaps but does not fix them: $5,000.00 is small next to a $3,602.40 overweight in FUND-A. FUND-A stays outside its band at 43.78%.
Step 2b: Buy and sell (switch B4)
With six trades expected, $6.00 is set aside for fees, so the target values are based on $91,000.00 + $5,000.00 − $6.00 = $95,994.00. FUND-A's target is 40% of that, $38,397.60, so the ideal sale is $3,602.40, or 30.02 units at $120.00, rounded up to 31.
| Holding | Action | Units | Trade $ | % after | Drift after (pts) |
|---|---|---|---|---|---|
| FUND-A | Sell | 31 | −$3,720.00 | 39.99% | −0.01 |
| FUND-B | Buy | 69 | +$4,140.00 | 20.00% | 0.00 |
| FUND-C | Buy | 99 | +$4,950.00 | 25.02% | +0.02 |
| FUND-D | Sell | 49 | −$1,225.00 | 4.99% | −0.01 |
| FUND-E | Buy | 3 | +$270.00 | 4.98% | −0.02 |
| FUND-F | Buy | 299 | +$299.00 | 5.01% | +0.01 |
Every holding ends within 0.02 points of its target. The price is 6 trades instead of 2, and two sales. In a taxable account those sales may create a capital gain (see below).
Costs and taxes of rebalancing
The Vanguard paper lists the "costs of rebalancing" as taxes, transaction costs, and time and labor. On taxes it says: "If rebalancing within taxable registrations, capital gains taxes may be due upon the sale if the asset sold has appreciated in value."
The IRS explains the basics in Topic no. 409, Capital gains and losses: "You have a capital gain if you sell the asset for more than your adjusted basis." And: "Generally, if you hold the asset for more than one year before you dispose of it, your capital gain or loss is long-term. If you hold it one year or less, your capital gain or loss is short-term."
Tax note, not tax advice. The spreadsheet does not know your cost basis, holding periods or account types, and it does not estimate tax. Trades inside tax-advantaged retirement accounts are generally treated differently from trades in a taxable account, and rules differ by country. Check your own situation with a qualified tax professional before selling. The capital gains tax calculator can help you estimate a single sale.
Fees matter too. The fee setting is a flat amount per trade; spreads and fund charges are not modelled. Vanguard's paper adds that when costs are mainly proportional to the trade size, rebalancing to the nearest edge of the band, not all the way to target, keeps trades smaller. This sheet always plans a trade to the target; you can trade less by hand.
Common mistakes
- Targets that do not add up to 100%. The summary shows Fix: targets add up to …. Every result depends on it.
- Old prices. The sheet has no price feed. Update prices on the day you trade, or the drift and trade sizes will be off.
- Looking at one account only. If the same plan covers a brokerage account and a retirement account, list holdings from both, or rebalance each against its own targets.
- Typing a band as a whole number. Enter 5% (or 0.05), not 5. A band of 500% means nothing is ever flagged.
- Forgetting the cash left over. Whole-unit rounding leaves some cash. Add it to the next contribution.
- Trading just because the sheet shows a number. If every holding is within its band, the plan still lists small trades. Decide first whether a rebalance is needed at all; your investment policy statement is the place to write that rule down.
FAQ
How do you calculate how much to buy or sell to rebalance?
Add new cash to the current portfolio value (less any expected fees), multiply by each holding's target percentage to get its target value, then subtract its current value. A positive result is a buy, a negative one a sell. Divide by the price for units. In the example, FUND-A is worth $42,000.00 against a target of $38,397.60, so the buy-and-sell plan sells about $3,602.40 of it.
What is a rebalancing tolerance band?
A band is how far a holding may drift from its target before you act. An absolute band is measured in percentage points (for example ±5 points around a 40% target). A relative band is a share of the target itself (25% of a 5% target is ±1.25 points). The spreadsheet uses the smaller of the two for each holding and flags it "Outside band".
Can you rebalance without selling?
Yes, if you are adding money. Direct new contributions to the holdings that are most underweight. The spreadsheet's "New cash only" mode does this and never sells. If the drift is large compared with the new cash, the portfolio will move toward the target but may not reach it, as in the example.
How often should you rebalance a portfolio?
There is no single right schedule. Investor.gov notes that some experts suggest regular intervals such as every six or 12 months and others a pre-set percentage drift, and that rebalancing "tends to work best when done relatively infrequently." A 2010 Vanguard paper concluded that annual or semiannual monitoring with 5% thresholds is likely a reasonable balance between risk control and cost for most broadly diversified stock and bond fund portfolios.
Does rebalancing trigger taxes?
It can. Selling an investment for more than its adjusted basis in a taxable account is a capital gain under IRS rules, and holding period decides whether it is short-term or long-term. Trades inside tax-advantaged accounts are generally treated differently. The spreadsheet does not estimate tax; check your own situation with a tax professional.
Can I use this rebalancing spreadsheet in Google Sheets?
Yes. In Google Sheets choose File → Import → Upload and pick the .xlsx, or upload it to Drive and open it with Google Sheets. The formulas use SUMIF, COUNTIF, MIN, MAX, ROUNDDOWN and ROUNDUP, which work in Excel 2016+, Google Sheets and LibreOffice. There are no macros.
Related tools and templates
- Investment policy statementWrite down targets and rebalancing rules
- Stock portfolio trackerTrack holdings and returns
- Investment trackerAll accounts in one sheet
- DCA trackerRegular contributions over time
- Crypto portfolio trackerCoins, cost basis and allocation
- Capital gains tax calculatorEstimate tax on a sale
- Stock average calculatorAverage price after a buy
- All templatesFree spreadsheets
Educational template only, not financial, tax or investment advice. Example data is fictional: FUND-A to FUND-F and all prices, units, targets and fees are made up and are not a suggested allocation. Investor.gov, IRS and Vanguard material was retrieved on 29 October 2026.