How to How to Use MIRR Function in Excel in Excel - Practical Guide

Learn to use the MIRR function to calculate the modified internal rate of return for investments with different financing and reinvestment rates. MIRR provides a more accurate ROI measurement than IRR by accounting for actual borrowing and reinvestment costs, essential for evaluating complex investment scenarios.

Why This Matters

MIRR is critical for financial analysts and investors comparing projects with varying cash flow timing and cost structures. It provides a more realistic return metric than standard IRR when financing and reinvestment rates differ.

Prerequisites

•Basic understanding of Excel functions and cell references
•Knowledge of cash flow concepts and investment returns (IRR, NPV)
•Familiarity with your organization's finance rate assumptions

Step-by-Step Instructions

Prepare your cash flow data

Arrange your investment cash flows in a single column with initial investment as negative value in first cell, followed by positive/negative returns in chronological order.

Open the MIRR function dialog

Click on an empty cell where you want the result, then go to Formulas > Financial > MIRR or type =MIRR( directly in the cell.

Select values array parameter

Enter the range containing all your cash flows (e.g., A1:A10) as the first argument, which includes both investment and return periods.

Enter finance and reinvestment rates

Input your finance_rate (borrowing cost) as second argument and reinvest_rate (reinvestment return rate) as third argument; use percentages as decimals (e.g., 0.05 for 5%).

Execute and interpret results

Press Enter to calculate MIRR, which displays as a percentage; compare this modified return against project thresholds or alternative investments to make decisions.

Alternative Methods

Use Function Wizard

Click fx button next to formula bar, search 'MIRR', select it, and fill parameters through dialog boxes with tooltips for each argument.

Compare with IRR function

Calculate both MIRR and standard IRR side-by-side to see impact of different financing/reinvestment assumptions on your investment analysis.

Tips & Tricks

✓Always ensure your first cash flow is negative (initial investment) and use consistent time periods between cash flows
✓Document your finance_rate and reinvest_rate assumptions clearly; these drive significant differences from IRR results
✓Test sensitivity by calculating MIRR with different rate scenarios to understand risk impact

Pro Tips

★Use named ranges for your values, finance_rate, and reinvest_rate to create self-documenting formulas that are easier to audit and modify.
★Build a sensitivity table with MIRR calculations using Data > What-If Analysis > Data Table to visualize how rate changes affect project viability.
★Remember MIRR assumes reinvestment at the reinvest_rate, making it more realistic than IRR for long-term projects with interim positive cash flows.

Troubleshooting

#NUM! error appears

Check that you have both positive and negative cash flows; MIRR requires at least one of each to calculate a valid return. Verify your rates are between -1 and 1 as decimals.

#VALUE! error displayed

Ensure all cells in your values range contain numbers only; text or blank cells cause this error. Verify finance_rate and reinvest_rate are entered as decimals, not percentages.

MIRR result seems unusually high or low

Verify your finance_rate and reinvest_rate assumptions match your actual business conditions; small rate changes significantly impact MIRR results.

Related Excel Formulas

IRR NPV XIRR XNPV RATE PV FV

Frequently Asked Questions

What's the difference between MIRR and IRR?

IRR assumes cash flows are reinvested at the IRR rate itself; MIRR separately specifies financing and reinvestment rates, providing a more realistic return metric. MIRR is particularly useful when your company's actual borrowing and reinvestment rates differ significantly.

Can MIRR be negative?

Yes, MIRR can be negative if your cash outflows exceed inflows when accounting for the specified financing and reinvestment rates, indicating the investment destroys value.

Why does my MIRR differ from my company's target return?

Your finance_rate and reinvest_rate assumptions may not match actual costs; adjust these parameters to reflect real borrowing costs and internal reinvestment opportunities, then recalculate.

Is MIRR better than NPV for investment decisions?

MIRR and NPV serve different purposes; use NPV for absolute value assessment and MIRR for comparing percentage returns. For projects with different sizes, NPV is more reliable for ranking.