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 expects certain values in between those two brackets. The values are known as arguments. The arguments that the PMT( ) function expects are these:
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:
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:
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:
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:
-
Click inside cell A4
-
Click inside the formula bar
-
Enter the following formula:
= 12% / 12
-
The press the return key on your keyboard
-
Excel will enter 0.01 in cell A4
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:
-
Click inside cell B4
-
Click inside the formula bar
-
Enter the following formula:
= 12 * 5
-
Press the Return key on your keyboard
-
Excel will enter 60 in cell B4
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:
-
Click inside cell C4
-
Click inside the formula bar
-
Enter this:
= B1
-
Press the return key on your keyboard
-
Excel will enter 10000 in cell C4, because this is the figure you entered into cell B1
Your spreadsheet should now look like the one below:
Time now to enter our PMT( ) function in cell D4. So, do the following:
Time now to enter our PMT( ) function in cell D4. So, do the following:
-
Click inside cell D4
-
Click inside the formula bar
-
Enter the following function:
=PMT(A4, B4, C4)
-
Press the return key on your keyboard
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:
-
Click inside cell B3
-
Click inside the formula bar
-
Type in = 12 %
-
Press the Return key on your keyboard
-
Click back inside cell B3 because we need to format the cell as a percentage
-
To format the cell as a percentage, click Format from the menu bar
-
From the drop down menu, click on Cells. The Format Cells dialogue box appears
-
Select the Number tab strip
-
Under Category, click on Percentage.
-
Click the OK button when you are done
-
Cell B3 should now read 12.00 %
= 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.
No comments:
Post a Comment