Mortgage payoff calculator with extra principal payment (Free Template)

Mortgage Payment Calculator with Extra Principal Payment

“No Macro used. No installation is necessary. Just download and start using.” – Kawser Ahmed (Template Developer)

Created using Excel 2016 version

Download Template

License: Personal Use (Not for resale or distribution)

Let us know (in the comment box) your criteria or the problems that you’re facing while using this template. We shall update the template.

Using our Excel calculator, you can manage your mortgage in several ways.

Use of Our Excel Calculator

Say, you have taken a mortgage loan of amount $200,000 at an interest rate of 6%. Original loan term was 30 years. You have paid your mortgage for 5 years.

Here are some scenarios that might reflect your situations:

Scenario 1

Now you want to pay some extra every month (say it is $200) and want to prepay your mortgage. You want to know how much money and time you’re saving with your extra payments. Also, you want to check out your mortgage schedule. All you can do with our calculator (Payoff Calc. (Extra Payment)).

You see, our calculator is showing the summary of your loan. You’re saving almost $35,314 of interest, and you are paying the mortgage 6 years and 6 months earlier of the due term.

Scenario 2

You have the remaining 25 years to pay off the mortgage. But you want to pay off your mortgage within the next 10 years. You want to know how much you have to pay extra to reach this goal. Our calculator will solve this problem, too! Use our Payoff Calc. (Target) Excel worksheet.

You see the above image. Our calculator is showing: you need to pay $1083.86 extra every month to pay off your mortgage in the next 10 years. And you’re saving around $139,735 of interest.

Scenario 3

You want to pay extra both regularly and in discrete ways. I mean you plan to pay $200 extra monthly and any big amount you want to pay off when you will have the chance. All you can do with our calculator (Payoff Calc. (Extra Payment)).

In the above image, take a look at the Extra Payment (Irregular) column. You see I have added some irregular payments.

And check out the loan summary. You’re saving around $47,305 of interest, and 7 years and 10 months of time.

So, it is the most versatile loan calculator available online. Use it yourself and share it with others.

What are the other options with the calculators?

Here are some other options with our Excel calculators:

  • Interest Compounding Frequency: In some countries (for example, Canada), payment frequency and interest rate frequency are different. In Canada, though you might have to pay the PMI monthly, interest is compounded semi-annually. Our calculator gives you options to select interest compounding frequency for Weekly, Bi-weekly, Semi-monthly, Monthly, Bi-monthly, Quarterly, Semi-annually, Annually.

Be aware of one thing: if you select payment frequency monthly, your interest compounding frequency cannot be weekly or bi-weekly as they are shorter than monthly. This rule is applicable to any payment frequency and interest compounding frequency pair.

  • Payment Type: There are basically two types of payment available: End of the Period and Beginning of the Period. Normally, we use the End of the Period payment type. But our calculator can be set also to calculate with the payment type “Beginning of the Period”.

Some mortgage-related terms you should know:

  • Principal Amount: The amount you take as a loan from your lender with the mortgage of your home.
  • Regular Monthly Payment: This is the payment you make every month to your lender. This payment includes the interest of the amount for a period and part of the principal.
  • Loan Terms: For the period, you take the loan. For mortgage-related loans, it is normally 15 to 30 years.
  • Annual Interest Rate (APR): This is the annual interest rate you promise to pay for your loan. Based on your payment frequency, it is divided by the no. of periods you pay a year. If the APR is 6% and you pay monthly, your interest rate for every period will be 6%/12 = 0.5%
  • Tax Deduction: The amount you pay for paying off your mortgage is tax-deductible.

Related Excel Templates

What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , and premium Excel consultancy services for Excel and business users. Feel free to contact us with your Excel projects.
Kawser Ahmed
Kawser Ahmed

Hello! Welcome to my Excel blog! It took me some time to be a fan of Excel. But now I am a die-hard fan of MS Excel. I learn new ways of doing things with Excel and share them here. Not only a how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can check out my courses at Udemy:

  1. Reply
    Brian W Robinson Aug 1, 2018 at 2:30 AM

    What if you have escrow with your monthly payment?

    • Hi Brian,

      The article mainly focuses on the mortgage calculator between two parties. For an escrow, if there are no conditions involved, it will look almost the same.

  2. How do I use the template if it opens in View Only mode.

Leave a reply

Advanced Excel Exercises with Solutions PDF