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

=IF(G14+E15–Monthly_Payment–D15<5,G14+E15–D15,Monthly_Payment)

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

=IF(G14+E15<Monthly_Payment+D15,G14+E15–D15,Monthly_Payment)

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

=ROUND(G14*B15*(A15–A14)/365,2)

The principal column calculation is similar to the previous example except that any additional

payment must be added in. The formula in F15 is

=C15+D15–E15

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.

Finishing touches

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

➜

Styles

➜

Conditional Formatting command. Add a formula rule with this formula:

=$G14<=0