What's the difference between MIRR and IRR?

IRR assumes all positive cash flows reinvest themselves at the IRR rate itself—often unrealistic. MIRR lets you specify separate reinvestment and financing rates, making it far more practical for real investments like marketing campaigns where you control where cash flows go and at what rate you can borrow.

What values should I use for finance_rate and reinvest_rate?

finance_rate is your cost of capital (what you pay to borrow or fund the investment)—often your company's discount rate or WACC. reinvest_rate is the realistic return on surplus cash (savings accounts, money-market funds, or other projects)—typically lower than optimistic IRR assumptions. When in doubt, use the same rate for both to be conservative.

Can MIRR be negative?

Yes. A negative MIRR means the investment loses value after accounting for borrowing costs and reinvestment assumptions—the project destroys wealth. This signals rejection unless non-financial factors (brand, strategic fit) override financial returns.

Why does MIRR matter when I can just calculate profit?

Profit ignores timing and cost of capital. MIRR lets you compare a $50,000 campaign to a $500,000 campaign on equal footing, and a 1-year project to a 3-year project. Two campaigns with identical profit but different cash flow patterns will have different MIRRs, guiding you to choose the one that uses capital most efficiently.

MIRR function

MIRR returns the modified internal rate of return, accounting for different borrowing and reinvestment rates on cash flows over time.

=MIRR(values, finance_rate, reinvest_rate)

Generate a MIRR formula

Describe what you need. The generator will reach for MIRR where MIRR is the right tool, and tell you when it is not.

Arguments

How MIRR reads its arguments

Argument	Required	Description
values	Required	An array of numeric cash flows where negative values represent investments or expenses and positive values represent returns or revenue. Must contain both positive and negative values; all same-sign flows will error.
finance_rate	Required	The annual borrowing or discount rate for negative cash flows, entered as a decimal (e.g., 0.08 for 8%). Represents the cost of capital funding the investment.
reinvest_rate	Required	The annual reinvestment rate for positive cash flows, entered as a decimal (e.g., 0.10 for 10%). Represents the return earned when positive cash flows are reinvested.

Returns

A decimal percentage representing the annualized rate of return (e.g., 0.1487 for 14.87%).

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Calculate campaign ROI accounting for capital cost and reinvestment

Period	Cash Flow
Q1 Campaign Spend	-50000
Q2 Revenue	15000
Q3 Revenue	35000
Q4 Revenue	25000

=MIRR(B2:B5, 0.08, 0.10)

Result: 0.1487 (or 14.87%)

The campaign generates 14.87% annualized return. MIRR accounts for the $50,000 upfront investment cost at 8% financing and assumes revenue reinvested at 10%. This is more realistic than simple IRR, which would ignore the distinct reinvestment rate.

2. See how reinvestment assumptions change the return metric

Period	Cash Flow
Q1 Campaign Spend	-50000
Q2 Revenue	15000
Q3 Revenue	35000
Q4 Revenue	25000

=MIRR(B2:B5, 0.08, 0.08)

Result: 0.0995 (or 9.95%)

Lowering reinvestment rate from 10% to 8% (matching finance rate) reduces MIRR to 9.95%. This demonstrates that optimistic reinvestment assumptions inflate returns; MIRR's dual-rate model reveals how sensitive ROI is to reinvestment expectations versus borrowing costs.

3. Compare different campaign spending strategies by MIRR

Period	Aggressive Spend	Conservative Spend
Q1	-75000	-35000
Q2	18000	10000
Q3	52000	20000
Q4	35000	18000

Aggressive: =MIRR(B2:B5, 0.08, 0.10) | Conservative: =MIRR(C2:C5, 0.08, 0.10)

Result: Aggressive: 16.30% | Conservative: 12.08%

The aggressive campaign with 50% higher spend achieves a higher MIRR (16.30% vs. 12.08%), despite larger upfront cost. MIRR enables fair comparison of strategies with different scales and timing—something raw profit cannot do alone.

Common errors

Which MIRR error are you seeing?

Error	Why it happens	How to fix it
#NUM!	All cash flows in the values array have the same sign (all positive or all negative). MIRR requires both investments (negative flows) and returns (positive flows) to mathematically compute a rate.	Restructure data to include initial outlays as negative values and subsequent revenues as positive. Example: use {-50000, 15000, 35000} not {50000, 60000, 80000}.
#VALUE!	The values array contains non-numeric data such as text, mixed types, or unintended headers included in the range reference.	Verify the range contains only numbers (B2:B5 not B1:B5 if B1 is a text header). Convert any text-formatted numbers to actual numbers.
#REF!	The values array references cells that have been deleted, moved, or the worksheet they belong to has been removed, breaking the cell references.	Restore deleted rows or update the range formula to point to valid cells. Check that B2:B5 (or your range) contains existing, accessible data.

Tips and when to use something else

MIRR assumes cash flows occur at regular, equal intervals (yearly, quarterly, etc.). For irregular timing, use XIRR and XNPV instead—they accept explicit date arguments.
Always enter rates as decimals: 0.08 for 8%, not 8. Entering 8 treats it as 800% and produces nonsensical results.
MIRR is often more realistic than IRR for real projects because it models financing costs and reinvestment rates separately rather than assuming all cash reinvests at one magical rate.
When comparing two projects via MIRR, use identical finance_rate and reinvest_rate for both; different rate assumptions make comparisons meaningless.

Related functions

IRRReturns the internal rate of return for a series of regular periodic cash flows, measuring annualized investment profitability.XIRRXIRR returns the annualized internal rate of return for cash flows at irregular date intervals, letting you evaluate project or investment profitability over time.NPVNPV returns the net present value of a series of cash flows discounted to their present value today, helping you decide if an investment creates value.XNPVXNPV returns the net present value of cash flows occurring at irregular intervals, discounting each flow by actual elapsed time.PVReturns the present value of an investment based on a series of regular payments and a constant interest rate.

Frequently asked questions

What's the difference between MIRR and IRR?: IRR assumes all positive cash flows reinvest themselves at the IRR rate itself—often unrealistic. MIRR lets you specify separate reinvestment and financing rates, making it far more practical for real investments like marketing campaigns where you control where cash flows go and at what rate you can borrow.
What values should I use for finance_rate and reinvest_rate?: finance_rate is your cost of capital (what you pay to borrow or fund the investment)—often your company's discount rate or WACC. reinvest_rate is the realistic return on surplus cash (savings accounts, money-market funds, or other projects)—typically lower than optimistic IRR assumptions. When in doubt, use the same rate for both to be conservative.
Can MIRR be negative?: Yes. A negative MIRR means the investment loses value after accounting for borrowing costs and reinvestment assumptions—the project destroys wealth. This signals rejection unless non-financial factors (brand, strategic fit) override financial returns.
Why does MIRR matter when I can just calculate profit?: Profit ignores timing and cost of capital. MIRR lets you compare a $50,000 campaign to a $500,000 campaign on equal footing, and a 1-year project to a 3-year project. Two campaigns with identical profit but different cash flow patterns will have different MIRRs, guiding you to choose the one that uses capital most efficiently.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17