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