You may be aware that Excel can calculate loan repayments. But you may not know that it can also work out lots of different loan combinations based on two variables.

In the image below we have worked out the monthly loan repayment based on three inputs (yellow cells).

The formula in cell B6 is.

=PMT(B3/12,B4*12,B2)

The formula in cell B7 is.

=(B6*B4*12)+B2

You may need to work out what the repayments are based on different terms and different interest rates.

The structure shown below will allow Excel to model these combinations.

Data Tables

Data tables allow you to calculate different results based on different inputs.

In our case we want to work out the monthly repayment based on 4 different terms and 4 different interest rates.

Note the entries in the data table structure need to be data entered, and not formulas linked to the input cells.

To calculate the various combinations, follow these steps.

1. In cell B9 enter the following formula. This is a link to the calculation we want to model.

=B6
2. Select the range B6:F13. This includes the calculation cell in the top left corner of the range and include the rows and columns with the entries we want to model.

3. Click the Data ribbon tab. Then click the What-If icon (far right). Then choose Data Table.

4. In the dialog that opens click in the Row input cell box and use the mouse to select cell B4. Click in the Column input cell box and use the mouse to select  cell B3. Click OK.

The table will be populated as shown below.

Note cell E11 matches the current combination (cell B6) based on inputs in cells B3 and B4.

If you change cell B9 and link it to cell B7 you will update the report and see the different total interest amounts for the combinations.

Data tables are a powerful feature. They allow you to display many different results based on different combinations.

Warning:

Using lots of data tables in a large model can impact performance and slow down the file.

You can download the example file at the button below.