Amortization Schedule

Amortization schedule is a table that shows total payments to be made on an amortizing loan, the loan balance at the start of each period, total payment during each period bifurcated into (a) interest payment and (b) principal repayment and the closing balance of the loan at the end of each period.

Loan and debt instruments take different forms: some, such as bonds, require only interest payment periodically and the principal is paid at maturity; others, mainly money-market instruments such as promissory notes, pay both the interest and principal at maturity date. Amortizing loans, on the other hand, require periodic payments comprising of (a) the interest expense accrued on the loan for the period, and (b) an amount representing repayment of the principal. Leases and mortgages are typical examples of amortizing loans.

Amortization schedule is a valuable tool that helps us understand cash flows requirements, work out periodic interest expense and find out loan balance to report on the balance sheet. An amortization schedule normally shows periods in rows and the columns show the following items (a) opening balance, (b) total payment, (c) interest expense, (d) principal repayment and (e) closing balance.

There are two types of amortization structures, the most common is where the total payment is fixed in each period and the interest component and principal repayment components fall over time; another structure is where the principal repayment is fixed for all periods and the interest and total payment figures change. We will discuss the loan amortization schedule that requires fixed total periodic payment.

Calculations

The principal balance at the start of the loan term is straight-forward, it just equals the loan amount or present value of minimum lease payments. In each subsequent period, the opening loan balance equals the loan balance at the end of previous period.

Where the total payment in fixed for each period, the periodic payment can be found out using the present value of annuity formula. We need to find PMT in the following formula

$$ Opening\ Loan\ Principal\ (PV)=PMT\times\frac{1-\left(1+\frac{APR}{m}\right)^{n\times m}}{\frac{APR}{m}} $$

Alternatively, we can use Excel PMT function.

The interest expense for a period equals the opening balance of the loan multiplied by the periodic interest rate. Periodic interest rate equals the annual percentage rate (APR) divided by the total number of compounding periods per year.

The principal repayment for a period equals total periodic payment minus the interest expense for the period.

The closing loan balance equals the opening balance of the loan minus the principal repayment during the period.

Example

You work at eZ publishing, Inc., a printing business. On 1 January 2017, your company signed a lease for printing equipment for 6 years at 8% per annum. Lease payments are to be made semiannually. The present value of the minimum lease payments is $10 million. Your CEO has asked you to calculate the interest expense related to the lease in financial year ended December 2019, the amount by which lease liability is reduced during the year, the lease liability balance as at the year end and relevant cash outflows for the year.

We can solve this problem by preparing an amortization schedule.

Solution

Your opening balance at the start of the lease is equal to the loan principal, i.e. $10 million.

The periodic lease payment equals $1,065,622 obtained by PMT(8%/2,6*2,-10000000) or solving the following equation for PMT:

$$ $10,000,000=PMT\times\frac{1-\left(1+\frac{8\%}{2}\right)^{6\times2}}{\frac{8\%}{2}} $$

Please note that interest rate for the half-year i.e. 4% (=8%/2) is included in the calculation.

Interest expense for the first period equals $400,000 (=$10,000,000 × 8% × ½) and principal repayment equals $665,522 (=$1,065,622 - $400,000).

Closing balance at the end of first period equals $9,334,478 (=$10,000,000 - $665,522).

Total payment for the second period ending 31 December 2017 equals the same as in first period because it is constant for all periods. The interest expense for the second period equals $373,379 (=$9,334,478 × 8% × ½) and the principal repayment equals $692,143 (=$1,065,522 - $373,379) and so on.

Following the same process, we can work out total payment, interest expense, repayment of principal and closing balance for each period and create the following amortization schedule:

Payment
No.
Payment
Date
Opening
Balance
Total
Payment
Interest
Expense
Principal
Repayment
Closing
Balance
ABC = A×8%/2D = B - CE = A - D
01-Jan-1710,000,0000010,000,000
130-Jun-1710,000,0001,065,522400,000665,5229,334,478
231-Dec-179,334,4781,065,522373,379692,1438,642,336
330-Jun-188,642,3361,065,522345,693719,8287,922,507
431-Dec-187,922,5071,065,522316,900748,6217,173,886
530-Jun-197,173,8861,065,522286,955778,5666,395,320
631-Dec-196,395,3201,065,522255,813809,7095,585,611
730-Jun-205,585,6111,065,522223,424842,0974,743,513
831-Dec-204,743,5131,065,522189,741875,7813,867,732
930-Jun-213,867,7321,065,522154,709910,8122,956,920
1031-Dec-212,956,9201,065,522118,277947,2452,009,675
1130-Jun-222,009,6751,065,52280,387985,1351,024,540
1231-Dec-221,024,5401,065,52240,9821,024,5400

Since the principal balance of an amortizing loan falls with each periodic payment, interest expense falls, and consequently, principal repayment component increases.

Looking at the table above, we can work out the amount of interest to be charged in financial year ended 31 December 2019, which equals $542,768 (=$286,955 + $255,813); total amount by which lease payable is reduced during the year, which is $1,588,275 (=$778,566 + $809,709); lease payable as at the year-end is $5,585,611 and total lease cash outflows for the year equals $2,131,043 (=2 × $1,065,522).

Written by Obaidullah Jan, ACA, CFAhire me at