Click on this link to download the Monthly Mortgage Calculator Microsoft Excel Template.
The Monthly Mortgage Payment template I created is a little different than most Mortgage calculators that you will find on the internet. Most internet Mortgage calculators will only calculate what your monthly payment will be based on the amount of the loan.
My template does this, but it also lets you plug in some other useful monthly payment information that will give you a more accurate estimate of what your monthly budget will look like.
One great feature of the template is that it is small enough and it's formatted to run on a Palm Treo 700w. It will probably run any PDA that runs Excel but the Tre0 700w is what I have tested it on. It comes in handy when you are out shopping for houses and you want to know how much your total monthly payment will be.
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.
* 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.
Subscribe to:
Posts (Atom)