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:

**Mortgage Amount â†’**the amount you have taken as a loan (Input Value)

**Annual Interest Rate â†’**the yearly interest rate on your loan (Input Value)

**Term Length (in Years) â†’**the total time remains for paying off your loan (Input Value)

**Number of Payments Per Year â†’**total number of payments you will make per year (Input Value)

**Total Number of Payment â†’**the estimated total number of payments you will make in the

*term length*

**Monthly Payment â†’**the amount you will pay each month for your loan

**Total Payment â†’**the sum total of the money you will pay off

**Total Interest â†’**the difference between your taken loan and the

*total payment*you will make

**Yearly Taxes â†’**the amount of tax that will be imposed upon your loan in a year

**Yearly Insurance â†’**the amount of insurance will be imposed upon your loan in a year

**PITI Payment â†’**payment including

*Principal, Interest, Taxes,*and

*Insurance*

**Â **We will make the outline for the ** Payment Schedule **by entering the following column headers for the

**7**columns.

**Date â†’**the scheduled date for making payments

**Payment No. â†’**the serial number of the payments you are making

**Payment Amount â†’**the total amount of money you are paying

**Interest Amount â†’**the interest amount on your payment

**Principal Value â†’**the difference between the

*Payment Amount*and the

*Interest Amount*

**Extra Amount â†’**if you want to pay any extra money

**Balance â†’**the balance of your loan

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

`=D6*D7`

âž¤ For calculating the **Monthly Payment **we will use the **PMT function** here

`=-PMT(D5/D7,D8,D4,0,0)`

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*D8`

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

`=D10-D4`

__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

`=D4*(3/100)`

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

`=D4*(2/100)`

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

`=D9+(D12/12)+(D13/12)`

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

`=IF($D$9<(L5+L5*($D$5/$D$7)),$D$9,(L5+L5*($D$5/$D$7)))`

**L5+L5*($D$5/$D$7)**becomes**10000+10000*(0.05/12) â†’ 10041.67**

**IF($D$9<(L5+L5*($D$5/$D$7)),$D$9,(L5+L5*($D$5/$D$7)))**becomes**IF(106.07<10041.67),106.07,10041.67) â†’**when the monthly payment of**106.07**is lower than the loan amount of**10041.67**then**IF**will return the monthly payment value of**106.07**otherwise the amount**10041.67****Output â†’**$106.07

âž¤ Apply the following formula for the **Interest Amount**

`=L5*($D$5/$D$7)`

Here, **L5 **is the ** Balance**,

**$D$5**is the

**and**

*Annual Interest Rate***$D$7**is the

**.**

*Number of Payments Per Year*The **Principal Value **can be calculated by subtracting the ** Payment Amount **from the

**Interest Amount**.

`=H6-I6`

For measuring the current balance write the given formula

`=L5-J6-K6`

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

Can be possible client wise auto update loan amotozation table?

Also if possible interest rate change so auto update automatic in excel

Extra Payments means (Start at Payment No,Extra Payment,Payment Interval,Extra Annual Payment,Payment,Total Extra Payments) Additional Payment already showing in your video ,Variable or Fixed Rate ,Impact of interest rate HIKE on your loan EMI & repayment schedule & Impact of interest rate CUT on your loan EMI & repayment schedule ? how to create in excel & Suppose provide only interest

add also penalty

Hi BHAVNESH,

Thank you very much for reading our article. For different clients, you can create separate sheets using our Excel template. Yes, it is possible to change the interest rate. Also, that will change all the values accordingly. You can see in the below image, we changed the interest rate from

5%to6.5%. Due to auto-update, all the values changed automatically.You can also change other input values marked by

1. You can change the yearly insurance and taxes rate if changes. There is also the option to change the extra payment in Excel. You can use the IPMT function to calculate the interest part only. For penalty, please describe the situation or share your wordbook. After that, we try to solve that problem. You can mail us at[email protected].