How to calculate your SIP's XIRR in a spreadsheet

Why do it yourself

Your account statement prints an XIRR for every SIP. So do the fund house's app and every portfolio tracker. They do not always agree, and when they don't, the quickest way to find out who is right is to rebuild the number yourself from the raw cash flows. It takes a dozen rows and one function.

This is a worked example on real NAVs, with the mistakes that make the answer go wrong. For what XIRR is and how it differs from CAGR and absolute return, read Absolute return, CAGR and XIRR first. This post covers how to calculate it.

The example SIP

The fund is the UTI Nifty 50 Index Fund, Direct Growth, one of the large index funds from UTI Mutual Fund. It is a plain choice: it tracks the Nifty 50, so the result says something about the market rather than about a manager. It is not a recommendation.

The SIP is ₹10,000 a month on the 5th, twelve instalments from October 2025 to September 2026. When the 5th was a weekend or a market holiday, the units were bought at the next business day's NAV, which is how the fund allots them (see cut-off timings and unit allotment). The holding is valued at the NAV of Friday 9 October 2026, ₹158.5759.

694.5604 units at ₹158.5759 are worth ₹1,10,141. You put in ₹1,20,000, so the position is down ₹9,859, an absolute return of -8.22%.

Build the cash-flow table

Open a blank sheet. Two columns, thirteen rows.

1. Column A, dates. Type each instalment date as a real date (06-10-2025), not as text. If Excel left-aligns it, it is text and the formula will fail.
2. Column B, amounts. Every instalment goes in as negative: -10000. Money leaving your bank account is a cash outflow.
3. Row 14, the closing value. Today's date (or the valuation date), and the current value as a positive number: 110141. This row stands in for selling everything. Without it there is nothing to measure the outflows against.
4. The formula. In any empty cell: =XIRR(B2:B14, A2:A14). Format the cell as a percentage.

Excel returns -14.56%. Google Sheets and LibreOffice use the same function and give the same answer. Our XIRR calculator gives -14.57%, because it counts a year as 365.25 days and Excel counts 365. That second-decimal difference is all you should expect between two correct tools.

Why -14.56% and not -8.22%

The two numbers measure different things. The -8.22% is the loss on the money, with no reference to time. XIRR is an annual rate, and it weights every rupee by how long it was actually invested.

Here, the first ₹10,000 was invested for a full year, but the last ₹10,000 for only 32 days. On average, each rupee was in the fund for about 201 days, a little over half a year. Losing 8.2% in about half a year is a much faster rate of loss than losing 8.2% over a whole year, so the annualised figure is larger.

The fund's own one-year return to 9 October 2026 was -9.76%, a point-to-point figure for a single lump sum. A lump sum of ₹1.2 lakh on 6 October 2025 would be worth ₹1,08,740 now, down 9.38%. The SIP lost less in rupees because its later instalments bought cheaper units, and it shows a worse XIRR because its money was exposed for a shorter time. Both are true at once. Why your SIP return differs from the fund's return walks through the same gap in a rising market.

The mistakes that give nonsense answers

A few more to watch:

• The first row must be the earliest date. Excel measures every other date from it, and a date before it returns #NUM!.
• Use the allotment date, not the debit date. A SIP debited on a Friday evening may be allotted on Monday. A day or two moves the second decimal place; across a long SIP it hardly matters, but it is the usual reason two trackers differ.
• Count the stamp duty. 0.005% of each purchase is deducted before units are allotted, so ₹10,000 buys ₹9,999.50 worth of units. Here it lowers the value to ₹1,10,135 and moves XIRR from -14.56% to -14.57%. Small, but it is why your statement's units never match a clean calculation.
• Don't annualise a month. XIRR on three instalments will turn a 2% move into a startling annual rate. Under a year, read the absolute return; past a year, the XIRR starts to mean something.
• Add redemptions as positive rows on their own dates, and reduce the closing value by what you sold. Leaving a withdrawal out makes the return look worse than it was.

A habit worth keeping

Download your consolidated account statement once a year and rebuild the table. It takes ten minutes and gives you a number you understand, rather than one you have to trust. If you want to see what the same SIP would have done in a different fund, the MF calculator backtests it on real NAVs and reports the XIRR, and the SIP calculator projects one forward at an assumed rate.

None of this is a view on the fund or on the market. One year of a Nifty 50 SIP that is currently under water says nothing about the next one; it is simply a clean set of cash flows to practise on.