Home Mortgage Calculator
Our free Home Mortgage Calculator for Excel is a powerful all-in-one worksheet that combines many of the features from our other mortgage and loan calculators. It lets you analyze a variable-rate mortgage or fixed-rate mortgage, and figure out how much you can save by making extra payments.
It helps you estimate the full mortgage payment (including insurance and interest), but if you want to estimate other monthly expenses of owning a home, you can try our Home Expense Calculator. Check out our other mortgage spreadsheets as well, and let us know if you need something that we don't have.
Home Mortgage Calculator
Description
This spreadsheet is an all-in-one home mortgage calculator. It lets you analyze a fixed or variable rate home mortgage. You can set up periodic extra payments, or add additional payments manually within the Payment Schedule. Use the spreadsheet to compare different term lengths, rates, loan amounts, and the savings from making extra payments. It also calculates the outstanding balance at the end of a specified number of years and the tax returned if the interest paid is tax deductible.
Update 12/11/2023: The updates in the new Excel version (3.1) provide a slightly improved layout and allow the worksheet to be duplicated for comparing different scenarios.
Using this Home Mortgage Calculator
The following video demonstrates a previous version (3.0), but the functionality is still the same.
Information about how to use our free home mortgage calculator and definitions of some of the terms are included as cell comments in the spreadsheet. So, if you have questions, you can hover the mouse over any cell that has a little red triangle in the corner.
This mortgage calculator can help you answer some of the following questions:
- How much can I save by making extra payments?
- How does the tax deduction from paying interest change over time?
- How much might my monthly payment change over time if I have a variable-rate mortgage?
- How soon could I pay off my home if I make extra payments?
- What will my loan balance be at the end of 3 or 5 years?
New Features of our Home Mortgage Calculator
- Estimates Property Taxes and Insurance for calculation of the PITI payment.
- Automatically calculates so-called "Accelerated Bi-Weekly" payments.
- Works for both US and Canadian mortgages (via the compounding option).
- Highly flexible extra payment options.
- Select a fixed-rate or variable rate mortage.
- Choose when to start the scheduled extra payments.
Analyzing an Existing Mortgage
There are a couple of ways to analyze your existing home mortgage.
The first is to enter the original loan amount and date and then make adjustments to the payment history within the Payment Schedule as needed. That is the simplest solution, so we've added a new feature to the Extra Payments section (at the suggestion of one of our users) that lets you specify what payment you want the extra payments to start at. So, if you've already been making payments for a couple of years, you can choose to have scheduled extra payments start on payment number 25.
The second approach is to enter the current mortgage balance and adjust the term length until the PI payment matches what you are currently paying. Remember that if paying monthly, you can enter a fraction of a year by entering a value like =10+5/12 (for 10 years and 5 months).
Note: This mortgage calculator does NOT work for so-called "simple interest mortgages" - you'll need to try our Simple Interest Mortgage Calculator instead. It also doesn't work for interest-only mortgages. For that option, check out our new Interest-Only Mortgage Calculator.
Using Goal Seek to Solve for the Loan Amount
If you want to solve for the loan amount by changing something else that is set up as a formula (such as the monthly Payment), you can use the Goal Seek feature in Excel. Watch the video below to see how.
Note: This is an older video, and although the user interface is different now, the Goal Seek feature still works the same way.
References & Resources
- Mortgage Loan at Wikipedia.org
- Home Mortgage Calculator at Bankrate.com - For an online mortgage calculator, this is a pretty good one.
- Using a Calculator to Prepay ARM at mtgprofessor.com - Explains what happens if you make extra payments with an adjustable-rate mortgage.
- Home Equity Lines of Credit (PDF) at FederalReserve.gov - an excellent read!
