how to use an Excel Table to work out interest rates

• 
  
  Start a new spreadsheet


• 
  
  Enter the same labels as in the image below


• 
  
  Enter the same values for the Rate, Months, and Loan

So the Rate (interest rate) is 9 percent, the Months value is 60, and the Loan is 10, 000 pounds.

• 
  
  Click inside cell D2


• 
  
  Click inside the formula bar


• 
  
  Enter the following formula:

=PMT(B3 / 12, B4, -B5)

• 
  
  Press the return key on your keyboard


• 
  
  Excel should give you the answer £207.58

Just in case you're unsure about that PMT Function, here's what it's doing. The first argument for PMT is the rate, meaning the interest rate. The cell B3 is where we had our interest rate of 9 percent. We need to divide that by 12 (the number of months in a year), otherwise the bank will be charging us 9 percent a month! The second argument, where we have B4, is nper. This is just the total number of months in our loan. The third argument is how much we want to borrow. We have this amount in cell B5. It is a minus figure because it's a debt.

Now that we have a function in place, we can construct our Excel Table. First, we need to tell Excel about those other interest rates. It will use these to work out the new monthly payments. Remember, Excel is recalculating the PMT function. So it needs some new values to calculate with.

• 
  
  So in cells C3, C4, and C5 enter 8%, 7%, 6%


• 
  
  Your spreadsheet should look like the one below

We have deliberately put the PMT function in cell D2. This is one Row up, and one Column to the right of our first new interest rate of 8%. The new monthly payments are going to go in cells D3 to D5. Excel needs you to set the table out this way.

So that Excel can work out the new totals, you have to highlight both the new values and the Function.

• 
  
  So click inside cell C2


• 
  
  Highlight the cells down to D5


• 
  
  Your spreadsheet should look like this one:

As you can see, the cells C2 to D5 are highlighted. This includes our new interest rate values, and our function in cell D2. We can now create a Table. So do this:

• 
  
  From the Excel menu bar, click on Data


• 
  
  From the drop down menu, click on Table


• 
  
  A small dialogue box appears like the one below:

There's not much to fill in on that dialogue box. But the term "Input Cell" does need explaining.

The Input Cell is the cell that you want Excel to substitute. The thing we want Excel to substitute is the interest rate. We had our interest rate in cell B3. So we use this as the Input Cell.

We wanted Excel to fill downwards, down a column. So we need the second text box on the dialogue box "Column input cell". If we were filling across in rows, we would use the "Row input cell" text box.

• 
  
  So click in side the "Column input cell" text box


• 
  
  Enter B3 as the Input Cell


• 
  
  Click the OK button


• 
  
  Excel will work out the new monthly totals for you


• 
  
  You should have the same values as in the image below:

So at an interest rate of 9 percent, we would be paying back just over two hundred and seven pounds. Excel has worked out that an interest rate of 8 percent will lower the monthly payments to just over two hundred and two pounds. At a 6 percent interest rate, the payments will be just over one hundred and ninety three pounds.

If you click inside cells D3, D4 and D5, then look at the formula bar, you will see this:

{=TABLE(,B3)}

That's Excel's way of telling you that a Table has been created.