MIRR function
MIRR returns the modified internal rate of return per period, a more realistic cousin of IRR that lets you set separate borrowing and reinvestment rates. It always gives a single answer.
Example
Read it as a sentence: find the modified internal rate of return for the cash flows in N1:N5, with a 10% cost of borrowing and a 12% reinvestment rate, which gives 14%.
Excel shows 14%
N
1
-1000
2
300
3
400
4
500
5
200
Data
Syntax
MIRR(values, finance_rate, reinvest_rate)
-
values(required) - Cash flows, with at least one negative and one positive.
-
finance_rate(required) - Rate paid on money used to fund negative flows.
-
reinvest_rate(required) - Rate earned on reinvesting positive flows.
Returns: Modified internal rate of return per period.
What Excel actually returns
Each formula below was run in desktop Excel. The result is exactly what the cell showed, errors included.
| Formula | Excel shows | Case |
|---|---|---|
=MIRR({-100,60,60},0.1,0.1) | 12% | Array constant |
=MIRR(Data!N1:N5,0.1,0.12) | 14% | Cash flows |
=MIRR(Data!N1:N5,-1,0.1) | 13% | Finance rate -1 |
=MIRR(Data!O1:O3,0.1,0.1) | #DIV/0! | All positive |
=MIRR(Data!N1:N5,0.1,-1) | #DIV/0! | Reinvest rate -1 |
=MIRR(Data!B1:B8,0.1,0.1) | #DIV/0! | Mixed range |
=MIRR(Data!N1:N5,"a",0.1) | #VALUE! | Text rate |
The Data sheet these formulas read
A cell that starts with = holds that formula.
B
N
O
1
10
-1000
100
2
20
300
200
3
TRUE
400
300
4
500
5
abc
200
6
30
7
FALSE
8
40
Data
Watch out for
- Needs both positive and negative values or returns #DIV/0!.
Where it works
- Excel
- MIRR is in every version of Excel from 2007 on.
- Google Sheets
- Same name, same behaviour for everyday use.
Part of Financial functions. Every result on this page was measured in desktop Excel, build 16.0.20527. Last updated .