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
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
- 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
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
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% to 6.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].