Mortgage Calculator with Extra Payments and Lump Sum in Excel

In this article, we will show you three quick steps to create a mortgage calculator with extra payments and a lump sum in Excel.


Download Practice Workbook

You can download the Excel file from the link below.


Using Mortgage/Loan Calculator with Extra Payments & Lump Sum in Excel

Becoming a debt-free person is the best blessing in this age of the debt. From big countries to small businesses, all are sunk in debt. From Warren Buffet to Ray Dalio, all big investors emphasize living beyond your means and saving the rest. We have developed an Excel template that will be the best tool in your journey to becoming debt-free. Let me explain the terminology and usage of this template. We can assure you that this is the most versatile Excel calculator for a 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:

  • The 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 a different payment frequency for your extra recurring payments.

Let me show you 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: 28.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 the 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, in addition to 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 the 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) for the respective payment periods.

We have input all the information in my template. See the following image. You can see that extra payments have been added in the 19th period (after we are done with a total of 18 payments).

Using Mortgage/Loan Calculator with Extra Payments & Lump Sum in Excel

Here is the loan summary:

We are saving:

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

So, by just adding two lump sum payments of amounts of $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.


Step-by-Step Procedures to Create a Mortgage Calculator with Extra Payments and Lump Sum in Excel

We will show 3 quick steps to create a mortgage calculator with extra payments and a lump sum in Excel. Firstly, we will enter the loan details. Following that, we will calculate the payment schedule. Thirdly, we will find the payment summary to complete our objective for this article.


Step 1: Entering Loan Details

We will type the loan details in this first step. We will use the Name Range and Data Validation features of Excel. Then, we will apply the IF, PMT, and VLOOKUP functions to our aid. Without further ado, let us jump into the steps.

  • To begin with, type the following values. Here, 1.5 years have passed after the loan disbursement, so we have typed 28.5 in the remaining years field.

Entering Loan Details to Create Mortgage Calculator with Extra Payments and Lump Sum in Excel

  • Then, press ALT, M, and N (one after another) to bring up the Name Manager dialog box.

Name Manager to Create Mortgage Calculator with Extra Payments and Lump Sum in Excel

  • Here, we can see the “periodic_table” named range on a hidden sheet, “Named Ranges”, and we have done this to keep users from accidentally changing values.

  • Afterward, select cell E11 and press ALT, A, V, and then V one after another to bring up the data validation dialog box.
  • Then, select List inside the “Allow:” box and type “=payment_types” as the source.
  • After that, press OK.

Data Validation to Create Mortgage Calculator with Extra Payments and Lump Sum in Excel

  • By doing so, we will see two items on the dropdown list.

  • Similarly, repeat the above steps for the cells E12, E13, and E15. The source will be “=payment_due” for the cells E12 and E13. Moreover, for cell E15 it will be “=INDIRECT(payment_frequency)”. Remember, the extra payment frequency is only available for “weekly” and “monthly” payment frequencies.
  • Then, type the number of extra payments in cell E14. We have typed $200.

  • Lastly, type this formula to find the value of the installment amount. We are using a conditional formula to determine the installment with the help of the PMT function.

=-IF(payment_type=1,PMT(rate,nper,loan,,1),PMT(rate,nper,loan,,0))

  • Then, type the following formula to calculate the interest rate per period.

=(1+apr/VLOOKUP(interest_compounded,periodic_table,3,0))^(VLOOKUP(interest_compounded,periodic_table,3,0)/VLOOKUP(payment_frequency,periodic_table,3,0))-1

Last Action from First Step to Create Mortgage Calculator with Extra Payments and Lump Sum in Excel

Formula Breakdown

  • Firstly, there are three VLOOKUP functions inside the formula. Each of them returns a value from the third column and matches exactly with the lookup range, which is on the hidden sheet.
  • VLOOKUP(interest_compounded,periodic_table,3,0)
    • Output: 12.
  • VLOOKUP(payment_frequency,periodic_table,3,0)
    • Output: 12.
  • Then, the formula reduces to, (1+apr/12)^(12/12)-1
    • Output: 0.00499999999999989.
  • Thus, we have completed the first step. We will show you how to calculate the rest of the summary values in the last step.

Step 2: Calculating Payment Schedule

We will calculate the mortgage payment schedule with extra payments and a lump sum in this step. We will use the IFERROR, AND, OR, EDATE, and MOD functions in this second step.

  • Firstly, create the row headings as shown in row 22.
  • Secondly, type the following formula to get the beginning balance.

=loan

Calculating Payment Schedule to Create Mortgage Calculator with Extra Payments and Lump Sum in Excel

  • Next, type this formula to get the payment number, and use the Fill Handle to autofill the formula.

=IFERROR(IF(I23<=0,"",B23+1),"")

  • Afterward, type this formula to get the due date and fill the formula downward.

=IF($E$11="End of the Period",IF(B24="","",IF(OR(payment_frequency="Weekly",payment_frequency="Bi-weekly",payment_frequency="Semi-monthly"),first_payment_date+B24*VLOOKUP(payment_frequency,periodic_table,2,0),EDATE(first_payment_date,B24*VLOOKUP(payment_frequency,periodic_table,2,0)))),IF(B24="","",IF(OR(payment_frequency="Weekly",payment_frequency="Bi-weekly",payment_frequency="Semi-monthly"),first_payment_date+(B24-1)*VLOOKUP(payment_frequency,periodic_table,2,0),EDATE(first_payment_date,(B24-1)*VLOOKUP(payment_frequency,periodic_table,2,0)))))

Formula Breakdown

  • OR(payment_frequency=”Weekly”,payment_frequency=”Bi-weekly”,payment_frequency=”Semi-monthly”)
    • Output: FALSE.
  • first_payment_date+B24*VLOOKUP(payment_frequency,periodic_table,2,0)
    • Output: 43467.
  • EDATE(first_payment_date,B24*VLOOKUP(payment_frequency,periodic_table,2,0))
    • Output: 43497.
  • first_payment_date+(B24-1)*VLOOKUP(payment_frequency,periodic_table,2,0)
    • Output: 43466.
  • EDATE(first_payment_date,(B24-1)*VLOOKUP(payment_frequency,periodic_table,2,0))
    • Output: 43466.
  • So, the formula reduces to, IF($E$11=”End of the Period”,IF(B24=””,””,IF(FALSE,43467,43497)),IF(B24=””,””,IF(FALSE,43466,43466)))
    • Output: 43497.
    • This means February 01, 2019.
  • Next, type another formula to get the due payment and fill the formula down.

=IF(B24="","",IF(I23<payment,I23*(1+rate),payment))

  • Then, type this formula to input the recurring extra payments.

=IFERROR(IF(I23*(1+rate)-D24<$E$14,I23*(1+rate)-D24,IF(B24=$I$17,$E$14,IF(B24<$I$17,0,IF(MOD(B24-$I$17,$E$18)=0,$E$14,0)))),0)

Formula Breakdown

  • IF(B24=$I$17,$E$14,IF(B24<$I$17,0,IF(MOD(B24-$I$17,$E$18)=0,$E$14,0)))
    • Output: 0.
  • I23*(1+rate)-D24
    • Output: 199800.898949694.
  • I23*(1+rate)-D24<$E$14
    • Output: FALSE.
  • So, the formula reduces to, IFERROR(IF(FALSE,199800.898949694,0),0)
    • Output: 0.
    • As the condition of IF is false, it will return the value of zero.
  • After that, manually type the lump sum amount.

Extra Payments to Create Mortgage Calculator with Lump Sum in Excel

  • Next, type another formula and fill it down. We will get the interest paid amount from this.

=IF(AND(payment_type=1,B24=1),0,IF(B24="","",I23*rate))

  • Then, type this formula to get the principal amount.

=IF(B24="","",D24-G24+E24+F24)

  • Lastly, type this formula and fill it in downward. This formula will return the ending balance amount.

=IFERROR(IF(H24<=0,"",I23-H24),"")

  • After doing everything, the final snapshot of this second method will be similar to this.

Payment Schedule Completed to to Create Mortgage Calculator with Extra Payments and Lump Sum in Excel


Step 3: Finding Summary Amount

In the last step, we will find the summary amount that we kept blank in the first step. We will apply the SUM, COUNTIF, DATEDIF, INDEX, and OFFSET functions in this step.

  • Firstly, type this formula to get the total amount to be paid.

=SUM(interest_paid,principal_paid)

Finding Summary Amount to Create Mortgage Calculator with Extra Payments and Lump Sum in Excel

  • Secondly, type this formula to get the total amount of interest to be paid.

=SUM(OFFSET($G$24,0,0,I10))

  • Thirdly, type the following formula to get the value of the estimated interest savings.

=nper*payment-loan-I8

  • After that, type this formula to find the total number of payments.

=COUNTIF(array,">0")

  • Next, type the following formula to calculate the total time.

=DATEDIF(first_payment_date,INDEX(dates,I10),"y") & " Years, " & DATEDIF(first_payment_date,INDEX(dates,I10),"ym") & " Months, " & DATEDIF(first_payment_date,INDEX(dates,I10),"md") & " Days"

Formula Breakdown

  • INDEX(dates,I10)
    • Output: 49796.
  • DATEDIF(first_payment_date,49796,”y”)
    • Output: 17.
    • Here, we have used “y”, that is why it has returned the year number. Similarly, if we input “ym” and “md” the function will return month and day respectively.
  • Therefore, the formula reduces to, =17 & ” Years, ” & 4 & ” Months, ” & 0 & ” Days”
    • Output: 17 Years, 4 Months, 0 Days.
  • Lastly, type this formula to return the value of the time saved. This formula is similar to the last formula, so we will not explain this.

=DATEDIF(INDEX(dates,I10),EDATE(first_payment_date,term*12),"y") & " Years, " & DATEDIF(INDEX(dates,I10),EDATE(first_payment_date,term*12),"ym") & " Months, " & DATEDIF(INDEX(dates,I10),EDATE(first_payment_date,term*12),"md") & " Days"

  • Finally, this concludes the steps to create a mortgage calculator with extra payments and the lump sum in Excel.

Output of Final Step to to Create Mortgage Calculator with Extra Payments and Lump Sum in Excel


Cautions When You’re Using This Excel Calculator

1) If the interest compounding frequency is lower than the frequency of regular payment, the template will show an error and will not provide correct results.

Cautions When You’re Using This Excel Calculator

You see an error showing when the interest compounding frequency is Semi-monthly and the 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 to or greater than the Regular Payment Frequency.

Observe the image below. Our 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”


Why Paying Off Your Loan Early Is a Good Decision?

When you took the loan, you had no plan to pay it off early. Right? But things change with time. You might have a raise in your job, this will be good news. But you might get laid off after you take 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 future 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 were 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 by a financial institution.


One Dilemma to Consider Before Paying Off Your Debt

We agree that all debts are not bad. In some cases, debts can be good for you if you have full control over them. 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 one year. You talked to a bank. They are offering you a loan at 8% interest. 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 diminish.
  • 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, a car) will increase your monthly expenditures.
  • You don’t know the future. A business always runs into several “X”’ factors that you don’t know. If a single unknown factor occurs, 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 a 6% APR for the next 30 years. You earn $4000 every month, and you’re in the 25% tax bracket in the USA. For the next 30 years, you save $300 in tax every month on your $4000 income. The total savings is $108,000.

Now here is the Calculation for Early Paying Off Your Debt:

After 2 years, you pay $300 every month. 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

Mortgage Interest Is Tax-Free. Is It Wise to be Debt Free?

And as the loan is paid in 19 years and 7 months, you don’t have to pay any loans for the next 10 years and 5 months. So, your total savings will be: ($1200 – $300) x 10 years 5 months = $112,500. We have deducted the $300 in tax savings from your monthly payment of $1200 to get even 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 and gives you more freedom in 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 cards or any other debts? Then pay them 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 like the mortgage loan is ruling your life? If so, then pay extra to pay off your loan faster.

Things to Consider Before Paying Off Your Loan Earlier

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


Auto Loan Calculator with Extra Payments and Lump Sum

When you finance a car, you are essentially taking out a loan from a financial institution in order to pay for the vehicle. The loan will have a set interest rate, and you will be required to make monthly payments until the loan is paid off. The size of your monthly payment will depend on the amount you are financing, the interest rate, and the length of the loan (usually expressed in months).

For example, let’s say you are financing $20,000 for a new car at 4% interest for 60 months. Your monthly payment would be $377.42. This means that every month for the next five years, you would need to pay $377.42 to the lender. At the end of the 60 months, you would have paid a total of $22,649.20, which would include the original $20,000 loan amount, plus interest.

It’s important to note that you don’t have to finance the entire cost of the car. You can put down a down payment (usually 10-20% of the total cost) and only finance the remaining amount. Doing so would lower your monthly payment as well as the interest amount that you need to pay back.

This is similar to the mortgage loan, and the auto loan is easier to get in the market. Moreover, the existing template can be used as an auto loan calculator with extra payments and a lump sum. You can enter your relevant values in the template of this article to create an auto loan calculator.


Conclusion

We have shown you quick steps to create a mortgage calculator with extra payments and a lump sum in Excel. 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 that you can use this template over and over again. In the online calculator, your calculations do not remain saved. You have to input the data again and again. But with an Excel template, you can input your data once and use it again and again.


Related Articles

Kawser

Kawser

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 them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

8 Comments
  1. Hey, This calculator is great! The only thing I added, and I think would probably be a help to people, is some summaries for what has already been spent towards principal and interest, and what is remaining on both.

    • I am keeping your note. Thanks, Jaime for your feedback.
      Best regards
      Kawser Ahmed

      • Hi Kawser,

        the excelsheet is great but if there is variable interest along the tenure and variable extra payment which can help to reduce tenure period.

        • Hi KAREN LING,
          Glad that you liked the template. This template has got fixed interest rate. You can add variable extra payments manually to your Excel file if you need it.
          Regards,
          Rafi (ExcelDemy Team)

  2. Hi, Thanks for the template. I live in the U.K. and, for many mortgages, interest is charged daily, so any overpayments reduce the interest amount straight away. Consequently it would be useful to be able to add the actual dates and amounts for the overpayments (rather than just the current month ‘period’ possible at the moment).
    Incidentally, the first payment is usually taken on a different date to the subsequent monthly payments (so the first payment is slightly different to take this different payment date into account).
    Any improvements to the template to include these would be great 🙂 Thanks!

  3. Really good calculator, however I pay a regular amount and the is reduces the minimum payment not the the term. Example I always make monthly payment of 1000 however the minimum payment is 750 for month 1, then 749 for month 2 etc. Is this possible?

    • Hi JON,
      Thanks for your feedback. Can you please elaborate on your problem?
      What I can say is that paying an extra amount from the minimum will reduce the next payments. You can proceed with your data. You can also share your Excel file with us and we will look into it.
      Regards.
      Rafi (ExcelDemy Team)

Leave a reply

ExcelDemy
Logo