Tuesday, April 3, 2007

Mortgage Calculator Template Explanation

Here are the excel fields and their explanations.

* Loan Amount: This will be the end loan amount. After your down payment and all other negotiations are done.
* Interest Rate: This is the fixed interest rate you get from the bank for you loan. Note: The field is formatted for %. So if the field is blank, type .05 for 5.00%. If the field has 5.00% and you wish to change it, just click on the field once and type 6 and enter, it will automatically put it as 6.00%
* Number of Years: This is the total number of years that will have the loan. Usually 30 years.
* Monthly Mortgage: This field is in red meaning that there is a formula here and data will be calculated and displayed for you.
* Mntly Hazard Ins.: This is the monthly amount the bank will charge you for your Hazard insurance.
* Semi Annual Taxes: In my area, property taxes are calculated every six months in the rear.
* Monthly Taxes: This field just calculates your monthly property taxes based on what you typed in for the Semi Annual Taxes field.
* Mortgage Insurance (PMI): This is the monthly mortgage insurance amount your bank will charge you. This amount will vary based on how much you put down against the loan amount. Typically you need to put down 20% to avoid mortgage insurance. The amount of PMI is usually 1/3700th to 1/1500th of the loan amount. There are 2 ways to figure this out. 1.) Ask your loan officer. 2.) Get a "Good Faith Estimate of Closing Costs" worksheet from your loan officer. This will have the PMI amount but just for the loan amount that the worksheet is for. Simply divide the PMI amount by the Loan amount and this will give you the decimal value of 1/xxxx. Simply convert it to a fraction and modify the formula in the excel template. The excel spreadsheet has the value of 1/2034 programmed in.

That's all there is to it. The excel spreadsheet can easily be modified to add more fields just make sure you modify any formulas as needed.

2 comments:

Anonymous said...
This comment has been removed by a blog administrator.
Unknown said...

Thanks you so very much. I have seen a lot of mortgage calculator, but you have explained it in a manner which everyone will understand. Thank you.
calculette prĂȘt immobilier