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