WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All In One Bundle
  • Login
Home » Financial Modeling Tutorials » Financial Calculators » Mortgage Calculator with Taxes and Insurance

Mortgage Calculator with Taxes and Insurance

By Harsh KataraHarsh Katara | Reviewed By Dheeraj VaidyaDheeraj 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 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 point, 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.

Step #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

Step #2 – Multiply the principal by the rate of interest.

PxR

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

Popular Course in this category
Sale
Financial Modeling Course (with 15+ Projects)
4.9 (927 ratings)
16 Courses | 15+ Projects | 90+ Hours | Full Lifetime Access | Certificate of Completion
View Course

PxRx(1+R)^N

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

N-1

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

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

Step #7 – Now sum up the value arrived in step 4 and step 6, which shall yield total periodical cash outflow

Step #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

  • Additional Payment Calculator
  • Using CD Interest Calculator
  • Calculating Mortgage in Excel
  • Mortgage APR vs Interest Rate
  • Financial Modeling Books
9 Shares
Share
Tweet
Share
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Download Coursera IPO Financial Model

By continuing above step, you agree to our Terms of Use and Privacy Policy.
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

* Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Login

Forgot Password?

WallStreetMojo

Download Mortgage Calculator with Taxes and Insurance

Coursera IPO Financial Model & Valuation Free Download