How to build a Free Mortgage calculator in Microsoft Excel
One of the really cool parts aspects of Microsoft Excel functions are Microsoft created for you to use. This means that rather than having to develop a function from scratch, you can use pre-built to a multitude of tasks like building your own Mortgage Calculator. The Mortgage Calculator or PMT function is a financial functions available are numerous.
Well, how to build A Mortgage calculator …
The first thing we have to do is start by establishing a few basic positions. So lets begin by starting a new workbook and clicking in the first cell A1. Enter the cell address A1 the heading – in monthly installments of the loan. Next off, enter into cell address A2 – Amount of the loan, cell address A3 – Interest Rate, e cell A4 – duration of the loan, then in A6 – Monthly Repayment.
In the example of mortgage calculator, we will take the loan amount, interest rate and loan term and calculate your monthly payment. Well then in the corresponding field B1 enter the value of $ 200,000 and make you fit the field as a currency. In cell B2 enter a value of 9.25% and the percentage size of the field and then enter a value for the duration of the loan 25. The value you enter the length of the field is ready for years.
Now its time to create the formula that will make your monthly payment calculation. The function we use for this calculation is called the PMT function. The PMT function always returns a negative number if one of the things we need to do is convert it into a positive number, but a little later.
There are three arguments we will use this formula and they are –
= PMT (rate Monthly interest, number of payments, amount borrowed)
So, working on the monthly interest rate we simply take the value of B3 and divide by 12 – B3/12. The PMT function works on the basis of the number of payments you will make, so if we will make monthly payments on our mortgage we simply take the number of years in cell B4 and multiply it by 12 – B4 * 12.
This means that in calculating the monthly payment for our Mortgages, we must enter the following formula –
= PMT (B3/12, B4 * 12, B2)
Now, as I have already words, the PMT function always returns a negative value, so as to convert it into a positive value we simply type the PMT function with the function it embodies for Absolute below –
= ABS (PMT (B3/12, B4 * 12, B2))
Just type in the above formula in cell B6 and press Enter. You must now format the cell address B6 as a currency and you can do this by simply pressing the dollar sign on the toolbar Formatting. Once you enter the formula and press Enter, you should get a result of $ 1,712.76. If you do not receive this response, simply return back and make sure you entered the formula correctly.
The cool part about this mortgage calculator is that you can go back and modify the values of B2, B3 and B4 are the loan amount, interest rate and loan term to work to ensure that your monthly repayment of mortgages will be.
The cool part about this tool is simple: it tells you very quickly if massive amounts of bank loans worthwhile and if you can really afford the mortgage. Why not check what your repayments will be if your interest rate increased 2 or 3%, it can be really interesting to see the impact on your budget.
Simple tools like this can save you thousands of dollars and can also help you see what changes in interest rates will have on your own budget. It certainly helps you build a budget Spreadsheet and the mortgage calculator to work on what you really can afford especially in these uncertain times.
About the Author
Find tips about medusa jellyfish and poisonous jellyfish at the Types Of Jellyfish website.
Wealth Masters International presents (The Ultimate Wealth Formula)
