Microsoft Office Tutorials and References

In Depth Information

**Creating Amortization Schedules**

Credit card calculations

The final type of loan amortization schedule is for credit card loans. Credit cards are different

beasts because the minimum payment varies, based on the outstanding balance. You could use

the preceding Payment Table method, but it offers only nine rows of varying payments —

probably not enough for most applications. You could also 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. You need a schedule that can predict the future

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:

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

minimum monthly payment of $25.

h
The payment must be at least equal to a base percentage of the outstanding debt.

Usually, the payment is a percentage of the outstanding balance but not less than a

specified amount.

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

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

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

The formula for the minimum payment is rather complicated — just like the terms of a credit

card. This example uses a minimum payment amount of $25 or 3% of the balance, whichever is

larger. This small minimum payment results in a very long payback period. If this borrower ever

hopes to get rid of that balance in a reasonable amount of time, he’ll need to use that additional

payment column.

The minimum payment formula, such as the one in B13, is

=MIN(F12+D13,MROUND(MAX(MinDol,ROUND(MinPct*F12,2)),PayRnd))

From the inside out: The larger of the minimum dollar amounts and the minimum percent is

calculated. The result of that is rounded to the nearest five cents. This rounded amount is then

compared with the outstanding balance, and the lesser of the two is used.

Of course, things get much more complicated when additional charges are made. In such a case,

the formulas would need to account for “grace periods” for purchases (but not cash

withdrawals). A further complication is that interest is calculated on the daily outstanding balance at the

daily effective equivalent of the quoted rate.