Financial

MIRR Function in Excel

Returns the modified internal rate of return for a series of periodic cash flows.

Syntax

  • =MIRR(values, finance_rate, reinvest_rate)

Arguments

  • values (required): Array of cash flows
  • finance_rate (required): Interest rate on borrowed funds
  • reinvest_rate (required): Interest rate on reinvested cash

Examples

  • =MIRR({-120000, 39000, 30000, 21000, 37000, 46000}, 0.1, 0.12) - Modified IRR - Result: 0.126

Excel MIRR function documentation

  • MIRR calculates modified internal rate of return using separate finance and reinvestment rates.
  • Syntax: =MIRR(values, finance_rate, reinvest_rate).
  • MIRR addresses one key limitation of [IRR](/functions/irr/) by using explicit reinvestment assumptions.
  • Related tools: [NPV](/functions/npv/), [XNPV](/functions/xnpv/), [PV](/functions/pv/), and [Financial functions](/categories/financial/).

MIRR inputs and interpretation

  • values must include at least one negative and one positive cash flow.
  • finance_rate applies to cash outflows (cost of capital).
  • reinvest_rate applies to positive cash inflows.
  • MIRR typically produces a single economically meaningful rate when IRR may have multiple roots.

Step-by-step: evaluate project with explicit rates

  • Step 1 — Place project cash flows in B2:B8 with initial investment negative.
  • Step 2 — Put finance rate in E1 and reinvestment rate in E2.
  • Step 3 — Calculate =MIRR(B2:B8, E1, E2).
  • Step 4 — Compare output against hurdle rate and alternative projects.
  • Step 5 — Cross-check ranking with [NPV](/functions/npv/) at your discount assumptions.

MIRR vs IRR vs XIRR

  • MIRR uses explicit finance and reinvestment assumptions.
  • IRR assumes reinvestment at IRR itself.
  • XIRR handles irregular dates but still follows IRR-style root finding.
  • For board reporting, MIRR can be easier to defend than raw IRR in non-conventional cash flows.

Worked examples to copy

  • Basic MIRR: =MIRR(B2:B7, 0.09, 0.06).
  • Named ranges: =MIRR(project_flows, wacc, reinvest).
  • Scenario test: change finance_rate from 8% to 12% and compare results.
  • Portfolio screen: compute MIRR per project and rank descending.

People also ask

  • When is MIRR preferred? — When reinvestment at IRR is unrealistic.
  • Can MIRR replace NPV? — Use both; MIRR is a rate metric, NPV is value metric.
  • MIRR #DIV/0 or #NUM? — Check sign mix and rate inputs.
  • Should I annualize MIRR? — It already reflects the periodic spacing of your data.

Common errors

  • Needs at least one positive and one negative value

Use cases

  • Project evaluation
  • Investment comparison
  • Capital budgeting

Frequently asked questions

  • What is MIRR and why use it over IRR? MIRR (Modified IRR) addresses IRR's flaws by using realistic reinvestment assumptions. IRR assumes cash flows are reinvested at the IRR itself (often unrealistic). MIRR lets you specify actual borrowing and reinvestment rates, giving more accurate project comparisons.
  • How do I choose finance_rate and reinvest_rate? Finance_rate: your cost of borrowing (loan rate, WACC). Reinvest_rate: realistic return on reinvested cash flows (savings rate, typical project return). Example: borrow at 8%, reinvest at 5%: =MIRR(cash_flows, 0.08, 0.05).
  • When does MIRR differ significantly from IRR? MIRR differs most when: cash flows alternate signs multiple times (IRR may have multiple solutions), IRR is very high (unrealistic reinvestment assumption), or project has large intermediate cash flows. MIRR always gives a single, realistic answer.

Editorial review

  • Reviewed by Excel.Directory Editorial Team. Updated May 2026.

When to use MIRR

  • Project evaluation — common Financial scenario for MIRR.
  • Investment comparison — common Financial scenario for MIRR.
  • Capital budgeting — common Financial scenario for MIRR.

MIRR in the Financial category

  • Browse all Financial functions at /categories/financial/ for related formulas.
  • MIRR syntax: =MIRR(values, finance_rate, reinvest_rate)
  • values (required): Array of cash flows
  • finance_rate (required): Interest rate on borrowed funds
  • reinvest_rate (required): Interest rate on reinvested cash
  • Confirm MIRR arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.

Formula checklist before you copy down

  • Confirm MIRR arguments match the syntax shown above before filling down.
  • Lock table and range references with $ when copying formulas across rows or sheets.
  • If results look wrong, check for text stored as numbers and invisible spaces with TRIM.
  • Spot-check three known input rows manually against expected output.

Related Excel functions

  • IRR (/functions/irr/): Returns the internal rate of return for a series of cash flows.
  • XIRR (/functions/xirr/): Returns the internal rate of return for a schedule of cash flows with specific dates.
  • NPV (/functions/npv/): Calculates the net present value of an investment based on a discount rate and future cash flows.

Errors to watch for

  • Needs at least one positive and one negative value — review causes on linked error pages in the directory.

Copy-paste audit workflow

  • Enter MIRR on three test rows with known expected output documented on a QA tab.
  • Fill down only after absolute references are locked on lookup tables and rate tables.
  • Compare against manual calculation or a calculator for financial and statistical functions.
  • Search this directory for comparison guides when choosing between similar functions in the same category.

MIRR worked examples to copy

  • =MIRR({-120000, 39000, 30000, 21000, 37000, 46000}, 0.1, 0.12) — Modified IRR. Expected result: 0.126.

MIRR reference summary for crawlers and offline review

  • MIRR belongs to the Financial category in Excel. Returns the modified internal rate of return for a series of periodic cash flows.
  • Full syntax: =MIRR(values, finance_rate, reinvest_rate). Open /functions/mirr/ for parameters, FAQs, and related pages.
  • Common mistakes: Needs at least one positive and one negative value
  • Pair this function with comparison guides when another Excel formula might fit the same task better.
  • Review fix-excel-formula-errors when unexpected errors appear after upgrading Excel or sharing across locales.