Using the Excel PV Function
SummaryThe Excel PV function calculates the present value of an investment or loan based on a constant interest rate. The PV function can be used with payments that are periodic and consistent (such as an auto loan), or a future value that represents an investment goal.
The PV function is one of Excel's financial functions.
Syntax=PV (rate, nper, pmt, [fv], [type])
Required. The interest rate per each period. Be sure that the unit used to specify rate matches the unit used in the nper argument. For example, if you are making monthly payments on an auto loan with 4.9% annual interest, the rate argument would be 4.9%/12 to find the monthly rate.
Required. The number of payment periods over the life of the loan. Similar to the rate argument, you must ensure nper is in the proper format. Using the same auto loan example, if the loan is for 5 years, you would enter nper as 5*12, or 60, to reach the number of payment periods over the life of the loan.
Required. The payment made each period. This value cannot change over the life of the loan or investment. The pmt argument typically includes only principal and interest, and not taxes or fees that may be associated with a loan. You can omit the pmt argument, but then the FV argument is required.
Optional. FV represents the future value you wish to obtain after the final payment is made. If omitted, the argument is assumed to be zero (0).
Optional. Specifies when payments are due. Use 1 if the payment is due at the beginning of the period and zero (0) if payments are due at the end of the period. If blank, the value is assumed to be zero (0).
Usage NotesThe present value (PV) function is a financial function that can find the present value of an investment, assuming periodic, constant payments and a fixed interest rate.
The PV function is used with annuities, such as an auto loan or mortgage. These are streams of cash flows that include the same amount each period. Cash can be an outflow, for a loan, or inflow, such as dividend payments from an investment.
For all arguments, cash that is paid out is represented by negative numbers and cash that is received is represented by positive numbers. For example, if you pay out $500 for an auto loan, the pmt argument would be -500. If you received a dividend check from an investment for $100, then pmt would be 100.
Rate and Nper Units
When entering values for rate and nper it is critical that you are consistent with the units used. This may require you to convert rates and periods (i.e. from an annual rate to a monthly rate).
For example, if you are making monthly payments on a 5-year car loan at 4.9% annual interest, use 4.9%/12 for rate (to get the monthly rate) and 5*12 for nper (to get the total number of monthly payments).
Failing to ensure the units are the same will produce incorrect results.
The type argument allows users to specify when a payment is made. Use one for the beginning of the period and zero (0) for the end of the period. If type is omitted, it is assumed to be zero (0). This will impact your final answer.