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.
In the previous movie I showed you how to use the NPV function to find the net present value of an investment. That function assumes that all of the cash flows happen at regular intervals, every month, every two months, every year, and so on. If you have a series of cash flows that occur at irregular intervals, you need to use the XNPV function to find the net present value. So for the XNPV function, you have two sets of arguments. The first is the discount rate and the discount rate is the return that you can expect on a guaranteed investment.
So for example, if you were able to invest in U.S. Treasury bill with a 5% rate, then that would serve as the discount rate. Next you need the values anddates. So the first of the values and those are simply the cash flows. Negative cash flows in this number format are shown in parentheses so we have a buy-in of $108,000. And the next each one of these cash flows you have the date. So we have May 1, 2011, June 1, 2011, August 1, 2011.
So you see that those dates occur at irregular intervals and again if you're cash flows occur at regular intervals then it's simpler to use NPV. So now let's go ahead and create our formula. I'll click in cell C15, type an equal sign, an then xnpv( and then the discount rate which is in cell c2, then a comma, then the values and those are in cells B5 through B12. So I'll just select those cells, type a comma, and then the dates of those cash flows are in cells C5 through C12, type a right parentheses. All the references look good, press Tab, and we see that the investment has a net present value of $27,221.31.
Now what would happen if the discount rate went down? In other words, what would happen if you were only able to get say a 2% return from T-Bills? Well, that would increase the net present value because you have lower guaranteed income from a guaranteed investment. So for example, let's say that I change the value in cell C2 to 2% then I just typed the 2, because I already have the percentage format applied. So I haven't the formula at all. Just changing that one input, then I can press Enter, and we see that the net present value goes up substantially.
So remember that if you have cash flows that occur at regular intervals then use NPV. If your cash flows occur at irregular intervals then use XNPV.
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.