Creating an Early Mortgage Payoff Calculator in Excel – 3 Examples

 

Introduction to Mortgage

In Mortgage calculation, consider:

  • Principal Amount: The original amount you took as the loan.
  • Regular Monthly Payment: The amount you will pay every month, which includes the interest amount and a portion of the principal amount.
  • Loan Terms: The total number of years to pay 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. If the home loan APR is 6%, the monthly interest rate will be 6%/12 = 5%.
  • Extra Payment: There are two types of Extra Payments: Regular Extra Payment and Irregular Extra Payment.
  • Interest Savings: If you make extra payments, you will save interest.
  • Tax Deduction: Mortgage interest is tax deductible.

 

Example 1 – With Monthly Extra Payments

Consider this situation: Blake took a home loan of $250,000 on Jan 10, 2018. He made 5 payments. His original loan term was 20 years. The annual Percentage Rate was 6%.
In the last 6 months, he paid extra $2000.
To see how much he has to pay if he wants to pay off his loan in the next 10 years:


Use this Payoff Calc. (Target) worksheet to enter details.

Loan Details early mortgage payoff calculator excel

  • You will get the following result.

  • He has to pay $954.10 extra every month to pay the loan in the next 10 years.
  • This is the summary of the loan:

Mortgage Summary early mortgage payoff calculator excel


Example 2 – With Quarterly Extra Payments

Blake wants to pay quarterly, not monthly.
Change the Extra Payment Frequency from Monthly to Quarterly.

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 –  With Recurring Extra Payments

Consider this situation: Fallon took a mortgage loan:

Original Loan Terms (Years): 20 years.

  • Loan Amount: 200,000$
  • APR (Annual Percentage Rate): 4.50%
  • Loan Date: March 10, 2018.
    She wants to extra pay her loan in two ways:
  • A recurring extra payment: She plans to pay 500$ extra every month. But it can be also bi-monthly, quarterly, and yearly.
  • And an irregular extra payment: When she has extra money, she wants to make extra payments

Here are some more details on her present decisions:

  • Extra Amount 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. In the above image, you see that she can add any amount of extra payment to her regular monthly and regular extra (recurring) payments.
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

Use the NPER function. The NPER function calculates how many months it will take to repay a loan of a given amount and interest rate.

Consider this dataset.

 

Steps:

  • Go C7 and enter the following formula.
=NPER(C5/12,-C6,C4)

NPER to calculate loan payoff

  • 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.
  • The negative sign is used for the monthly payment, as the amount is paid.

 


Things to Consider

Observe the image below.

Checklist for early mortgage payoff calculator excel


Download Practice Workbook

Download the free template.


 

Related Excel Templates


<< Go Back to Mortgage CalculatorFinance TemplateExcel Templates

Get FREE Advanced Excel Exercises with Solutions!
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