How to Use PV Function in Google Sheets

This guide will explain how to use the PV function in Google Sheets.

Present value is the current value of a loan or an investment. Investors often calculate the PV of their assets, which may include stocks, bonds, and annuities.

You can calculate the present value of an annuity using the built-in PV function in Google Sheets. You can also use the function to determine the current value of an investment given some future investment target.

In this guide, we will provide a step-by-step tutorial on how to use the PV function. We will also cover how to modify your formula to account for yearly and monthly payments.

The Anatomy of the PV Function

The syntax of the PV function is as follows:

=PV(rate, nper, payment_amount, [future_value], [end_or_beginning])

Let’s look at each argument to understand how to use the PV function.

• rate refers to the interest rate of our annuity investment.
• nper refers to the number of payments the investor must make.
• payment amount refers to the amount to be paid per period.
• future_value is an optional argument that determines the future value remaining after the final payment has been made.
• end_or_beginning is an optional argument that specifies when payments are due. A value of 0 indicates payments are due at the end of the period. A value of 1 indicates payments are due at the beginning. By default, this value is set to 0.
• The user must ensure that the rate and nper arguments are always using the same units. For example, monthly payments will have to use the annual interest rate divided by 12. Similarly, the number of periods should be equal to the number of months (total years * 12)

A Real Example of the PV Function in Google Sheets

Let’s explore a few simple examples of the PV function in action.

Using the PV Function to Calculate Present Value of Annuities

Suppose you have an annuity with the following details:

• an annual interest rate of 2.5%
• a duration of 3 years
• an annual payment of $1000
• payments are due at the end of the period

Given these details, how much is the present value of our annuity?

In the table above, we’ve created a table that lists all the details we need to compute for the present value of our annuity. 

We’ll add these details to our PV function to determine the present value of our annuity. We’ll use the following formula:

=PV(B1/12,B2*12,B3,B4,B5)

The PV function indicates that the present value of our annuity is -$2,856.02, assuming we make payments annually. The value is represented as a negative number since we’re referring to cash you pay out rather than receive.

If we’re following a monthly payment plan, we will need to modify our formula:

=PV(B1/12,B2*12,B3,B4,B5)

The annual interest rate must be divided by 12. We’ll also need to multiply the number of years by 12 to get the proper number of periods in our argument.

Using the PV Function to Calculate How Much to Invest

We can also use the PV function to understand how much you need to place in a savings account to reach some target amount.

Suppose you have a savings account with 5% annual interest. Given that you want your account to hold $50,000 after 20 years, how much should you invest now?

We can use the following formula to calculate the result:

=PV(B1,B2,,B3,)

The PV function reveals that we need to add $18,844 to our savings account to reach our future goal. Since we won’t be making any periodic payments, we can leave the third argument blank. Since the third argument is set as a required function, our formula will skip to the next argument by adding two commas in a row.

Click on the link below to create your own copy of our examples.

Head to the next section to read our step-by-step tutorial on how to use the PV function to compute the present value of an annuity

How to Use the PV Function in Google Sheets

1. Create a table in your spreadsheet that lists the information you need to compute the present value of your annuity.
   
2. Next, select the cell where you want to place the PV function.
   
3. Type the PV function and enter the details from the table earlier as arguments. In this example, we’ll use the formula =PV(B1,B2,B3,B4,B5).
   
4. Hit the Enter key to evaluate the PV function.
   
   In the example above, we’ve determined that the present value of our annuity is $2856.02.
   
5. When working with monthly payments, divide the annual interest rate by 12 (B1/12) and multiply the number of years to pay by 12 (B2*12). We’ll use the formula =PV(B1/12,B2*12,B3,B4,B5).
   

These are all the steps you need to use the PV function to calculate the present value of an annuity or investment.

If you want to find the future value of an annuity instead, you can read our post on how to use the FV function. You can also read our guide on the RATE function if you want to know the interest rate of an annuity given its present value.

That’s all for this guide! Be sure to check out our library of spreadsheet resources, tips, and tricks!