Easy-to-follow 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 risk-free 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 risk-free 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.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 70716 Viewers
80 Video lessons · 127730 Viewers
52 Video lessons · 62588 Viewers
59 Video lessons · 48307 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.