How To Use Data Tables in Excel

How To Use Data Tables in Excel (Video transcript)

We have here a series of interest rate across and different payment option down. And this is the payment period. It is 60 months. How can we produce a matrix of comparison table.

Let’s first get a master formula here in B3. We will use the PMT function. So let’s put the formula here. =pmt(it requires 3 arguments, the first one is the rate and the yearly rate is 3%, let’s divide it by 12 since it is yearly payment and COMMA, the number of period, the payback period is 60 and COMMA, and finally the amount of borrowing, 2000 and select that cell)

It is a negative result. It is expect just for the sake of viewing let’s change this to positive. So this is the monthly payment amount.

Now let’s create the data table. Let’s highlight the cells in advance we want to generate the matrix including the interest rows and the columns. Let’s go to data, What if analysis and Data table.

Now the row inputs will be this row, the interest rate and the column input will be amount of many borrowed. Let’s format it a bit.

That is we have generated a matrix based on the maser formula.