Mortgage Calculation Tool

Example of How to Use The Calculator  

(Section 1) Instructions

(Section 2) Loan Formation Inputs

Choose State from List

Texas; This allows the calculator to estimate cost factors for Private Mortgage Insurance, Property Taxes, and Homeowners Insurance. You have the option to enter these factor or whatever factors you have. 

Input Home Sale Price

$800,000

Input Down payment as % of Home Sale Value

15% ; Since the down payment is <20%, you will probably have to pay Private Mortgage Insurance. So you will get a message that says you should assume you will have a PMI cost (which is computed later on in the calculator)

Results from Inputs so Far

From the two inputs above, the calculator calculates

• the down payment as 15% x $800,000 = $120,000 and
• the loan amount as (1-15%)($800,000) = $680,000. 

Input the Annual Interest Rate

7% ; We will assume this is a fixed rate to be compounded monthly (typical for US mortgages). So, the applied interest each month will be 7%/12 = .5833…%

Input the Loan Duration in Years

30 years; This means , barring addition of extra payments, the loan will require 12 x 30 = 360 payments to pay off the loan. 

Input the Starting Date

24-Feb-2024; 

(Section 3) One-Time Closing Costs 

This section lists the one-time, up-front, closing costs associated with most mortgages. 

Input Origination Fee Basis Points

1; This means 1% of the loan. Origination fees are paid to the lender to cover the cost of processing, underwriting, and closing your loan. Expressed as basis points where each point is 1/100. ( 0.5 to 2 meaning .5% to 2% x loan.). The Loan Origination Fee will therefore be 1% x $680,000 = $6,800

Note: You might have to back calculate the points if you were given a cost. For example, 6800/680,000 = 1%  = 1 point

Input Discount Basis Points

1: This means 1% of the loan. Discount Points are optional upfront fees paid to the lender in exchange for a lower interest rate on your mortgage. Expressed as basis points meaning 1/100; range 0.25 to 2 meaning .25% to 2% of loan. The Discount Points Fee will therefore be 1% x $680,000 = $6,800

Note: You might have to back calculate the points if you were given a cost. For example, 6800/680,000 = 1%  = 1 point

Other Closing Costs

You can manually enter other closing costs as well:

• Input Home Inspection Fee: $0
• Input Mortgage Insurance Premium; $0
• Input Closing Costs: $0
• Input Other Fees: $1,200

Total Points and Other Closing Costs

The calculator sums up the Total Points & Other Closing Costs: $14,800

Section (4) PITI Costs: Principal + Interest + Taxes +  Insurance and Other

PITI (and other) costs are the monthly, recurring home mortgage and other home ownership  costs. 

Mortgage Principal + Interest (Monthly)

The calculator computes the monthly, fixed mortgage payments by using the Present Value Ordinary Annuity equation. The excel function that computes this is “=pmt(rate,nper,pv,[fv],[type]). Read my blogs

• Time Value of Money (TVM) Concepts, Formulas and Functions and 
• Mortgage Calculation Primer

to learn more about mortgage equations. 

The constant , recurring, monthly payment = $4,524

Yearly Property Taxes (Taxes)

Enter the yearly property taxes as a % of the home value. 

Estimating Property Taxes: I used a wallethub article titled “Effective Real Estate Tax Rates in Various States” . Source: https://wallethub.com/edu/states-with-the-highest-and-lowest-property-taxes/11585

Given the state you entered, the calculator will estimate a cost factor (as a % of the home value), but you can manually enter any number you want. I’ll use the default number of 1.63%. Notice the standalone calculator box to the right. You can use this to estimate a percentage based on a total cost you might have. 

Input % = 1.63%
The calculator computes the tax as 1.63% x $800,000 = $13,040 

Yearly Home Owners Insurance (HOI)

Enter the yearly Home Owners Insurance as a % of the home value. 

Estimating HOI: I used a bankrate.com article titled “Average cost of homeowners insurance by state for $250,000 in dwelling coverage”. Source: https://www.bankrate.com/insurance/homeowners-insurance/states/#state

Given the state you entered, the calculator will estimate a cost factor (as a % of the home value), but you can manually enter any number you want. I’ll use the default number of .85%. Notice the standalone calculator box to the right. You can use this to estimate a percentage based on a total cost you might have.  

Input % = 0.85%  
The calculator computes the cost as .85% x $800,000 = $6,800

Yearly Private Mortgage Insurance PMI

Enter PMI , Private Mortgage Insurance, as a % of the loan value. You can use the default value based on Urban Institute data (based on Loan/Value and FICO score) or use your own value. Let’s enter the default value of .23%. Notice the standalone calculator box to the right. You can use this to estimate a percentage based on a total cost you might have. 

Estimating PMI: I used Urban Institute data. https://www.urban.org/ and search for “Mortgage Insurance Data”. The report I used for 2024 was “Mortgage Insurance Data at a Glance”. 
Look at the PMI Premium Row of the tables titled “FHA Compared with PMI: 96.5% or 95% or 90% or 85% LTV Ratio”

Choose (from list) your fico score range:  720–739

% Input:  .23%

The PMI cost will therefore be, .23% x $680,000 = $1,564

Enter Other Yearly Costs

Other Yearly Costs (e.g. HOA Dues etc.): $0.0

Total Monthly PITI and other Costs

Total PITI and other costs on a monthly basis will be = $6,308

(Section 5 / Section 9) Extra Payments

You can add extra payments into any month. Go all the way down to section 9 and enter the payments in the desired months, then scroll back up. In this example we’ll add an additional $1,000 dollars a month for the first two years (24 extra $1,000 payments).  

(Section 6) 

If you see any shaded red cells in the extra payments of section 9, payments are too high. Zero those cells out and the red shade format should clear. 

(Section 7 ) Results and Error Check

If you entered extra payments in section (9) , then you will have two sets of results to compare in this section: with-extra-payment results and without-extra payment results. Also, the error check cells at the bottom of this section need to be green (all red shaded error cells need to be cleared, otherwise, don’t use the results).

For the same loan amount of  $680,000: 

Number of Payments

• with-extra-payments:       324
• without-extra-payments: 360

Loan Pay Off Date

• with-extra-payments:         1/24/2051
• without-extra-payments:   1/24/2054

(7.1) Months saved with extra payments

• 36

(7.2) Closing Costs (one time fees)

Closing costs are the same for both cases:  $14,800.00  

Monthly “Regular” Payment (using excel pmt())

The monthly mortgage payment remains fixed for both cases: $4,524.06

Monthly Tax + Home Owners Ins. + Other

The monthly tax, home owners insurance, and other costs are the same for both cases: $1,653.33 

Monthly PMI (expires sooner with extra pmts)

The monthly PMI charge (because down payment < 20% of loan) is $130.33 for both cases. PMI Payments stop when the balance< 20% of the home value. With extra payments, the PMI will stop sooner. 

(7.3) Total Starting Monthly Cost

The total starting monthly cost are the same in both cases:  $6,307.72. This is described as the “starting “monthly cost because the PMI payment will drop off once the balance is <20% of the home value.  

Total Interest Paid

• with-extra-payments:        $809,278
• without-extra-payments:  $948,661

Interest Savings

By making the extra payments the borrower saves $139,382 in interest payments. 

(7.4)Total Principal & Interest (P&I)

The P&I cost is the sum of the loan amount and the total interest paid. 

• with-extra-payments:       $1,489,278
• without-extra-payments:  $1,628,661

Total Payments Excluding Extra Payments

The sum of the constant periodic payments from the amortization table are listed below. They don’t include the extra payments. 

• from the extra payments case but extra payments excluded:  $1,465,278
• without-extra-payments:  $1,628,661

Total Extra Payments (sum of table inputs)

$24,000.0.  Adding this number to 1,465,278 gives 1,489,278. 

PMI Payments Stop Date (Bal.< 20%xHome)

PMI payments stop when the loan balance becomes less than 20% of the home value. PMI payments will end sooner when extra payments are made. 

• with-extra-payments:       3/24/2026 
• without-extra-payments: 3/24/2029

Total PMI Paid (will be less with extra pmts)

• with-extra-payments:        $3,258.33
• without-extra-payments:  $7,950.33

PMI Savings

$4,692.00

Total Tax + Home Owners Ins. + Other

The recurring monthly Total Tax + HOI + Other are multiplied by the total number of payments to give the total recurring costs over the life of the loan.

• with-extra-payments:        $535,680
• without-extra-payments:  $595,200

Tax/HOI/Other Savings

$59,520

(7.5)Total Payments Over Life of Loan

Summing up all the costs = Total Tax + HOI + Other + Total PMI + Total extra payments + Total regular payments gives:

• with-extra-payments:       $2,028,216
• without-extra-payments: $2,231,811

Total Savings

$203,594

(7.6) All Error Checks

All error check windows need to be green shaded. Don’t use the data if you cant clear any red cell windows.

(Section 8) Graphics

The Amortization chart shows the monthly costs read off the left axis. Notice that the principal portion of the monthly payment increases with time and the interest portion decreases. The white line is the remaining loan balance which goes to zero as time progresses (read off of right axis). 

The second chart shows the ending balances with or without extra payments. A summary of the cost savings are provided on the chart. 

As a reference check, you can use this excellent on-line calculator:  https://www.mortgagecalculator.org/additional-payment-calculator.

Return to Menu