Mortgage Payoff Calculator with Extra Payment (Free Excel Template)

Last updated on July 28th, 2018

mortgage payoff calculator with extra payment

Mortgage Payment Calculator with Extra 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.

Living a debt-free life is a thing that everyone wants.

At least, I would not like to see my debt is eating up my hard-earned monthly income.

To lead a debt-free life, you need to have a solid plan. How would be your solid plan?

  • The solid plan will track your every dollar
  • The plan will check out where you can cut expenditure and save the dollar
  • Force yourself to cut some manageable big expenditures
  • With the saved dollars, increase the monthly payment of your home mortgage loan

Using my Mortgage Payoff Calculator Extra Payment, you can easily find out how much extra payment you have to pay every month (or at any interval) with your regular payment to pay off the loan early.

If you have already downloaded my Excel calculator, you will find basically two calculators:

  • Payoff Calc. (Target)
  • Payoff Calc. (Extra Payment)

Let me show you how to use both the calculators with a number of examples.

How Much Extra Should You Pay to Pay off your Mortgage Early?

Example 1

Blake had taken a home loan of amount 250,000 on Jan 10, 2018. He has made already 5 payments. His original loan term was 20 years. Annual Percentage Rate is 6%.

For the last 6 months, he has tracked down all his expenditures and found a way to extra pay $2000 a month with the regular payment of his mortgage loan.

Now he is planning to see how much he has to pay extra if he wants to pay off his loan in the next 10 years (rather than 20 years).

In this case, use my Payoff Calc. (Target) worksheet in the following way:

So, the result is: Blake has to pay $954.10 extra every month if Blake wants to pay the loan in the next 10 years rather than the 20 years (his original loan terms).

On the right side of the worksheet, you will find the summary of the loan like Total Amount to be Paid, Total Interest to be Paid, Est. Interest Savings, Total Time, etc.

Example 2

What if Blake wants to pay the extra payment quarterly, not monthly?

Simple. Just change the Extra Payment Frequency from Monthly to Quarterly.

Blake finds that after every 3 months, he has to pay 2892.20 extra to pay off the loan in the next 10 years.

Mortgage Payoff Calculator with Extra Payment (Recurring / Irregular / Both)

Example 3

Fallon has taken a mortgage loan of an amount for her newly bought home.

Here are her loan details:

Original Loan Terms (Years): 20 years.

Loan Amount: 200,000$

APR (Annual Percentage Rate): 4.50%

Loan Date: March 10, 2018.

With her regular loan payments, she wants to extra pay her loan in two ways:

  • A recurring extra payment: She plans to pay 500$ extra after every month. But it can be also bi-monthly, quarterly, and yearly.
  • And an irregular extra payment: When she would have some extra amount to pay, she wants to pay to her lenders.

So, here are some more details of her present decisions:

Extra Amount You Plan to Add: $500

Extra Payment Frequency: Monthly

Extra Payment Starts from Payment No.: 10

Extra Irregular Payment: Don’t know the date but she can add it in any loan period.

This is her loan summary now. In the above image, you see that she can add any amount of extra payment to her regular monthly and regular extra (recurring one) payment.

And she will be able to repay her loan completely in 11 years, 4 months, and 0 days.

More Calculator Options to Make Your Life Easier

My Excel calculators have some more options that will make your life easier. These options make these calculators more versatile when compared with the other calculators available online.

Here are those options:

  • Interest Compounding Frequency: In some countries, payments are due monthly but interest might compound in other periods. For example, in Canada, mortgage loan payment is monthly, but the interest rate is compounded semi-annually. Interest Compounding Frequency has options like weekly, bi-weekly, semi-monthly, monthly, bi-monthly, quarterly, semi-annually, and yearly.
But the caution is: if your payment is monthly, interest compounding frequency cannot be less than monthly, I mean if the Interest Compounding Frequency is weekly or bi-weekly but regular payment is monthly, on the right side you will see a warning.
  • Payment Type: You can also use a different payment type for your calculations. For some loans, Payment Type can be at the beginning of the period. In most cases, payment is done at the end of the period. Whatever it is: there are two values for your use.
  • Total Time: In the loan summary part, you will be able to find the real Years, Months and Days to pay off all the loan with your activities.
  • Interest Savings: Est. Interest Savings is a good measurement of how much you are saving with your prepayments.

Factors to Consider Before Paying Off Your Mortgage Early

There are some factors you should consider before you are going to prepay your mortgage.

1) Is there any prepayment penalty practiced by your lenders?

Some lenders might have some penalties for prepayments. Consult with the lenders or check out the terms and conditions that you had accepted when you took the loans. If there is any penalty, consult with your lenders to find a solution to this situation.

2) Any high paying DEBIT Card or Any Loan You’re Paying?

If you have any high paying DEBIT card or Car Loan you’re continuing with, it is better to pay them at first.

Say, you’re are paying a 12% (APR) DEBIT card loan for an amount of $10,000. Your monthly interest charge will be $100. In your sense, it is not a big amount to bother. But in reality, if it would be your Mortgage Loan, you would have to pay only $50 (only interest). So, if you pay your DEBIT card loan at first, you’re actually saving $50/month, that is actually 600$/year.

3) Have you saved enough in your emergency fund?

You know emergency happens. Save enough amount at your emergency fund. Then plan to prepay your mortgage loan.

4) Is your mortgage loan ruling your life?

For someone, bearing a loan for year after year is really bothering. Sometimes, the person might find himself in a place where he might feel that the loan is actually controlling himself. In this situation, try to vanish the loan from your life. Carrying a loan for 20-30 years, it is about one-fourth or one-third of your total lifespan. So, when you are in a situation to prepay your loan, get rid of the loan in the shortest possible time.

In my workbook, you will find a worksheet (named Prepayment Checklist) where you can check out the factors. If all the factors are green, you can attempt to pre-pay your loan.

Benefits You will Enjoy if You Pay Off Your Mortgage Early

Being a debt-free will open many doors in front of you. Here are some:

1) Saving Money

You will save a lot of money as interest savings if you can prepay your home loan. This will make your life more flexible and enjoying.

2) Though Interest Spent is tax deductible, you’re losing money at the end of the day

Some people might come with a logic that interest spend is tax deductible. But my question is how much?

For example, you pay $1000 interest on the home loan. So, you’re saving 250$ (assuming tax rate is 25%) every month. But the rest $750 is going to the lender and it is a spent.

So, if you pay early, you can save that $750 every month. And there are other schemes where you can save money and that money is tax deductible.

3) Save for retirements or to establish businesses

Your saved money will able you to save money for your retirement or you can set up your own business. Owning a business can give you more financial freedom if you can become successful in business.

Pitfalls You might Face for Paying Off Your Mortgage Early

Though the pitfalls are not many prepayment decision comes with some.

My most important reason is…

At one point of life, you might need a good amount of cash to start a business or for some major emergencies. Money in the checking account is easily accessible than to get money by refinancing your home. Consider this before start paying off your mortgage loan early.

Final Thoughts

From the above discussion, I think it is clear to us that paying off your loan early is a major decision to take. Think about all the factors that we talked about.

Mortgage-Related Terms

  • Principal Amount: The original amount you took from a lender as the loan.
  • Regular Monthly Payment: This is the amount you will pay every month. This includes the interest amount of the loan for a period (normally month) and a portion of your principal amount.
  • Loan Terms: This is the total number of years you and lender have agreed upon to pay off all the interest and loan. For mortgage loan, it is normally 15-30 years.
  • Annual Interest Rate (APR): Annual Interest Rate you will pay for your loan. Say, your home loan APR is 6%, then interest rate for a month will be 6%/12 = 0.5%.
  • Extra Payment: Extra payment you want to pay every month. After paying your monthly amount, whatever you pay is considered as extra payment. There are two types of Extra Payments: Regular Extra Payment and Irregular Extra Payment. It totally depends on your lenders how you can pay your extra amount.
  • Interest Savings: If you make extra payments with your regular payments, you will save some interest. This is referred to as Interest Savings.
  • Tax Deduction: Mortgage interest is tax deductible.

Related Excel Templates


Kawser on EmailKawser on FacebookKawser on LinkedinKawser on TwitterKawser on Youtube
Hello!

Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply.

Keep in mind this African proverb:

"If you want to go fast, go alone,
If you want to go far, go together."

Let's together explore Excel deeply!

We will be happy to hear your thoughts

      Leave a reply