Unless they are bankers or accountants, most of my Excel students are less than enthusiastic when it comes to learning data tables. They find it hard to relate to the complicated business problems typically used to introduce data tables in most course materials. Data tables take a lot of up-front setup work before they can be used for what-if analysis, so students are naturally reluctant to go through all that trouble if the results have no meaning for them. Here is a short lesson in one-variable data tables that may make this technique more relevant to your students.
Can I afford my dream car?
For this demo, I want to determine whether I can afford my dream car—a 1999 low-mileage, fully loaded Lexus priced at $34,000. For that, I will need to know what my monthly payment would be.
Excel’s PMT function can help me do just that. But first I need to give PMT the following data: the interest rate of the car loan, the term of the loan in months, and the amount of the loan. These are entered into the function as follows:
Searching the Internet, I learn that the current average used car loan rate was 9.88 percent for 36 months with 20 percent down.
To make this data available to the PMT function, I enter 9.88 percent for the used car rate in D3 and 36 for the term of the loan in D4. For the loan amount, I enter $27,200 in D5. This is the price of the car I am interested in minus the required 20 percent down payment of $6,800 (see 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, D3 is divided by 12. (If payments were made quarterly, D3 would be divided by 4; if semiannually, by 2; etc.)
Excel calculates $876.14 for the monthly payment, as shown in Figure C.
New or used?
Paying close to $900 a month for a used car seems a bit risky. What if I use the same down payment to take out the same loan on a new car? Would it be worth sacrificing a few luxury options to get a new car rather than an older one that was fully loaded? Before I check out a dealer, I need to see what the difference in payments would actually be. My research shows that the average rate for a 36-month loan on a new car loan varies according to the borrower’s credit rating and is even less than the used car rate if your credit is good:
- New car loan rate with good credit: 8.99 percent
- New car loan rate with fair credit: 10.99 percent
- New car loan rate with poor credit: 13.95 percent
Using the one-variable data table, I can compare the monthly payments on a new car loan for $27,500 for all three rates.
Setting up the one-variable table
To build the data table, list the rates for a new car in a column, as shown in Figure D.
Next to the New Car Rates column, add a second column labeled Monthly Payment (see F9 in Figure E). Excel will display the results for each loan rate in this column.
Next, enter =D6 in cell F10. This tells Excel to use the PMT function in D6 to calculate the new car loan payments (see Figure F).
The next step is to select the cells that make up our one-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 and the PMT function that will be used to calculate the monthly payments for each of those values. All that’s left is to identify which variable in the PMT function should be replaced with the values in the cells of the New Car Rates column. 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 Table dialog box. To tell Excel to replace the variable D3 in the PMT function with the values in the New Car Rates column, D3 is entered as the Column Input Cell in the Table dialog box (see Figure I).
After clicking OK, the results are displayed in the Monthly Payment column (see Figure J).
The data table has confirmed my original assumption: If I can sacrifice a few options, I actually pay less per month for this year’s model (provided my credit is good) than I would for the fully loaded used car. Even if my credit is fair, the difference in the monthly payments between the new and used models is only about $14.00.
One-variable vs. two-variable data tables
Now that the students know how to set up a one-variable table, they can use it to see if they can afford their own dream cars, using actual interest rates from their local banks. This example shows how helpful data tables can be when making decisions that involve only one changing factor: in this case, the interest rate. However, suppose we wanted to compare three different models, one more expensive than the other, against varying interest rates. In my next lesson, I’ll show you how two-variable data tables can be used to decide whether or not we should settle for less than our dream car.
Do you have a great Excel tip you would like to share with your fellow TechRepublic members? What do you think of Mary Ann’s real-life example? Post a comment or write to Mary Ann Richardson and share your thoughts.