A to Z of Excel Functions: The PV Function

Welcome back to our regular A to Z of Excel Functions blog.  Today we look at the PV function.

The PV function

PV (present value) is one of the financial functions.  It calculates the present value of a loan or an investment, based upon a constant interest rate.  You may use PV with either periodic, constant payments (such as a mortgage or other loan) or a future value that is an investment goal (say).

This will find the present value (loan amount) of what is affordable, based upon a set monthly payment.

PV employs the following syntax to operate:

The PV function has the following arguments:

• rate: this is required and represents the constant interest rate for the loan.  For example, if you obtain an automobile loan at a 12 percent annual interest rate and make monthly payments, your interest rate per month is 12%/12, or 1.00%.  You would enter 13%/12, 1.00% or 0.01 into the formula as the rate
• nper: this is also required and denotes the total number of payments for the loan / annuity.  For example, if you were to agree a five-year car loan and make monthly payments, your loan would have 5*12 (or 60) periods.  You would enter 60 into the formula for nper
• pmt: also necessary, this is the payment made each period which cannot change over the life of the annuity.  Typically, pmt includes principal and interest but no other fees or taxes.  For example, the monthly payments on a $10,000, five-year car loan at 12 percent are $222.44.  You would enter -222.44 into the formula as the pmt.  If pmt is omitted (which seems strange, given it is required!), you must include the fvargument
• fv: this is the first of two optional arguments.  This is the future value, or a cash balance you want to attain, after the last payment is made.  If fv is omitted, it is assumed to be zero (0), i.e. the future value of a loan is nil.  If fv is omitted, you must include the pmt argument
• type: this final argument is also optional.  This the number zero [0] or one [1] and indicates when payments are due:

It should be further noted that:

• the payment returned by PV includes principal and interest but considers no taxes, reserve payments or other fees sometimes associated with loans
• make sure that you are consistent about the units you use for specifying rate and nper.  If you make monthly payments on a four-year loan at an annual interest rate of 12%, use 12%/12 for rate and 4*12 for nper.  If you make annual payments on the same loan, use 12% for the rate and 4 for nper
• an annuity is a series of constant cash payments made over a continuous period.  For example, a car loan or a mortgage is an annuity
• in annuity functions, cash you pay out, such as a deposit to savings, is represented by a negative number; cash you receive, such as a dividend check, is represented by a positive number.  For example, a $1,000 deposit to the bank would be represented by the argument -1,000 if you are the depositor and by the argument 1,000 if you are the bank
• Microsoft Excel solves for one financial argument in terms of the others. If the rate is not zero [0], then:

• if the rate is zero [0], then:

Please see my example below:

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every other business day.