Mortgage Calculator with Taxes and Insurance

Article byHarsh Katara
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

Mortgage Calculator with Taxes and Insurance

This mortgage payment calculator, along with taxes and insurance, allows you to calculate the monthly installment amount required to be repaid on the borrowed amount, wherein interest is paid 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

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 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%.

Financial Modeling & Valuation Courses Bundle (25+ Hours Video Series)

–>> If you want to learn Financial Modeling & Valuation professionally , then do check this ​Financial Modeling & Valuation Course Bundle​ (25+ hours of video tutorials with step by step McDonald’s Financial Model). Unlock the art of financial modeling and valuation with a comprehensive course covering McDonald’s forecast methodologies, advanced valuation techniques, and financial statements.

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 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:

    P

  2. Multiply the principal by the rate of interest.

    PxR

  3. 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 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.

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

[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 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.

[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 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.

Recommended Articles

This has been a guide to Mortgage Calculator with Taxes and Insurance. Here we learn how to use a mortgage calculator with taxes and insurance along with some examples. You can learn more from the following articles –