Excel functions / Financial
MIRR function
Rate of return when money borrowed and money reinvested earn different rates.
Syntax
=MIRR(values, finance_rate, reinvest_rate)
Arguments
values: Cash flows at regular intervals: at least one negative and one positive.finance_rate: The interest rate paid on the money invested.reinvest_rate: The interest rate earned on the money that comes back.
When to use it
MIRR gives the rate of return on a series of cash flows, like IRR, but with two realistic assumptions: money borrowed costs a finance rate, and money received is reinvested at a reinvestment rate. IRR assumes everything is reinvested at the IRR itself, which flatters good projects.
Examples
A five-year project
A B C D 1 Year Cash flow 2 0 -120000 12.6% 3 1 39000 4 2 30000 5 3 21000 6 4 37000 7 5 46000 In D2:
=MIRR(B2:B7, 10%, 12%)→ 12.6%10% is the cost of borrowing; 12% is what returns can be reinvested at.
Common mistakes
- Only one sign. The cash flows need at least one negative and one positive value, or the result is #DIV/0!.
MIRR in other languages
Excel shows function names in its own language. In these languages it also puts a semicolon between arguments where English has a comma.
- Czech: MOD.MÍRA.VÝNOSNOSTI
- Danish: MIA
- Dutch: GIR
- Finnish: MSISÄINEN
- French: TRIM
- German: QIKV
- Hungarian: MEGTÉRÜLÉS
- Italian: TIR.VAR
- Norwegian: MODIR
- Polish: MIRR
- Portuguese (Brazil): MTIR
- Portuguese (Portugal): MTIR
- Russian: МВСД
- Spanish: TIRM
- Swedish: MODIR
- Turkish: D_İÇ_VERİM_ORANI