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

arguments:

•
values

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

finance_rate
—
This is the interest rate you pay on the money used in

the cash flows.

•
reinvest_rate

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.

In
Figure 13.14
,
you are analyzing a business that was started 5 years ago

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%.