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.

6 comments:

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 )

Thanks

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.

Unknown 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.