Loan Amortization Schedule

Amortized loan payments are a combination of interest rate and principal. However, the share of each payment that is interest and principal changes. Each payment reduces the principal owing and so lessens the amount of interest owing in the next period.

We can calculate the amount of interest and principal in each payment using a loan amortization schedule. The schedule is very useful in business because interest payments are tax deductible.

Example 1

Consider an amortized loan with a principal of $10,000, a term of 5 years, and a rate of 10%. The annual payments are $2,637.97.


At the end of the first year the interest owing is

Interest owing=0.10×$10,000=$1,000.

The payment at the end of the first year is $2,637.97, thus the principal repaid at the end of the year is the amount left over after interest is paid:

Principal repaid=$2,637.97−$1,000=$1,637.97.

The repayment of principal lowers the balance owing (at the end of the first year) to

Principal owing= ($10,000−$1,637.97) =$8,362.03.

The full schedule of payments, interest, and principal payments is shown in Table 1.

Table 1 Amortized Loan Payment Schedule

1 Year Interest Owing at End of Year Payment Principal Repayment Principal Owing at End of Year
2 1 $10,000*0.1=1000.00 2637.97  2637.97-1000=1637.97 10,000-1637.97=8362.03
3 2 8362.03*0.1=836.20 2637.97 1801.77 6560.26
4 3 656.03 2637.97 1981.94 4578.32
5 4 457.83 2637.97 2180.14 2398.18
6 5 239.82 2637.97 2398.15 0.00

Look at the row labeled “Year 2” of Table 1, which shows values for the second year of the loan. Notice how interest owing at the end of the second year is $836.20, which is simply 10% of the principal owing at the end of the first year.

Look at the final row of the table. Notice how the last payment covers the last amount of interest owing and repays the remaining principal. Very tidy!

If you were a business owner and wanted to know your interest expense in year 2, it would be $836.20.

A quick way to solve for the principal outstanding is to use the amortized loan equation. However, we change the focal date to the date of an intermediate payment t (where t is between 0 and n, and n is the maturity date).

\[\begin{matrix}   Principa{{l}_{t}}=PMT\times PVIF{{A}_{i,n-t}} & \left( 1 \right)  \\\end{matrix}\]

This equation expresses the fact that the principal outstanding on the loan is the present value of the remaining payments. In actuarial mathematics, this is called the prospective method for solving for the loan balance.

Example 2 Principal Outstanding on an Amortized Loan

Consider an amortized loan with principal of $10,000, a 5-year term (n = 5) and an interest rate of 10%(i = 0.10). The annual payments are$2,637.9748. How much principal is owed after the first payment is made?


We can solve for the principal outstanding using Equation 1 where t = 1.

\[\begin{align}  & \text{Princiap}{{\text{l}}_{\text{t}}}\text{=PMT }\times \text{ PVIF}{{\text{A}}_{\text{i,n-t}}} \\ & PVIF{{A}_{0.10,4}}=\frac{1-\frac{1}{{{\left( 1.10 \right)}^{4}}}}{0.10}=3.1698965 \\ & \text{Princiap}{{\text{l}}_{\text{t}}}\text{=}\$\text{2637}\text{.9748}\times\text{3}\text{.169865=}\$\text{8362}\text{.03}\\\end{align}\]

There is $8,362.03 owing after the first loan payment is made.

Next we provide an Excel spreadsheet which shows how to construct a loan amortization schedule using Excel.

Excel Sheet Download

Loan Amortization Calculator

Click the link below to open the Loan amortization calculator where you can simply plugin in the numbers to compute the annual, quarterly, monthly, semi-monthly, bi- weekly, and weekly amortization schedule.

Loan Amortization Calculator