**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:

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.

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

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

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

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.

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.

Post a Comment