In this course, author Curt Frye shows how to perform a wide range of financial calculations quickly and easily using the many financial functions found in Excel 2010. The course details dozens of functions for evaluating cash flows; calculating depreciation; determining rates of return, bond coupon dates, and security durations; and more.
Calculating an investment's internal rate of return lets you identify the interest rate at which your investment would have a net present value of zero. In other words, your formula tells you that discount rate at which you would break even on a given investment. If the formula, which uses the IRR function, returns a value greater than the interest rate generated by risk-free investments such as T-Bills, then you should make the investment. If not, you should pass. The IRR function has two arguments: a range of values that reflect future cash flows and optionally a guess at the rate of return.
Excel starts it's guessing with the rate of 10%, which will work in most cases. The cash flows may either be positive, which is income, or negative, which is an expense, but they must all occur at regular intervals such as either every month or every year. So let's go ahead and compare these two investments. The first has a $100,000 buy-in and then payments of 20, 20, 20, and $40,000 totaling 120 over the course of five years, and then the second investment also has the total payout of 120,000, but it's over six years and our guess is at 10% is in cell b3.
So I'll click now in cell B14 and create the formula for investment number 1. So it's =irr( and then the values, which are in cells A6 through A11. So I'll just select those cells, type a comma, and then type in the reference to cell b3, which has our guess at the interest rate. Type a right parentheses to close up the formula, verify that everything looks right, and then press Tab. When I do I see that the internal rate of return for investment 1 is 5.73%.
Now I can click over in cell E14 and create the same formula for investment number 2. So it's =irr( and then the values are in cells D6 through D12, so I'll just select them, type a comma, and then the guess for the interest rate is in cell b3 so I'll type b3, right parentheses, and then press Tab, and we see that that investment has an internal rate of return of 5.47%.
So if an investment's internal rate of return is higher than the discount rate you assume for a risk-free investment, you should go ahead. You can also use internal rate of return to compare two separate investments. Just figure out the cash flows, make sure they're regular intervals, add to an internal rate of return for each of the investments and take the one that's higher.
Find answers to the most frequently asked questions about Excel 2010: Financial Functions in Depth .
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.