1. Calculation functions
  2. All Functions
  3. Financial Functions
  4. PV

Use the PV function to calculate the principal value of a loan (the amount you can afford to borrow), or the present value of an investment (the initial amount needed to reach a future goal).

For example, imagine you want to purchase a home. You know you can get a mortgage with a 5% interest rate, you'll have 30 years to repay the loan, and you've budgeted $1,000 per month as repayment. You can use the PV function to find the initial loan amount you can afford to borrow.

Alternatively, imagine you want to save $1,000 by this time next year. You know you can open a savings account and receive annual interest of 10%. You can use the PV function to find out how much money you need to start with to achieve that goal.

Syntax

PV(rate, nper, pmt, fv, type)

The PV function has the following arguments.

Argument Data type Description
rate (required)

Number (Percentage)

The interest rate per period, specified as a percentage. For example, if you get a loan at a 10% annual interest rate, and make monthly repayments, the interest rate per period is 0.83% (10%/12).

nper (required)

Number

The total number of payment periods. For example, if you get a two-year loan and make monthly repayments, your loan has 24 payment periods (2*12).

pmt (required)

Number

The payment made each period. This is fixed, and cannot change over the duration of the loan.

fv (required)

Number

The future value of the loan of investment, after the final payment has been made.

For a loan, this is typically 0. For an investment, this is typically the end balance you want to reach.

type (required)

Number

Whether the payments are made at the end or beginning of each period.

Enter 0 if payments are made at the end of the period. Enter 1 if payments are made at the beginning of the period.

Constraints

This function has no constraints.

Notes

Make sure that you use consistent units for the rate, nper, and pmt arguments.

For example, repayments for a loan over three years may be paid monthly. In this case, the number of periods is 36 (12*3), and the annual interest rate should be divided by 12.

A different type of loan over the same length might be paid quarterly. In this case, the number of periods is 12 (4*3), and the annual interest rate should be divided by 4.

Examples

This section shows some example formulas using the PV function.

Example 1: Use the PV function to find the principal value of a loan

This example shows how you can use the PV function to find the principal value of a loan (the amount you can afford to borrow).

Imagine you can get a mortgage with a 5% annual interest rate, you'll have 30 years to repay the loan, and you've budgeted $1,000 per month as repayment. You can use the PV function to find the initial loan amount you can afford to borrow.

You have the following line items and values in a module.

Line item Value
Interest rate

5%

Number of periods

360

Payment amount

1000

Formula

PV(Interest rate/12, Number of periods, Payment amount, 0, 0)

Note that the annual interest rate is divided by 12 in the formula, as repayments are made monthly. The fv argument is set to 0, as you're calculating the principal value of a loan. Type is set to 0 in the formula to show that payments are made at the end of each period.

Result

$186,281.62

Example 2: Use the PV function to find the present value of an investment

This example shows how you can use the PV function to find the present value of an investment (the initial amount needed to reach a future goal).

Imagine you want to save $1,000 by this time next year. You know you can open a savings account and receive annual interest of 10%. You can use the PV function to find out how much money you need to start with to achieve your goal.

You have the following line items and values in a module.

Line item Value
Interest rate

10%

Number of periods

12

Future value

1000

Formula

PV(Interest rate/12, Number of periods, 0, Future value, 0)

Note that the annual interest rate is divided by 12 in the formula, as repayments are made monthly. The pmt argument is set to 0, as you're calculating the present value of an investment. Type is set to 0 in the formula to show that payments are made at the end of each period.

Result

$70,144.24

Excel equivalent

PV