PV Function - Excel
Overview
The PV function in Excel is a useful tool for calculating the present value of an investment based on interest rate, number of payments, and payment amounts. Additional data points are future value and the timing of payments. This function helps determine the current worth of a future sum of money.
Example:
=PV(0.05, 10, -200)
Syntax:
=PV(rate, nper, pmt, [fv], [type])
rate: The interest rate per period.
nper: The total number of payment periods.
pmt: The payment made each period. It remains constant throughout the annuity's duration.
[fv]: An optional argument representing the future value, or a cash balance you aim to attain after the last payment is made (defaults to 0 if omitted).
[type]: An optional argument indicating whether payments are due at the beginning or end of the period (0 or omitted for end-of-period payments, 1 for beginning-of-period payments).
Example
In this example, the PV function takes in three inputs to give us the future value output. First it takes in the rate, which is 5% in this example. Next it takes in the number of payment periods, which is 10 in this example. Finally it takes in the payment amount each period, which is -100 in this example. The payment amount is negative because it’s an outgoing payment. The final output of this function is a present value of “$772.17”.
Example: Optional input [fv]
In this example, we will use the optional “future value (fv)” input for the PV function. All of the inputs are the same as the previous example except this time we are adding the fv input, which is -1000 in this example. The fv input is negative because it represents a final outgoing investment. The final output of this function is a present value of “$1,386.09”.
Example: Optional input [type]
In this example, we will use the optional “type” input for the PV function. The “type” input has two options, you can either enter 0 to indicate that a payment is due at the end of a period or a 1 to indicate that a payment is due at the beginning of a period. In this example I show the difference between the 0 type entry and the 1 type entry. When the payment is due at the beginning of a period (1 type entry), the future value tends to be higher. The reason for the future value being higher with the payment period at the beginning is because you have more time to earn interest on that money.
