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 flow. See my Annuity Calculator for the details.


Billy Gee said...

These are very useful functions, especially for financial analysis. I would mention the excellent excel template installed with the software for loan amortization. It iv very impressive and a great learning tool for a more advanced worksheet.

Anonymous said...

I try to calculate annuity using the PMT formula Unlikely my ecxcel does not recognise the formula. Any recommendation/ comments would be appreciated.
Thanks. Florin

Anonymous said...

Very useful info , but a little correction ...

TYPE=1 for annuity due
TYPE=0 for regular annuity ( PMT at the end of the year )


Anonymous said...

Rather remarkable that there isn't a single numerical example...

annuity rates comparison said...

Excel formulas are at best here. This is by far most complex formula one can apply over excel cells. What Excel version should I try for this annuity formula.

Andy Volodin said...

Thanks for these forumals. I was searching them to calculate things for my projects. The excel is a great tool. TYPE=1 for annuity due. To calculate loans - that's it.