The functions that Microsoft has created for you to use are one of the cool parts. This means that you can use pre-built ones to do a plethora of tasks like Building your own Mortgage Calculator instead of developing a function from scratch.
One of the many financial functions available is the mortgage Calculator or PMT function. Given below is a short description of how to prepare a mortgage calculator.
Setting up a few basic headings is the first
thing we have to do in preparing mortgage calculator. So let's begin by
starting a new workbook and clicking in the first cell A1.
Enter into
cell address A1 the heading - Monthly Loan Repayments. Next off, enter
into cell address A2 - Amount of Loan, cell address A3 - Interest Rate,
cell address A4 - Length of Loan and then in A6 - Monthly Repayment.
In example mortgage calculator, we will calculate your monthly repayment by taking the Loan Amount, Interest Rate, and Length of Loan. In the parallel field B1, enter the value of $200,000 and ensure you format the field as a currency.
Enter a value of 9.25% in cell B2, set up the field as a percentage, and then at last enter in a value for the Length of the Loan as 25. In the form of years, you enter the value into the length of the loan field.
For calculating the monthly repayment, now its time to create the formula. PMT functions is the name of the function that we will use for this calculation.
Since the PMT function always returns a negative numbers, converting the number into a positive one is one of the things we will require to do, but a little on that later.
For this formula, we will use the following there arguments. They are
=PMT (Monthly Interest Rate, Number of Payments, Amount Borrowed)
So to calculate the Monthly Interest Rate, we take the value in B3 simply and divide it by 12 - B3/12. The PMT function works on the basic of the number of payments you are going to make, so if we are going to make monthly payments on our mortgage, we take the number of years in cell B4 simply and multiply it by 12 - B4 *12.
This means that we require entering the following formula to calculate the Monthly Repayment for our mortgage
= PMT (B3/12, B4*12, B2)
Since, the PMT function always returns a negative value, to turn this into a positive value we simply type the PMT function with the Absolute Function encapsulating it as shown below:
= ABS (PMT (B3/12, B4*12, B2))
Press the enter key by simply typing the above formula into the cell B6. By simply pressing the Dollar symbol on the Formatting Toolbar, you must now format the cell address B6 as a currency. The moment you enter the formula and press enter, you should get a result of $1712.76.
Go back simply and ensure that you have entered the formula correctly if you don't get this answer.
The cool part about this Mortgage Calculator is that to work out what your monthly mortgage repayments will be, you can go back and change any one of the values in B2, B3, and B4, which are the Loan Amount, Interest Rate, and Length of Loan.
Knowing quickly whether borrowing massive amounts from the bank is worth it and whether you can really afford that mortgage are the coolest parts regarding this sample tool.
It can be interesting to see the impact on your budget, if your interest rate went up by 2 or 3%, why not check out what your repayments will be.
Thousands of dollars can be saved by using a simple tool like this and can also help you see what changes interest rates will have on your own budget.
It is certainly worthwhile building yourself a Budgeting Spreadsheet and the mortgage calculator to work out just what you really can afford particularly in these uncertain times.
Related Articles:
|
