Next Generation Excel: Modeling In Excel For Analysts And MBAs (For MS Windows And Mac OS), 2nd Edition

Chapter 32

Amortization Tables

Amortization 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—such as the United States—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 comprehension of the PMT function is important before moving on to applying the other financial functions, a brief ...