Microsoft Office Tutorials and References
In Depth Information
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.
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
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
rate, nper, pv, fv, type
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.