Free tool · live IRR
IRR (internal rate of return), XIRR (IRR on exact dates) and NPV (net present value), in one place.
For monthly flows, the tool turns the monthly IRR into a yearly one: (1 + monthly IRR)12 − 1.
The short version
Quick answer: IRR (internal rate of return) is the return per period, usually a year, that makes NPV (net present value) zero. In Excel, use =IRR(values) for equal periods and =XIRR(values, dates) for exact dates. Illustrative: pay ₹1,00,000, get back ₹30,000, ₹35,000, ₹40,000 and ₹25,000 over four years. The IRR is about 11.54% a year.
Guide
IRR, explained from zero
IRR is the interest rate your investment earns on its own. It answers one question: “What yearly return did this money make?” It works for any pattern of money out and money in. That includes many payments, not just one deposit and one payout.
Think of a fixed deposit (FD). You know its rate, say 7% (illustrative). IRR finds the “FD rate” hidden inside any investment. Then you can compare it with that FD, a loan, or your hurdle rate.
If the IRR is higher than the return you need, the investment adds value. If it is lower, it does not.
There is no direct formula you can solve by hand. IRR is the rate that makes the NPV zero: every cash flow, brought back to today, adds up to nothing. You find it by trying rates until NPV hits zero.
0 = CF0 + CF1 ÷ (1 + IRR)1 + CF2 ÷ (1 + IRR)2 + … + CFn ÷ (1 + IRR)n
CF means cash flow. CF0 is today’s amount, usually negative because you pay it. CF1 comes after one period, and so on. Excel and this tool make the guesses for you.
Illustrative example: at 11.54%, the four inflows below are worth exactly ₹1,00,000 today.
| Year | Cash flow | ÷ (1 + IRR)year | Value today |
|---|---|---|---|
| 0 | −₹1,00,000 | 1.0000 | −₹1,00,000 |
| 1 | ₹30,000 | 1.1154 | ₹26,896 |
| 2 | ₹35,000 | 1.2442 | ₹28,131 |
| 3 | ₹40,000 | 1.3878 | ₹28,823 |
| 4 | ₹25,000 | 1.5480 | ₹16,150 |
| Total | ₹30,000 | ≈ ₹0 |
Plain takeaway: at the IRR, what you get back is worth exactly what you put in.
Put the cash flows in one column, today’s outflow first, as a negative number. Then type =IRR(B2:B6). Excel reads the cells in order and treats them as one period apart. It needs at least one negative and one positive value. Blank cells and text are skipped.
Illustrative: in the example above, =NPV(10%, B2:B6) shows ₹3,024. The right NPV is ₹3,326. The calculator gives you both formulas, sized to your rows.
Use XIRR when money moves on real calendar dates that are not evenly spaced. SIP (systematic investment plan) instalments, a part-payment on a flat, or a bonus paid in March all fit. XIRR counts the actual days between payments, so each rupee is timed exactly.
0 = Σ Pi ÷ (1 + XIRR)(di − d1) ÷ 365
Pi is each payment and di its date. Excel discounts every payment on a 365-day year from the first date. In Excel, type =XIRR(B2:B14, A2:A14), with dates in column A. Its partner, XNPV, is NPV on exact dates.
Illustrative: ₹10,000 a month for 12 months, worth ₹1,30,000 a month after the last one. The simple gain is 8.33%. The XIRR is about 15.67% a year, because most instalments were invested for under a year.
Excel returns #NUM! if any date comes before the first one. This tool sorts the dates for you and tells you.
Count how often your cash flows switch between negative and positive. A rule from algebra (Descartes’ rule of signs) says there can be at most that many IRRs. One switch, the usual “pay first, collect later” shape, gives at most one.
| Cash flows | Sign switches | What IRR does |
|---|---|---|
| −1,00,000 · 30,000 · 35,000 · 40,000 · 25,000 | 1 | One IRR: 11.54% |
| −1,00,000 · 2,30,000 · −1,32,000 | 2 | Two IRRs: 10% and 20% |
| 1,000 · 500 · 200 | 0 | No IRR at all |
Plain takeaway: more than one sign switch means IRR can mislead, so decide with NPV or MIRR.
MIRR (modified IRR) fixes this. It assumes you borrow for the outflows at a finance rate and reinvest the inflows at a reinvestment rate. In Excel: =MIRR(values, finance_rate, reinvest_rate).
Not always. IRR is a percentage, so it ignores size. Earning 40% on ₹10,000 adds less money than 15% on ₹10 lakh (illustrative). When you can pick only one of two choices, pick the one with the higher NPV at your hurdle rate.
IRR also implicitly assumes money that comes back early is reinvested at the IRR itself. MIRR lets you set your own reinvestment rate instead.
Use IRR as a quick signal, and NPV for the final call.
It scans rates from −99% to 10,000%, and further out if none turns up, and narrows in on every rate where the NPV crosses zero. So it finds all the IRRs, not just the one nearest a guess. If no rate works, it says so plainly instead of showing an error code.
Equal periods follow Excel’s IRR, NPV and MIRR. Dated flows follow XIRR and XNPV, with a 365-day year from the first date. We tested it against Microsoft’s own worked examples. Monthly, quarterly or half-yearly IRRs are turned into a yearly rate.
Your numbers stay in your browser until you ask for the full breakdown.
FAQs
There is no single good IRR. Compare it with your hurdle rate: the return you could earn elsewhere at a similar risk. For the usual pay-first, collect-later shape, an IRR above that rate means a positive NPV.
IRR assumes every cash flow is exactly one period apart. XIRR uses the real date of each cash flow and counts the days on a 365-day year. Use XIRR for SIPs and any payments on uneven dates.
ROI (return on investment) is total gain divided by cost. It ignores how long the money was invested. IRR is a yearly rate that accounts for when each rupee went out and came back.
Yes. A negative IRR means you got back less than you put in. In Microsoft’s own example, −70,000 followed by four inflows totalling 66,000 gives an IRR of about −2.1%.
Your values may lack a negative or a positive number. Or Excel could not find an answer within 20 tries. Check the signs, or add a guess as the second argument, like =IRR(B2:B6, 20%).
For one investment and one payout, yes, almost. CAGR (compound annual growth rate) is the yearly growth from start to end. XIRR gives the same rate, counting a 365-day year. With many payments, only XIRR works.
This calculator is for learning and planning only. It is not investment advice. Results depend on the cash flows and rates you enter; the ₹ examples on this page are illustrative. Excel is a trademark of Microsoft Corporation, which does not endorse QuintEdge.
