5.1 Basics

К оглавлению1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 
17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 
34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 
68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 
85 86 87 88 89 90 91 92 93 94 95 96 97 

Problem. To purchase a house, you take out a 30 year mortgage. The present value (loan amount) of the

mortgage is $300,000. The mortgage charges an interest rate / year of 8.00%. What is the annual payment

required by this mortgage? How much of each year's payment goes to paying interest and how much

reducing the principal balance?

Solution Strategy. First, we use Excel’s PMT function to calculate the annual payment of a 30 year

annuity (mortgage). Then we will use a time line and simple recursive formulas to split out the payment

into the interest component and the principal reduction component.

FIGURE 5.1 Spreadsheet for Loan Amortization - Basics.

How To Build Your Own Spreadsheet Model.

1. Inputs. Enter the inputs in the range B4:AF9.

2. Year and Freeze Panes. Enter 1, 2, 3, …, 31. in the range B9:G11. A simple way to do this is to

enter 1 in cell B9, enter 2 in cell C9, hover the cursor over the lower right corner of cell C9, and

when you see the "fill handle" (it looks a "+" sign) drag it all the way across to cell G11. Select

C10 and click on Window | Freeze Panes. This locks in the column and row titles.

3. Beg. Principal Balance. The principal balance at the beginning of Year 1 is the full amount of

the loan (i.e., the present value). Enter =B4 in cell B10. We will return to the rest of this line in a

moment.

4. Payment. The Excel PMT function can be used to calculate an annuity payment using the

following format =PMT(Interest Rate / Year, Number of Years, -Present Value, 0). Enter

=PMT($B$5,$B$6,-$B$4,0) in cell B11. The $ signs in the formula lock in the row and column

when copying.

5. Interest Component in year t = (Interest rate/year) * (Beginning Principal Balance in year t).

Enter =$B$5*B10 in cell B12.

6. Principal Component in year t = Payment - (Interest Component). In other words, whatever

part of the payment is leftover after paying the interest goes to reducing the principal balance.

Enter =B11-B12 in cell B13.

7. Beg. Principal Balance in year t = (Beg. Principal Balance in year t-1) - (Principal Component

in year t-1). Enter =B10-B13 in cell C10

8. Copy The Formulas. Select the range B11:B13 and copy it to C11. Select the range C10:C13

and copy it to the range D10:AE10. Select the cell AE10 and copy it to AF10.

The Annual Payment is $26,648. Figure 2 shows the final years of the time line for the loan.

FIGURE 5.2 Final Years of the Time Line of Loan Amortization - Basics.

The principal balance drops to zero in year 31 after the final payment is made in year 30. The loan is paid

off! It doesn't matter whether the zero amount in cell AF10 displays as positive or negative. The only

reason it would display as negative is due to round off error in the eighth decimal or higher, which is

irrelevant of our purposes.

The Interest Component depends on the size of the Beg. Principal Balance. In year 1 the interest

component starts at its highest level of $24,000 because the Beg. Principal Balance is at its highest level

of $300,000. The interest component gradually declines over time as the Principal Balance gradually

declines over time. The interest component reaches its lowest level of $1,974 as the Beg. Principal

Balance reaches its lowest level of $300,000. The principal repayment component is the residual part of

the payment that is left over after the interest component is paid off. In year 1 when the interest

component is the highest, the principal component is the lowest. Even though you made a payment of

$26,648 in year 1, only $2,648 of it went to paying off the principal! The principal payment gradually

increases over time until it reaches its highest level of $24,674 in year 30.

Problem. To purchase a house, you take out a 30 year mortgage. The present value (loan amount) of the

mortgage is $300,000. The mortgage charges an interest rate / year of 8.00%. What is the annual payment

required by this mortgage? How much of each year's payment goes to paying interest and how much

reducing the principal balance?

Solution Strategy. First, we use Excel’s PMT function to calculate the annual payment of a 30 year

annuity (mortgage). Then we will use a time line and simple recursive formulas to split out the payment

into the interest component and the principal reduction component.

FIGURE 5.1 Spreadsheet for Loan Amortization - Basics.

How To Build Your Own Spreadsheet Model.

1. Inputs. Enter the inputs in the range B4:AF9.

2. Year and Freeze Panes. Enter 1, 2, 3, …, 31. in the range B9:G11. A simple way to do this is to

enter 1 in cell B9, enter 2 in cell C9, hover the cursor over the lower right corner of cell C9, and

when you see the "fill handle" (it looks a "+" sign) drag it all the way across to cell G11. Select

C10 and click on Window | Freeze Panes. This locks in the column and row titles.

3. Beg. Principal Balance. The principal balance at the beginning of Year 1 is the full amount of

the loan (i.e., the present value). Enter =B4 in cell B10. We will return to the rest of this line in a

moment.

4. Payment. The Excel PMT function can be used to calculate an annuity payment using the

following format =PMT(Interest Rate / Year, Number of Years, -Present Value, 0). Enter

=PMT($B$5,$B$6,-$B$4,0) in cell B11. The $ signs in the formula lock in the row and column

when copying.

5. Interest Component in year t = (Interest rate/year) * (Beginning Principal Balance in year t).

Enter =$B$5*B10 in cell B12.

6. Principal Component in year t = Payment - (Interest Component). In other words, whatever

part of the payment is leftover after paying the interest goes to reducing the principal balance.

Enter =B11-B12 in cell B13.

7. Beg. Principal Balance in year t = (Beg. Principal Balance in year t-1) - (Principal Component

in year t-1). Enter =B10-B13 in cell C10

8. Copy The Formulas. Select the range B11:B13 and copy it to C11. Select the range C10:C13

and copy it to the range D10:AE10. Select the cell AE10 and copy it to AF10.

The Annual Payment is $26,648. Figure 2 shows the final years of the time line for the loan.

FIGURE 5.2 Final Years of the Time Line of Loan Amortization - Basics.

The principal balance drops to zero in year 31 after the final payment is made in year 30. The loan is paid

off! It doesn't matter whether the zero amount in cell AF10 displays as positive or negative. The only

reason it would display as negative is due to round off error in the eighth decimal or higher, which is

irrelevant of our purposes.

The Interest Component depends on the size of the Beg. Principal Balance. In year 1 the interest

component starts at its highest level of $24,000 because the Beg. Principal Balance is at its highest level

of $300,000. The interest component gradually declines over time as the Principal Balance gradually

declines over time. The interest component reaches its lowest level of $1,974 as the Beg. Principal

Balance reaches its lowest level of $300,000. The principal repayment component is the residual part of

the payment that is left over after the interest component is paid off. In year 1 when the interest

component is the highest, the principal component is the lowest. Even though you made a payment of

$26,648 in year 1, only $2,648 of it went to paying off the principal! The principal payment gradually

increases over time until it reaches its highest level of $24,674 in year 30.