XIRR Formula Explained with Examples (India)

Behind the simple =XIRR spreadsheet function lies an elegant piece of financial mathematics. Understanding the formula demystifies why XIRR is the gold standard for measuring SIP and irregular-investment returns in India, and helps you troubleshoot when a calculation misbehaves. This article explains the XIRR equation in plain language, shows how the iterative solution works, and walks through rupee examples so the mechanics click.

In everyday use you will rely on a XIRR calculator or spreadsheet, but the formula below is what those tools are actually solving under the hood.

Expert insight: XIRR is simply the discount rate that makes all your dated cash flows balance to zero. If you can picture that, you understand the entire formula.

The XIRR Equation

The formal XIRR formula is written as the sum, over all cash flows, of each cash flow discounted back to the first date:

Σ CFi ÷ (1 + r)^(di / 365) = 0

Here, CFi is the i-th cash flow (negative for investments, positive for redemptions and current value), di is the number of days between the first cash flow and the i-th cash flow, and r is the annualised rate we are solving for. The exponent di/365 converts the day gap into a fraction of a year, which is what makes XIRR sensitive to exact dates rather than assuming equal periods.

Why There Is No Direct Solution

Unlike a simple interest formula, you cannot rearrange the XIRR equation to write r = something, because r appears inside multiple exponents with different powers. Instead, software uses numerical methods, typically the Newton-Raphson iteration, to try a rate, check how far the sum is from zero, adjust the rate, and repeat until the equation balances. This is why XIRR is always computed by a tool rather than a single hand calculation, and why an optional guess value can help the process start close to the answer.

Worked Example: Two Cash Flows

Start with the simplest case. You invest ₹1,00,000 on 1 January 2024 and it grows to ₹1,15,000 by 31 December 2024, which is 365 days later. The equation becomes −1,00,000 + 1,15,000 ÷ (1 + r)^(365/365) = 0. Solving, (1 + r) = 1,15,000 ÷ 1,00,000 = 1.15, so r = 15%. With exactly one year between two flows, XIRR equals the simple annual return, confirming the formula behaves sensibly.

Worked Example: Uneven Dates

Now suppose you invest ₹50,000 on 1 January 2024 and another ₹50,000 on 1 July 2024 (182 days later), and the folio is worth ₹1,08,000 on 31 December 2024 (365 days from the first flow). The equation is −50,000 − 50,000÷(1+r)^(182/365) + 1,08,000÷(1+r)^(365/365) = 0. No algebra can isolate r, so a solver iterates and finds r of roughly 11.4%. The second installment, invested for only half the year, is discounted by a smaller exponent, which is exactly the date-weighting XIRR is designed to capture.

How the Exponent Works

The exponent is the engine of XIRR. A recent installment has a small exponent, so it is discounted only slightly; an old installment has a large exponent and is discounted more. This is how the formula gives each rupee its fair, time-weighted contribution to the overall return.

Relationship to IRR

XIRR is the extended, date-aware cousin of the internal rate of return (IRR). Plain IRR assumes cash flows occur at regular, equal intervals, which almost never matches a real SIP where dates drift and top-ups happen. XIRR replaces the assumption of equal periods with actual day counts, making it the correct choice for Indian mutual fund investing. When cash flows genuinely are annual and equal, XIRR and IRR agree.

Benefits of Understanding the Formula

Knowing the equation helps you interpret unusual results, such as why a high absolute gain over a short period produces an even higher XIRR, or why an error appears when all your cash flows share the same sign. It also builds confidence that the platform figure on your statement is not a black box but a transparent, date-weighted calculation you could reproduce. For anyone planning goals against their tax situation, this clarity makes return comparisons far more meaningful.

Challenges and Limitations

The iterative nature of XIRR means it can occasionally fail to converge, returning an error if the guess is poor or if the cash flows never change sign. It also assumes reinvestment at the same rate, which can flatter very high returns. Because it is expressed as a single annual figure, XIRR hides the volatility along the way, so two funds with identical XIRR can have very different risk profiles.

Common Mistakes to Avoid

• Expecting a closed-form answer. XIRR must be solved iteratively; there is no single formula for r.
• Same-sign cash flows. If nothing is negative or nothing is positive, the equation has no valid root.
• Measuring time in months. The formula uses days over 365; approximating in months introduces error.
• Omitting the current value. Without a final positive cash flow, an unredeemed portfolio cannot be solved.
• Confusing XIRR with IRR. IRR assumes equal periods; XIRR uses actual dates.
• Ignoring the guess parameter. Supplying a reasonable guess helps unusual data sets converge.

Best Practices and Expert Recommendations

• Anchor dates to the first cash flow. Measure di from the earliest transaction for consistency.
• Keep at least one negative and one positive flow. This guarantees the equation has a solution.
• Use precise transaction dates. Day-level accuracy is what makes XIRR reliable.
• Add a guess when errors appear. A value like 0.1 often resolves convergence issues.
• Cross-check simple cases by hand. A one-year, two-flow example should equal the simple return.
• Interpret alongside risk. Treat XIRR as one input, not the whole story.

A Quick Sense-Check for Your Own Portfolio

Once you understand the formula, a simple sanity check keeps you honest. Add up everything you invested and compare it with your current value to get the absolute gain, then ask whether your XIRR figure is plausible given how long that money has been invested on average. If your SIP is only a year old and shows a 10% absolute gain, an XIRR near 18–19% is reasonable because the average holding period is roughly half a year. If a five-year-old portfolio shows a 60% absolute gain, an XIRR around 10% is consistent. When the XIRR looks wildly out of line with this rough logic, it usually points to a wrong date, a missing cash flow, or a sign error, and the formula tells you exactly where to look.

Frequently Asked Questions

What is the mathematical formula for XIRR?
XIRR solves Σ CFi ÷ (1 + r)^(di/365) = 0, where CFi is each dated cash flow, di is the number of days from the first cash flow, and r is the annualised return being solved for.

Why can XIRR not be solved directly?
Because the unknown rate r appears inside several exponents with different powers, it cannot be isolated algebraically. Software uses iterative methods like Newton-Raphson to find the rate that balances the equation.

What does the guess value do?
The guess gives the iterative solver a starting point. Most of the time the default works, but for unusual cash flows a guess such as 0.1 (10%) helps the calculation converge instead of returning an error.

Is XIRR the same as IRR?
XIRR is IRR with actual dates. IRR assumes cash flows fall at equal intervals, while XIRR uses exact day counts, making XIRR the right tool for irregular SIPs and top-ups.

Does the formula use 360 or 365 days?
The standard XIRR convention annualises using 365 days, so the exponent for each cash flow is the number of days from the first flow divided by 365.