Mortgage Calculator with Taxes and Insurance

Mortgage Calculator with Taxes and Insurance

This mortgage payment calculator, along with taxes and insurance, allows you to calculate the monthly installment amount that is required to be repaid on the amount that is borrowed wherein interest is paid at periodically, reducing the principal amount.

Mortgage Formula with Taxes and Insurance

[P*R*(1+R)^N]/[(1+R)^N-1]+(I/N)+(T/N)


Wherein,
  • P / pv is the loan amount
  • R / rate is the rate of interest per annum
  • N / nper is the number of period or frequency wherein loan amount is to be paid
  • PMT is the EMI amount
  • I is the Insurance Amount per annum
  • T is the taxes amount per annum
The loan Amount
$
Rate of Interest per annum
%
Number of period or frequency wherein loan amount is to be paid PMT is the EMI amount.
Insurance Amount per annum.
$
Taxes amount per annum.
$

About Mortgage Calculator with Taxes and Insurance

Mathematically it can be calculated:

[P*R*(1+R)^N]/[(1+R)^N-1]+(I/N)+(T/N)

In Excel, we can use this function:

PMT(rate, nper, pv, fv, (type)) and add up I/N+T/N

In order to use this mortgage calculator with taxes and insurance formula, 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 pointBasis PointBasis points or BPS is the smallest unit of bonds, notes and other financial instruments. BPS determines the slightest change in interest rate, to be precise. One basis point equals 1/100th part of 1%.read more, and it may get canceled when the equity amount crosses 20%.

How to Use this Calculator?

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 loans amounts to those who have a good credit score and less amount to those who have a lower credit score. First, we shall enter the principal amount:

    P

  2. Multiply the principal by the rate of interest.

    PxR

  3. Now, we need to compound the same by rate until the loan period.

    PxRx(1+R)^N

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

    N-1

  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 step 4 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.

You can download this Mortgage Calculator with Taxes and Insurance here – Mortgage Calculator with Taxes and Insurance

Mortgage Calculator with Taxes and Insurance Examples

Example #1

Christin wants to purchase a house city wherein he was working and currently staying on a rent basis. The flat that proposes to buy is costing around $139,800. He has applied for a housing loan; he was approved 75% of the property value. He already had equity of more than 25% and hence 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 he wanted installments to be paid on a monthly basis. Insurance will be charged 1% annually, and property taxes will be 3%. Based on the above information, you are required to calculate the EMI amount, which is equally monthly installments along with 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, we shall calculate the loan amount, which is 139,800 * 75%, which is 104,850.
  • The number of periods it is required to be paid in 18 years, but since here, 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 equally installments.
  • And lastly, the rate of interest 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.

[P*R*(1+R)^N]/[(1+R)^N-1]+(I/N)+(T/N)
mortgage with taxes example 1

=[104,850*0.79%*(1+0.79%)^216]/[(1+0.79%)^216–1]+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 100% for $200,000. The loan period is 30 years, and the rate of interest is 9.63% compounded annually. Further to that, 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 are required to calculate the total monthly outgo amount and the excess amount paid as interest.

Solution:

  • We need to calculate the EMI amount. For that 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 in 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 equally installments.
  • And lastly, the rate of interest 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.

[P*R*(1+R)^N]/[(1+R)^N-1]+(I/N)+(T/N)
mortgage with taxes example 2

=[200,000*0.80%*(1+0.80%)^360]/[(1+0.80%)^360–1]+(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

Conclusion

This is one step ahead of the mortgage calculator, as here we introduce property taxes and property insurance as well. These are additional payments other than mortgage installments, and hence buying your own property not only eats up interest but also taxes and insurance. As insurance of the property has almost become mandatory so as to reduce the credit exposure of banks in case the property destroys and further taxes are almost levied on all properties with hardly any exception.

Recommended Articles