How to calculate real return when you keep adding money

You paid in €6,000 over a year and finished with €6,400. Is that a 6.7% return, or a 12.5% return? Both come out of the same four numbers, and the difference is not rounding.

Every percentage-gain formula divides a gain by an amount invested. As soon as you contribute monthly, that phrase stops having a single meaning — the amount invested was €500 in January and €6,000 in December. Choosing a denominator is choosing what you are measuring.

The worked case

€500 on the first of every month for twelve months. No withdrawals, no trading. The account ends the year at €6,400.

The obvious calculation:

The problem is that €6,000 was never invested for a year. Your December €500 was invested for one month. Your January €500 was invested for twelve. Average the twelve contributions by how long each one was actually in the market and you get 6.5 months, not 12.

Solve instead for the annual rate that reconciles all twelve dated deposits with the ending value, and the answer is:

Almost double. Not because anything was calculated incorrectly the first time, but because 6.67% is the growth of a sum that sat there all year, and no such sum existed.

Method one: money-weighted (XIRR)

This is the one you can do yourself, in a spreadsheet, in about two minutes. It gives you the single annual rate your money actually earned, with your contribution timing baked in.

Building it

1. Column A: the date of every cash flow.
2. Column B: the amount. Deposits are negative — money leaving you. Withdrawals are positive.
3. Final row: today’s date, and your current total value as a positive number.
4. Anywhere: =XIRR(B:B, A:A)

For the example above:

The sign convention is the only place this goes wrong. If every number is positive, there is nothing to solve and the function errors. Deposits leave your pocket, so they are negative.

What it will and will not tell you

• It is annualised, so a six-month period returns a yearly rate, not the growth over those six months.
• It needs one sign change to converge. A portfolio that has only ever received deposits works because the closing value is the positive.
• It includes your timing. Two people holding identical portfolios who contributed on different days get different XIRRs, and both are correct.

Method two: time-weighted

Time-weighted return removes your contribution timing entirely, which is what makes it the only figure you can hold up against an index.

The method is to break the period at every cash flow, measure the growth within each segment, and multiply:

Because each segment ends immediately before money moves, the deposit itself never sits inside a measured period. It ends one and starts the next.

The mathematics is easy. The data is not. For twelve monthly contributions you need twelve portfolio valuations — the total value on the day before each deposit — and that means repricing every holding you had at the price it traded at on each of those dates. Almost nobody has that recorded, which is why time-weighted return is normally computed by software and why your broker does not show it.

Choosing between them

The rule underneath the table: if the comparison is with something that has no deposits — an index, a fund, your own prior year — use time-weighted. If the question is about your money, use money-weighted.

Three things that quietly break the number

• Dividends. If they are reinvested inside the account they are not cash flows and must not appear in the XIRR list — they are already in the ending value. If they were withdrawn, they are positive cash flows on the date received. Counting them twice is the most common error here.
• Foreign currency. Run the calculation in one currency throughout, converting each flow at its own date’s rate. Mixing a euro deposit with a dollar valuation produces a number that is part return and part exchange rate, with no way to separate them afterwards.
• Transfers between accounts. Moving €5,000 from one broker to another is not a deposit and not a withdrawal. If you track the two accounts separately it looks like both, and both figures end up wrong.

The short version

The number your broker shows divides your gain by everything you have ever paid in, as though it all arrived on day one. For a single lump sum that is exact. For monthly contributions it is a rough figure whose error is the size of your deposit schedule — and in the example above, that error was larger than the return itself.