How to Track Investment Returns in Google Sheets (TWR vs MWR)

Open a brokerage app and the dashboard might show “+8.4% YTD” on the account screen. Open a portfolio tracker fed by the same transactions and the number reads “+6.1%”. Same account, same date. Neither is wrong. They’re answering different questions.

That’s the whole problem with portfolio returns. There is no single number. The percentage you see depends on whether you’re asking “is this fund any good” or “what return did you personally earn given when you added and pulled money”. A spreadsheet can show both. Most apps show one and don’t tell you which.

This walkthrough builds both numbers in Google Sheets on a sample portfolio, explains where they diverge, and notes which one answers which question.

Why simple percentage change is wrong

The first instinct is the right instinct: end value minus start value, divided by start value. For a portfolio that nobody touched all year, this works.

It stops working the moment a contribution or withdrawal hits the account. Consider a $50,000 portfolio that ends the year at $63,264. Naively that’s 26.5%. Spectacular. Except the holder also added $9,500 over the course of the year. Of the $13,264 increase, $9,500 is just transferred cash - the actual investment gain is $3,764, or about 6.3% on contributions plus starting balance.

Even that 6.3% is misleading. It treats every dollar as if it sat in the market for the full year, when in fact $4,500 of it only had four months to compound. TWR and MWR both correct for this, in different ways.

Time-weighted return: how the funds performed

TWR strips out the effect of when you contributed. It asks: if you had put in $1 on January 1 and never touched it, what return would that dollar have earned by year-end? That’s a clean measure of the investments themselves.

This is the metric the GIPS standards require professional managers to report, because contributions and withdrawals are usually outside the manager’s control. TWR isolates the part the manager influences: the investment selection itself.

The mechanics: break the year into sub-periods, with a new sub-period starting every time money flows in or out. Calculate the return for each sub-period using only the portfolio values just before and just after the cash flow. Then chain them.

Money-weighted return: what you personally earned

MWR is the internal rate of return on your actual cash flows. It includes the effect of timing. If you added money right before a rally, MWR rewards that. If you added money right before a drop, MWR penalizes it.

In Google Sheets, MWR is calculated with XIRR, which solves for the discount rate that sets the net present value of your cash flows to zero. You feed it a list of dollars in and out, with dates. It returns an annualized rate.

MWR answers “what did you actually get out of this portfolio?” TWR answers “is this investment any good in isolation?”

The sample portfolio

Here are the actual events for the year.

The portfolio went up 5% in Q1, dropped 5% from April to September, then rallied 7% to year-end. A $5,000 contribution in April, a $4,500 contribution in September, and a $1,150 dividend that was automatically reinvested into the same holdings (so it doesn’t show as a separate cash flow - it’s already inside the ending value).

Total contributed during the year: $59,500. Ending value: $63,264. Crude gain: $3,764.

Setting up the data in Google Sheets

Three columns are enough.

Cash flow convention: contributions are negative (money leaving your pocket, going into the portfolio). Withdrawals and the final ending value are positive. This sign convention is what XIRR expects.

The “before” and “after” rows on contribution dates are essential for TWR. You need the portfolio value the moment before money was added, so you can isolate the return of the existing money over the prior sub-period. Most brokerage statements show the closing balance for each day, which works as both “end of prior period” and “start of new period” - except on contribution days, where you need to subtract the contribution amount from the post-contribution balance to get the pre-contribution value.

Calculating TWR step by step

Three sub-periods this year, defined by the two contribution dates.

Sub-period 1: Jan 1 to Apr 1. The portfolio went from $50,000 to $52,500 with no cash flows.

Return₁ = 52,500 / 50,000 = 1.0500

That’s a 5.00% return for the period.

Sub-period 2: Apr 1 to Sep 1. Starting value (after the Apr contribution) is $57,500. Ending value (just before the Sep contribution) is $54,625.

Return₂ = 54,625 / 57,500 = 0.9500

A 5.00% loss for the period.

Sub-period 3: Sep 1 to Dec 31. Starting value (after the Sep contribution) is $59,125. Ending value is $63,264.

Return₃ = 63,264 / 59,125 = 1.0700

A 7.00% gain for the period.

Chain them:

TWR = (1.0500 × 0.9500 × 1.0700) - 1
    = 1.0673 - 1
    = 0.0673
    = 6.73%

The portfolio’s underlying return for the year was 6.73%. That’s the answer to “how did the investments do”.

In Google Sheets, with sub-period returns in cells D2, D3, D4:

=PRODUCT(D2:D4) - 1

Or, computed inline from a values range:

=PRODUCT(ARRAYFORMULA(C3:C5 / C2:C4)) - 1

(Where the values column has been arranged so each row is a sub-period boundary.)

Calculating MWR with XIRR

XIRR takes two parallel ranges: the cash flow amounts and the cash flow dates.

=XIRR(B2:B5, A2:A5)

Where:

The function returns 0.0684, or 6.84%. That’s the money-weighted return.

The full syntax is XIRR(cashflow_amounts, cashflow_dates, [rate_guess]). The optional rate_guess defaults to 0.1 (10%); leaving it blank is fine for ordinary portfolios. If XIRR returns #NUM!, the function couldn’t converge - usually because the cash flows don’t have at least one negative and one positive value, or the implied return is extreme.

The returned figure is annualized regardless of period length. A two-year history gives you an annualized return; a six-month history gives you a six-month return projected to a full year. This means short periods can produce dramatic numbers - a 5% gain over one month annualizes to 79.6%. Short-period XIRR carries the same interpretive caveat as any annualized short-term result: small moves get amplified.

Comparing the two numbers

Same portfolio, same year, two answers.

The 11 basis point gap is small but informative. MWR is higher because the contributions happened at lucky moments. The September contribution went in just after a 5% drop, so the new money got the full benefit of the 7% recovery without first absorbing the loss. Good timing, even if accidental.

If both contributions had landed on January 1 instead of mid-year, MWR would equal TWR exactly - all the money would have ridden the same path. If the contributions had been timed badly (right before the April-September drop), MWR would have come in below TWR.

TWR ignores timing entirely. MWR is timing’s report card. Neither is “right”. For comparing your portfolio to a fund or benchmark, TWR is the apples-to-apples number. For measuring what your money personally earned, MWR is the more candid one.

What dividends do to each number

A dividend that gets reinvested (DRIP) doesn’t appear as a separate cash flow in either calculation. It’s already inside the ending portfolio value. The $1,150 dividend in the example bought additional shares at the December 15 price, and those shares are part of the $63,264 ending value.

A dividend that gets paid out as cash and stays in cash needs to show up. For TWR, it counts as a withdrawal at the date it left the invested portfolio (creating a new sub-period boundary). For MWR/XIRR, it appears as a positive cash flow on the date received.

If the same $1,150 dividend had been paid out on Dec 15 and held as cash through year-end, the XIRR cash flows would change to:

The total received is still $63,264 - the dividend just shifts $1,150 of it earlier. XIRR on these flows comes in at 6.84%, about one basis point higher than the all-reinvested version, because $1,150 returned to you on Dec 15 is worth marginally more than $1,150 returned on Dec 31. The gap is trivial on a one-year horizon but grows when dividends are large or compounded across many years.

For a more thorough walkthrough of dividend logging, the investment portfolio tracker post covers the column structure that lets you separate cash dividends from reinvested ones cleanly.

The vanity-return trap

Several brokerage apps display only “total account growth” on the dashboard. That’s the simple percentage change in account value, contributions and all. It is the most flattering number to show during years when contributions are heavy, because every dollar contributed gets counted as “growth”.

A $50,000 portfolio that grew $13,264 to $63,264 shows “+26.5%” by this measure - satisfying to look at, meaningless as an investment performance number. The real performance is somewhere between TWR (6.73%) and MWR (6.84%), depending on which question you’re asking.

When comparing portfolios across people or funds across providers, TWR is the only fair measure. When evaluating your own decisions about when to add money, MWR is the diagnostic. Total account growth is a vanity metric that mostly tells you whether you contributed more than you lost.

A common point of confusion: which annualization

Both XIRR and the chained TWR shown above produce annualized rates when the data spans roughly a year. For periods longer than a year, both annualize. For periods shorter than a year:

• XIRR annualizes by default. A 5% gain over six months becomes ~10.25% annualized.
• The chained TWR formula above gives a period return, not annualized. To annualize, raise to the power of (365 / days in period).

Mixing the two without realizing this gives you wildly different numbers for the same portfolio. Picking one convention, annualized or period returns, and applying it consistently across the sheet keeps the comparison honest.

When the spreadsheet runs out

A from-scratch sheet with three columns and two formulas is enough for a single account, single year. It gets fiddly when:

• You have multiple accounts (taxable, IRA, 401(k), HSA) and want a consolidated TWR.
• You hold international funds with currency conversion at each cash flow date.
• You want rolling 1-year, 3-year, and since-inception XIRR all visible at once.
• Dividends arrive at irregular dates and you want both reinvestment and cash-payout scenarios trackable.

At that point, a pre-built template with the holdings, dividend, and allocation structure already wired saves the rebuilding effort. The dated cash flows that TWR and XIRR need still go on a sheet of your own, since the holdings structure records positions rather than transactions.

Get the template

Our Investment Portfolio Tracker Essentials reports the plain cost-basis return the article opens with. The dashboard shows total value, total gain, and overall return on cost across your holdings. The TWR and XIRR figures covered above are a layer you add on a separate sheet, because the Holdings columns run from ticker through % return with no dated cash-flow log.

Investment Portfolio Tracker Essentials ($19 tier): the dashboard reports a cost-basis return on cost, not a time-weighted or money-weighted figure. Those you build on a sheet of your own using the method above.

• Investment Portfolio Tracker Essentials, $19: Dashboard and Holdings sheets for Excel or Google Sheets. Enter your positions, and market value, gain/loss, cost-basis return, and allocation by holding calculate automatically.
• Investment Portfolio Tracker Ultimate, $29: Adds a Dividends sheet with income and yield, an Allocation sheet with sector targets and rebalancing variance, a Performance sheet with a year-over-year comparison and a two-year annualized return, and room for up to 50 holdings.
• Financial Planning Spreadsheet, $29: A Google Sheets projection that runs to an end year you set, with your stock holdings feeding the asset total behind its cash flow and net worth view.

Related

• Investment Portfolio Tracker (Excel, With Dividend Log and ROI)
• Dividend Income Tracker in Google Sheets (Free + Paid Versions)
• Investment Returns Calculator: Setting Realistic Expectations
• Compound Interest Calculator: The Math Behind Long-Term Growth
• Google Sheets vs Excel for Investment Tracking