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:

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

Excel mortgage calculator with 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

outline

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.

Excel mortgage calculator with taxes and insurance

➤ 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

monthly payment

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

monthly payment

➤ Apply the following formula for having the Total Payment

=D9*D8

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

monthly payment

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

=D10-D4

Excel mortgage calculator with taxes and insurance


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.

Excel mortgage calculator with taxes and insurance

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

=D4*(2/100)

PITI payment

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.

Excel mortgage calculator with taxes and insurance


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.

Excel mortgage calculator with taxes and insurance

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

basic input for payment schedule

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

basic input for payment schedule

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

basic input for payment schedule

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

basic input for payment schedule

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

Excel mortgage calculator with taxes and insurance


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

Excel mortgage calculator with taxes and insurance

➤ Apply the following formula for the Interest Amount

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

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

calculation for payment schedule

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

=H6-I6

calculation for payment schedule

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.

calculation for payment schedule

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.

calculation for payment schedule

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

calculation for payment schedule

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

Excel mortgage calculator with taxes and insurance


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

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo