Using Excel to solve Annuities Due Problems
Annuities Due are similar to Ordinary Annuities except that the payment happens at the beginning of the payment interval rather than the end. The word problems will be very similar and include the same concepts; however, we must now pay careful attention to whether the payment is at the beginning or end of the payment interval.
In Excel, we use all the same formulas & spreadsheets that we did for Ordinary Annuities, only this time, we change the “type” argument.
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)
To indicate to Excel which type of Annuity it is, we need to tell it when the payments are due:
| Set type equal to | If payments are due |
|---|---|
| 0 or omitted | At the end of the period |
| 1 | At the beginning of the period |
Example 1
Grace is considering expanding the size of her store. The space directly next to hers just became vacant and went up for sale. Grace is now discussing the terms of a $50,000 loan with their bank’s lending officer. The interest rate on the loan is 7.5% compounded monthly. Previously (see More Examples: Calculating PMT, n, and i for Ordinary Annuities), Grace explored making payments at the end of the month. Let’s see if there is a difference if she makes payments at the beginning of the month.
How many months will it take to repay the loan if the beginning-of-month payments are $350?
Let’s start with the spreadsheet that we used when Grace paid at the end of the month:
Notice, the Annuity type = 0. This means that we are calculating the number of payments for an Ordinary annuity. To solve for the number of payments for an annuity DUE, we change this number to 1.
When we change the type to 1 (pay at the beginning of the month), we see that it will take Grace 351 payments instead of 359 payments.
How many months will it take to repay the loan if the beginning-of-month payments are $100 higher?
Try this yourself, then check your solution: Calculating Annuity DUE – NPER (n) for grace – Template 2
