Present value and annuity calculator

All five quantities are linked by one time-value-of-money equation, the one behind spreadsheet PV, FV, PMT, NPER and RATE. Give any four and the fifth follows; the rate is found numerically.

PV(1 + i)^n + PMT(1 + i·t)((1 + i)^n − 1)/i + FV = 0
i = annual rate / periods per year; t = 1 for payments at the start of each period, else 0

Assumptions and limits

• Signs matter: amounts you pay out are negative and amounts you receive are positive, so an investment and its payouts have opposite signs.
• The rate per period is the annual rate divided by the periods per year (a nominal rate).
• When several rates satisfy the equation, the one nearest 10% a year is reported.
• An educational calculation, not financial, tax, legal or investment advice.

Worked example

An annuity paying 500 a month for 20 years at 8% has a present value of −59,777.15, as spreadsheet PV(8%/12, 240, 500) returns: you would pay 59,777.15 now to receive it. Solving for the rate of a 48-month loan of 8,000 repaid at −200 a month gives 9.24% a year, matching RATE(48, −200, 8000) × 12.

References

• Microsoft Excel: PV function
• Microsoft Excel: PMT function
• Microsoft Excel: NPER function
• Microsoft Excel: RATE function
• Microsoft Excel: FV function