Start learning with our library of video tutorials taught by experts. Get started
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.
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.
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.