Chapter 15: Using Formulas for Financial Analysis
15
Principal The amount of each payment applied to the loan balance. The formula =E11-
G11 is entered in F11 and filled down through F370.
Interest The amount of each payment that is interest. The balance after the prior pay-
ment is multiplied by the interest rate divided by 12. The total is rounded to two decimal
places. The formula =ROUND(H10*$C$3/12,2) is entered in G11 and filled down through
G370.
FIGURE 15.13
A partial amortization schedule
Balance The balance of the loan after the payment. The formula =C2 is entered in H10
representing the original amount of the loan. Starting in C3 and continuing down to C370,
the formula =H10-F11 reduces the balance by the principal portion of the payment.
In the example shown in Figure 15.13, the number of years was entered as 15, compared to
30 in Figure 15.12. Reducing the length of the loan increases the amount of the payment.
The final step is to hide rows beyond the loan term. This is done with conditional format-
ting that changes the font color to white. A white font color against a white background
effectively hides the data. The formula for the conditional formatting is shown here and in
Figure 15.14.
=$D12>$C$4*12