# Excel: Annuity Calculations

Annuities are fixed payments over a period of time. In financial models, annuities are especially relevant to modelling interest and redemption payments for loans, bonds or leasing contracts.

For the calculation of an annuity, these are the required inputs:

• The loan interest rate per period and the total number of periods. It is important to distinguish here between nominal and effective interest rates. Interest rates are usually given as nominal annual interest rate, but compounded semi-annually, quarterly or (as is the case with most mortgages) monthly. A 12 % per year nominal interest rate, compounded semi-annually, would mean 6 % every 6 months, resulting in a 12.36 % effective annual rate.
• The present value. When modelling a loan from a borrowers perspective, this will usually be the loan's principal amount.

• The future value of the annuity. When modelling a loan from a borrowers perspective, this will usually be 0 (zero).
• Payment timing: Usually, payments are assumed at the end of a period - called an "ordinary annuity". Sometimes, annuities are due at the beginning of a period, then called "annuity due".

In Excel, PMT ("payment") is the formula that returns the total amount of the annuity based on the inputs in the list above. PPMT ("principal payment") returns the amount of the loan repayment for a specific period, whereas IPMT ("interest payment") returns the interest payment. The sum of PPMT and IPMT, naturally, is the same as the result of PMT.

The following screenshot shows how to apply PMT, PPMT and IPMT: