Early Mortgage Payoff Calculator in Excel (3 Practical Examples)

Get FREE Advanced Excel Exercises with Solutions!

Living a debt-free life is a thing that everyone wants.
At least, I would not like to see my debt eating up my hard-earned monthly income.
To lead a debt-free life, you need to have a solid plan. An early mortgage payoff calculator in Excel is the plan.

  • It will track your every dollar
  • It will check out where you can cut expenditures 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 Early Mortgage Payoff Calculator in Excel, 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)

In this article, I will show you how to use an early mortgage payoff calculator in Excel with examples.


Introduction to Mortgage

Let’s first look at some critical definitions regarding Mortgage calculation.

  • 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 a month) and a portion of your principal amount.
  • Loan Terms: This is the total number of years you and the lender have agreed upon to pay off all the interest and loan. For a mortgage loan, it is normally 15-30
  • Annual Interest Rate (APR): Annual Interest Rate you will pay for your loan. Say, your home loan APR is 6%, then the interest rate for a month will be 6%/12 = 5%.
  • Extra Payment: Extra payment you want to pay every month. After paying your monthly amount, whatever you pay is considered an 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.

Early Mortgage Payoff Calculator in Excel (3 Practical Examples)

In this section, we will demonstrate 3 different examples of using an early mortgage payoff calculator. Let’s start then!


Example 1: Use of Monthly Extra Payment Frequency

Blake had taken a home loan of amount $250,000 on Jan 10, 2018. He has already made 5 payments. His original loan term was 20 years. The 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 to put in the loan details.

Loan Details early mortgage payoff calculator excel

  • You will get the following result.

  • 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, Interest Savings, Total Time, etc.

Mortgage Summary early mortgage payoff calculator excel


Example 2: Use of Quarterly Extra Payment Frequency

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.

Quarterly Extra Payment early mortgage payoff calculator excel


Example 3: Application of Recurring Extra Payment

Now I will show another example. This time I will use the Mortgage Payoff Calculator for Extra Payment (Recurring / Irregular / Both).
Suppose, 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 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 to 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) payments.
And she will be able to repay her loan completely in 11 years, 4 months, and 0 days.

Quarterly Extra Payment early mortgage payoff calculator excel


Early Loan Payoff Calculator in Excel

Now, let’s learn something about loan payoff and the NPER function. The NPER function will calculate how many months it will take to repay a loan of a certain amount and the interest rate.

Consider this dataset for this case.

To calculate the number of months, we will follow the steps.

Steps:

  • Go to C7 and write down the following formula
=NPER(C5/12,-C6,C4)

NPER to calculate loan payoff

  • Then, press ENTER. Excel will calculate the number of months.

It will take approximately 66 months to repay the loan.

Note:

  • The annual rate is divided by 12 because we are calculating the number of months.
  • The negative sign for monthly payment is that you are paying this amount.

Advantages & Pitfalls of Early Mortgage Payoff

Being 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 enjoyable.

2) Though Interest Spent is Tax Deductible, You’re Losing Money at the End of the Day

Some people might come up with the logic that interest spending 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 the tax rate is 25%) every month. But the rest of $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 Retirement or 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 a successful business.

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


Things to Remember

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 penalties for prepayments. Consult with the lenders or check out the terms and conditions you 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 CREDIT Card or Any Loan You’re Paying?

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

Say, you are paying a 12% (APR) CREDIT 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 CREDIT card loan at first, you’re actually saving $50/month, which is actually 600$/year.

3) Have You Saved Enough in Your Emergency Fund?

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

4) Is Your Mortgage Loan Ruling Your Life?

For someone, bearing a loan 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 him. In this situation, try to vanish the loan from your life. Carrying a loan for 20-30 years 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.

Checklist for early mortgage payoff calculator excel


Download Practice Workbook

Download the free template and use it.


Conclusion

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.


Related Excel Templates


<< Go Back to Mortgage CalculatorFinance TemplateExcel Templates

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
Kawser Ahmed

Kawser Ahmed is a Microsoft Excel Expert, Udemy Course Instructor, Data Analyst, Finance professional, and Chief Editor of ExcelDemy. He is the founder and CEO of SOFTEKO (a software and content marketing company). He has a B.Sc in Electrical and Electronics Engineering. As a Udemy instructor, he offers 8 acclaimed Excel courses, one selected for Udemy Business. A devoted MS Excel enthusiast, Kawser has contributed over 200 articles and reviewed thousands more. His expertise extends to Data Analysis,... Read Full Bio

2 Comments
  1. This sheet has bugs. Monthly option is not avavilable in extra payment frequency. Also the summary table does not update

    • Hello MAX
      Thank you for your comment. I have examined the entire workbook properly. As per my understanding, the sheet works fine.
      The “Monthly” option in Extra Payment Frequency will be available if you select the “Monthly” option in the Regular Payment Frequency field. Actually, the options in Extra Payment Frequency are dependent on the Regular Payment Frequency field.

      Coming to the 2nd issue, the summary table updates properly on my device. In your case, could you please check the version of Excel you are operating? This sheet has some advanced functions that may not work in all Excel versions.

      Please let us know if you are satisfied with this answer. If not, please feel free to ask us. You can even send your dataset to the following address: [email protected]

      Thanks again. Have a good day!

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo