Mortgage calculator with extra payments and lump sum [Excel Template]

Mortgage calculator with extra payments and lump sum

Mortgage calculator with extra payments and lump sum (Free Excel Template)

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

Becoming a debt-free person is the best blessing in this age of the debt. From big country to small business all are sunk in debt. From Warren Buffet to Ray Dalio, all big investors emphasize on living beyond your means and saving the rest.

I have developed an Excel template that will be the best tool in your journey of becoming debt free.

Let me explain the terminology and uses of this template. I can assure you that this is the most versatile Excel calculator on the mortgage loan.

You can use this calculator in three ways:

  • Calculating your regular payments (PMT)
  • Deposit recurring extra payments
  • Depositing irregular / lump sum payments when you are able

The template will show you the following outputs:

  • Total amount paid over the lifetime of the loan
  • Total interest paid
  • Estimated interest savings (if you made recurring extra payments or irregular/lump sum extra payments)
  • Total number of payments
  • Time saved (if you made recurring extra payments or irregular/lump sum extra payments)

Further options:

  • Two types of Payment
    • End of the Period
    • Beginning of the Period
  • You can choose your Interest Compounding Frequency. In some countries, for example, Canada, payment frequency and interest compounding frequency can be different.
  • You can choose different payment frequency for your extra recurring payments

Using Mortgage calculator with extra payments and Lump Sum

Let me show the use of this template with an example.

Suppose you have loan details like the following:

  • Original Loan Amount: $200,000
  • Original Loan Terms (Years): 30
  • Remaining Years:50. It means you have already paid the regular payments for the period 30-28.50 = 1.5 years
  • Annual Percentage Rate: 6%
  • Loan Date (mm/dd/yy): 1/1/2019
  • Payment Type: End of the Period
  • Regular Payment Frequency: It means you pay your regular payments every month.
  • Interest Compounding Frequency: Monthly. This is the normal case: Regular Payment Frequency will be same as the Interest Compounding Frequency. For different Interest Compounding Frequency & Regular Payment Frequency, Interest Compounding Frequency must be equal to or higher than the frequency of the Regular Payment.
  • Extra Amount You Plan to Add ($): 200. So, except your regular PMT payments, you want to pay an extra $200.
  • Extra Payment Frequency: So, you want to add your extra payment ($200) every month. You can also choose Bi-monthly, Quarterly, Semi-annually, Annually. For “Monthly” Extra Payment Frequency, you cannot choose Weekly or Bi-weekly frequencies. The template will show errors.
  • Adding Lump Sum Payments: On 13th and 17th periods, let’s add two lump sum payments $10,000 and $15,000 respectively. You will input these values directly under the column Extra Payment (Lump Sum) on the respective payment periods.

I have input all the information in my template. See the following image.

mortgage calculator with extra payments and lump sum payments

You see that extra payments have been added on 19th period (after we are done with a total of 18 payments).

Here is the loan summary:

Loan Summary with extra payments and lump sum amount payments

We are saving:

  • Interest: $120,330.21
  • Time: 12 years and 8 months

So, just adding two lump sum payments of amounts $10,000 and $15,000, adding $200 of extra payments every month, and depositing our regular payment we saved a lot of time and a good amount of money.

Cautions when you’re using this Excel calculator

1) If Interest Compounding Frequency is lower than the frequency of Regular Payment, the template will show an error and will not provide correct results.

warning if interest compounding frequency is lower than the frequency of the regular payment frequency.

You see an error is showing when Interest Compounding Frequency is Semi-monthly and Regular Payment Frequency is Monthly.

The error says:

<< Warning! When interest is compounded ‘Semi-monthly’, Payment cannot be ‘Monthly’

2) Extra Payment Frequency will be equal or multiple of Regular Payment Frequency.

Observer the image below. Regular Payment Frequency is Monthly but our Extra Payment Frequency is Bi-Weekly. So, on the right side of the Summary table, we are seeing an error.

“Warning! Regular Payment Frequency & Extra Payment Frequency don’t MATCH. Check out them”

warning when extra payment frequency is not equal or multiple of regular payment frequency

Why paying off your loan early is a good decision?

When you took the loan, you did not have the plan to pay off your loan early. Right?

But things change with time. You might have a raise in your job, this will be good news. But you might get lay off after you took the loan.

We live in a fast-changing world. Sometimes it is tough to predict the very next 30 days. How can you predict your financial futures for the next 30 years?

Another scenario is (it has happened in my life), realizing the importance of becoming debt-free. When you took the loan, you did not realize that you’re going to be a slave of the financial system. This post-realization creates an urge in people to become debt-free.

So, whatever the situation is DECIDING TO BECOME DEBT-FREE IS THE BEST DECISION. It will give you freedom. Your hard-earned money will not be drained to a financial institution.

One dilemma to consider before paying off your debt

I agree all debts are not bad.

In some cases, debts can be good for you if you have full control over your debt.

Suppose you have a running project. The project is making a good return on your investment.

Say the ROI of the project is 25%.

So, if you invest $100,000, you can earn $25,000 after 1 year.

You talked to a bank. They are offering you a loan with interest 8%. So, after giving the interest to the bank ($8,000), you’re earning $17,000 every year.

This return can prompt you to take the loan.

But here are the risk factors:

  • Your project might have new competitors. So, your sales or returns from the investment can be diminishing.
  • If you don’t have good control over yourself and money, you might spend the money on non-productive things. Spending on non-productive things (for example, car) will increase your monthly expenditures.
  • You don’t know the future. A business always runs with several ‘X’ factors that you don’t know. For a single unknown factor, your business return may collapse.

Mortgage interest is tax-free. Is it wise to be debt free?

In the USA and some other countries, mortgage interests are tax-free. This is one reason people don’t get excited to pay off the loan earlier.

Suppose you’re planning to pay $1200 every month for your $200,000 loan at APR 6% for the next 30 years.

You earn $4000 every month and you’re are in the 25% tax bracket (USA).

For the next 30 years, you save $300 tax every month for your $4000 income.

Total savings is $108,000.

Now here is the calculation for early paying off your debt:

You pay $300 every month after 2 years. So, you save $75 every month for tax exemptions.

Total tax savings = $75 x 19 years 7 months = $17,625

Total tax exemptions for the regular monthly payments: $300 x 19 years 7 months = $70,500

Loan summary with extra payments

And as the loan is paid in 19 years and 7 months, you don’t have to pay any loan for the next 10 years and 5 months.

So, your total savings will be: ($1200 – $300) x 10 years 5 months = $112,500. I have deducted the tax savings $300 from your monthly payment $1200 to get more fair savings.

So, our grand savings total will be: $17,625 + $70,500 + $112,500 = $200,625

It is good, right? Paying off your loan earlier saves you time and money, give you more freedom on your life.

Things to consider before paying off your loan earlier

Ask the following questions before paying off your loan earlier:

  • Are there any prepayment penalties? If so, change your bank and choose one that doesn’t have any prepayment penalties.
  • Do you have any high paying credit card or any other debts? Then pay them at first. Mortgage loan interest rates are the lowest in the debt world. So, it is always better to pay your 15% credit card debt before paying your 6% mortgage loan
  • Have you saved enough in your emergency fund? If not, then save for your emergency fund at first. Then consider paying off your mortgage loan earlier.
  • Are you feeling the mortgage loan is ruling your life? If so, then pay extra to pay off your loan faster.

I have made a Prepayment checklist in the template workbook. If all the factors give you green signals, then start paying off your mortgage loan faster.

Mortgage loan prepayment check list

Related Excel Templates

Conclusion

This is one of the most versatile Excel templates to calculate your extra payments and lump sum with your mortgage loan. And the biggest benefit is you can use this template over and over again. In an online calculator, your calculations do not remain saved. You have to input data again and again. But with an Excel template, you can input your data once and use it again and again.


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 here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

We will be happy to hear your thoughts

      Leave a reply