Spreadsheets for Finance: Calculating Present Value and Net Present Value

If you're thinking of spending money now to receive payments in the future—maybe purchasing an annuity or bond, making a loan, or selling a winning lottery ticket—you'll want to know what the value is today of the payments that you'll receive in the future. That's what present value is, and you can calculate it the same way in any version of Excel or Google Sheets using the Present Value function.

A similar calculation you might want to do is net present value, which takes the original cost into account. We'll look at both of these calculations in this tutorial.

Screencast

You can follow along with this tutorial in any version of Excel for Windows or Mac, or any alternate spreadsheet app, including Numbers and Google Sheets. We've included a template spreadsheet file that you'll find on the top left of this tutorial that you can use to follow along, or just make your own spreadsheet as you work through the tutorial so you can get practice using your own skills.

Now, it's time to crunch some numbers.

Calculating Present Value

To calculate present value, there are three pieces of information you need—rate, periods, and payment—and two others—future value and type—that are optional:

Rate: the periodic interest rate to be applied. This is usually in terms of annual rate, but doesn't have to be.
Periods: the number periods during which the payments will be made. This is typically monthly periods, but might be quarterly or bi-annually.
Payment: what the amount is of the regular payments to be made. For the present value function to work, the periodic payments must always be the same.
Future value: you can optionally specify the value of the instrument will be when all the payments are complete. If you don't use this argument, the function assumes that the value is 0.
Type: if the regular payments are made at the beginning of the period, the type is equal to 1. If the payments are made at the end of the period, the type is equal to 0. This is also an optional argument. If you don't specify it, the function assumes that the type is 1.

When specifying the rate and the number of periods, make sure the units are consistent. For example, if the payment periods are monthly, make sure to use the monthly interest rate. If the units aren't consistent, you'll get an incorrect result.

The syntax for calculating Present Value (PV) is:
=PV(rate, nper, pmt, [fv], [type])

Open Present value.xlsx and go to the PV workbook, or type what's in the screen capture below in your own spreadsheet, substituting the numbers for your own values if you wish:

Click cell B10 and enter the function:
=PV(B4/12,B5*12,B3,B6,B7)

Notice that the result has a negative value: -198,003.49.

The reason it's negative is because it's a cash outflow. If that looks strange, you can fix it either by making the monthly payment negative—which might look equally strange—or by editing the formula to put a negative sign before the monthly payment (B3). So edit the formula to be this:

=PV(B4/12,B5*12,-B3,B6,B7)

That makes the result look like this:

So in plain English, this means that the value today of receiving payments of $1000 every month for 20 years is just over $198,000.

Calculating Net Present Value

As the name implies, Net Present Value looks at an investment from a differential perspective. It's the difference between the present value of an investment's cost and the present value of the revenue the investment generates (which is what we calculated above). Unlike the regular Present Value function, the Net Present Value function lets you use an uneven cash flow.

There are three pieces of information you need:

Rate: interest or discount rate, i.e. the amount you could anticipate to make from that money otherwise.
Revenue stream: the value of each payment amount. These are multiple values and they can all be different.
Cost: the initial cost of the investment.

The syntax is:
=NPV(interest, revenue stream) – initial cost

Note that since the initial cost is a cash outflow, we'll either have to subtract it, or make it a negative amount on the worksheet and add it.

When we get the result from the net present value function, there will be three possibilities:

NPV > 0. This means the investment brings in more money than it costs, so it might be worthwhile.
NPV = 0. This means the investment costs as much as it brings in. We probably won't do it, because why do all the work just to break even?
NPV < 0. This means the investment costs more than it makes, so it's a losing proposition.

In Present value.xlsx, go to the NPV workbook, or type in what's in the screen capture below:

Click in B19 and enter the function:
=NPV(B3,B6:B15)-B4

This gives us a result of 1,656.05.

With these values, you can see that you'll come out ahead, since it's a positive amount. But you can also see that it isn't as great as it looks if you were to simply compare the initial cost with the total revenue and ignore the net present value. That's because there are 10 years and a 2% discount involved.

To make this point more clear, reduce the revenue for years 7, 8 and 9 as follows:

That gives us a revenue that initially looks good because it earns $6700, but the NPV calculation shows that it's a losing proposition.

Conclusion

Using the Present Value function, we can evaluate how much we would be willing to pay for an investment with a constant, regular cash flow. Depending on whether you're on the paying or receiving end, you could use this to calculate an auto loan or mortgage, or an insurance payout or coupon bond.

With the Net Present Value function, we can evaluate whether a cash flow that's regular or irregular is worth the initial investment, after taking the time and initial cost into account. Both of these are valuable ways to help you make your best financial decisions based on facts, not just instinct.

Spreadsheets are powerful tools to help you in your business and personal financial decisions. If you haven't already followed our earlier Spreadsheets for Finance tutorials, be sure to check them out as well:

Please note: This tutorial is not intended to give you financial advice, but only to explain how to use spreadsheets for present value calculations. Please consult a qualified financial advisor before making any financial decisions.