Part II: Working with Formulas and Functions
FIGURE 15.14
Conditional formatting to hide rows
This formula compares the payment number in column D to the number of years in C4 times
- When the payment number is larger, the formula returns TRUE, and the white font color
formatting is applied. When the payment number is less than or equal to the total number
of payments, no conditional formatting is applied.
See Chapter 5, “Formatting Worksheets,” for more information on conditional formatting.
Creating a variable-rate mortgage amortization schedule
In Figure 15.13, we created an amortization schedule for a loan with a fixed interest rate.
There are also loans where the rate changes at times during the life of the loan. Often,
these loans have an interest rate that’s tied to a published index, such as the London
Interbank Offered Rate (LIBOR), plus a fixed percentage. Those interest rates are usually
stated as “LIBOR plus 3%,” for example.
Figure 15.15 shows an amortization schedule for a loan with a variable interest rate. A Rate
column was added to the amortization schedule so that it will be obvious when the changes
occur. A separate table is used to record when the rate changes.
