IRR & MIRR Rate of Return Formulas
Learn how to calculate Internal Rate of Return in Excel with IRR, XIRR, and MIRR. Handle irregular venture capital cash flows and realistic reinvestment rates.
Private equity investors, venture capitalists, and corporate acquisition teams compare deals by examining the Internal Rate of Return (IRR). IRR represents the annualized compounded return rate that sets the Net Present Value (NPV) of all cash inflows and outflows to zero.
However, traditional IRR assumes payments occur on strict, identical yearly intervals and that intermediate dividends can be reinvested at the same high IRR rate. Excel provides two advanced formulas to address these real-world conditions: XIRR and MIRR.
Quick Comparison: Return Rate Trio
| Formula | Cash Flow Timing | Reinvestment Assumption | Best Fit in Real Work |
|---|---|---|---|
IRR(values) |
Uniform annual periods | Reinvested at IRR rate (unrealistic) | Standard academic textbook valuation models. |
XIRR(values, dates) |
Exact calendar dates | Compounded daily across real calendar | Venture capital, private equity, and irregular project exits. |
MIRR(values, fin_rate, reinv_rate) |
Uniform periods | Reinvested at market safe rate (e.g. 5%) | Realistic corporate capital budgeting when cash is put back into money markets. |
Essential Mechanics and Requirements
1. Sign Rule Requirement
Both IRR and XIRR require at least one negative cash flow (the investment outlay) and at least one positive cash flow (revenue or exit proceeds). If all numbers are positive or all are negative, Excel returns a #NUM! error.
2. The Power of XIRR for Real Investments
Because startup funding rounds and dividend distributions happen on irregular days of the week, XIRR produces an exact annualized return by tracking the actual number of days elapsed between dates:
=XIRR(C2:C6, B2:B6)Hands-on Practice Playground
Use the venture capital cash flow table below to calculate standard IRR, calendar-exact XIRR, and realistic MIRR. Follow each objective to complete the exercise.
In cell D2, calculate the annualized return across cash flows C2:C6 using standard IRR.
In cell D2, calculate the annualized return assuming uniform periods across cash flow stream C2:C6 using the IRR formula.
In cell E2, calculate the true annualized return using XIRR with dates B2:B6.
In cell E2, calculate the true annualized internal rate of return reflecting exact calendar timing using XIRR with cash flows C2:C6 and date schedule B2:B6.
In cell F2, calculate the realistic return with MIRR using an 8% finance rate and 5% reinvestment rate.
In cell F2, calculate the modified internal rate of return for cash flows C2:C6 assuming an 8% finance rate and a 5% realistic reinvestment rate using MIRR.
Ready to apply IRR & MIRR Rate of Return Formulas in a real workbook?
Put professional financial formulas into practice inside an executive cash flow model.
Open 12-Month Cash Flow StatementShare This Lesson
Spread the word with your peers and challenge your friends