
Easytofollow video tutorials help you learn software, creative, and business skills.Become a member
Calculating an investment's internal rate of return enables you to identify the interest rate at which the investment's future cash flows have a net present value of zero. In other words, your formula tells you the discount rate at which you would break even on a given investment. If the IRR formula returns a value greater than the interest rate generated by riskfree investments, you should take the plunge. If not, then you should pass on the opportunity. The IRR function has two arguments, a range of values that reflects future cash flows, and optionally, a guess at the rate of return.
Excel starts by guessing at a rate of 10 %, which will work in most cases, but in extreme cases, you should start the guessing at a higher or lower value. The investment cash flows may be positive representing income to you, or negative representing an expense to you but they must all occur at regular intervals, such as monthly or annually. So, let's take a look at the two investments that I have summarized in my worksheet. We have two investments, one, with an initial investment of $100,000 and payments of these amounts for five years, then we have the same $100,000 investment but we have payments over 6 years.
So the idea is to determine which of the two has the better internal rate of return. To do that, we input our IRR formulas and the values go from A4 to A9. I won't put it in a guess because I think Excel's default starting value of 10% will allow it to find the answer that it needs. So I'll just close the parenthesis, hit Return, and we see that this investment has an internal rate of return of 5.73%. Now, let's do the same thing for the second investment, =IRR, and the values are in the range of D4 to D10.
Again, I'm not going to put it in the guess because I think the default value is fine. I'll close the parenthesis, hit Return, and we see that the second investment has an internal rate of return of 5.47%. So we should go with the first investment. If an investment's internal rate of return is higher than the discount rate, you assume for your riskfree investments, such as your treasury bills. You should go ahead with the new plan assuming the risks of the new investment aren't too great. The riskier in investment, the more you should adjust the internal rate of return to account for the possibility that the investment might fail.