Annuity Functions in Excel

Microsoft Excel provides various functions to perform constant annuity calculations. These functions can be used for both loans and investments.

Constant annuities are streams of equal cash flows occurring at equal intervals.

All annuities are governed by a set of variables. Excel provides functions for all these variables.

These variables are:

Rate: The interest rate per period

Nper: Total number of periods in the annuity

PMT: The payment per period

PV: The present value of cash flows. The total amount that a series of future cash flows is worth now.

FV: The future value of cash flows based on periodic, equal cash flows.

An additional input is the “Type”, which is 0 for a regular annuity or annuity in arrears where the first payment occurs one period from now and 1 for an annuity due or annuity in advance where the first payment occurs now.

The excel functions for these annuity variable are discussed below:

PMT

This function calculates the payment for a loan based on constant payments and a constant interest rate.

PMT (rate, nper, pv, fv, type)

Example:

You have taken a loan for $100,000 with an annual interest rate of 7%. You want to repay this loan in 10 equal annual installments. What will be the annual payment?

Enter the formula:

= PMT (7%, 10, 100,000, 0, 0)

= - $14,237.75

Note that both Fv and type are optional arguments. Their default value is zero.

Further, assume that your lender allows you to make a final payment of $10,000 after 10 years, to help you reduce the annual payments. This is where the variable fv will come to use.

The formula now will be:

= PMT (7%, 10, 100,000, -10,000, 0)

= - $13,513.98

The other formulas are:

PV(rate,nper,pmt,fv,type)

FV(rate,nper,pmt,pv,type)

NPER(rate,pmt,pv,fv,type)

RATE(nper,pmt,pv,fv,type,guess)

You can use the same example above to try these functions.