Microsoft Office Tutorials and References

In Depth Information

**Using MIRR to Calculate Internal Rate of Return, Including Interest Rates**

valuesto interpret the order of cash flows. You need to be sure to

enter your payment and income values in the sequence you want. If an

array or a reference argument contains text, logical values, or empty

cells, those values are ignored.

•
guess

guess
—
This is a number that you guess is close to the result of IRR.

Microsoft Excel uses an iterative technique for calculating IRR.

Starting with guess, IRR cycles through the calculation until the res-

ult is accurate within 0.00001%. If IRR cannot find a result that works

after 20 tries, a #NUM! error is returned. In most cases, you do not

need to provide guessfor the IRR calculation. If guessis omitted, it is

assumed to be 0.1, which is 10%. If IRR gives a #NUM! error, or if the

result is not close to what you expected, you can try again with a dif-

ferent value for guess.

IRR is closely related to NPV, the net present value function. The rate of

return calculated by IRR is the interest rate corresponding to a net present

value of zero. The following formula demonstrates how NPV and IRR are re-

lated: Enter = NPV(IRR(B1:B6),B1:B6)

NPV(IRR(B1:B6),B1:B6) in a cell, which equals 3.60E-08. Within

the accuracy of the IRR calculation, the value 3.60E-08 is effectively zero.

In
Figure 13.13
,
the formula in cell B17 shows that the business investment

would generate a rate of return of 2.66% if analyzed over a 5-year period.

The arguments for this function include the initial $50,000 investment in the

business as well as the net incomes from the next 5 years.

Similar formulas in cells B14 and B15 return a #NUM! error. The formulas

were edited to add a guessvalue. Based on the
–
12% return through 4 years,

guessfor 3 years was
–
10%.

Using

Using
MIRR

MIRR
to Calculate Internal Rate of Return, Including Interest Rates

to Calculate Internal Rate of Return, Including Interest Rates

MIRR calculates a modified internal rate of return. This function assumes

that cash flows from the business are reinvested at some interest rate. It

also offers an argument to specify the initial interest rate of the business

loan used to purchase the business.

Syntax

MIRR(values,finance_rate,reinvest_rate)

The MIRR function returns the modified internal rate of return for a series of

periodic cash flows. MIRR considers both the cost of the investment and the