How to amortize a loan

One of the most familiar applications of TVOM concepts is the amortization schedule.

An amortization schedule is a list of balances, payments, and interest charges from loan inception until payoff.

For example, we have a one-year $1,000 loan at 5% compounded monthly. How do we construct a schedule that shows the allocation of payments to principal and interest over the life of the loan?

The first step is to determine the payment amount that will amortize the loan. This can be accomplished either with the TVOM equation or with the Excel PMT function.

We'll start with PV of an annuity equation.

In this situation the inputs to the PV of an annuity equation are as follows. PV = 1,000.00

i = 0.05/12

n = 1 x 12

. and rearranging the above equation to solve for PMT we get.

. so the payment amount that will amortize the $1,000 loan is $85.61 (rounded).

Alternatively, we can use the Excel PMT function to calculate the payment amount as shown here.

. where the inputs and cell formulas look like this.

Now that we know the payment amount we can prepare a schedule showing each payment and its breakout between interest and principal. The principal

piece is applied to reduce the balance at the beginning of each payment period.

Preparation of the schedule involves a lot of repetitive math operations and this makes it an ideal candidate for solution in Excel as we can see below.

. where the inputs and cell formulas look like this.

Note how the payment amount consists of both an interest and a principal piece as the total of the payments (1,027.29) is equal to the sum of the principal (1,000) and interest (27.29).

Also note how the interest portion of the payment gradually decreases over the term. This is because the interest calculation is being made against an ever shrinking principal balance.

Finally, a caution on rounding.

Note how the ending balance zeroed out. This is how an amortization schedule should work; we are paying off the loan and when we are finished there should be nothing left.

If we had directly input a value of 85.61 into the schedule we would have had a residual balance of $0.03. However, because we used a payment amount that was calculated by Excel's PMT function, the precision was much greater than 2 decimal points. As a result, the schedule fully amortizes and there is no messy residual left at the end.