How to Work out Interest Rates in Excel

 

The financial function we're going to explore will calculate the monthly payment amounts on a loan. The function we're going to use is PMT( ).

 

The PMT ( ) Function

If you want to know how much a loan will cost you, then the PMT ( ) function is ideal. How much will I have to pay back if I borrow a thousand pounds over 2 years? Over 5 years? Over 10 years? You can calculate this with the PMT ( ) function.

The PMT( ) function expects certain values in between those two brackets. The values are known as arguments. The arguments that the PMT( ) function expects are these:

PMT(rate, nper, pv, fv, type)

The last two, fv and type, are optional. If all this sounds very confusing, then don't worry: we'll clear it up with an example.

The example is this: You want to borrow ten thousand pounds from your friendly banker. You also want to pay it back over 5 years. What you need to know is - how much will this loan cost me every month?

We'll work it out with the PMT( ) function. First, create a new spreadsheet like the one below:

The figure in cell B1 is the amount we want to borrow - ten thousand pounds. Our financial function PMT( ) will go under Monthly Amount, in cell D4.

Before we can enter our function, we need to work out the Interest Rate, the Number of Payments, and the Present value. These are the arguments for our function. Let's start with the Interest Rate, the first of our arguments (rate).

The bank tells us that the interest rate is 12 percent. This is for the entire year. As we want to know how much to pay back each month, we need to divide this figure by 12 (12 months in a year; divide this into 12 percent). Of course, this is a simple division: 12 divided by 12 is 1. But we'll let Excel work it out. So do the following:

= 12% / 12

The next thing we need to work out is how many payments there are in total. We are paying back the loan every month for 5 years. As there are 12 months in a year, the formula is just 12 multiplied by 5. So do this:

= 12 * 5

OK, we have now worked out the second argument for our PMT( ) function - the nper bit. We can now move on to the pv part of the argument, or Present Value. The Present Value is sometimes known as the Principal. It is what the loan is worth now, and not say 5 years into the future. In other words, it's ten thousand pounds for us. So for the Present Value column in your spreadsheet, do this:

= B1

Your spreadsheet should now look like the one below:

Time now to enter our PMT( ) function in cell D4. So, do the following:

=PMT(A4, B4, C4)

 

Excel should now have entered the monthly payments in cell D4. It is usually in red, and with a minus sign at the start (minus because it's what you owe to the bank). The answer you should have in D4 is -£222.44.

So we have to pay back to the bank every month two hundred and twenty two pounds forty four pence.

The final column is "Total Paid Back". To work out that answer you need to multiply the monthly payments by the number of payments. Which should cause you no problems at all. When you're done, the final spreadsheet looks like this:

 

Varying the Interest Rate

We'll now change that spreadsheet slightly. What we'll do is vary the number of payments. At the moment, we're saying 12 * 5 for the number of payments. But what if we decide we want to pay it back over ten years? How much are the monthly payments then? And how much do we pay back in total?

We also want to vary that interest rate. There is plenty of competition for loans. What if we can get a better deal for our interest payments? How does it effect the monthly payments if the interest rate is 11 percent?

We can make only a few slight changes to the spreadsheet to answer these questions.

Insert two more rows into your spreadsheet, and add two labels. Your spreadsheet sheet match the one below:

To get the interest rate, we entered a formula. We entered this:

= 12 % / 12

If we put the percentage figure in a cell of its own, we could then reference that cell in our formula. We could just put this:

= B3 / 12

Then we could vary the interest rate by changing the number in cell B3. To clear any confusion, do the following:

You can now change the formula for your interest rate. So click inside the cell where you interest rate is, probably cell A6. Click inside the formula bar. Change the formula from this:

= 12 % / 12

to this:

= B3 / 12

When you press the Return key on your keyboard, all of your monthly payment terms should stay the same. The difference is that you can now alter the interest rate from cell B3. Test it out. Change the interest to 10 percent and see what happens to your Monthly Amount figure.

Exercise

The Number of Payments formula currently reads = 12 * 5. Change this formula so that number of years is coming from cell B2.

When you're finished, you final spreadsheet will look like this one:

Play about with the figures in cells B1 to B3 and watch the D6 and E6 figures change. What about a ten year loan with interest of 15 percent?

 

PMT extra

You can use the PMT( ) function to work out your savings. You might want to know how much you'll have to put away in the bank if you want to save 2 thousand pounds in one year. Remember the full PMT( ) function? It was this:

PMT(rate, nper, pv, fv, type)

To work out how much you'll have to save each month, you need that fv part. The fv stands for Future Value. You would then use the function like this (assume that the interest rate is a massive 10 percent):

= PMT(10% / 12, 12 * 1, 0, 2000)

The zero is for the pv part. The pv was Present Value. Because you haven't saved anything yet, the present value of your savings is zero. The 2000 is how much you want to save over 1 year

 

In the next part, we'll take a look at how to use IF Statements in your spreadsheets. This is known as Conditional Logic.

Move on to If Statements in Excel -->

<--Back to the Excel Contents Page