Excel 2010 Bible

Chapter 15: Creating Formulas for Financial Applications

341

3. Enter various values for the second input cell in successive rows, to the left and
   below the input values for the first input. In this example, the second input value is
   the loan amount, and the values for various loan amounts are in B11:B16.


4. Create a reference to the formula that will be calculated in the table. This reference
   goes in the upper-left corner of the data table range. In this example, cell B10 contains
   the following formula:
   =B6


5. Select the rectangular range that contains the entries from the previous steps. In this
   example, select B10:I16.


6. Choose Data ➪ Data Tools ➪ What-If Analysis ➪ Data Table. Excel displays the Data
   Table dialog box. (Refer to Figure 15.7.)


7. For the Row Input Cell field, specify the cell reference that corresponds to the first
   input cell. In this example, the Row Input cell is B2.


8. For the Column Input Cell field, specify the cell reference that corresponds to the
   second input cell. In this example, the ColumnInput cell is B1.


9. Click OK. Excel inserts an array formula that uses the TABLE function with two
   arguments.

After you create the two-way data table, you can change the calculated cell by changing the cell ref-

erence in the upper-left cell of the data table. In this example, you can change the formula in cell

B10 to =B8 so that the data table displays total interest rather than payment amounts.

Tip
If you create very large data tables, the calculation speed of your workbook may be slowed down. Excel has a
special calculation mode for calculation-intensive data tables. To change the calculation mode, choose
Formulas ➪ Calculation ➪ Calculation Options ➪ Automatic Except For Data Tables. n

Calculating a loan with irregular payments ..............................................................

So far, the loan calculation examples in this chapter have involved loans with regular periodic pay-

ments. In some cases, loan payback is irregular. For example, you may loan some money to a

friend without a formal agreement as to how he will pay the money back. You still collect interest

on the loan, so you need a way to perform the calculations based on the actual payment dates.

Figure 15.9 shows a worksheet set up to keep track of such a loan. The annual interest rate for the

loan is stored in cell B1 (named APR). The original loan amount and loan date are stored in row 5.

Formulas, beginning in row 6, track the irregular loan payments and perform calculations.