Mortgage Calculator with Taxes and Insurance in Excel (With Easy Steps)

Step 1 – Making the Outline

We need the following values for the calculator. Each value will be entered in a row.

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 also need 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

Step 2 – Calculation of the Monthly Payment

Provide the basic inputs for Mortgage Amount, Annual Interest Rate, Term Length (in Years), and the Number of Payments Per Year like the following figure.

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)

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 to convert 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 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 3 – Calculating the PITI Payment

Suppose we have a 3% of tax on the Mortgage Amount yearly.

The Yearly Taxes can be calculated by using the following formula:

=D4*(3/100)

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)

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)

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 4 – Entering Basic Inputs in the Payment Schedule

Enter the first date as the date on which you took the loan.
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.

We will get the dates for the subsequent months.

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

Link up the value of cell L5 with the Mortgage Amount in cell D4.

Enter different extra amounts you want to pay on some fixed dates like below.

Step 5 – Calculating the Payment Schedule

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)

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.

=H6-I6

For determining the current balance, use 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.

We have attained all of the values for Payment No. 1.

Use the AutoFill feature of Excel for the rest of the cells.

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

You can use the AutoFill feature to continue he calculations up to the cell where the Balance goes to zero.
The last payment date in the sample will be 7/11/2029 (we used Freeze Panes to show the top of the table).