rate (required) - the interest rate per period. For example, if the annual interest rate is 6% and periods are one month, then the interest rate is =6%/12 = 0.5% (0.005). You can enter rate as 6%/12 as a reminder of how it is derived. nper (required) - The total number of payment periods in the annuity. For example, a 5-year car loan with monthly payments has 60 periods. You can enter nper as 5*12 to note how the number was determined. pmt (required) - The payment made each period. This number cannot change over the life of the annuity. In annuity functions, cash paid out is represented by a negative number. Note: If pmt is not provided, the optional fv argument must be supplied. fv (optional) - The future value. This is the cash balance required after all payments have been made. When fv is omitted, it defaults to zero, and pmt must be supplied. type (optional) - type is a boolean that controls when when payments are due. Supply 0 for payments due at the end of the period (regular annuities) and 1 for payments due at the end of the period (annuities due). Type defaults to 0 (end of period).
Examples
The PV function can be used to calculate the present value of a loan, when the interest rate, payment, and number of periods are known. For example, the present value of a 5-year loan with an annual interest rate of 4.5% and monthly payments of $93.22 is approximately $5,000: In the worksheet shown above, the formula in C10 is:
Present value of annuity
To calculate the present value of an annuity that pays 10,000 per year for 25 years, with an annual interest rate of 7%: To returns a positive present value, enter payment as a negative number: Also see: Present value of an annuity.
Investment goal
To calculate the initial investment required to reach $15,000 in 10 years with an annual interest rate of 5%: Enter future value as a negative number to get a positive result:
PV versus NPV
Both the PV function and the NPV function calculate present value, but there are differences in the way they operate:
The PV function can only be used when cash flows are constant and don’t change. The NPV function can be used to calculate the present value of uneven cash flows spaced evenly in time. The PV function has a type argument to handle regular annuities and annuities due. The NPV function always assumes a regular annuity, where payments are due at the end of the period.
Notes
A stream of cash flows that includes the same amount of cash outflow (or inflow) each period is called an annuity. For example, a car loan or a mortgage is an annuity. When each period’s interest rate is the same, an annuity can be valued using the PV function. In annuity functions, cash you pay out (such as a deposit to savings) is represented by a negative number; cash you receive, such as a dividend check, is represented by a positive number. For a $2,500 deposit to a bank, pmt would be -2500 if you are the depositor, and 2500 if you are the bank.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
