Calculates the modified internal rate of return for a series of periodic cash flows.
構文
MIRR(values, finance_rate, reinvest_rate)引数
values必須
An array or reference to cells that contain numbers representing a series of payments and income.
finance_rate必須
The interest rate you pay on the money used in the cash flows.
reinvest_rate必須
The interest rate you receive on the cash flows as you reinvest them.
MIRR improves upon the standard IRR function by accounting for both the cost of investment and the interest received on reinvested cash. It assumes that positive cash flows are reinvested at the reinvestment rate and negative cash flows are financed at the finance rate.
=MIRR({-1000, 300, 400, 500}, 0.1, 0.12)→0.1261Calculates the MIRR for an initial investment of 1000 followed by returns of 300, 400, and 500, with a 10% finance rate and 12% reinvestment rate.
Prepare cash flow data
List your initial investment as a negative number followed by periodic returns in a single column or row.
Apply the MIRR function
Select a cell and enter the MIRR formula, referencing your data range and specifying the finance and reinvestment rates.
MIRR is more realistic because it assumes reinvested cash flows earn the reinvestment rate rather than the internal rate of return itself.