| One of the really cool parts aspects of Microsoft | | | | simply take the number of years in cell B4 and multiply |
| Excel is the functions Microsoft has created for you to | | | | it by 12 - B4 *12. |
| use. This means that rather than have to develop a | | | | This means that to calculate the Monthly Repayment |
| function from scratch you can use pre-built ones to do | | | | for our mortgage we need to enter the following |
| a plethora of tasks like Building your own Mortgage | | | | formula - |
| Calculator. The Mortgage Calculator or PMT function is | | | | = PMT(B3/12, B4*12, B2) |
| just one of many Financial Functions available. | | | | Now as I said before, the PMT function always returns |
| Okay, so how to build a mortgage calculator... | | | | a negative value, so to turn this into a positive value |
| The first thing we have to do is to start by setting up a | | | | we simply type the PMT function with the Absolute |
| few basic headings. So lets begin by starting a new | | | | Function encapsulating it as shown below - |
| workbook and clicking in the first cell A1. Enter into cell | | | | = ABS(PMT(B3/12,B4*12,B2)) |
| address A1 the heading - Monthly Loan Repayments. | | | | Simply type the formula above into the cell B6 and |
| Next off, enter into cell address A2 - Amount of Loan, | | | | press the enter key. You must now format the cell |
| cell address A3 - Interest Rate, cell address A4 - | | | | address B6 as a currency and you can do that by |
| Length of Loan and then in A6 - Monthly Repayment. | | | | simply pressing the Dollar Symbol on the Formatting |
| In example mortgage calculator, we will take the Loan | | | | Toolbar. As soon as you enter the formula and press |
| Amount, Interest Rate and Length of Loan and | | | | enter you should get a result of $1712.76. If you do not |
| calculate your Monthly Repayment. Okay so in the | | | | get this answer, simply go back and make sure that |
| corresponding field B1 enter the value of $200,000 and | | | | you have entered the formula correctly. |
| make sure you format the field as a currency. In cell | | | | The cool part about this Mortgage Calculator is that |
| B2 enter a value of 9.25% and format the field as a | | | | you can go back and change any one of the values in |
| percentage and then finally enter in a value for the | | | | B2, B3 and B4 which are the Loan Amount, Interest |
| Length of the Loan as 25. The value you enter into the | | | | Rate and Length of Loan to work out what your |
| Length of the Loan field is in years. | | | | monthly mortgage repayments will be. |
| Now its time to create the formula that will do your | | | | The cool part about this simple tool is that it tells you |
| calculation for the Monthly Repayment. The function | | | | really quickly whether borrowing massive amounts |
| we will use for this calculation is called the PMT | | | | from the bank is worth it and whether you can really |
| function. The PMT function always returns a negative | | | | afford that mortgage. Why not check out what your |
| number so one of the things we will need to do is to | | | | repayments will be if your interest rate went up by 2 |
| convert it into a positive number, but a little on that later. | | | | or 3%, it can be really interesting to see the impact on |
| There are three arguments we will use for this formula | | | | your budget. |
| and they are - | | | | Simple tools like this can save you thousands of dollars |
| = PMT(Monthly Interest Rate, Number of Payments, | | | | and can also help you see what changes interest |
| Amount Borrowed) | | | | rates will have on your own budget. It is certainly |
| So to work out the Monthly Interest Rate we simply | | | | worthwhile building yourself a Budgeting Spreadsheet |
| take the value in B3 and divide it by 12 - B3/12. The | | | | and the mortgage calculator to work out just what |
| PMT function works on the basic of the number of | | | | you really can afford especially in these uncertain |
| payments you are going to make, so if we are going | | | | times. |
| to make monthly payments on our mortgage we | | | | |