Financial Functions

<div dir="ltr" style="text-align: left;" trbidi="on"> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> The financial function we're going to explore is called PMT( ). You use this function when you want to calculate things like the monthly payment amounts on a loan, or how much per month a mortgage will cost you. We'll use it to work out how much per month a loan will cost us. Here's what we'll do.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> We've decided to take out a loan of ten thousand pounds from our friendly banker. We're going to be paying it back over 5 years. The question is, how much per month is this going to cost us?</div> <h3 align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> The PMT( ) Function in Excel</h3> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> The PMT( ) function expects certain values in between its two round brackets. The values that go in round brackets are known as arguments. The arguments for the PMT( ) function are these:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>PMT(</b>rate, nper, pv, fv, type<b>)</b></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Only the first three are needed, and you can miss the final two out, if you like.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> We'll work out our monthly loan costs with the help of the PMT( ) function. First, create a new spreadsheet like the one below:</div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Excel 2007 Spreadsheet" height="121" src="http://www.homeandlearn.co.uk/excel2007/images/2pmt.gif" width="631" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> If you look at cell B1 on the spreadsheet, you'll see a figure of £10, 000. This is the amount we want to borrow. The labels on Row 3 show what else we need: An interest rate, the number of payments we'll make over the 5 years, the present value of the loan, the amount we'll have to pay back each month, and the total amount paid back after 5 years. But we only need the first three for our PMT() function.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> In cell A4, we'll need an interest rate. In cell B4 we'll need the number of payments, and in cell D4 we'll need the Present Value of the loan. First is interest rate.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Imagine that the interest rate given to us by the bank is 24 percent per year. For the PMT( ) function, we need to divide this figure by 12 (the number of months in a year) So try this:</div> <ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <li>Click into cell A4 on your spreadsheet</li> <li>Enter the following formula:</li> </ul> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>= 24% / 12</b></div> <ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <li>Hit the enter key to see the answer appear, as in the image below:</li> </ul> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img border="1" height="145" src="http://www.homeandlearn.co.uk/excel2007/images/2pmt2.gif" width="456" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Now that we have an interest rate, the next thing we need for the PMT( ) function is how many payments there are in total. We have to pay something back every month for 5 years. Which is a simple formula. So,</div> <ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <li>Click into B4 on your spreadsheet and enter the following:</li> </ul> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>= 12 * 5</b></div> <ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <li>Hit the enter key to see a figure of 60 as the answer.</li> </ul> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> This figure of 60 is for the second argument of the PMT( ) function - the <b>nper</b>. This is just the number of payments.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Now that you have a figure in cell A4 (<b>rate</b>), and a figure in cell B4 (<b>nper</b>), there's only one more to go - the Present Value (<b>pv</b>).</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> The Present Value of a loan, also known as the Principal, is what the loan is worth at the present time. Since we haven't made any payments yet, this is just 10, 000 for us. </div> <ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <li>Click into cell C4 on your spreadsheet and enter the following:</li> </ul> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>= B1</b></div> <ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <li>Hit the enter key</li> <li>You'll see a figure of 10, 000 appear, and your spreadsheet should now look ours below:</li> </ul> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img border="1" height="140" src="http://www.homeandlearn.co.uk/excel2007/images/2pmt3.gif" width="568" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> OK, we now have all the parts for our PMT() function: a rate (A4), an nper (B4), and a pv (C4). Try this:</div> <ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <li>Click into cell D4 on your spreadsheet</li> <li>Enter the following function:</li> </ul> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>=PMT(A4, B4, C4)</b></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Hit the enter key on your keyboard, and you'll see the monthly amount appear. The figure you should have is -£287.68. The reason there is a minus sign before the total is because it's a debt: what you owe to the bank.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> But this is what your spreadsheet should look like:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="The montly payment amount" border="1" height="165" src="http://www.homeandlearn.co.uk/excel2007/images/2pmt4.gif" width="568" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> The only thing left to do is see how much this loan will cost us at the end of 5 years. All you need to do here is multiply the monthly amount in cell D4 by the number of payments in cell B4. Enter your formula for this in cell E4, and you spreadsheet will look like ours below:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img border="1" height="119" src="http://www.homeandlearn.co.uk/excel2007/images/2pmt5.gif" width="568" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> So a ten thousand pounds loan, at the interest rate the bank is offering, means we'll have to pay back just over 17 thousand pounds over 5 years.</div> <h3 align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Tweaking the Values</h3> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> We can change the spreadsheet slightly to give us more control. For your figure in cell B4, the number of payments, you entered 12 * 5. This is 12 months multiplied by 5 years. But what if we wanted to pay the loan back over 10 years, or 15? How much will our monthly payments then be? And will be the final cost of the loan?</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Also, the interest rate seems a bit high. What if we can get a better rate elsewhere?</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> By making a few changes to or spreadsheet, we can amend these values more easily. First we'll need two new rows.</div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <br /></div> <h3 align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Inserting New Rows in Excel</h3> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> We need to insert new rows in our spreadsheet. To insert a new row, click into cell A2. Then click on the <b>Home</b> tab at the top of Excel. Locate the <b>Cells</b> panel, and click the <b>Insert</b> item:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="The Cells panel in Excel 2007" height="85" src="http://www.homeandlearn.co.uk/excel2007/images/2InsertCellsPanel.gif" width="130" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> From the Insert menu, click on <b>Insert Sheet Rows</b>:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Insert a new row" height="162" src="http://www.homeandlearn.co.uk/excel2007/images/2InsertCellsPanel2.gif" width="168" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Excel will insert a new row for you. Do this again to get two blank rows. Add two new labels,<b>Num of Years</b> and <b>Interest</b>. Your spreadsheet sheet will then look like this:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="Two new rows have been inserted" height="160" src="http://www.homeandlearn.co.uk/excel2007/images/2pmt6.gif" width="568" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <br /></div> <h3 align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Adapting the PMT Formula</h3> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> We can adapt the formulas we've entered so far, in order to make them more usable. As an example, we'll adapt the interest rate.</div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> To get the interest rate for cell A4, we entered a formula:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>= 24% / 12</b></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Instead of having the interest rate in cell A4, however, we can place it at the top, in cell B3 on our new Row. We can then alter the interest rate by simply typing a new one in cell B3. To clear all that up, try the following:</div> <ul style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <li>Click inside cell B3, which is the Interest cell in the image above</li> <li>Click inside the formula bar</li> <li>Type in = 24%</li> <li>Cell B3 should now read 24.00% (In Excel 2010 and 2013 you may have to format the cell to a <b>Percentage</b> value from the <b>Home</b> tab then the <b>Numer</b> item.)</li> </ul> <span style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;">To change the formula for your interest rate click inside of cell A6. Change the formula from this:</span><br /> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>= 24% / 12</b></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> to this:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>= B3 / 12</b></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> Hit the enter key on your keyboard and nothing should change on your spreadsheet. But the difference is that you can enter a new interest rate in cell B3, and see how this effects the loan amounts. Try it out by typing 23% in cell B3:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img border="1" height="185" src="http://www.homeandlearn.co.uk/excel2007/images/2pmt7.gif" width="568" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> As you can see, the interest rate has changed to a rather long figure. But notice the Monthly Amount - it has gone down to £281.90. The total amount we have to pay back has changed, too. Play around with the interest rate in cell B3, just to get a feel for how it works.</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <br /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>Exercise</b></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> In cell B6 of your spreadsheet, you have the following formula:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <b>= 12 * 5</b></div> <div style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> This calculates the number of months for the loan. Change this formula so that the number of years is coming from B2. Your finished spreadsheet should look like ours below:</div> <div align="center" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <img alt="The finished Excel 2007 spreadsheet" border="1" height="185" src="http://www.homeandlearn.co.uk/excel2007/images/2pmt8.gif" width="568" /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> If you play around with the values in cells B1, B2 and B3 you should be able quickly see the new loan repayments.</div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> <br /></div> <div align="left" style="background-color: white; font-family: Arial, Verdana, Geneva, Helvetica, sans-serif;"> In the next part, you'll see what Conditonal Logic is, and how to use it in Excel. First, try this project. It's all to do with Averages, so shouldn't cause you too many problems.</div> </div>

Financial Functions

The financial function we're going to explore is called PMT( ). You use this function when you want to calculate things like the month...

Read more »