Calculating an investment’s internal rate of return enables you to identify the interest rate at which the investment 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.
- [Instructor] Calculating an investments internal rate of return lets you identify the interest rate at which the 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. To demonstrate how to calculate internal rate of return in Excel, I will use the internal rate of return workbook. You can find that in the chapter two folder of the exercise files collection. IRR takes two sets of arguments. The first is a series of cash flows, which I have here in cells B4 through B10 and I have them labeled by period, although those values aren't included in the calculation.
The second argument which is optional is a guess. What happens in the background is that internat rate of return, the IRR function, makes a series of guesses as to the discount rate that would result in the cash flows displayed here in B4 through B10 having a net present value of zero. If you don't give it a guess to start with, it will assume 10% but to make that value explicit I've put it in cell B11. To calculate internal rate of return, you use the IRR function.
So in cell B13 I will type =irr followed by a left parenthesis and then I just need to identify the values and then if I have one, the guess. The values which I have is an $85,000 initial investment followed by a series of positive flows are in B4 through B10. So I'll select those and then type comma and then the guess is in cell B11, and again you can leave that out if you want. Type a right parenthesis and enter and I see that I have an approximate internal rate of return of 28%.
So that means this investment would break even, in other words, have a zero net present value if my discount rate, the risk free rate were 28%. That's an extremely high rate, especially when you consider that most government bonds only pay about 3 1/2%. So this looks like a very good project assuming all of our assumptions are correct.
- Calculating the effect of interest rates and inflation
- Finding the arithmetic and geometric means of growth rates
- Calculating the future and present value of an investment
- Calculating loan payments for a fully amortized loan
- Calculating the effect of paying extra principal with each payment
- Finding the number of periods required to meet an investment goal
- Calculating net present value and internal rate of return
- Building a cash tracking worksheet
- Visualizing cash flows using a waterfall chart