Join Curt Frye for an in-depth 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 T-Bills 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 buy-in 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 buy-in. 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 buy-in.
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.
- Analyzing loans, payments, and interest
- Discovering the interest rate of an annuity
- Determining depreciation using the straight line, declining balance, double-declining 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
Q: Where can I learn more about Excel formulas?
A: Discover more on this topic by visiting Excel formulas on lynda.com.