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.

• 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:

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.

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.

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.

• 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.

Download Practice Workbook

Download the free template.

Related Excel Templates

• Mortgage Repayment Calculator with Offset Account and Extra Payments in Excel
• How to Make Chattel Mortgage Calculator in Excel
• How to Create Reverse Mortgage Calculator in Excel
• Calculator for Effective Interest Method of Amortization
• Creation of a Mortgage Calculator with Taxes and Insurance in Excel
• Interest Only Mortgage Calculator with Excel Formula
• Biweekly Mortgage Calculator with Extra Payments in Excel
• How to Create Fixed Rate Mortgage Calculator in Excel
• How to Create Offset Mortgage Calculator in Excel
• Mortgage Calculator with Extra Payments and Lump Sum in Excel

<< Go Back to Mortgage Calculator | Finance Template | Excel Templates