Part II: Working with Formulas and Functions
Using dates instead of payment numbers
The two amortization schedules for this section and the previous one use the payment
number to identify each payment. In reality, those payments will be due on the same day
of the month. This allows the amortization schedule to be used for loans that start on any
date. Figure 15.16 shows an amortization schedule using dates.
FIGURE 15.16
A date-based amortization schedule
To modify the schedule to show the dates, follow these steps:
- Enter the first payment date in cell D11.
- Enter the following formula in D12 and fill down:
=DATE(YEAR(D11),MONTH(D11)+1,DAY(D11))
- Change the Pmt No column in the rate table column to the date the rate
changed.
- Change the formula in the conditional formatting to the following formula:
=$D12>=DATE(YEAR($D$11),MONTH($D$11)+($C$4*12),DAY($D$11))
