How to Calculate Present Value (PV) in Excel

This guide will explain how to calculate the present value in Excel.

When planning your finances, you may need to determine the present value of your annuity. For example, if you have a financial goal to reach in 20 years, you may want to calculate how much to invest today.

We can use the PV function in Google Sheets to find the exact amount to invest or loan given several inputs, such as the interest rate, the duration of the annuity, and the payment amount per period.

In this guide, we will provide a step-by-step tutorial on how to calculate the present value of an annuity in Microsoft Excel. 

The Anatomy of the PV Function

The syntax of the PV function is as follows:

=PV(rate, nper, pmt, [fv], [type])

Let’s look into each of these parameters to understand how the PV function works.

• rate refers to the interest rate of our loan or investment. Do note that this rate refers to the rate for each period.
• nper refers to the total number of payment periods in the annuity.
• pmt refers to the total amount to be paid per period. This amount typically covers both the principal and interest but excludes taxes and other fees. If the pmt amount is omitted, the user must provide an fv argument.
• fv is an optional argument that determines the future value or cash balance you want to attain after the final payment. Omitting this argument assumes that we want this amount to be 0 (which is typically the case for a loan).
• type is an optional argument that specifies when payments are due. If the argument is set to 0, payments are due at the end of the period. A value of 1 indicates that payments are due at the beginning of the period. By default, the type is set to 0.
• Note that the rate and nper arguments should always use the same units. For example, monthly payments will have to use the annual interest rate divided by 12.

A Real Example of Calculating the Present Value in Excel

Present value refers to the current value of a series of future annuity payments. Annuities are financial products that provide a stream of payments over a period of time. These payments can be in the form of investments with regular pay-outs or loans that require repayment over time.

Let’s explore a few examples to show how you can use the PV function to find the present value of a particular loan or investment with a constant interest rate.

Calculating the Present Value of an Investment

Suppose you have an investment goal of having $500,000 in 10 years. You anticipate a 6% interest per year, paid monthly, on a particular financial investment. What is the lump sum you need to invest today to reach your investment goal?

We can use the PV function to calculate the present value of your investment, which will have a future value of $500,000. Here’s the formula that will return our result:

=PV(6%/12,10*12,,500000)

After evaluating our present value formula, we’ve determined that we need to invest $274,816.37 today to reach our investment goal in 10 years.

Calculating the Present Value of a Loan

Let’s look at an example where we’ll use the PV function to find the appropriate loan amount that is within our budget.

Suppose you are planning on purchasing a home, and these are some of the details of the loan you wish to use:

• The bank offers a loan with a 4% interest rate, payable every month
• You have a 20-year repayment period
• You can afford a $1000 payment every month

Using these details, we can create the following PV formula:

=PV(4.5%/12,20*12,-1000)

Given these details, we find out with our PV function that we can afford a loan of $158,065.44.

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 calculate the present value of an annuity or loan in Excel.

How to Calculate Present Value (PV) in Excel

1. Identify the details of the loan or investment necessary to compute the present value. You can create a table to help list each of these details.
   We’ll usually need the annuity’s interest rate, the total number of payments, the fixed amount to pay for each period, the future value of the annuity, and whether payments are made at the start or end of the period. For loans, we’ll leave the future value blank.
   
2. Next, select the cell where you want to output the present value.
   
3. Enter the arguments for the PV function.
   In our example above, we just need to enter the cell references of B1 to B5 in order.
   
4. Evaluate the PV function by hitting Enter.
   The function should return with the present value of the annuity.

These are all the steps you need to know to calculate the present value of a loan or investment in Excel.

To learn more about using Excel for calculating interest rates, you can read our post on how to calculate monthly compound interest.

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