Excel Two Variable Data Tables

Since my new job (see previous post) will require me to visit customer sites I thought that I should probably get a new car. After all, turning up in a transit van may not create a professional impression.

 

So, a new car. But, as with everything these days – could I afford it? “Of course you can”, say the dealers and the banks as they offer different finance deals.

 

With so many different interest rates its hard to choose, espcially with each one offering a different payment period.

 

If I settle on a car worth £8000, pay £1000 deposit, what are the monthly payments going to be.

 

Well, we can use the PMT function for that:

 

 

The monthly payment is given by =PMT(rate, nper, pv), where:

Rate is the interest rate – divided by 12 because we are looking at monthly payments

Nper is the number of periods over which we are paying – 48 months in this case

PV is the present value of the loan amount.

 

The result is red and negative because it is an outgoing.

 

That is great but we may want to compare several rates and several payment periods to find the best one for us. This is where a data table comes in.

 

1. Create the bare bones

 Create a table that shows the range of interest rates and range of number of repayments you may be interested in.

 

In this example we are going to make D10 equal to the result of the PMT function, so in cell D10 type =B6

 

2. Create the Data Table

Highlight the range D10 to I21.

From the Data tab click on the drop down under What if in the Data Tools group and select Data Table

 

Since the months appear in the first row of the table and are specified by cell B4 in the function the Row Input Cell is B4

Similarly the column input cell becomes B5

 

Click OK to see the repayment for each month and each interest rate.

 

 

3. Total Payable

While the monthly payment is all well and good, it is important to remember that credit costs extra, and we may want to keep an eye on how much we pay back in total. The total repayment is just the monthly repayment multiplied by the number of months:

 

 

Note that by making the formula in E25 read =E11*E$10 I can copy this across the columns and then down the rows and complete the rest of the table very quickly.

 

Conditional Formatting

If we set ourselves a grand total budge then we can apply conditional formatting to this table to see which options fit within our total budget.