Calculating the Periodic Payment (PMT) in an Ordinary Annuity
Sometimes we need to solve for the payment amount within ordinary annuities. We can use MS Excel to do that using the PMT function.
where:
- Rate is the interest rate for the loan.
- Nper is the total number of payments for the loan.
- Pv is the present value, or the total amount that a series of future payments is worth now; also known as the principal.
- Fv is the future value, or a cash balance you want to attain after the last payment is made. (if FV is omitted, it is assumed to be 0)
- Type is the number 0 or 1 that indicates when payments are due.
- 0 indicates at the end of the period
- 1 indicates at the beginning of the period
- If type is omitted, it is assumed to be 0
Example 1
Markham Auto Body wishes to accumulate a fund of $300,000 during the next 18 months in order to open at a second location. At the end of each month, a fixed amount will be invested in a money market savings account with an investment dealer. The planning assumption is that the account will earn 3.6% compounded monthly. What should the monthly investment be in order to reach the savings objective? (Source: Jerome & Worswick, 2020, Example 12.1A).
For this example we are given:
- Nominal Rate = 3.6%
- Compounding / year = 12
- PV = 0
- FV = 300,000
- Type = 0
- number of years (18 months = 1.5 years)
We can set up our spreadsheet to calculate the payment for both simple and general ordinary annuities.
Try recreating the spreadsheet above on your own.
Click this link to see the completed spreadsheet: calculating pmt for ordinary annuity – Template 1
Example 2
A $5,000 loan requires payments at the end of each quarter for four years. The interest rate on the loan is 9% compounded monthly. What is the size of each payment? (Source: Jerome & Worswick, 2020, Example 12.1B).
For this example we are given:
- Nominal Rate = 9%
(compounding frequency) = 12
(payment frequency) = 4- PV = 5,000
- FV = 0
- Type = 0
- number of years: 4
We can set up our spreadsheet to calculate the payment for both simple and general ordinary annuities.
Try recreating the spreadsheet above on your own.
Click this link to see the completed spreadsheet: calculating pmt for ordinary annuity – Template 2
For more information on the PMT function, see Microsoft Support
