How to Calculate Portfolio Allocation Drift in Excel

1. What portfolio allocation drift means

An asset allocation is the percentage of a portfolio assigned to categories such as stocks, bonds and cash. Market movement, contributions, withdrawals and distributions can change those percentages over time. The resulting difference from the target is allocation drift.

A positive result means the category is above its target weight; a negative result means it is below target. For example, a current stock weight of 68% compared with a 60% target has drifted +8 percentage points. It is not “13.3% over target” for the purpose of this table.

Rebalancing is the process of bringing a portfolio back toward its chosen allocation. Investor.gov and FINRA describe several approaches, including selling an overweight category, adding to an underweight category or directing ongoing contributions differently. Neither source prescribes one universal review schedule or threshold.

2. Prepare the source data before calculating drift

Create a tab named Allocation. Use one row per asset class and start with three required inputs: asset class, current market value and target weight.

Keep the categories mutually exclusive. If “US stocks” and an S&P 500 ETF are separate rows, you may double-count the same exposure unless the ETF row is a holding and the other row is a calculated asset class. Decide whether the table is organized by holding, account, asset class, region or sector—and do not mix levels.

Use values from the same date. Convert foreign holdings into one base currency before calculating weights. Include portfolio cash once, and exclude money outside the boundary you defined.

If the holdings, accounts and transfers are not yet reconciled, begin with our investment portfolio tracking guide. If you also want to separate investment performance from irregular deposits and withdrawals, see the XIRR guide.

3. Build the allocation drift formulas in Excel or Google Sheets

Assume your first three categories occupy rows 2 through 4. Enter values in column B and targets as percentages in column C.

Current portfolio weight

=IFERROR(B2/SUM($B$2:$B$4),0)

Copy the formula down column D and format the cells as Percentage.

Allocation drift in percentage points

=D2-C2

Copy down column E. Positive values are above target; negative values are below target.

Target value at the current portfolio total

=SUM($B$2:$B$4)*C2

Dollar gap

=F2-B2

A positive dollar gap means the category is below its target value. A negative gap means it is above target. This sign is the opposite of the drift column because the question changes from “Where am I now?” to “How much separates the current value from the target value?”

4. Worked portfolio allocation drift example

Consider a hypothetical $50,000 portfolio with a 60% stock target, 30% bond target and 10% cash target.

The target values are $30,000 for stocks, $15,000 for bonds and $5,000 for cash. The dollar gaps sum to zero because this version redistributes the existing $50,000 total. The table describes the gap; it does not tell the investor whether or when to trade.

For monitoring, add an absolute drift column with =ABS(E2). This is helpful when you want to sort by distance from target without losing the original positive or negative direction.

5. Portfolio allocation drift calculator

Enter up to three categories to verify a worksheet result. Targets must total 100%. The calculation stays in your browser and is not stored.

6. Calendar reviews vs. threshold reviews

A spreadsheet becomes more useful when it records the policy being checked. Two common frameworks are:

Investor.gov notes that investors may rebalance at regular intervals or when allocation moves beyond a predetermined percentage. FINRA likewise says there is no official timeline. The appropriate approach depends on the portfolio, costs, taxes and the investor’s written plan.

To create a neutral spreadsheet flag, place your chosen review threshold in J2 and use:

=IF(ABS(E2)>=$J$2,"Review","Within range")

Label it “Review,” not “Buy” or “Sell.” The formula can identify a condition without turning a recordkeeping sheet into personalized advice.

7. Model how new contributions affect allocation

One approach described by Investor.gov and FINRA is directing new contributions toward underweight categories. You can model this separately before changing the original table.

1. Enter the proposed contribution in a single input cell.
2. Add it to the existing portfolio value to calculate a projected total.
3. Multiply the projected total by each target weight.
4. Subtract each current category value to see the projected gap.
5. Confirm that proposed allocations do not exceed the available contribution.

If the existing overweight amount is large, contributions alone may not restore every target immediately. The model should show the remaining drift instead of forcing negative contribution amounts. Keep this scenario separate from actual transactions until a decision has been made.

8. Common portfolio drift spreadsheet errors

Save dated snapshots instead of allowing live quotes to rewrite the history. A monthly snapshot with portfolio total, category values, weights, targets and notes makes later reviews auditable.

Prefer a ready-made portfolio tracker?

You can build the allocation table yourself. If you want an existing workbook for holdings, transactions and portfolio review, browse our Investment Portfolio Trackers collection.

ETF & Index Fund Tracker

Organize fund holdings across accounts and currencies, inspect allocation and identify portfolio drift. Review the live product page for its current features and requirements.

View ETF & Index Fund Tracker

Stock Portfolio Tracker

Track stock purchases and sales, transaction fees, profit and loss, dividends and multiple currencies in Excel or Google Sheets.

View Stock Portfolio Tracker

The product pages specify Microsoft 365 for Excel files or a Google account for Google Sheets. Mobile editing may be limited. Product features can change, so review the product page before purchasing.

Portfolio allocation drift: FAQ