Creation of a Mortgage Calculator with Taxes and Insurance in Excel

If you are looking for the procedure of creating your own mortgage calculator with taxes and insurance in Excel, then this article is useful for you.
Here, you will get the automatic calculator for calculating mortgage payments and besides this, you will know the procedure also.

Download Workbook

5 Steps to Create a Mortgage Calculator with Taxes and Insurance in Excel

Here, we will demonstrate each step of making a full calculator for calculating mortgage payments with taxes and insurance. You will get the steps containing the making of outlines, formulas to calculate the amounts throughout this article.

Step-01: Making the Outlines to Create a Mortgage Calculator with Taxes and Insurance

Firstly, we will make the basic outlines of the Mortgage Information by giving the name of the categories which we will calculate or give entry the values later.
Here, we have entered the following names:

 We will make the outline for the Payment Schedule by entering the following column headers for the 7 columns.

Read More: Mortgage Calculations with Excel Formula (5 Examples)

Step-02: Calculation of Monthly Payment

To calculate the mortgage payment firstly you need to give the basic inputs Mortgage Amount, Annual Interest Rate, Term Length (in Years), and the Number of Payments Per Year like the following figure.

➤ Now, we will calculate the Total Number of Payment by multiplying the Term Length (in Years) and the Number of Payments Per Year.

➤ For calculating the Monthly Payment we will use the PMT function here

Here, D5 is the Annual Interest Rate, D7 is the Number of Payments Per Year, D8 is the Total Number of Payment, D4 is the Mortgage Amount, the first 0 is for future value and the second 0 is for making the payment at the end of each period.
We have divided D5 by D7 for converting the annual interest rate into a monthly interest rate for calculating monthly payments and added a negative sign prior to the function because this function will return a negative value due to the payment of money.

➤ Apply the following formula for having the Total Payment

D9 is the Monthly Payment and D8 is the Total Number of Payment.

➤ For calculating the Total Interest subtract the Total Payment from the Mortgage Amount.

Step-03: Calculation of PITI Payment to Create a Mortgage Calculator with Taxes and Insurance in Excel

Suppose, we have a 3% of tax on the Mortgage Amount yearly and so the Yearly Taxes can be calculated by using the following formula

Here, D4 is the Mortgage Amount.

For a 2% Insurance rate on the Mortgage Amount yearly, use the following formula to calculate the Yearly Insurance value

Finally, for the PITI Payment which means the payment includes the main amount, interest amount, tax amount and insurance amount apply the following formula

Here, D9 is the Monthly Payment, D12 is the Yearly Taxes and D13 is the Yearly Insurance. We have divided the yearly taxes and insurance value by 12 to get the monthly values.

Step-04: Entering Basic Inputs in Payment Schedule

In this step, we will prepare our Payment Schedule.

➤ Enter the first date as the date on which you took the loan. And, the second date will be the date after one month of the previous date on which you will make your first payment.

➤ Select the two dates and drag down the Fill Handle tool.

In this way, we will get the dates for the cells up to which we will drag our Fill Handle tool.

➤ In the Payment No. column enter the serial numbers starting from zero.

➤ We will link up the value of cell L5 with the Mortgage Amount in cell D4.

You can enter different extra amounts you want to pay on some fixed dates like below.

Step-05: Calculation for Payment Schedule to Create a Mortgage Calculator with Taxes and Insurance

➤ Now, we will get the amount of payment for the first payment using the following formula

➤ Apply the following formula for the Interest Amount

Here, L5 is the Balance, $D$5 is the Annual Interest Rate and $D$7 is the Number of Payments Per Year.

The Principal Value can be calculated by subtracting the Payment Amount from the Interest Amount.

For measuring the current balance write the given formula

L5 is the Balance for the previous payment, J6 is the Principal Value and K6 is the Extra Amount.

So, we have attained all of the values for Payment No. 1.
Similarly, for the other payments use the AutoFill feature of Excel for the rest of the cells.

So, here is the outlook for our mortgage calculator where we are getting the payments up to serial number 9.

For knowing the last payment date you can use the AutoFill feature for continuing the calculations up to the cell where the Balance will be zero.
So, we can see the last payment date will be 7/11/2029 (m/dd/yyyy format, and for showing the last value here we have used the Freeze Panes option).

Conclusion

In this article, we tried to show the ways to create a mortgage calculator with taxes and insurance in Excel easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.

Further Readings

• How to Use Formula for Mortgage Principal and Interest in Excel
• Interest Only Mortgage Calculator with Excel Formula (A Detailed Analysis)
• Loan Amortization Schedule with Variable Interest Rate in Excel
• How to Use Formula for 30 Year Fixed Mortgage in Excel (3 Methods)
• Excel Interest Only Amortization Schedule with Balloon Payment Calculator
• How to Use Formula for Car Loan Amortization in Excel (with Quick Steps)
• Mortgage Repayment Calculator with Offset Account and Extra Payments in Excel