13.3 Amortization Schedule
Amortization Schedule
In the previous two sections, you have been working on parts of an entire puzzle. You have calculated the interest and principal portions for either a single payment or a series of payments. Additionally, you calculated the final payment amount along with its principal and interest components. The next task is to put these concepts together into a complete understanding of amortization. This involves developing a complete amortization schedule for an annuity (loan or investment annuity). Additionally, you will create partial amortization schedules that depict specific ranges of payments for a particular annuity.
The Complete Amortization Schedule
An amortization schedule shows the payment amount, principal component, interest component, and remaining balance for every payment in the annuity. As the title suggests, it provides a complete understanding of where the money goes.
Your BAII Plus Calculator
The calculator speeds up the repetitive calculations developed in the previous two sections required in the amortization schedule. To create the schedule using the calculator, adapt the steps as follows:
Step 1: Load the calculator with all seven time value of money variables, solving for any unknowns. Ensure that PMT is keyed in with two decimals, and obey the cash flow sign convention.
Steps 2–4: Unchanged.
Steps 5–7: Open the AMORT function. Set the P1 = 1 and P2 = 1. In the appropriate column, record the BAL, PRN, and INT rounded to two decimals.
Step 8: Repeat steps 5–7 by increasing the payment number (P1 and P2) by one each time. Ensure P1 = P2 at all times.
Step 9: Unchanged.
Step 10: Set the P1 and P2 to the final payment number. Record the INT amount.
Steps 11–12: Unchanged.
Step 13: Set the P1 = 1 and P2 = final payment number. Record the INT amount.
Important Notes
The “Missing Penny”
In the creation of the amortization schedule, you always round the numbers off to two decimals since you are dealing with currency. However, as per the rules of rounding, you do not round any numbers in your calculations until you reach the end of the amortization schedule and the annuity has been reduced to zero.
As a result, you have a triple rounding situation involving the balance along with the principal and interest components on every line of the table. What sometimes happens is that a “missing penny” occurs and the schedule needs to be corrected as per step 12 of the process above. In other words, calculations will sometimes appear to be off by a penny. You can identify the “missing penny” when one of the two standard calculations using the rounded numbers from the schedule becomes off by a penny.
In these instances of the “missing penny”, you adjust the schedule as needed to ensure that the math works properly at all times. The golden rule, though, is that the balance in the account (BAL) is always correct and should NEVER be adjusted. Follow this order in making any adjustments:
- Adjust the PRN if necessary such that the previous BAL – PRN = current BAL.
- Then adjust INT if necessary such that PMT – PRN = INT
Usually these adjustments come in pairs, meaning that if you need to adjust the PRN up by a penny, somewhere later in the schedule you will need to adjust the PRN down by a penny. Ultimately, these changes in most circumstances have no impact on the total interest (INT) or total principal (PRN) components, since the “missing penny” is nothing more than a rounding error within the schedule.
Example 13.3.1: Payment Plan on a Dishwasher
Tamara purchased a new dishwasher from The Bay for $895.94. By placing it on her Bay credit card, she can pay off the dishwasher through a special six-month payment plan promotion that charges her 5.9% compounded monthly. Construct the complete amortization schedule for Tamara and total her interest charges.
Solutions:
Construct a complete amortization schedule for the dishwasher payments along with the total interest paid.
Calculator instructions:
| N | I/Y | PV | PMT | FV | P/Y | C/Y |
|---|---|---|---|---|---|---|
| 6 | 5.9 | 895.94 |
Answer: -151.903441 Re-keyed as: -151.90 |
0 | 12 | 12 |
| Payment Number | Payment Amount ($) (PMT) | Interest Portion ($) (INT) | Principal Portion ($) (PRN) | Principal Balance Remaining ($) (BAL) |
|---|---|---|---|---|
| 0–Start | $895.94 | |||
| 1 | $151.90 | $4.41 | $147.49 | $748.45 |
| 2 | $151.90 | $3.68 | $148.22 | $600.22 |
| 3 | $151.90 | $2.95 | $148.95 | $451.28 |
| 4 | $151.90 | $2.22 | $149.68 | $301.59 |
| 5 | $151.90 | $1.48 | $150.42 | $151.18 |
| 6 | $151.90 + BAL | $0.74 | $151.16 + BAL | $0.02 |
| Total | $911.42 | $15.48 | $895.94 + BAL |
The complete amortization schedule is shown in the table below. Adjust for the “missing pennies” (noted in bold italics) and total the interest.
| Payment Number | Payment Amount ($) (PMT) | Interest Portion ($) (INT) | Principal Portion ($) (PRN) | Principal Balance Remaining ($) (BAL) |
|---|---|---|---|---|
| 0–Start | $895.94 | |||
| 1 | $151.90 | $4.41 | $147.49 | $748.45 |
| 2 | $151.90 | $3.67 | $148.23 | $600.22 |
| 3 | $151.90 | $2.96 | $148.94 | $451.28 |
| 4 | $151.90 | $2.21 | $149.69 | $301.59 |
| 5 | $151.90 | $1.49 | $150.41 | $151.18 |
| 6 | $151.92 | $0.74 | $151.18 | $0.00 |
| Total | $911.42 | $15.48 | $895.94 |
The total interest Tamara is to pay on her dishwasher is $15.48.
The Partial Amortization Schedule
Sometimes, businesses are interested only in creating partial amortization schedules, which are amortization schedules that show only a specified range of payments and not the entire annuity. This may occur for a variety of reasons. For instance, the complete amortization schedule may be too long (imagine weekly payments on a 25-year loan), or maybe you are solely interested in the principal and interest portions during a specific period of time for accounting and tax purposes.
Example 13.3.2: A Partial Loan Amortization Schedule on a Loan
Molson Coors Brewing Company just acquired $1.2 million worth of new brewing equipment for its Canadian operations. The terms of the loan require end-of-quarter payments for eight years at 8.3% compounded quarterly. For accounting purposes, the company is interested in knowing the principal and interest portions of each payment for the fourth year and also wants to know the total interest and principal paid during the year. Construct the partial amortization schedule.
Solution:
Construct a partial amortization schedule for the fourth year of the loan along with the total interest and principal paid during the year.
Calculator instructions:
| N | I/Y | PV | PMT | FV | P/Y | C/Y |
|---|---|---|---|---|---|---|
| 32 | 8.3 | 1,200,000 |
Answer: -51,691.71391 Re-keyed as: -51,691.71 |
0 | 4 | 4 |
| Payment Number | Payment Amount ($) (PMT) | Interest Portion ($) (INT) | Principal Portion ($) (PRN) | Principal Balance Remaining ($) (BAL) |
|---|---|---|---|---|
| 12 | $839,147.91 | |||
| 13 | $51,691.71 | $17,412.32 | $34,279.39 | $804,868.52 |
| 14 | $51,691.71 | $16,701.02 | $34,990.69 | $769,877.83 |
| 15 | $51,691.71 | $15,974.96 | $35,716.75 | $734,161.08 |
| 16 | $51,691.71 | $15,233.84 | $36,457.87 | $697,703.22 |
| Total | $206,766.84 | $65,322.15 | $141,444.69 |
The partial amortization schedule for the fourth year is shown in the table below. Adjust for the “missing pennies” (noted in bold italics) and total the interest.
| Payment Number | Payment Amount ($) (PMT) | Interest Portion ($) (INT) | Principal Portion ($) (PRN) | Principal Balance Remaining ($) (BAL) |
|---|---|---|---|---|
| 12 | $ 839,147.91 | |||
| 13 | $51,691.71 | $17,412.32 | $34,279.39 | $804,868.52 |
| 14 | $51,691.71 | $16,701.02 | $34,990.69 | $769,877.83 |
| 15 | $51,691.71 | $15,974.96 | $35,716.75 | $734,161.08 |
| 16 | $51,691.71 | $15,233.85 | $36,457.86 | $697,703.22 |
| Total | $206,766.84 | $65,322.16 | $141,444.69 |
The total interest paid in the year is $65,322.15, and the principal portion is $141,444.69.
Exercises
In each of the exercises that follow, try them on your own. Full solutions are available should you get stuck.
- A farmer purchased a John Deere combine for $369,930. The equipment dealership sets up a financing plan to allow for end-of-quarter payments for the next two years at 7.8% compounded monthly. Construct a complete amortization schedule and calculate the total interest.
- Ron and Natasha had Oasis Leisure and Spa install an in-ground swimming pool for $51,000. The financing plan through the company allows for end-of-month payments for two years at 6.9% compounded quarterly. Ron and Natasha instruct Oasis to round their monthly payment upward to the next dollar amount evenly divisible by $500. Create a schedule for the first three payments, payments seven through nine, and the last three payments.
- Hillary acquired an antique bedroom set recovered from a European castle for $118,000. She will finance the purchase at 7.95% compounded annually through a plan allowing for payments of $18,000 at the end of every quarter.
a) Create a complete amortization schedule and indicate her total interest paid.
b) Recreate the complete amortization schedule if Hillary pays two additional top-up payments consisting of 10% of the principal remaining after her third payment as well as her fifth payment. What amount of interest does she save?
For full solutions see Solution to Exercises.
