The Best Way To Build A Mortgage Calculator For Free in Microsoft Excel!

refinance mortgage calculator

One of the really cool parts sides of Microsoft Excel is the functions Microsoft has created for you to use. This suggests that instead of have to develop a function from nothing you can use pre-built ones to do a multitude of tasks like Building your own Mortgage Calculator. The Mortgage Calculator or PMT function is just one of many Financial Functions available.

Okay, so how to build a mortgage calculator

The first thing we have to do is to start by setting up a few basic headings. So lets start by starting a new workbook and clicking in the 1st cell A1. Enter into cell address A1 the heading - Monthly Loan payments. Next off, enter into cell address A2 - quantity of Loan, cell address A3 - IR, cell address A4 - Length of Loan and then in A6 - Monthly Repayment.

In example mortgage calculator, we’ll take the Loan Amount, Interest Rate and Length of Loan and work out your Monthly Repayment. OK so in the corresponding field B1 enter the value of $200,000 and ensure you format the field as a currency. In cell B2 enter a value of 9.25% and format the field as a percentage and then finally enter in a value for the Length of the Loan as 25. The value you enter into the Length of the Loan field is in years.

Now it’s the time to create the formula which will do your mortgage calculation for the Monthly Repayment. The function we shall use for this mortgage calculation is known as the PMT function. The PMT function always returns a negative number so one of the things we will need to do is to convert it into a positive number, but a little on that later.

There are three bitching we’ll use for this formula and they’re -

= PMT(Monthly Interest Rate, Number of Payments, Amount Borrowed)

So to work out the Monthly interest rate we simply take the worth in B3 and divide it by twelve - B3/12. The PMT function works on the basic of the amount of payments you’re going to make, so if we’re going to make monthly payments on our mortgage we simply take the period of years in cell B4 and multiply it by twelve - B4 *12.

This implies that to calculate the Monthly Repayment for our mortgage we want to enter the following formula -

= PMT(B3/12, B4*12, B2)

Now as I pointed out before, the PMT function always returns a negative value, so to turn this into a positive price we simply type the PMT function with the comprehensive Function encapsulating it as shown below -

= ABS(PMT(B3/12,B4*12,B2))

Simply type the formula above into the cell B6 and press the enter key. You need to now format the cell address B6 as a currency and you can do that by simply pressing the Dollar Symbol on the Formatting Toolbar. As quickly as you enter the formula and press enter you must get a consequence of $1712.76. If you don’t get this answer, simply go back and ensure that you have entered the formula in the right way.

The cool part about this Mortgage Calculator is that 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 to work out what your monthly mortgage repayments will be.

The cool part about this simple tool is that it tells you really quickly whether borrowing great amounts from the bank is worth it and whether you can actually afford that mortgage. Why not check out what your payments will be if your rate of interest went up by 2 or 3%, it can be truly engaging to see the result on your budget.

Easy tools like this can save you thousands of bucks and can also help you to see what changes IRs will have on your own budget. It is actually worthwhile building yourself a Budgeting Spreadsheet and the mortgage calculator to work out just what you actually can afford especially in these uncertain times.