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