A mortgage is a long-term loan used to acquire ownership of, or build equity in, any residential property, such as a condo, ranch, townhouse, flat, etc. As a type of secured loan, the property itself serves as collateral, which means, if you default on your mortgage payments, the lender has a legal right to repossess the house to recover any outstanding debt. Whether you are using this Mortgage Payment Calculator to compare mortgage plans or for academic purposes, simply enter the core inputs and enjoy the results. The built-in amortization math tells you how the principal and interest components of your payment shift over time. This allows you to compare 15 vs 30-year mortgage plans, test biweekly vs monthly payment frequencies, or see how a change in the annual interest rate affects your actual home price.
What is a Mortgage Plan?
A mortgage plan is a written legal contract between a lender and a private borrower. In most countries, this document is recorded in public land records to make it legally enforceable.
How Long Does a Home Mortgage Last?
Most mortgages span a fixed period of 15, 20, or 30 years. Mortgage duration has a direct impact on how much you end up paying in interest by the end of the loan term. As a rule of thumb, longer mortgage plans offer lower monthly payments, but you end up paying more in total, while shorter plans charge higher monthly payments, but you pay far less (in interest) overall.
How Often Do I Have to Pay the Mortgage?
Depending on the lender or your budget as a borrower, you may choose to pay back the loaned amount at any frequency, such as:
- Monthly (most common)
- Semi-Monthly (on the 1st and 15th of each month)
- Bi-Weekly (26 Payments per year)
- Weekly (52 Payments per year)
What Makes Up Your Mortgage Payment
If you’re buying a house for the first time, you’ll be surprised to know that each single mortgage payment is made of many parts, such as government taxes, HOA fees & home insurance.
Here’s a quick overview of each cost element:
Principal
Each payment consists of a Principal and an Interest component. Principal is simply the amount of money you initially borrowed. So, for a $100,000 loan, the principal is $100,000. With each payment, you pay a certain amount of money to lower your principal debt.
Interest
This is what lenders charge you for borrowing, typically expressed as a percentage of the principal. For example, a 6% rate on a $100,000, 30-year mortgage equals a PI payment of $599.55/month.
Note that earlier payments (say, the first few years of a mortgage plan) dedicate a greater share to pay off interest, while later payments are used to lower your principal balance.
Property tax
On top of your PI Payment (Principal-Interest), your mortgage payment also includes a property tax. Local governments charge annual property taxes with a national average rate of about 0.8 to 1.3% (in the U.S.).
That said, your tax bill may be higher or lower depending on where the property is, its assessed value, local tax rules, and any exemptions you qualify for.
Insurance
Specify your monthly home insurance cost. Two types of insurance costs may be a part of your monthly mortgage payment: regular home insurance and private mortgage insurance. Let’s explore each:
- Regular Home Insurance
Your mortgage payment may also cover the cost of insuring your house, more or less, against any damage, theft, or other accidental losses or injuries. Note that while home insurance isn’t required by law, most lenders still make it a condition of the mortgage.
Insurance costs tend to vary based on:
- Location of the property
- The current condition of the property
- How much coverage is offered by the insurance plan? Explore HO plans (HO-1 to HO-8) for standardized policy “forms” used by most insurers, but sometimes marketed under different names.
- Private Mortgage Insurance
If the down payment is under 20%, you also need to pay PMI, or Private Mortgage Insurance. But don’t worry—this can be dropped once you own 20% equity. The PMI policy is there to protect the lender if you ever default on your payments.
But it’s good news for buyers, too, as they can buy a home mortgage plan with a smaller upfront payment!
HOA fees
Next, we have the Home Owners Association Fee (HOA). HOA fees typically cover:
- Maintenance and repairs in common areas
- Landscaping and seasonal cleanups
- Amenities: pools, gyms, or clubhouses
- Community security systems or patrol
- Shared utilities like water, sewage, or lighting
- Regular trash and recycling services
Now that you have a solid handle on recurring housing costs, let’s put that knowledge to use. With our Mortgage Payment Calculator, you can calculate mortgage payments quickly with just a few simple inputs. Let us see how to use it.
How Our Template Works
Whether you are using the offline Excel version or online Google Sheets, our pre-built mortgage calculator works on the same principle. Once you manually plug in a few basic data prompts, we instantly give you a snapshot of your payments.
Add lender details
Diligently identify the person or entity lending you money; this could be a bank, credit union, mortgage company, or even a private financier.
Add their updated contact address (postal or physical) – preferably including a zip code. This ensures proper documentation, especially if you are paying off multiple mortgages. Feel free to duplicate the default worksheet to manage more than one home mortgage.
Select interest calculation method
Decide how you want to calculate your interests on each payment. There are two ways of doing this:
- Flat-rate Method: Interest rate stays fixed for the entire loan term, and is invariably charged on the original loaned amount
- Reducing Rate Method: Interest is charged on the remaining balance after each repayment, so it decreases over time
What’s Next?
In the Input table, supply details about your mortgage agreement, as well as the property you are planning to buy, how much you paid up-front, etc.
Check out the table below to see what kind of data output is automatically generated for you based on your entries:
| What You Tell Us | What We Tell You |
|---|---|
| Home Price – Market or listed price of the property in question. Also known as its appraised value. | Actual Home Price – this is how much you end up paying for the property, inclusive of interest accrued over the loan term. |
| Down payment: Define the percentage of the home price (above) you’ll pay upfront. | Loan Amount—After subtracting the down payment from the Home Price, we calculate the total amount you need to borrow, or the Principal. |
| Loan Term – This refers to the total fixed duration of the mortgage term, usually 10, 15, 20, 25, or 30 years. | No. of Payments Based on the loan term you set and the payment frequency you choose in the drop-down menu, we calculate the total number of mortgage payments you can expect to make over the course of the mortgage plan. For example:30-year loan, monthly payments → 360 payments (30 × 12)10-year loan, weekly payments → 520 payments (10 × 52) |
| Payment Frequency—or how often the payments are processed. We offer market-standard options to choose from: Weekly, Bi-Weekly, Semi-Monthly, Monthly, Quarterly, Semi-annual, and Annual. Choose the one agreed upon by you and the lender. | |
| Interest Rate (IR) – This is the annual IR offered by the lender. The rate you get depends on a range of factors, such as your credit score and history, loan-to-value ratio, loan amount, and loan term. Note: The higher the AIR, the more you will pay in interest over the life of the loan. | Based on the AIR, we automatically calculate your Interest rate per period (AIR/12)The total amount of interest you pay over the mortgage termYour total PI payment per period |
What About Other Fees?
A mortgage payment includes more than just your PI payment, which you owe to the lender directly.
Right below Input, add details of any recurring housing costs applicable to you, alongside your loan repayments.
But there’s the tricky part—most housing costs are charged to you every year. Once you know the annual cost—or what percentage of your home’s value it represents—leave the rest to us. Our built-in formulas spread those housing costs across each month for you.
Summary Table
Finally, in the summary table of this template, you will be able to see a clean picture of your financial commitment. Here, you can see the total number of payments you will make based on the data provided earlier, the date of your final mortgage installment, the interest rate per period, and the total PI payment for each month. But this calculator does not stop just here. It will also calculate for you the exact amount you will pay, which also includes the other housing costs. You don’t have to do anything, as the spreadsheet automatically calculates this based on the data you provided earlier.
This gives you a better sense of what kind of financial commitment you are walking into.
Built-In Mortgage Visualisation
This visual aid shows a breakdown of the actual home price you can expect to pay by the end of the term. With a color-coded pie chart, you can easily visualize the share or % of each component of your grant total at a glance, including:
- How much you pay upfront (Down payment)
- How much you initially borrowed (Loan)
- How much you pay in interest over the entire mortgage term
- How much you pay in recurring housing costs (tax, insurance, etc)
Looking for a different layout?
Feel free to change the style and color scheme of any graphic element on Excel to suit your individual needs & aesthetic preferences.
Target Template Users
- First-time homebuyers
- Aspiring homeowners
- Current homeowners
- Real estate investors
- Financial planners
- Personal finance enthusiasts
- Lenders
- Mortgage brokers
To Sum Up…
You now have a clear picture of how mortgage payments work and how each line item affects your budget. This Excel calculator turns those rules into numbers you can easily use to your benefit.
To get started, download our free Mortgage Payment Calculator right NOW, plug in your numbers, and run as many what-if scenarios as you need!
Technical Note
This template does not account for inflation, bank policies, or any financial, legal, or government rules that could affect your loan terms.
No macros are used. Simply download the file, enable editing, and start entering and analyzing your mortgage data. For online use, feel free to access the template via Google Sheets for cloud-based sharing, editing, and collaboration.
Spreadsheets can be fragile. Even if the file is clean when you download it, edits can introduce mistakes. Use this template only if you are comfortable with Excel and can spot and fix errors. If you need help, see Microsoft’s guide “How to avoid broken formulas in Excel.”
