XIRR, the honest one for a SIP

Chapter 2 required one amount in at the start and nothing added. Almost nobody invests that way. This chapter is the measure for how people actually invest.

Why CAGR breaks

CAGR needs a start value and an end value. A monthly investment has twenty-four start values, each on a different date, each invested for a different length of time.

The Deposits and small savings subject met this with a recurring deposit: twelve instalments, the first invested for twelve months and the last for one, so the average rupee was present for about half the term. Dividing total gain by total contributions gave about 3.8% on a 7% product — a number that measured nothing.

The error is always the same: treating money that arrived late as though it had been there from the beginning. It inflates the denominator and understates the rate.

What XIRR solves for

XIRR takes a list of dated cash flows and finds the single annual rate that makes their present value zero.

$\sum_{i} \frac{C_i}{(1+r)^{d_i/365}} = 0$

where $C_i$ is each cash flow — negative when you pay money in, positive when you take it out or when the holding's current value is counted — and $d_i$ is the number of days from the first flow.

There is no closed-form solution. The rate is found by iteration: guess, evaluate, adjust. Spreadsheets and the calculator on this site do that for you, and the important thing is not the search but what the answer means.

The answer means: the constant annual rate at which every rupee, from the day it arrived, would have had to grow to produce what you have now. Each rupee is credited for exactly the time it was present. That is why it works where CAGR cannot.

Building the list

This is the practical skill, and it is simpler than it looks.

Date	Amount	Sign
Each contribution	the amount paid	negative
Each withdrawal or payout received	the amount received	positive
Today	current value of the holding	positive

Three rules that catch most mistakes:

Signs must be consistent. Money leaving your pocket is negative; money arriving is positive. Getting this backwards returns a nonsensical rate or no answer at all.

The final value is a flow, not a balance. You enter today's value as a positive cash flow on today's date, as though you sold everything. You have not sold anything; the entry stands for what you could realise.

Use actual dates, not month numbers. The whole point is day-count precision. A contribution on the 1st and one on the 28th are not the same.

For mutual funds the consolidated account statement gives you every transaction with its date and the current value, so the list can be built from one document. Note what the published fund return cannot do here: SEBI requires it to be CAGR over standard periods, which assumes a single investment held throughout. The factsheet does not know when you invested, so it cannot tell you your rate. Only your own flows can.

Reading the answer

XIRR is an annual rate, directly comparable with a deposit rate, a loan rate, inflation, or another portfolio's XIRR. That comparability is the payoff.

Two cautions.

It is sensitive to recent flows when the holding is large. A big contribution shortly before you measure has had little time to grow and can drag the figure down, saying more about your timing than about the investment.

It is a money-weighted figure, which means it mixes the investment's performance with the effect of when you put money in. That is exactly what you want for judging your own outcome and exactly what you do not want for judging a manager. Chapter 4 separates the two, and the distinction is the most useful one in this subject.

Working the problem

₹10,000 a month for 24 months, now worth ₹2,75,000.

Total contributed: ₹2,40,000. Gain: ₹35,000.

The naive figure: 35,000 / 2,40,000 = 14.6%, and people then call this the return, sometimes dividing by two to "annualise" it to 7.3%. Both are wrong.

Why it is the wrong calculation. It uses ₹2,40,000 as though the whole sum were invested for the whole period. It was not. The first instalment was invested for 24 months and the last for one, so the average rupee was invested for about 12.5 months — a little over a year.

So the money actually at work, measured in rupee-years, is roughly ₹2,40,000 × (12.5/24) ≈ ₹1,25,000 for the full two years, not ₹2,40,000. Dividing by a figure nearly twice too large halves the apparent rate.

Roughly what the real rate is. ₹35,000 of gain on an average invested balance of about ₹1,25,000, earned over about two years:

$\frac{35{,}000}{1{,}25{,}000} \approx 28\% \text{ over two years} \approx 13\% \text{ a year}$

An XIRR over the actual 24 dated contributions plus today's value gives approximately 13% a year — close to the naive 14.6% by coincidence of arithmetic, and nearly double the "annualised" 7.3% that halving produces.

The coincidence is worth naming, because it is how the naive method survives: for a SIP of roughly two years, total-gain-over-total-contributions happens to land near the true annual rate, since the two errors — ignoring timing and not annualising — push in opposite directions and partly cancel. Change the horizon and the cancellation fails. Over ten years the naive figure is wildly too high as a total and wildly too low as a rate; over six months it is too low on both counts. A method that is right by accident at one horizon is not a method.

The point

CAGR needs one start value and one end value, so it cannot measure a stream of contributions on different dates. XIRR solves for the single annual rate that makes every dated flow consistent with the current value, crediting each rupee for exactly the time it was present, which makes it directly comparable with any other annual rate. Build the list from your own statement with contributions negative, receipts positive and today's value as a flow on today's date. Dividing total gain by total contributions uses a denominator roughly twice too large, and the fact that it lands near the right answer for a two-year SIP is an arithmetic coincidence that fails at any other horizon.