Microsoft Office Tutorials and References
In Depth Information
Using MIRR to Calculate Internal Rate of Return, Including Interest Rates
interest received on reinvestment of cash. This function takes the following
values — This is an array or a reference to cells that contain num-
bers. These numbers represent a series of payments (negative values)
and income (positive values) occurring at regular periods. values
must contain at least one positive value and one negative value to
calculate the modified internal rate of return. Otherwise, MIRR re-
turns a #DIV/0! error. If an array or a reference argument contains
text, logical values, or empty cells, those values are ignored;
however, cells with the value 0 are included.
finance_rate — This is the interest rate you pay on the money used in
the cash flows.
reinvest_rate — This is the interest rate you receive on the cash
flows as you reinvest them.
MIRR uses the order of values to interpret the order of cash flows. You need
to be sure to enter your payment and income values in the sequence you want
and with the correct signs. In other words, enter positive values for cash
received and negative values for cash paid.
with a $120,000 loan. The business has generated profits of $17,000, $34,000,
$38,000, $5,000, and $32,000. The original loan had an interest rate of 4%, and
the profits were reinvested at 0.10%. The MIRR in cell B10 is 1.0%. For com-
parison, the IRR of the same cash flows would be 1.64%.