Ready to watch this entire course?
Become a member and get unlimited access to the entire skills library of over 4,900 courses, including more Business and personalized recommendations.
Start Your Free Trial Now Overview
 Transcript
 View Offline
Released
6/28/2011 Analyzing loans, payments, and interest
 Discovering the interest rate of an annuity
 Determining depreciation using the straight line, declining balance, doubledeclining balance, and other methods
 Calculating the future value of an investment with variable returns
 Finding the discount rate of a security
 Converting between fractional prices and decimal prices
 Determining the yield of securities that pay interest periodically
Skill Level Intermediate
Duration
Views





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 buyin 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 TBills? 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.




Public Link
Video: XNPV: Calculating net present value given irregular inputs