Annuity Formula

The PV, FV, NPER, RATE, and PMT functions in Excel can be used for both an ordinary annuity (payments made at the end of the period, type=0) and annuity due (payments made at the beginning of the period, type=1).

The PMT function can be used to calculate the annuity payment amount given the annual interst rate (i), number of payments (n), and initial principal (P).

A =PMT( i, n, -P, 0, type)

The PV function can be used to calculate the present value of the annuity. When the payment amount represents withdrawals from a retirement account, the present value would represent the initial principal:

P =PV( i, n, -A, 0, type)

The FV function can be used to calculate the future value of an annuity:

F =FV( i, n, -A, 0, type)

The NPER function can be used to calculate the number of payments:

n =NPER( i, -A, P, 0, type)

The RATE function can be used to calculate the interest rate. It requires iteration and an initial guess at the rate (default is 0.1):

i =RATE(n, -A, P,0, type, guess)

You can also use these functions for an inflation-adjusted annuity payment, which is actually an exponential gradient series cash