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:

Excel: Annuities
Excel: Annuities

The diagram below shows the typical evolution of an annuity: At first, interest payments are high, and the amount of principal paid back is low. Towards the end of the loan, the weights change. Home-owners with mortgages will be familiar with this development.

Excel: Development of an annuity over time
Excel: Development of an annuity over time

Excel is a very flexible tool for annuity calculations: Instead of calculating the amount of an annuity, you can solve the problem for any other variable. For example, if you know a company can afford a fixed amount of loan payments (i.e. the annuity) over a fixed amount of time, you could derive the possible amount of money the company could borrow, and so on. The relevant functions are PV (solves for the present value), FV (the future value), NPER (the number of periods), and RATE (the interest rate).

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.