ads
ads
How to create a mortgage calculator free in Microsoft Excel
article by Chris Le Roy
One of the really cool parts aspects of Microsoft Excel functions from Microsoft that you created using. This means that rather than use a function from scratch, ready-made ones, to develop a variety of tasks such as building you do your own mortgage calculator. The Mortgage Calculator or PMT function is just one of many Financial Functions available.
Okay, so how to make a mortgage calculator …
The first thing to do is, by establishing a few basic items to . begin May start by A1 a new workbook and click in the first cell. In cell A1 of the address header – Monthly repayment of loans. Further away, in cell A2 enter the address – the amount of the loan, cell address A3 – Interest, cell address A4 – Length of the loan and then in A6 – Monthly Repayment
In the example of mortgage calculator, we will take. The loan amount, interest rate and length of the loan and calculate your monthly repayment. Okay, so in the appropriate box B1 the value of 0.000 and make sure you format the field as a currency. In cell B2, a value of 9.25% and format the field as a percentage and then finally in a value for the duration of the loan up to 25 in force. The value entered in the duration of the loan field is in years.
Now it’s time the formula for calculating your monthly repayments this will create. The function that we want to use for this calculation, the PMT function is called. The PMT function always returns a negative number, so one of the things we need to do is to convert it into a positive number, but a little later.
There are three arguments we will use for this recipe and they are – 
article by Chris Le Roy
One of the really cool parts aspects of Microsoft Excel functions from Microsoft that you created using. This means that rather than use a function from scratch, ready-made ones, to develop a variety of tasks such as building you do your own mortgage calculator. The Mortgage Calculator or PMT function is just one of many Financial Functions available.
Okay, so how to make a mortgage calculator …
The first thing to do is, by establishing a few basic items to . begin May start by A1 a new workbook and click in the first cell. In cell A1 of the address header – Monthly repayment of loans. Further away, in cell A2 enter the address – the amount of the loan, cell address A3 – Interest, cell address A4 – Length of the loan and then in A6 – Monthly Repayment
In the example of mortgage calculator, we will take. The loan amount, interest rate and length of the loan and calculate your monthly repayment. Okay, so in the appropriate box B1 the value of 0.000 and make sure you format the field as a currency. In cell B2, a value of 9.25% and format the field as a percentage and then finally in a value for the duration of the loan up to 25 in force. The value entered in the duration of the loan field is in years.
Now it’s time the formula for calculating your monthly repayments this will create. The function that we want to use for this calculation, the PMT function is called. The PMT function always returns a negative number, so one of the things we need to do is to convert it into a positive number, but a little later.
= PMT (monthly interest rate, number of payments, loan amount)
So to work the monthly interest rate for the value we just take in B3 and divide it by 12 – B3/12. The PMT function works on the basis of the number of payments you want to do, so if we go to the monthly payments on our mortgage we simply the number of years in cell B4 and multiply it to make 12 are – B4 * 12 .
This means that the monthly repayment on our mortgage we have to charge to enter the following formula – = PMT (B3/12, B4 * 12, B2)
Well, as I already said, the PMT function always gives a negative value, so with this in a positive value we simply have the PMT function with the absolute function, they encapsulate turn as shown below –
= ABS (PMT (B3/12, B4 * 12, B2))
Just type in the above formula in cell B6 and press the Enter key. You must now format the cell address B6 as a currency and you can do just by the dollar symbol on the Formatting toolbar. Once you enter the formula and press Enter, you should get a result of 12.76. If you do not answer, go back and make sure that you entered the formula correctly.
The cool part about this mortgage calculator is that you go back and change one of the values in B2, B3 and B4, the loan amount, interest rate and length of the loan to find out what are your monthly mortgage repayments. The cool part about this simple tool is that it tells you really quickly whether borrowing massive amounts from the bank worth it and whether you really afford that mortgage. Why not check what your repayments will be if your interest rate increased 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 you can see also help to have the changes in interest rates on your own budget. It is worthwhile building Budgeting Spreadsheet and the mortgage calculator to work out what you really can afford especially in these uncertain times. About the Author Chris Le Roy has either Microsoft Excel 2003 Fundamentals Cheat Sheets Microsoft Excel Spreadsheet Training and tips on Microsoft Excel on his website.
0 comments