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)
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.
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%
|
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 
is:
![\[ i= \frac{j}{m} \]](https://ecampusontario.pressbooks.pub/app/uploads/quicklatex/quicklatex.com-a3b371b93431ebe5007b83129dca458e_l3.png "Rendered by QuickLaTeX.com")
where
is the nominal interest rate
is the number of payments/compounding per year
In the example above,
- is in the cell B1
- 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 
, which is the nominal rate compounded 
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 
(the periodic rate).
The formula to determine is:
![\[ i_2=(1+ i_1) ^\frac{m_1}{m_2}\]](https://ecampusontario.pressbooks.pub/app/uploads/quicklatex/quicklatex.com-0ac22aabd0f16d5475202d961783822d_l3.png "Rendered by QuickLaTeX.com")
where
is the number of payments per year
In excel, the formula would be written like this 
^
- 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 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 then referenced that cell in the PMT function.
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.
