How To Build A Mortgage Calculator For Free in Microsoft Excel

Home | My Account | Post Project | Browse Projects | RSS Feeds New!

How To Build A Mortgage Calculator For Free in Microsoft Excel
By: Chris Le Roy , Posted On: 2007-10-14

Home > Articles > Computers and Technology > Software

Other Recent Articles On "Software":

Most Popular Articles On "Software":

One of the really cool parts aspects of Microsoft Excel is the functions Microsoft has created for you to se. This means that rather than hve to develop a function from scrtch you can use pre-built ones to do a plthora of tasks like Building your own Mrtgage Calculator. The Mortgage Calculator or PMT fnction is just one of many Fnancial 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 b

ilding yourself a Budgeting Spreadsheet and the m

rtgage calculator to work out just wh

t you really can afford especially in th

se uncertain times.

The article How To Build A Mortgage Calculator For Free in Microsoft Excel was Submitted by Chris Le Roy through Articles.GetACoder.com network. Here's the additional information: Chris Le Roy has vailable Microsoft Excel 2003 Basic Cheat Sheet 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 - www.1-on-1.biz/Products/MSEXCEL/ "> Microsoft Excel Spreadsheet Training . www.1-on-1.biz/tipsonmicrosoftexcel/tipsonMicrosoftExcel.asp "> Tips on Microsoft Excl are also Available .

Featured Articles

1. Improving Your Business With Software Development by Anne Catherine
Having started yur own business, you would definitely wnt to maximize the returns and ncrease the overall efficiency of the bsiness and thus, maximize profits. All ths is possible with the help of Sftware Development. The use of good bsiness software can result in increasing the fficiency of your business and put it on the rght track to achieving success.

2. Heavenly Sword Review by Sayed Islam
Heavenly Sword was expected to be a lunch title when the Sony PlayStation 3 mde its debut, but that never cme to fruition. Instead, Heavenly Sword was dlayed until September 2007. The game is a Sny PlayStation 3 exclusive video game.

3. How to find the best inkjet printers and laser printers by
In the case of an nkjet printers, inspect the print that cmes out. Look at how well the clours have been reproduced, and skin tnes in particular. With a laser prnters, inspect the denseness of the blck. Is it really black or mre of a dark charcoal grey.

4. Common ways spyware can infect your PC by
Common ways in which spyware can nfect your machine is through shareware, and scurity exploits. These are both extremely cmmon, and in many cases, the vctim may not even know their cmputer is infected.

5. Great Plains Integration tools: Microsoft Dynamics GP Integration manager by
Microsoft Dynmics GP, current version is 10.0, as of Octber 2007 – is successor of frmer Great Plains Software Dynamics/eEnterprise.

6. Will Laser Printer Dominate The Market In The Future? by
The mrket trend is telling us that mre and more people are switching to lser printers for the very same rason….To Save Money In a Long Rn! If you ask anyone who wns a laser printer, they should tll you that it’s a much mre cost-effective option, both personal and bsiness wise.

7. Zamzar File Converter by Colin Richards
Zamzar - okay so it's a wird name, but it's a great srvice that will come in handy. Zmzar is a free online file cnversion site that gives you the bility to convert a file from one frmat to another without having to dwnload any software.

8. Island Inkjet - Are You Making Money On Your Empty Inkjet Cartridges? by John Synder
Instead of recycling your inkjet cartridges, another route may be to have them refilled. There are several companies out there which assist with refilling inkjet cartridges, but you have to be careful that the company knows what they're doing. Each company produces its own formula of ink which they put into their cartridges, the wrong kind of ink could create a malfunction with the printer, rendering it useful, or if the cartridge is improperly handled it could leak and damager the printer.

9. Network Time Protocol (NTP) - The Way it Works by Richard N Williams
styl='padding-left: 15px;'> Network Time Protocol (NTP) is prbably the Internet's oldest protocol. Developed at the Unversity of Delaware, it has been in use and cntinually updated for the last 25 yars. In short it is a prtocol designed to synchronize the clocks on cmputers and networks across the Internet or Lcal or Wider Area Networks (LANs/WANS).

10. Managing Your Customer Database by
Managing your cstomer relationships is vital to the sccess of your business; you neglect yur customers at your peril. With a smll customer base and a simple sles process in a relatively static mrket a business may operate an ffective Customer Relationship Management (CRM) solution bsed on index cards or a smple Microsoft Excel spreadsheet to keep clent data. In such an instance, a smll business owner may be able to kep client information such as address and tle...