The PV function in Excel returns the present value of an investment. The alternative function is the FV function (Future Value). The PV Function is used to obtain the current value of a future sum of money. Let’s walk through how this calculation is done below.
Syntax:
=PV (rate, nper, pmt, [fv], [type]) rate = (APR/m) m=12 is monthly, m=4 is quarterly
Arguments:
- rate – The interest rate per period.
- nper – The total number of payment periods.
- pmt – The payment made each period.
- fv – [optional] A cash balance you want to attain after the last payment is made. If omitted, assumed to be zero.
- type – [optional] When payments are due.
- 0 = end of period – Default(opens in a new tab)
- 1 = beginning of period.
PV Function Usage Example:
Let’s look at the following scenario.
Here, we have an annual interest rate of 5%. In our worksheet, we set the number of years to 5 (n) and payments per year to (4) for a quarterly rate. The rate is then converted to a quarterly rate as well. Since we are making payment of 100 a month, we enter pmt as -100.
- If you are the depositor (Paying a payment), the pmt is entered as a negative number.
- If your the bank or creditor, you enter the pmt as a positive number. This would also include dividends and any other cash you might receive.
Here is the formula we entered into cell C13.
=PV(C6,C8,C7,C10,C11)
