Use realistic rates (≥0.01) or switch to the manual array formula if rates are zero or you’re on older Excel.
Use realistic rates (≥0.01) or switch to the manual array formula if rates are zero or you’re on older Excel.
- Zero or negative rates: If either your financing or reinvestment rate is zero, Excel throws a #DIV/0! error. Stick with rates of 0.01 or higher.
- Older Excel versions: The MIRR function has been around since Excel 2007, but if you’re stuck on an older build, use this manual array formula instead:
=((SUMIF(A1:A10,">0",A1:A10)*(1+C1)^(COUNTIF(A1:A10,">0")-1))/ABS(SUMIF(A1:A10,"<0",A1:A10)))^(1/COUNTIF(A1:A10,"<>0"))-1
- Multiple projects: Copy the same formula down each project row, then run a sensitivity test on the reinvestment rate in one shot using Data ➔ Forecast ➔ What-If Analysis ➔ Data Table.
Yes—MIRR ranks projects more reliably than IRR, but always sanity-check your input rates against market data.
Yes—MIRR ranks projects more reliably than IRR, but always sanity-check your input rates against market data.
Generally, yes—MIRR ranks projects more reliably than IRR because it avoids multiple rates. That said, MIRR still depends on the two rates you plug in. Feed it unrealistic inputs and the rankings can flip, so always sanity-check against current market data.
Use your company’s WACC for the financing rate and a conservative market rate or expected return for reinvestment.
Use your company’s WACC for the financing rate and a conservative market rate or expected return for reinvestment.
For the financing rate, use your company’s weighted average cost of capital (WACC). For the reinvestment rate, many firms use their expected return on new projects or a conservative market rate. Honestly, this is the best approach—it keeps assumptions consistent across the company.
Build a template that automatically pulls your firm’s current WACC into both the financing and reinvestment rate cells.
Build a template that automatically pulls your firm’s current WACC into both the financing and reinvestment rate cells.
Create a template that automatically pulls your firm’s current WACC into both the financing and reinvestment rate cells. That way every analyst uses the same baseline numbers. (No more “my IRR was different because I used a different rate” excuses.)
Set conditional formatting to flash red for #DIV/0! or #VALUE! errors to catch data-entry mistakes early.
Set conditional formatting to flash red for #DIV/0! or #VALUE! errors to catch data-entry mistakes early.
Add conditional formatting so any cell that returns #DIV/0! or #VALUE! flashes red. You’ll catch mistakes before they hit the CFO’s desk—saving everyone a last-minute scramble.
Schedule an annual review; rerun the model if your cost of capital moves more than 50 basis points.
Schedule an annual review; rerun the model if your cost of capital moves more than 50 basis points.
Schedule an annual review. If your firm’s cost of capital shifts more than 50 basis points, rerun the model so your capital-budgeting decisions stay in sync with today’s market.
MIRR handles most uneven cash flows, but extreme alternating patterns may still yield odd results—pair with NPV to double-check.
MIRR handles most uneven cash flows, but extreme alternating patterns may still yield odd results—pair with NPV to double-check.
MIRR handles most uneven cash flows just fine, but extreme patterns—like alternating large inflows and outflows—can still give odd results. In those cases, pair MIRR with NPV to double-check your ranking.
Use NPV for short-term projects; reserve MIRR for longer-term investments where reinvestment assumptions matter.
Use NPV for short-term projects; reserve MIRR for longer-term investments where reinvestment assumptions matter.
Short-term projects usually don’t need MIRR—NPV is simpler and just as reliable. Save MIRR for longer-term investments where reinvestment assumptions really move the needle.
Excel 365 or 2021 users can use the built-in MIRR function; others must build the manual array formula.
Excel 365 or 2021 users can use the built-in MIRR function; others must build the manual array formula.
Check your Excel version first. If you have Excel 365 or 2021, the built-in MIRR function is ready to go. Otherwise, build the manual array formula I showed earlier—it’s clunky, but it works.
The biggest mistake is using the same rate for both financing and reinvestment—always use different, realistic rates.
The biggest mistake is using the same rate for both financing and reinvestment—always use different, realistic rates.
Plugging in the same rate for financing and reinvestment defeats the whole point. Use different, realistic rates—your financing cost shouldn’t match your reinvestment return.
Yes—once the formula and toggle cells are set, copy down your project list and run a sensitivity table on the reinvestment rate.
Yes—once the formula and toggle cells are set, copy down your project list and run a sensitivity table on the reinvestment rate.
Absolutely. Once you’ve built the formula and toggle cells, copy it down your project list. Then run a quick sensitivity table on the reinvestment rate to see how rankings shift—all in one step.
Explain MIRR to leadership as “IRR, but smarter—it gives one clean rate you can compare across projects.”
Explain MIRR to leadership as “IRR, but smarter—it gives one clean rate you can compare across projects.”
Tell them it’s like IRR, but smarter. While IRR can give multiple answers, MIRR gives one clean rate you can compare across projects. That makes it easier to defend your capital-budgeting decisions to leadership.
MIRR replaces IRR by giving you one reliable rate when cash flows zigzag between inflows and outflows.
MIRR replaces IRR by giving you one reliable rate when cash flows zigzag between inflows and outflows.
MIRR replaces IRR by giving you one reliable rate when cash flows zigzag between inflows and outflows. IRR can spit out multiple rates when cash flows flip-flop between positive and negative, which makes comparing projects a nightmare. Modified IRR fixes this by discounting all outflows at your financing rate and compounding all inflows at your reinvestment rate—boom, one clean percentage you can actually trust.
Plug =MIRR(cash_flows, financing_rate, reinvestment_rate) into Excel instead of IRR when you need a realistic reinvestment rate and just one return number per project.
Enter =MIRR(A1:A10, B1, C1) in Excel 365 (build 2026) to calculate a single MIRR.
Enter =MIRR(A1:A10, B1, C1) in Excel 365 (build 2026) to calculate a single MIRR.
Here’s the fastest way to do it:
- Fire up Excel 365 (build 2026) and drop your cash flows in column A—start with the initial outflow in cell A1.
- In any empty cell, type the MIRR formula exactly like this:
=MIRR(A1:A10, B1, C1)
• A1:A10: your cash-flow range
• B1: your financing (borrowing) rate as a decimal, e.g., 0.06 for 6 %
• C1: your reinvestment rate as a decimal, e.g., 0.10 for 10 %
- Hit Enter and you’ll get one clean percentage—usually lower than the IRR you got before.
- (Optional) Add a toggle cell so you can tweak both rates without rewriting the formula every time.
Fix #DIV/0! errors by using realistic rates ≥ 0.01 and fall back to the manual array formula in older Excel versions.
Fix #DIV/0! errors by using realistic rates ≥ 0.01 and fall back to the manual array formula in older Excel versions.
- Zero or negative rates: If either your financing or reinvestment rate is zero, Excel throws a #DIV/0! error. Stick with rates of 0.01 or higher.
- Older Excel versions: The MIRR function has been around since Excel 2007, but if you’re stuck on an older build, use this manual array formula instead:
=((SUMIF(A1:A10,">0",A1:A10)*(1+C1)^(COUNTIF(A1:A10,">0")-1))/ABS(SUMIF(A1:A10,"<0",A1:A10)))^(1/COUNTIF(A1:A10,"<>0"))-1
- Multiple projects: Copy the same formula down each project row, then run a sensitivity test on the reinvestment rate in one shot using Data ➔ Forecast ➔ What-If Analysis ➔ Data Table.
Yes—MIRR generally ranks projects more reliably than IRR because it avoids multiple rates.
Yes—MIRR generally ranks projects more reliably than IRR because it avoids multiple rates.
MIRR usually ranks projects more reliably than IRR because it never gives you multiple rates. That said, MIRR still depends on the two rates you plug in—feed it unrealistic inputs and the rankings can flip, so always sanity-check against current market data.
Use your company’s WACC for financing and a conservative market rate or expected project return for reinvestment.
Use your company’s WACC for financing and a conservative market rate or expected project return for reinvestment.
For the financing rate, use your company’s weighted average cost of capital (WACC). For the reinvestment rate, many firms use their expected return on new projects or a conservative market rate.
Build a template that auto-populates the firm’s current WACC into both rate cells to keep all analysts consistent.
Build a template that auto-populates the firm’s current WACC into both rate cells to keep all analysts consistent.
Create a template that automatically pulls your firm’s current WACC into both the financing and reinvestment rate cells. That way every analyst uses the same baseline numbers.
Set conditional formatting to flag #DIV/0! or #VALUE! cells in red to catch data-entry errors early.
Set conditional formatting to flag #DIV/0! or #VALUE! cells in red to catch data-entry errors early.
Add conditional formatting so any cell that returns #DIV/0! or #VALUE! flashes red. You’ll catch mistakes before they hit the CFO’s desk.
Update your reinvestment assumption annually or when WACC moves more than 50 basis points.
Update your reinvestment assumption annually or when WACC moves more than 50 basis points.
Schedule an annual review. If your firm’s cost of capital shifts more than 50 basis points, rerun the model so your capital-budgeting decisions stay in sync with today’s market.
MIRR handles most uneven cash flows, but extreme patterns may still require NPV validation.
MIRR handles most uneven cash flows, but extreme patterns may still require NPV validation.
MIRR handles most uneven cash flows just fine, but extreme patterns—like alternating large inflows and outflows—can still give odd results. In those cases, pair MIRR with NPV to double-check your ranking.
No—use NPV for short-term projects; reserve MIRR for longer-term investments where reinvestment assumptions matter.
No—use NPV for short-term projects; reserve MIRR for longer-term investments where reinvestment assumptions matter.
Short-term projects usually don’t need MIRR—NPV is simpler and just as reliable. Save MIRR for longer-term investments where reinvestment assumptions really move the needle.
Excel 365 and 2021 include the built-in MIRR function; otherwise use the manual array formula.
Excel 365 and 2021 include the built-in MIRR function; otherwise use the manual array formula.
Check your Excel version first. If you have Excel 365 or 2021, the built-in MIRR function is ready to go. Otherwise, build the manual array formula I showed earlier—it’s clunky, but it works.
The biggest mistake is using the same rate for both financing and reinvestment.
The biggest mistake is using the same rate for both financing and reinvestment.
Plugging in the same rate for financing and reinvestment defeats the whole point. Use different, realistic rates—your financing cost shouldn’t match your reinvestment return.
Yes—once set up, copy the formula down your project list and run a sensitivity table on the reinvestment rate.
Yes—once set up, copy the formula down your project list and run a sensitivity table on the reinvestment rate.
Absolutely. Once you’ve built the formula and toggle cells, copy it down your project list. Then run a quick sensitivity table on the reinvestment rate to see how rankings shift—all in one step.
Explain MIRR to leadership as “IRR, but smarter—one clean rate for fair project comparisons.”
Explain MIRR to leadership as “IRR, but smarter—one clean rate for fair project comparisons.”
Tell them it’s like IRR, but smarter. While IRR can give multiple answers, MIRR gives one clean rate you can compare across projects. That makes it easier to defend your capital-budgeting decisions to leadership.
MIRR is preferred over IRR when cash-flow direction reverses; otherwise NPV suffices.
MIRR is preferred over IRR when cash-flow direction reverses; otherwise NPV suffices.
MIRR is preferred over IRR when cash-flow direction reverses; otherwise NPV suffices.
According to the Corporate Finance Institute, MIRR addresses IRR’s multiple-rate limitation by assuming reinvestment at a separate, realistic rate.
The Investopedia entry on MIRR further notes that MIRR provides a single rate that better reflects economic reality.
Research published by the Journal of Applied Corporate Finance demonstrates that MIRR rankings align more closely with NPV-based decisions than IRR rankings do.
Modified NPV calculates the terminal value of the project’s cash inflows using explicitly defined reinvestment rates that reflect the profitability of investment opportunities ahead of the firm.
Modified NPV calculates the terminal value of the project’s cash inflows using explicitly defined reinvestment rates that reflect the profitability of investment opportunities ahead of the firm.
Modified NPV calculates the terminal value of the project’s cash inflows using explicitly defined reinvestment rates that reflect the profitability of investment opportunities ahead of the firm.
The modified internal rate of return (MIRR) is a financial yardstick that gauges an investment’s attractiveness and lets you compare different projects.
The modified internal rate of return (MIRR) is a financial yardstick that gauges an investment’s attractiveness and lets you compare different projects.
The modified internal rate of return (MIRR) is a financial yardstick that gauges an investment’s attractiveness and lets you compare different projects. Capital budgeting uses MIRR to test whether a project is worth pursuing.
A variation called MIRR fixes IRR’s biggest flaw and gives managers tighter control over the assumed reinvestment rate of future cash flows.
A variation called MIRR fixes IRR’s biggest flaw and gives managers tighter control over the assumed reinvestment rate of future cash flows.
A variation called MIRR fixes IRR’s biggest flaw and gives managers tighter control over the assumed reinvestment rate of future cash flows. The standard IRR can overstate a project’s future value, but MIRR doesn’t.
The MIRR formula in Excel is =MIRR(cash flows, financing rate, reinvestment rate).
The MIRR formula in Excel is =MIRR(cash flows, financing rate, reinvestment rate).
The MIRR formula in Excel is:
=MIRR(cash flows, financing rate, reinvestment rate)
Cash flows are the individual cash flows from each period. The financing rate is the cost of borrowing when cash flows turn negative.
NPV beats IRR because it handles multiple discount rates without breaking a sweat.
NPV beats IRR because it handles multiple discount rates without breaking a sweat.
NPV beats IRR in the example above because it handles multiple discount rates without breaking a sweat. Each year’s cash flow can be discounted separately, which makes NPV the better method.
MIRR usually paints a clearer profit picture than IRR.
MIRR usually paints a clearer profit picture than IRR.
Both methods aim to pick the best project, but MIRR usually paints a clearer profit picture than IRR. It does this by reinvesting cash flows at the cost of capital and avoiding the multiple-rate trap that trips up IRR.
The modified internal rate of return (MIRR) assumes positive cash flows are reinvested at the firm’s cost of capital and initial outlays are financed at the firm’s borrowing cost.
The modified internal rate of return (MIRR) assumes positive cash flows are reinvested at the firm’s cost of capital and initial outlays are financed at the firm’s borrowing cost.
The modified internal rate of return (MIRR) assumes positive cash flows are reinvested at the firm’s cost of capital and initial outlays are financed at the firm’s borrowing cost. In short, MIRR mirrors real-world financing and reinvestment more closely.
To calculate MIRR for each project, use MIRR = (Future value of positive cash flows / present value of negative cash flows)^(1/n) – 1.
To calculate MIRR for each project, use MIRR = (Future value of positive cash flows / present value of negative cash flows)^(1/n) – 1.
To calculate MIRR for each project, Helen uses:
MIRR = (Future value of positive cash flows / present value of negative cash flows)^(1/n) – 1.
MIRR is almost always lower than IRR, and many argue it makes a more realistic assumption about the reinvestment rate.
MIRR is almost always lower than IRR, and many argue it makes a more realistic assumption about the reinvestment rate.
MIRR is almost always lower than IRR, and many argue it makes a more realistic assumption about the reinvestment rate. One side effect is that the project may not be able to generate cash flows as predicted, which can overstate the project’s NPV.
MIRR fixes two big IRR problems: it eliminates multiple IRRs for projects with weird cash-flow timing and solves the reinvestment problem.
MIRR fixes two big IRR problems: it eliminates multiple IRRs for projects with weird cash-flow timing and solves the reinvestment problem.
Advantages. MIRR fixes two big IRR problems: it eliminates multiple IRRs for projects with weird cash-flow timing and solves the reinvestment problem we just talked about. It also shows how sensitive an investment is to changes in the cost of capital.
The advantages of internal rate of return include capturing the time value of money, being simple to use, and not requiring a hurdle rate up front.
The advantages of internal rate of return include capturing the time value of money, being simple to use, and not requiring a hurdle rate up front.
-
Advantage: Captures the time value of money.
-
Advantage: Simple to use and understand.
-
Advantage: Doesn’t require a hurdle rate up front.
-
Disadvantage: Ignores the size of the project.
-
Disadvantage: Ignores future costs.
NPV doesn’t care about reinvestment assumptions, so changes in the reinvestment rate don’t move the net present value.
NPV doesn’t care about reinvestment assumptions, so changes in the reinvestment rate don’t move the net present value.
NPV doesn’t care about reinvestment assumptions, so changes in the reinvestment rate don’t move the net present value. IRR, on the other hand, assumes every cash flow is reinvested at the same rate the project earns, so even a small change in the reinvestment rate can swing IRR results.
MIRR removes the chance of multiple rates of return that plague IRR.
MIRR removes the chance of multiple rates of return that plague IRR.
Advantages of MIRR: It removes the chance of multiple rates of return that plague IRR. It also values project inflows at the company cost of capital and compounds them to a terminal value. Unlike IRR, it can handle any future cash flows that pop up during the project’s life.
To calculate the payback period use: Payback Period = Initial investment / Cash flow per year.
To calculate the payback period use: Payback Period = Initial investment / Cash flow per year.
To calculate the payback period use:
Payback Period = Initial investment / Cash flow per year
Example: Invest Rs 1,00,000 with an annual payback of Rs 20,000 → 1,00,000/20,000 = 5 years.
NPV is the dollar difference between the present value of cash inflows and outflows over time, while IRR is the single rate that makes those cash flows break even.
NPV is the dollar difference between the present value of cash inflows and outflows over time, while IRR is the single rate that makes those cash flows break even.
NPV and IRR both measure investment worth, but NPV is the dollar difference between the present value of cash inflows and outflows over time, while IRR is the single rate that makes those cash flows break even.
Corporate Finance Institute: Weighted Average Cost of Capital (WACC)
Edited and fact-checked by the TechFactsHub editorial team.
