The PV function in Excel is used to calculate the present value of an investment or loan, given a fixed interest rate and a series of future payments. It can be useful for evaluating loans, investments, annuities, or other financial products.
The syntax for the PV function is as follows:
PV(rate, nper, pmt, [fv], [type])
Here’s an explanation of each argument:
- rate: The interest rate for each period. If you have an annual interest rate, but your periods are monthly, you would divide the annual rate by 12.
- nper: The total number of payment periods in the investment or loan.
- pmt: The payment made in each period; it cannot change over the life of the investment or annuity. If payments are required at the end of the period, this value should be negative.
- [fv] (optional): The future value, or a cash balance you want to attain after the last payment is made. If omitted, it’s assumed to be 0.
- [type] (optional): A value of 0 or 1 that indicates when payments are due. Use 0 if payments are due at the end of the period and 1 if payments are due at the beginning. If omitted, it defaults to 0.
Example:
Suppose you want to find out the present value of a series of $500 monthly payments for 5 years, assuming an annual interest rate of 6%. Payments are made at the end of each month.
Here’s how you would set it up in Excel:
- rate: `0.06/12` (because it’s a monthly payment, so you divide the annual rate by 12)
- nper: `5*12` (5 years, with 12 payments per year)
- pmt: `-500` (the payment is cash outflow, so it’s negative)
Your function would look like this:
=PV(0.06/12, 5*12, -500)
If you want to know the present value of these payments assuming there is a future value goal in mind, include that as well. Adjust the formula as necessary according to your specific needs, such as adding the future value or specifying when payments are made.
Unlock Your Potential
Excel
Access
Power BI
