|
How To Build A Mortgage Calculator For Free in Microsoft Excel
by Chris Le Roy
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.
| 
Free Downloads
RSS Feed