Investment Science: Amortization schedule of a Mortgage/loan payment

In this post, we will look into the amortization schedule of a mortgage/loan payment, which will breakdown the loan into equal periodic payments (typically monthly payments for a car of home loan), also this schedule will contain the interest paid on a particular period or month, the principal paid, balance principal remaining after the current payment, the schedule repeats itself till the outstanding payment reaches zero.

We will illustrate the amortization schedule with a simple example.

Consider a loan of $1000 for 5 years at an yearly interest rate of 10% payable every year.

We know that the present value (P) of an annuity that pays an amount A every individual period for 'm' periods for 'n' years at an yearly interest rate 'r' is given by

For the above case,

P = $1000, n = 5, m = 1, r = 10%, r/m = 10%, therefore

A = $263.80

This is the yearly amount paid for the loan, the total interest paid = A * (n * m) - P = (263.80 * 5) - 1000 = $319

We will break this down into yearly schedule based on the below reusable spreadsheet for computing the amortization schedule for a given mortgage/loan payment.

Reusable Spreadsheet for computing the amortization schedule of a mortgage/loan payment

The above spreadsheet can be used for any mortgage/loan payment for a principal 'P' for 'm' periods per year for 'n' years at an yearly interest rate r%.

From the above figure, we can infer the following properties.

1. The balance in principle at any period i is given by

For example, for the above loan schedule, the balance principal after two years is given by

Using the spreadsheet:

The above excel can be reused for any mortgage/loan payment for a principal 'P' for 'm' periods per year for 'n' years at an yearly interest rate r%, for using this, all you need to do is to copy the contents of row 15 (corresponding to period 1) of the spreadsheet to the next n * m - 1 rows (till the balance principal becomes zero).