If you are trying to calculate the present value of an investment that has a constant payment schedule and a constant interest rate, the PV function will come in handy.
In this tutorial, I will show you how to use the PV function in Google Sheets.
Table of Contents
PV Function Syntax
The PV function will return the present value of an annuity investment that has periodic payments that are a constant amount and a constant interest rate.
The syntax of this function is:
PV(rate, number_of_periods, payment_amount, [future_value], [end_or_beginning])
- rate – the interest rate
- number_of_periods – the number of payments that will be made
- payment_amount – the amount that is paid for each period
- future_value – this is an optional argument. It is the future value remaining after the last payment has been made
- end_or_beginning – this is an optional argument. It controls whether payments are made at the end or beginning of each period
- 0 – this means payments are due at the end of each period. It will be set to this by default
- 1 – this means payments are due at the beginning of each period
The most important thing to know when using this function is that you need to have your interest rate, periods, and payment amounts in consistent units with each other.
What this means is that if you have a 12-month loan with payments made monthly, your annual interest rate needs to be divided by 12 and the number of payments is also 12.
If you have a 12-month loan that is paid quarterly, your annual interest rate would be divided by 4 and your number of payments would also be 4.
Using the PV Function
Now, let’s take a look at actually using this function.
For this example, I will be using the PV function to calculate the present value of a 2-year loan with a 3% annual interest rate and $500 payments made monthly.
Here is my data in my spreadsheet:
Here are the steps to set up the PV function with this data:
1. First type an equals sign in the cell where you want to calculate PV. Then type “PV” and press Tab to begin entering into the function. This first argument is the interest rate. My interest rate in this example is in cell B1. Since payments are made monthly, I have to take this interest rate and divide it by 12, so I have B1/12.
2. The next argument is the number_of_periods. This is a 2-year loan with payments made monthly, so the total will be 24 payments. In this spreadsheet, this is in cell B2
3. The next argument is the payment amount. This in cell B3 in this example
4. After you’ve entered the last argument you can place your closing parenthesis and press enter to calculate the function. In this example, the present value is $11,632.99
In the example above my final formula is:
=PV(B1/12,B2,B3)
I have left off the optional arguments of future_value and end_or_beginning. If you have a future_value that you want to use for your calculation, you will simply place that value as the fourth argument in the function.
Similarly, I have left off the end_or_beginning argument, so it will assume payments are made at the end of each period. To change this you would place a 1 as the fifth argument.
Closing Thoughts
If you are familiar with some of the other financial function options available in Google Sheets you may notice this function is similar to the FV function.
The key differences are PV is used for calculating present value, whereas FV is used for calculating the future value.
Otherwise, the syntax of both functions is the exact same. So once you learn one, you essentially know them both.
More Google Sheets Tutorials:
How to Use the NPV Function