How To Build A Mortgage Calculator For Free in Microsoft Excel!
Posted by Chris Le Roy on: 2006-06-14 18:46:41
Self SEO > Software Articles
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.
Chris Le Roy has available Microsoft Excel Shortcuts to help you with Microsoft Excel. To learn more about the mortgage calculator simply check our Chris's correspondence course where you can earn yourself Microsoft Excel Certificates issued by his company without even leaving home - Microsoft Excel Spreadsheet Training Tips on Microsoft Excel are also Available.
 Print this article  Tell a friend
 | User comments: |
 santosh005
- Posted on: 2010-12-21 05:32:48
Fixed Rate Mortgages Know Your Rate!
Nothing is ever certain in the world of finances, and theres no way of predicting how the market will change in the future. However, if you want to be able to plan your budget precisely, then a fixed rate mortgage might be the right option. The repayments will be fixed for a set period of time usually between the first one and five years of your mortgage, so you can be sure that any rises in the interest rate will not affect you. The term the rate remains fixed can be as long as ten year.
======================================
Mortgage Advice
watson012
- Posted on: 2011-08-06 10:17:23
I am very much important information in this blog and using the great services in this blog. This is wonderful info by providing the nice info in this blog<a href="http://www.webhostingsites.com/webhostingpad.html">WebHostingPad</a>
watson012
- Posted on: 2011-08-06 10:23:44
I am very much important information in this blog and using the great services in this blog. This is
wonderful info by providing the nice info in this blog WebHostingPad Review
watson012
- Posted on: 2011-08-06 10:25:07
I am very much important information in this blog and using the great services in this blog. This is
wonderful info by providing the nice info in this blog. WebHostingPad Review
watson012
- Posted on: 2011-08-06 10:46:33
Wow, this is display the nice info in this blog and the great technology is visible in this blog. I am very
much satisfied by the info in this blog.[http://www.webhostingsites.com/webhostingpad.html
]WebHostingPad Reviews[/url]
markfort11
- Posted on: 2011-08-11 13:25:48
This is really admired for this info in this blog that to utilize the nice services are visible in this blog. Thank you very much for providing the nice technology in this blog that to helpful info in this blog how to become doctor
markfort11
- Posted on: 2011-08-20 09:17:10
I am very much inspired for this technology in this blog that to using the nice info in this blog. Thanks a lot for sharing the amazing info in this website that to great technology in this blog.
project management certifications
hirmani
- Posted on: 2011-08-22 13:15:25
These articles are providing in this website and differently inspiring sand the wonderful information is visible in this blog that has the amazing staff are requiring the information in this website. I had great technology is visible in this blog and nice approach is visible in this website. Thanks you very much for providing the nice information in this blog. I had really like this information and that to using the great services in this blog <a href="http://unlimitedwebhostinginformation.com">unlimited web hosting information</a>|<a href="http://findsite5review.com/">find site5 review</a>|<a href="http://web-hosting-help.net">web-hosting-help</a>|<a href="http://topunlimitedwebhostreviews.com">top unlimited webhost reviews</a>|<a href="http://webhostingunlimitedhelp.com">webhosting unlimited help</a>|<a href="http://cheapblogwebhosting.net">cheapblogwebhosting</a>|<a href="http://findgreengeeksreview.com/">find green geeks review</a>|<a href="http://webdomainhostinginformation.com">web domain hosting information</a>|<a href="http://cheap-hosting-linux.net">cheap-hosting-linux</a>|<a href="http://websitehostinggeeks.net">web site hosting geeks</a>
stephans
- Posted on: 2011-08-22 13:39:06
This is very much happy for the great approach is visible in this blog and defiantly using this info in this blog <a href="http://smartwebhostingtips.com">smart webhosting tips</a> |<a href="http://fatcowbusinesshonesthostingreviews.com">fatcow business honest hostin greviews</a> |<a href="http://webdomainhostinfo.net">web domain host info</a> |<a href="http://affordable-hosting-packages.com">affordable-hosting-packages</a> |<a href="http://cheap-web-hosting-tips.com">cheap-web-hosting-tips</a> |<a href="http://lowcosthostreviews.com">low cost host reviews</a> |<a href="http://weblinuxhostinggeeks.com">web linux hosting geeks</a> |<a href="http://webhostnewsupdates.com">webhost news updates</a> |<a href="http://linux-web-hosting-tips.com">linux-web-hosting-tips</a> |<a href="http://webhostingdomaininfo.net">webhosting domain info</a>
stephans
- Posted on: 2011-08-22 13:40:07
This is very much happy for the great approach is visible in this blog and defiantly using this info in this blog <a href="http://smartwebhostingtips.com">smart webhosting tips</a>
WilliamErickson1
- Posted on: 2011-08-25 20:23:45
It was really love this website that to info wise is very great for this website. I had really like this info in this blog. This info is very nice info in this blog. Thanks a lot for providing the nice info.
Toronto Escorts
CharlesbmcCray
- Posted on: 2011-09-08 12:54:37
This is really very impressed for the amazing services in this blog and the really helpful info in this blog. I had really satisfied by the great technology in this blog and the nice services in this blog. For more information just visit our site Future steel buildings
torysaxton
- Posted on: 2011-10-18 09:25:19
Wow, this is really wonderful technology is visible in this blog that to using the great services in this blog. I am very much enjoyed for the great info is visible in this blog that to sharing the great technology in this website. Visit us at Vancouver Escorts
robesondel
- Posted on: 2011-11-15 04:46:13
This really is greatly satisfied through the great info is seen within this blog that to discussing the truly amazing info is seen within this blog. Thanks for supplying the truly amazing info is seen within this blog. Xray technician
robesondel
- Posted on: 2011-11-16 14:35:51
This is providing the nice technology in this website that to utilize the great services in this blog. I am big fan of this website and helpful info in this blog and the nice impression in this blog.
What is medical billing
Torontoescorts
- Posted on: 2011-11-17 12:19:10
I really admired for this info in this blog and the interesting info in this website. Those points are really great for the nice impression in this blog. Thanks a lot for sharing the info.For more details please visit us at Toronto escorts
| Post New Comment |
This site does not allow anonymous comments. Registered members can login to participate. Registration is free and takes only a few seconds
|
