Lesson 16: Single input Table

Single input Data table

Using the Single input Data Table, we may compare different values with each other, very quickly .

Below is an example of the different payments if, the amount of interest varies between 7.5 and 6%.
First, create a single input table in which you can examine the effect of different values for a variable using one or more formulas.

Select cell E3 in which the amount of your monthly mortgage payment is calculated.
Click on the “Function Wizard”
Choose the ‘PMT’ function from the “Financial” category
Click “Next”
Interest is in cell-B2/12
Click on “Tab”
The number of periods for payment is cell B3
Click on “Tab”
Current value is cell B4 (loan amount)
Click “Finish” and the amount of the monthly mortgage payment is displayed.

In a single input table, interest rate fluctuations, on which payment amount calculations are made, must be included in column D4 to D10 (these are the input values).

It is compulsory that the cell with the formula (cell E3), in the input table, is placed there:
It should be in the row above the first input value and, a cell to the right of the column with the input value.

To carry out a “what if … ” comparison, select the full range of input values and formulas (D3 to E10

Choose “Table” from the “Data” menu.
We have to give the input values in a column, so choose the “Column input cell” (“Column input cell“) box and enter the rate of interest as cell B2.

Click OK

You can now apply as many formulas as you want on the table you just created
In cell F3, you can, for instance, calculate the amount of total interest paid:

Place the cursor in cell F3
In the “Formula bar”, type the formula: “= (E3 * B3) + B4”
Press Enter
You can now calculate the total interest paid at an interest rate of 7.50% and a term of 360 months.
Select the entire range (D3 to F10)
Choose “Table” from the “Data” menu.
Choose “Column input cell” box in the worksheet and select the cell with the rate of interest (cell B2)
Click OK and the total interest paid is calculated
You can now format number using the number format options.

Double Input Data table

With the Double Input Table, for example, we can see how the amount of the monthly repayment will differ with different interest rates and different time periods.

First, we define the amount of the monthly mortgage payment as above

Select the cell A8 where the amount of your monthly mortgage payment is to be calculated.
Click on the “Function Wizard” button.
Choose the ‘PMT’ function from the “Financial” category
Click “Next”
Interest is cell-B2/12
Click on “Tab”
The number of periods is cell B3
Click on “Tab”
Current loan value is cell B4 (loan amount)
Click “Finish” and the amount of the monthly mortgage payment is displayed.

In this table or matrix, the formula in cell A8 refers to two sets of input cells:

The first series (interest rates) are under the formula cell
The second series (numnber of months) are to the right of the formula-cell

Select the entire range (A8 to E14)
Choose “Table” from the “Data” menu.
Now you must fill in both boxes:
The “Row input cell” is the time period or number of months (B3) and the “Column input cell” is the rate of Interest (B2).
Click Ok

You can change the rate of interest, time period and the loan amount to see what the corresponding payment amount.

Awesome!
You've completed Lesson 16 START NEXT LESSON

Lesson 16

Office 97 - Excel