What you need to know before you start

When solving annuities problems (Ordinary or Annuities Due), we will be using a number of the built in functions of Excel. Specifically,

  • FV(rate, nper, pmt, pv, type)
  • PV(rate, nper, pmt, fv, type)
  • NPER(rate,pmt,pv,fv,type)
  • PMT(rate,nper,pv,fv,type)
  • RATE(nper,pmt,pv,fv,type)
  • CUMIPMT(rate,nper,pv,start_period,end_period,type)
  • CUMPRINC(rate,nper,pv,start_period,end_period,type)

There are a number of “arguments” (input you give to a function) that are required in order for the function to work properly.

Entering Rates and the Percent Format

To ensure you enter the rates correctly, please review the note on Entering Rates in Excel

Often, a question will give you information, but you will need to do some calculations in order to use it in the function. In particular, nper and rate often need to be calculated in order to be used in the function.

nper is the total number of payments for the annuity

Total Number of Payments in the Annuity is

=

Number of payments per year    x   Number of years of the annuity

For example, if you took a loan for $1000 for 10 years and you made payments monthly,

Number of payments in the annuity = 12 x 10 = 120 payments in total

Entering input:

When using the functions in Excel, we can enter the input into the function in a number of ways:

  • enter the number calculated above
  • enter the formula =10*12
    • Note: in Excel, we must use a ” * ” sign for multiplication
  • use a cell reference

The first two options are inefficient, especially if you are using more than one formula in your spreadsheet.

Instead, use the third option; enter your values/formulas in a cell and use cell references (as shown below)

screen shot - functions - 1

As you can see above, I have still entered some values as input into my function.

To make things even more flexible, I can enter the values in another area and use cell references for the entire function.

screenshot updated function - 2

This way, should I need to change any of the input values, it will automatically update the results of my function.

Original

Rate changed to 1.5%

Screen shot of Excel using functions - changed

rate is the periodic interest rate for the loan

Simple Annuity (payments per year = compounding per year)

For a simple annuity, whether it’s an Ordinary Annuity or Annuity Due, the formula to determine i is:

    \[ i= \frac{j}{m} \]

where

  • j is the nominal interest rate
  • m is the number of payments/compounding per year

In the example above,

  • j is in the cell B1
  • m is in the cell B3

the Excel formula would be =B3/B1

General Annuity (payments per year ≠ compounding per year)

For a general annuity, whether its an Ordinary Annuity or Annuity Due, we are given i_1, which is the nominal rate compounded m_1 times per year. Since the compounding per year is not equal to the payments per year, we have to use a more complicated formula to find i_2 (the periodic rate).

The formula to determine i_2 is:

    \[ i_2=(1+ i_1) ^\frac{m_1}{m_2}\]

where

  • i_1 = \frac{j}{m_1}
  • m_2 is the number of payments per year

In excel, the formula would be written like this =(1+i_1)^(m_1/m_2)-1

  • to indicate an exponent, you use the ” ^ ” symbol

Note: Cell references are easier to work with and more efficient when working with larger spreadsheets

Example of the formula for i_2 for a General Annuity

The following example uses cell references in the PMT function for a general annuity.

To simplify the spreadsheet, I first calculated i_2 then referenced that cell in the PMT function.

screenshot using formula for i2

PMT, PV and FV

Although you can normally use the  numbers as given in a question, the sign (+  or -) must be entered correctly for the function to give you the correct output.

See the chapter on Cash Flow Sign Convention for more information on this.

 

License

Icon for the Creative Commons Attribution-NonCommercial 4.0 International License

Using Excel in Business Math Copyright © by Lisa Koster is licensed under a Creative Commons Attribution-NonCommercial 4.0 International License, except where otherwise noted.

Share This Book