Chapter 15: Using Formulas for Financial Analysis
15
FIGURE 15.15
A variable rate amortization schedule
The Rate column contains the following formula to select the proper rate from the rate
table:
=VLOOKUP(D11,$K$11:$L$23,2,TRUE)The Interest column formula changes to use the rate in column G rather than the rate in C3:
=ROUND(I10*G11/12,2)The Rate column uses a VLOOKUP with a fourth argument of TRUE. The fourth argument of
TRUE requires that the rate table be sorted in ascending order. Then VLOOKUP looks up the
payment number in the rate table. It doesn’t require an exact match, but it returns the row
where the next payment number is larger than the lookup value. For instance, when the
lookup value is 16, VLOOKUP returns the second row of the rate table because the payment
number in the next row, 98, is larger than the lookup value.
See Chapter 14 for more examples of VLOOKUP.The interest rate column formula is similar to the one used in the example shown in
Figure 15.13, except that the absolute reference to $C$3 is replaced by a reference to
column G (G11 for the formula in row 11).