Excel Mortgage Calculator

The excel mortgage calculator is composed of 3 sheets but only the “Mortgage” tab requires any input from users.

Key mandatory fields include:

• Property Purchase Date: Enter the Property’s purchase date. This date will be used as a starting point for the loan amortization schedule.
• Property Value: Indicate how much you will buy the property for
• Down Payment: Indicate the initial up-front payment for the purchase of the property. A 20% down payment is usually considered to be the norm in the U.S., although it is possible to go as low as 3.5% for certain government-backed mortgages (FHA mortgage for instance). Once entered, the spreadsheet will automatically calculate the resulting loan amount.
• Interest Rate: Enter the loan’s stated annual percentage rate (APR)
• Loan Term: Most fixed-rate mortgages in the U.S. are scheduled to amortize over 30 years. Other customary tenors include 10, 15 and 20 years. Note that our calculator has been built to work with loans up to 30 years.

Other optional fields that may apply to your situation include:

• Interest-Only Period: If your loan is an Interest-Only Loan, indicate the length (in years) of the Interest-Only Period.
• Loan Insurance – PMI: A Private Mortgage Insurance is a type of insurance conventional mortgage lenders require when home buyers put down less than 20% of the home’s purchase price. If applicable, note than the average annual PMI premium typically ranges from 0.55% to 2.25% of the original loan amount each year.
• LTV Threshold for PMI Cancellation: Most lenders require payment of PMI if your down payment is below 20% of the home’s purchase price. In most cases, PMI will continue to be paid until the Loan To Value (LTV) reaches 80%.
• Property Value Annual Growth Rate: Enter the expected annual appreciation rate of your property. This figure is only used for the “Property Value & Loan Balance” graph on the right side of the Mortgage sheet.
• Add Extra Monthly Payment: Enter your amount for recurring monthly mortgage prepayments. For custom prepayment amounts (on a month-by-month basis), please enter your figure in the “Custom” column of the “Optional Prepayment” section of the schedule. As a result, you will see that your mortgage is being amortized and repaid more quickly. The mortgage calculator will show you how much is being saved in interests and how many months or years are being saved off the original loan term.

Yes, our excel mortgage calculator is completely free to use.

Unlike other online mortgage calculators, our spreadsheet can be used offline as long as you have Microsoft Excel installed on your computer. Additionally, your work can easily be saved and continued later. On the privacy and security side, no data is being shared or tracked by external third parties: what is in your spreadsheet stays in your spreadsheet!

Finally, the spreadsheet offers additional tools and features that most online mortgage calculators don’t provide such as custom or recurring optional extra payments, interest-only loans, and payment of Private Mortgage Insurance based on a Loan To Value threshold.

Finally, the spreadsheet is easy to print and easy to share with others.

Absolutely, you can either add recurring monthly extra payments or custom payments on a month-to-month basis

Prepaying your mortgage with extra principal payments will have two key impacts on your mortgage:

• It will reduce the overall length of your mortgage (i.e. your mortgage will be repaid earlier than initially projected)
• It will reduce the total amount of interests paid over the life of the loan

Prepaying your mortgage will however not reduce your monthly payment.

Private Mortgage Insurance is usually required by conventional mortgage lenders when home buyers put down less than 20% of the home’s purchase price. Note that the average annual PMI premium typically ranges from 0.55% to 2.25% of the original loan amount each year.  In most cases, PMI will continue to be paid until the Loan To Value (LTV) reaches 80% or below (the lender is required to stop PMI payments when the LTV reaches 78%).