Join Curt Frye for an indepth discussion in this video NPV: Calculating the net present value of an investment, part of Excel 2010: Financial Functions in Depth.
The net present value of an investment is the present value of the investment minus the amount of money it costs to buy in. You can calculate the net present value of an investment using the NPV function. When you do all the investments cash flows must occur at the same interval for the calculation to be accurate. The NPV function has two arguments: rate, which refers to the discount rate, and a range of values that contain the investments future cash flows. So I'll start with the discount rate. The discount rate which is here in cell B3 and it's 4% is the amount of income that you can receive from a guaranteed investment such as, the U.S. Treasury bill.
So if TBills are paying 4% then that is what you are comparing in these other investments against. Now the other arguments that you will need are a series of cash flows. So in this case for Investment 1 and we are comparing two different investments here, we have an initial investment of $100,000 and that number is in parentheses because it is a negative number. And then we have 5 years of $25,000 payouts and we're comparing that to a second investment which again has a $100,000 buyin and then it has five payments of $20,000 and then a final payment of $25,000.
So what we're trying to decide is whether investment 1 or investment 2 makes more monetary sense. In other words, which has the higher net present value? To do that, we can create an NPV function. So I'll click in cell B15 and type =npv left parentheses. Now we can add the rate which is in cell B3 and I'm going to assume that interest is compounded monthly. So I will type / 12 to divide the rate by 12, then type a comma, and now we can enter in the cash flow values.
If you type in the cells individually or individual values if you like then you would type in up to 254 values, but the easiest way to do it is to select the cash flows themselves so that the formula has a range to work with. So I'll select cells A8 through A12 and yes, I left cell A7 out on purpose. I'll show you why in a second. So those are the cash flows that I will receive. Now I'll type a right parentheses, but before I press Enter I need to include the $100,000 buyin. So I'll add the amount and cell a7 which is $100,000.
So I'll type A7 and I don't need a parentheses or anything. So with that in place I'll press Tab and I see that the net present value of this investment, investment 1, is 23,000 and about $750. So now I'll go over to cell E15 and I'll do the same thing for the cash flows for investment 2. So in cell E15 type =npv( and then the rate, once again it's in cell B3, and compounded monthly so divide by 12, and then I'll select cells D8 through D13, type a right parentheses then a plus sign, and D7 for the $100,000 buyin.
So everything looks good in the formula. Press the Tab key and I see that the net present value in this investment is $23,513.51. So it's close, but the net present value of investment 1 is higher so that's the one that makes more economic sense.
Author
Curt FryeReleased
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
Related Courses

Managing and Analyzing Data in Excel 2010
with Dennis Taylor1h 32m Appropriate for all 
Excel 2010: Data Validation in Depth
with Dennis Taylor59m 45s Intermediate

Introduction

Welcome1m 6s

Disclaimer29s


1. Analyzing Loans, Payments, and Interest

2. Calculating Depreciation

3. Determining Values and Rates of Return

4. Calculating Bond Coupon Dates and Security Durations

5. Calculating Security Prices and Yields

6. Calculating Prices and Yields of Securities with Odd Periods

Conclusion

Additional resources1m 5s

 Mark as unwatched
 Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.
CancelTake notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.
Share this video
Embed this video
Video: NPV: Calculating the net present value of an investment