When teaching Excel classes, I’ve found that it pays to use real-life examples to keep users interested. In my recent article “Use real-life examples to teach Excel data tables,” I showed how to use Excel’s one-variable data table to compare the payments on a 36-month, used-car loan with those on the same loan for a new car. We found that, because rates are generally lower for new cars, when we applied the same loan amount toward a new car instead of a used one, we would actually save a few dollars a month.
The trade-off was that we would have to settle for a low-end model, minus some luxury options that would have come with the older model, which was a 1999 fully loaded, low-mileage Lexus. Or would we? Unlike used-car loans, which are limited to a 36-month term, new-car loans can be extended over 48 months. For this data table lesson, we will use the two-variable data table to see if, by extending the payments, we can afford a more luxurious model.
Setting up the PMT function
To see how extending the term can help us, let’s first use the PMT function to determine what the monthly payment would be if we took the same $34,000 we were going to spend on the fully loaded used car and applied it to a new-car loan. Our research indicates that the current average rate on a 48-month, new-car loan is 9.10 percent. If we apply the same $6,800 down payment to the new-car loan, the loan amount would again be $27,200. To find out the monthly payment, we first enter the average interest rate, the term of the loan, and the amount of the loan in an Excel worksheet, as shown in Figure A.
The next step is to enter the PMT function that references these cells, as shown in Figure B. Because the payments are made monthly, E2 is divided by 12. (If payments were made quarterly, E2 would be divided by 4; if semi-annually, by 2; etc.)
Excel calculates the monthly payment to be $678.17, which is considerably less than $876.14, the monthly payment we had previously calculated for the used-car loan (see Figure C).
Let’s go shopping!
Now that we’ve found that we can substantially reduce our payments by extending the term of the loan to 48 months, we are now ready to go shopping for that new car. What we find is that the starting price of a 2001 model is $30,500. If we add some options to it, we can get a pretty nice car for $38,555. Nevertheless, the model comparable to our used car, in terms of options, costs $54,005. Can we afford it? Checking out our local banks, we find a range of interest rates available for 48-month, new-car loans. We decide to limit our comparison to the three lowest interest rates, which are as follows:
We now have all the information we need to set up the two-variable data table.
Setting up the two-variable data table
The first step in setting up the two-variable data table is to enter the values for the first variable, in this case, the 48-month interest rates of our local banks. These are entered into the first column of the data table, as shown in Figure D.
Next, we enter the values for the second variable, which are the loan amounts for the three cars under evaluation. When we apply the $6,800 down payment to the low-end model, which we’ll call Car A, the loan amount is $23,700 ($30,500 minus $6,800). Applying the same down payment to the midrange model, or Car B, the loan amount is $31,755 ($38,555 minus $6,800). Likewise, for the high-end model, Car C, the loan amount is $47,205 ($54,005 minus $6,800). These values are entered into the first row of the data table, as shown in Figure E.
Next, enter =E5 in cell D9. This tells Excel to use the PMT function in E5 to calculate the new-car loan payments (see Figure F).
The next step is to select the cells that make up the two-variable data table, as shown in Figure G.
Selecting these cells provides Excel with most of the information it needs to complete the table—namely, the three values for the interest-rate variable, the three values for the loan-amount variable, and the PMT function that will be used to calculate the monthly payments for each of those values. All that’s left to do is to identify which variable in the PMT function should be replaced with the values in the cells of the New Car Rates column and which variable should be replaced with the values in the cells representing the loan amounts for each car. To do that, click Data in the Menu Bar and then click Table in the drop-down list, as shown in Figure H.
This will bring up the Data Table dialog box. To tell Excel to replace the variable E4 in the PMT function with the values in the first row of the data table, E4 is entered as the Row input cell in the Data Table dialog box. Likewise, to tell Excel to replace the variable E2 in the PMT function with the values in the New Car Rates column, E2 is entered as the Column input cell in the Data Table dialog box (see Figure I).
After clicking the OK button, the monthly payments for each car for each interest rate are displayed (see Figure J).
Using the two-variable data table
The results for Car B confirm that by applying the same down payment and extending the term of the loan, we can lower the monthly payments by as much as $100 a month over that of a used car, without having to sacrifice too many luxury options. But what if we extended the term to 60 months? Or 72 months? Now that we have set up the data table, we can answer both these and similar what-if questions, simply by plugging in the appropriate data.
We want to know if you have a great method for teaching end users Excel. Let us know what works and what doesn’t for your classes. Post a comment or write to Mary Ann Richardson.