Loan Amortization Schedule
Loan Amortization
Loan Amortization refers to the repayment of a loan by scheduled regular payments over time. Each payment consists of a combination of principal and interest. Each payment first pays off the interest for that period and any remaining amount of the payment is applied against the principal. The Amortization Period is the period required to repay a loan. At the end of the loan’s term, the principal balance becomes due. The term of the loan and amortization period may not be the same length. We will learn more about this as we work through this chapter.
Common loans that are amortized are auto loans, home loans (mortgages) and personal loans.
Loan Amortization Schedule
A Loan Amortization schedule breaks down each payment into its interest and principal components for the full term of the loan. It also provides the principal balance outstanding after each payment.
The schedule shows
- payment number
- payment amount
- interest portion
- principal portion
- the outstanding balance.
We can use Excel to easily construct a loan amortization schedule.
Example – Constructing an amortization schedule
Grace considers the option of renovating her current store as an alternative to expanding her bookstore. She would like to hire a designer to help her design a better layout. Her total cost of hiring the designer and the cost of renovations will be approximately $20,000. With a business renovations loan, she can expect an interest rate of 7.5% compounded quarterly over a term of 2 years. She will have end-of-quarter payments.
Step 1
Calculate the PMT required. Make sure to round to the nearest cent.
Step 2
Using the periodic interest rate, use formulas to calculate
Once you have the first row completed, you can copy each row until the final row.
TIPS
- Use absolute referencing when you create your formula for the interest portion so it copies properly.
- Use =ROUND(number/cell reference, 2) to round PMT to two decimal places
- Note: if the annuity is a General annuity, you will need to use
when calculating the interest portion. (the Excel Spreadsheet has been set up to take care of either simple or general annuities)
We will need to do separate calculations for the final payment.
Calculating the Final Payment
The final payment is calculated as follows:
Interest on the final payment
Final Payment:
Or you can use one formula:
To see the completed amortization table see the following Excel file: Amortization Schedule – Template
