Microsoft Office Tutorials and References

In Depth Information

payment results in a very long payback period. If this borrower ever hopes to pay off that balance in a reason-

able 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 res-

ult of that is rounded to the nearest five cents. This rounded amount is then compared with the outstanding bal-

ance (plus interest), 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.

Summarizing Loan Options Using a Data Table

If you're faced with making a decision about borrowing money, you have to choose between many variables,

not the least of which is the interest rate. Fortunately, Excel's data table feature can help by summarizing the

results of calculations using different inputs.

The workbook loan data tables.xlsx contains the examples in this section and can be

found at this book's website.

The data table feature is one of Excel's most under-used tools. A data table is a dynamic range that summarizes

formula cells for varying input cells. You can create a data table fairly easily, but data tables have some limita-

tions. In particular, a data table can deal with only one or two input cells at a time. This limitation becomes clear

as you view the examples.

Creating a one-way data table

A
one-way data table
shows the results of any number of calculations for different values of a single input cell.

Figure 13-4 shows the general layout for a one-way data table.