Mortgage Calculator with Taxes and Insurance

Mathematically it can be calculated:

In Excel, we can use this function:

To use this mortgage calculator with taxes and insurance formulas, one needs to know the principal amount in which the amount is borrowed, the loan period, the rate of interest, the annual tax amount, and the insurance amount. One can also include PMI if payable. PMI stands for private mortgage insurance, which is only required when the loan amount is greater than 80% and is usually provided at a fixed basis point, and it may get canceled when the equity amount crosses 20%.

One needs to follow the below steps in order to calculate the monthly installment amounts.

1. First of all, determine the loan amount required. Banks usually provide more loan amounts to those with a good credit score and fewer to those with a lower credit score. First, we shall enter the principal amount:

   

2. Multiply the principal by the rate of interest.

   

3. We need to compound the same by rate until the loan period.

   

4. We now need to discount the above result obtained in step 3 by the following:

   

5. After entering the above formula in excel, we shall obtain installments periodically.

    

6. Determine the annual taxes on the property and annual insurance of the property and divide the same by loan period by monthly, for example.

    

7. Now sum up the value arrived in steps four and step 6, which shall yield total periodical cash outflow.

    

8. One can also obtain periodical installments by entering below excel formula:

   
   PMT(rate, nper, pv, fv, (type)) and add up value arrived in step 6.

Example #1

Christin wants to purchase a house city wherein he is working and currently staying on a rent basis. The flat that he proposes to buy costs around $139,800. He has applied for a housing loan; he was approved for 75% of the property value. He already had more than 25% equity and decided to take a loan for the approved amount. The rate of interest applicable to this loan was 9.5% fixed.

He wants the loan tenure to be 18 years and installments to be paid monthly. Insurance will be charged 1% annually, and property taxes will be 3%. Based on the above information, you must calculate the EMI amount, monthly installments, and taxes and insurance.

The EMI amounts will begin at the end of the period.

Solution:

• We need to calculate the EMI amount for that first, and we shall calculate the loan amount, which is 139,800 * 75%, 104,850.
• The number of periods it is required to be paid is 18 years, but since Christin is going to pay monthly; hence the number of payments that he shall be required to be paid is 18*12, which is 216 equal installments.
• And lastly, the interest rate is 9.5% fixed, which shall be calculated monthly, which is 9.5%/12, which is 0.79%.
• Insurance of the property : 1% of 139,800 which is $1,398 annually and property taxes is 3% of 139,800 which is $4,194.

Now we shall use the below formula to calculate the Total outgo amount.

=/+1,398/12+4,194/12

=1,014.86+116.50+349.50

=1,480.86

Example #2

Mr. KRK proposes to purchase a commercial property on a monthly installment basis. The loan amount that was approved was 100% for $200,000. The loan period is 30 years, and the interest rate is 9.63% compounded annually. Furthermore, Mr. KRK is also required to pay an insurance amount of $3,800 and property taxes of $2,000. Based on the above information, you must calculate the total monthly outgo amount and the excess amount paid as interest.

Solution:

• We need to calculate the EMI amount. First, we shall calculate the loan amount, which is 200,000 * 100%, which is 200,000.
• The number of periods it is required to be paid is 30 years, but since here, Mr. KRK is going to pay monthly; hence the number of payments that he shall be required to be paid is 30*12, which is 360 equal installments.
• And lastly, the interest rate is 9.63% fixed, which shall be calculated monthly, which is 9.63%/12, which is 0.80%.
• The insurance amount is given as 3,800, and property taxes are given as 2,000.

Now we shall use the below formula to calculate the EMI amount.

=/+(3,800/12)+(2,000/12)

=1,700.71+316.67+166.67

=2,184.04

• Therefore, the total outgo amount for Mr. KRK for 30 years shall be $2,184.04
• Total interest outgo equals to ($1,700.71 * 360) – $200,000 which is $412,255.92

This is one step ahead of the mortgage calculator, as we introduce property taxes and insurance here. These are additional payments besides mortgage installments, so buying your property eats up interest, taxes, and insurance. Insurance of the property has almost become mandatory to reduce banks' credit exposure in case the property is destroyed. Further taxes are almost levied on all properties with hardly any exception.