IRR vs XIRR vs MIRR

IRR answers one specific question: what single discount rate makes this project's NPV zero? It's the project's own break-even rate of return, and you compare it against a hurdle rate to accept or reject. Run =IRR(values, [guess]) on the stream −120000, +50000, +45000, +40000, +30000, equally spaced from t = 0 to t = 4, and it returns 15.43%. If your cost of capital is 10%, the project clears its hurdle.

What does IRR assume, and how does MIRR fix it?

But IRR carries a buried and often unrealistic assumption, and you should be able to state it on demand:

IRR implicitly assumes each interim cash flow is reinvested at the IRR itself.

That's fine when the IRR is close to your real reinvestment rate. For a headline 40% deal, though, it assumes you can redeploy every distribution at 40%, and you almost never can. So it overstates the true economic return of high-IRR projects. MIRR (modified IRR) exists to remove the fantasy. It lets you set two realistic rates separately:

MIRR(values, finance_rate, reinvest_rate)

finance_rate is the cost of the money you pay out, used to discount the negative flows to t = 0. reinvest_rate is the rate you can actually reinvest the positive flows at, used to compound them forward to the final period. On the same stream, with a 10% finance rate and an 8% reinvestment rate, =MIRR(values, 0.10, 0.08) = 11.98%. That's well below the 15.43% IRR, because the interim cash is now assumed to earn a realistic 8% instead of an imaginary 15.43%. You can check every step by hand. Compound the positive flows to the end at 8% (₹50k×1.08³ + ₹45k×1.08² + ₹40k×1.08 + ₹30k = ₹1,88,673.60). Then take the ratio to the ₹1,20,000 outlay and annualise over the four periods: (188673.60 / 120000)^(1/4) − 1 = 11.98%.

Which should you use: IRR, XIRR or MIRR?

And XIRR is simply IRR for dated flows. It's the same "rate that zeroes the NPV" idea, but it honours the real calendar, as the XIRR function in Excel showed. So the professional's decision tree is short. Dated flows go to XIRR. Equal periods where you accept the reinvestment assumption go to IRR. Equal periods where you want a realistic reinvestment rate go to MIRR. Whenever the IRR is high, report IRR alongside MIRR, so the reader sees both the headline and the realistic number.