How To Build A Mortgage Calculator For Free In Microsoft Excel

How to​ Build a​ Mortgage Calculator For Free in​ Microsoft Excel
One of​ the​ really cool parts aspects of​ Microsoft Excel is​ the​ functions Microsoft has created for you​ to​ use .​
This means that rather than have to​ develop a​ function from scratch you​ can use pre-built ones to​ do a​ plethora 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 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 take the​ Loan Amount,​ Interest Rate and Length of​ Loan and calculate your Monthly Repayment .​
Okay so in​ the​ corresponding field B1 enter the​ value of​ $200,​000 and make sure 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 its time to​ create the​ formula that will do your calculation for the​ Monthly Repayment .​
The function we will use for this calculation is​ called 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 arguments we will use for this formula and they are -
= PMT(Monthly Interest Rate,​ Number of​ Payments,​ Amount Borrowed)
So to​ work out the​ Monthly Interest Rate we simply take the​ value in​ B3 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 simply take the​ number of​ years in​ cell B4 and multiply it​ by 12 - B4 *12.
This means that to​ calculate the​ Monthly Repayment for our mortgage we need to​ enter the​ following formula -
= PMT(B3/12,​ B4*12,​ B2)
Now as​ I​ said before,​ the​ PMT function always returns a​ negative value,​ so 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))
Simply type the​ formula above into the​ cell B6 and press the​ enter key .​
You must 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​ soon as​ you​ enter the​ formula and press enter you​ should get a​ result of​ $1712.76 .​
If you​ do not get this answer,​ simply go back and make sure that you​ have entered the​ formula correctly.
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 massive amounts from the​ bank is​ worth it​ and whether you​ can really afford that mortgage .​
Why not check out what your repayments will be if​ your interest rate went up by 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 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 especially in​ these uncertain times.

You Might Also Like:

Powered by Blogger.