Excel PV (Present Value) Function - Returns the Present Value of an Investment.

The PV function returns the value in today’s dollars of a series of future payments assuming a constant payment and interest rate.

The Syntax for the PV (Present Value) function is:
    PV(rate,nper,pmt,[fv],[type])

Argument Description
Rate The interest amount for the investment
Nper The total number of deposits
Pmt The payment (the amount of the deposit)
FV Optional - The future value, or a cash balance you want to attain after the last payment is made.
Type Optional - The type is omitted or set to 0 if the payments are made at the end of the period. The type should be set to 1 if the payments are made at the beginning of the period.

The arguments for the PV and FV functions are the same except the PV function has an optional FV argument and the FV function has an optional PV argument.

A thousand dollars today is worth more than a thousand dollars in the future unless there is a negative interest.  If somebody offered to give you a thousand dollars today or a thousand dollars 5 years from now, which would you prefer?  One of the reasons that a thousand dollars today is worth more than a thousand dollars 5 years now is because you could be making interest on the money and have more than a thousand dollars 5 years from now

If the interest rate is 5 percent, then the present value of $100 a year from now would be 100 /1.05 which would be approximately $95.  Another words that $95 today has the same value as $100 next year. The concept here is that the present value of a future amount is less than the actual future amount.

The higher the interest rate the lower the present value.  Think of it this way as inflation increases how much you can buy with your money decreases.  If the interest rate is 10 percent, then the present value of $100 a year from now would be 100 /1.1 which would be approximately $91

Practice - Use Future Value for Analysis

Is it better to pay cash for a truck costing $20,000 or is better to make monthly payments for 5 years at $380 a month with an interest rate of 5%.

  1. Create a worksheet as shown below.
    Excel Present Value displays start of practice worksheet

  2. In Cell C2 type =B2. A payment today is the same as the present value.
  3. In Cell C8 type =PV(  The first argument is the rate. The interest rate in B7 is an annual rate. Since we will be making monthly payments we needed to divide it by 12. Type B7/12
    Enter a comma to separate the arguments.
    The second argument is nper (number of periods). The total number of payments is 5 years times 12 months. Type B6*12
    Enter a comma to separate the arguments.
    The third argument is pmt (payment). The payment will be $380 a month. Because this is an outlay of money it should be a negative number. Type -380
    Type ) to close the function. The function should now be =PV(B7/12,B6*12,-380)
    Press Enter. The result is shown below.
    Excel Present Value displays result of pv practice worksheet

    The value of today’s dollars in paying cash for the truck is $20,000. The value of today’s dollars in paying for the truck through a 5 year loan is $20,136.47. Therefore, we would save $136.47 in today’s dollars if we paid cash.