Microsoft Office Tutorials and References

In Depth Information

The formula in cell G15 (and copied down) calculates the principal portion of the payment (and additional pay-

ment, if made):

=(D15–F15)+E15

Finishing touches

Because the loan term is specified by the user, I used conditional formatting to hide the rows that extend beyond

the specified term.

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:H374 and choose the Home
⇒
Styles
⇒
Conditional Format-

ting command. Add a formula rule with this formula:

=$H15<0

For more information on conditional formatting, see Chapter 19.

Credit card calculations

Another type of loan amortization schedule is for credit card loans. Credit cards are different because the min-

imum payment varies, based on the balance. You could use the method where the payments are entered directly

in the schedule. When the payments are different every time, however, the schedule loses its value as a predictor

or planner. Here, I describe a schedule that can predict the payments of a credit card loan.

Credit card calculations represent several nonstandard problems. Excel's financial functions (PV, FV, RATE,

and NPER) require that the regular payments are at a single level. In addition, the PMT function returns a single

level of payments. With IRR and NPV analysis, the user inserts the varying payments into a cash flow.

Credit card companies calculate payments based on the following relatively standard set of criteria:

•
A minimum payment is required.
For example, a credit card account might require a minimum monthly

payment of $25.

•
The payment must be at least equal to a base percentage of the debt.
Usually, the payment is a percent-

age of the balance but not less than a specified amount.

•
The payment is rounded,
usually to the nearest $0.05.

•
Interest is invariably quoted at a given rate per month.

Figure 13-3 shows a worksheet set up to calculate credit card payments.