How to calculate your time-weighted return in a spreadsheet
Can I build the number my platform won't give me — and prove it?
No add-in, no macro, one column of returns and one PRODUCT formula. Here is the exact build — and how to check it reproduces a known answer to the fourth decimal.
One column of returns, one formula — the time-weighted figure your platform won't give you per fund.
How to calculate your time-weighted return in a spreadsheet
Your platform probably will not give you a time-weighted return per fund. The fund factsheet has one; your own holdings do not. So if you want to know how an investment actually performed — stripped of your own deposit timing — you have to build it. The good news: it takes one column of sub-period returns and a single PRODUCT formula. No add-in, no macro, no XIRR.
This is the hands-on companion to our cornerstone on how investment return is actually calculated, which explains why time-weighted return is the right tool for judging an investment. Here we just build it, and then check the build against a known answer.
What you need before you start
True time-weighted return has one hard requirement: a valuation of the portfolio on every date money moved in or out. That is the whole reason it is harder than it looks, and the reason fund-level figures are common while personal ones are rare. You need the value of your holdings immediately before each contribution and each withdrawal.
If you do not have a valuation at every flow date, you cannot compute a true time-weighted return — you can only approximate one. The standard approximation is the Modified Dietz method, which weights each flow by the fraction of the period it was invested. We cover that, and where it is good enough, in a companion piece. For this article, assume you have the valuations. Most people can reconstruct them from monthly statements.
The method, in four steps
Step 1 — break the period at every cash flow. Each stretch between two consecutive flows is a sub-period with no money moving in or out. List them in a table: the date, the portfolio value at the start of the sub-period (the value immediately after the previous flow), and the value at the end (immediately before the next flow).
Step 2 — compute each sub-period's return. Within a flow-free stretch the return is the clean simple return:
In a cell, if the start value is in B2 and the end value in C2:
Crucially, the cash flow itself does not appear in this formula. Flows live at the boundaries between sub-periods, not inside them. That is the difference between true time-weighted return and the Modified Dietz approximation, which folds the flow into a single blended formula — and it is the most common conceptual slip in the whole topic.
Step 3 — link the sub-periods geometrically. This is the step people expect to be hard and is not. You multiply the growth factors (1 + each sub-period return) together and subtract one. If your sub-period returns sit in D2:D13:
In Google Sheets the same formula works. That single cell is your time-weighted return for the whole period.
Step 4 — annualise, only if you need to. If your window is exactly a year, you are done. If it is longer or shorter and you want an annual figure, raise the growth factor to the power of 1/years:
That is the entire method. One return column, one PRODUCT, one optional annualisation.
A worked build you can copy and check
The value of a how-to is whether it reproduces a known answer. Here is the exact dataset from our cornerstone — a realistic rand portfolio over twelve months — laid out the way your sheet should look. Each month's market move acts on the post-flow balance, so each month is already a clean, flow-free sub-period.
Month | Sub-period return |
|---|---|
Mar 2026 | −3.0% |
Apr 2026 | −4.5% |
May 2026 | +0.5% |
Jun 2026 | −2.0% |
Jul 2026 | +1.0% |
Aug 2026 | +1.5% |
Sep 2026 | +6.0% |
Oct 2026 | +4.5% |
Nov 2026 | +3.5% |
Dec 2026 | +5.5% |
Jan 2027 | +2.5% |
Feb 2027 | +3.0% |
Put those twelve returns in D2:D13 and enter =PRODUCT(1+D2:D13)-1. The result is 0.194348, or 19.4348%.[1] That is the investment's time-weighted return for the year. If your sheet returns the same figure on the same inputs, your method is sound — and you can now trust it on your own numbers. (This result has been independently verified against a hand calculation; the cornerstone carries the full month-by-month table with values and flows.)
The trap that quietly breaks the answer
There is one mistake that does not announce itself, because the spreadsheet still returns a plausible number. Never enter an internal move as a cash flow. Selling one fund to buy another inside the same account moves nothing across your account boundary — it is not a contribution or a withdrawal, and it must not create a sub-period break. The same is true of a dividend reinvested inside the portfolio. (How reinvested dividends are handled is its own companion piece).
If you wrongly split a sub-period at a rebalance, or treat a reinvested dividend as new money, your sub-period returns will be computed on the wrong bases and your PRODUCT will be quietly wrong. Nothing errors. The number just is not your return. This is exactly how a carefully built personal spreadsheet produces a confident, incorrect figure — and why a worked example you can reproduce to four decimals is worth more than a formula on its own.
When the spreadsheet is the wrong tool
A spreadsheet is the right tool for one fund, one account, over one clean window where you have all the valuations. It stops being the right tool the moment you have eleven accounts in two currencies, dividends landing on dates you did not record, and rebalances you have to reconstruct from memory. At that point the work is not the formula — it is assembling a correct, reconciled valuation on every flow date, across every account, which is the part no spreadsheet does for you.
If you want both numbers without building the table — the time-weighted return and your personal money-weighted return on the same cash flows — our calculator does it, and tells you whether the gap between them is even large enough to matter.
To conclude
Time-weighted return is not hard arithmetic. It is one return column and one PRODUCT formula, and you have just seen it reproduce 19.4348% on a real twelve-month series. What is hard is everything upstream of the formula: getting a correct portfolio valuation on every date money moved, across every account you hold. Get that right and the spreadsheet is honest. Get it wrong — split a sub-period at a rebalance, treat a reinvested dividend as a contribution — and the spreadsheet is confidently wrong, with nothing to tell you which.
About the Author
Rian Cronje comes to personal finance from the outside. After 25 years in corporate finance — Group Financial Controller roles, multi-currency consolidations and digital transformation, the unglamorous rigour of making a business's accounts actually reconcile — he found almost none of that discipline had reached the way individuals track their own wealth. He is not an advisor; he has nothing to sell you about where to put your money. He built Mintelo to close that gap: to hold a person's wealth to the standard a company holds its own books, and to break down the jargon that keeps capable people — him once included — locked out of their own numbers.
Sources
Method (sub-period simple return, geometric linking) per CFA Institute, Global Investment Performance Standards — Guidance Statement on Calculation Methodology, gipsstandards.org (accessed 19 Jun 2026). Worked result re-verified 19 Jun 2026: PRODUCT of the twelve monthly growth factors = 1.194348, i.e. TWR 19.4348%. Full dataset with values and flows in Cornerstone 2.
See your real net worth across every account — in rand or dollar.
One reconciled view, the same number whichever way it's checked. Mintelo is the personal wealth platform built like a real one.