The Present Value Excel function (PV) is a Financial formula that calculates and returns the present value of a loan or an investment based on constant payments, and interest rate. In this guide, we’re going to show you how to use the Present Value Excel function (PV) and also go over some tips and error handling methods.


Supported versions

  • All Excel versions

Present Value Excel Function (PV) Syntax

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


Arguments

rate The interest rate for the loan.
nper The total number of payments for the loan.
pmt The constant payments for the loan.
[fv] Optional. The future value, or a cash balance you want after the last payment is made. The default value is 0 (zero).
[type]

Optional. When payments are due.

0 = end of period. (Default)

1 = beginning of period.


Examples

Without future value and due date type

The present value Excel (PV) function has 3 required arguments: rate, nper and pmt. Using these arguments, you can calculate the present value of a loan based on constant payments. The function assumes that payments are made at the end of each period, and the loan is to be paid in full.

For example, the formula below calculates the present value of a loan with monthly payments of $3,000 for 36 months with a 6% annual interest.

=PV(0.06/12,36,-3000)

Please note that, 6% interest is divided by 12, because interest is given as an annual rate and periods are in months.

With future value and due date type

You can specify a target cash balance for after the last payment is made, and whether payments are to be made at the beginning of each period or the end.

The following formula is the extended version of the first example. It calculates same payment amount for the same period amount with the same interest rate. The difference is that there is $50,000 left after the last payment, and payments are made at the beginning of each period.

=PV(0.06/12,36,-3000,50000,1)

PV

Download Workbook


Tips

  • If pmt is entered as a positive number, the calculated payment amount will be negative. This is because the money is “coming out of pocket”. If you’d like to show negative numbers instead, multiply everything with ‘-1’.
  • Other related financial functions: