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

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

A =

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

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.

**$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.

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

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

1. The balance in principle at any period i 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).

## No comments:

Post a Comment