Microsoft Office Tutorials and References

In Depth Information

**Chapter 32: Amortization Tables**

CHAPTER
32

Amortization Tables

A mortization is a common concept used in finance and accounting. It is defined as

being the steady decrease of a loan/liability by installments or loan repayments.

By using an amortization table, you are calculating the reduction of the balance of a

loan over time.

It is possible to download a number of amortization table programs or ready-

made Excel spreadsheets from the Internet; however, creating one by yourself will

allow you to master some of your Excel financial skills. This chapter will incorporate

some of these functions in addition to the PMT (Payment), which was covered in the

previous chapter. You will learn to apply the additional PPMT, IPMT, CUMIPMT,

and CUMPRINC Excel functions.

AMORTIZATION EXAMPLE

Consider a home loan/mortgage of $600,000 to be paid off in monthly installments

over a period of 25 years, applying an interest rate of 6.00 percent. You have to

create a loan amortization table for the 300 monthly payments (25 years

12

months/year). In addition, you may want to calculate the cumulative interest for

certain years, since in some countries

×

it is possible to

deduct this amount from your annual income when doing a tax return declaration.

Figure 32.1 shows the example described. I calculated the monthly payment

using the PMT function as covered in the previous chapter. Since excellent com-

prehension of the PMT function is important before moving on to applying the other

financial functions, a brief review is in order.

In general, the PMT function is used to calculate the payment for a loan based on

constant payments and a constant (fixed) interest rate. The Excel syntax of the function is:

—

such as the United States

—

ð

Þ

PMT

rate, nper, pv, fv, type

Where:

rate
is the interest rate per period and is expressed as a percentage. In this

example, the loan is made at a 6 percent annual interest rate, and paid back

in monthly installments. The interest rate per month is 0.5 percent (6/12).

nper
is the total number of payment periods for a loan or annuity. In this

example, since the payments are made on a monthly basis, the loan has

12*25, or 300 monthly payment periods.