Microsoft Office Tutorials and References
In Depth Information
Creating Amortization Schedules
The point of these formulas is to allow the user to overwrite the formula with a literal date value
and not affect the rest of the dates. In cell A17, the user replaced the formula by entering a new
date, which changed the calculation for that payment but did not affect future payments.
Because you provide a separate column for an additional payment, the payment should never
change — except that it needs to account for any previous rounding errors in the last payment.
The formula in C15 is
Normally, if the remaining balance is less than the normal payment, just the balance (plus
interest) is paid. However, in this example, I don’t want a last payment of less than $5. If a normal
payment would leave such a balance, it is just added to the last payment. There’s nothing wrong
with a really small final payment. If you don’t mind it, you can simplify the formula to
The interest calculation now has to account for the fact that the user may make a payment early
or late. Instead of dividing the rate by 12, as in the last example, the rate is multiplied by a ratio of
the number of days outstanding to 365. The formula in E15 is
The principal column calculation is similar to the previous example except that any additional
payment must be added in. The formula in F15 is
The balance is computed by subtracting the principal portion of the current payment from the
previous balance, exactly as it was in the previous example.
As you can see in Figure 13-2 (which hides rows in the middle so you can see the last payment),
the final payment is represented in row 127, and there are no calculations below that. I didn’t just
guess right, however. All the cells in the schedule, starting in row 15, have conditional formatting
applied to them. If column G of the row above is zero or less, both the background color and the
font color are white, rendering them invisible.
To apply conditional formatting, select the range A15:G374 and choose the Home
Conditional Formatting command. Add a formula rule with this formula: