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)
- 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).
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.
- Then, press ALT, M, and N (one after another) to bring up the Name Manager dialog box.
- 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.
- 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.
- Then, type the following formula to calculate the interest rate per period.
- 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.
- Output: 12.
- 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
- Firstly, create the row headings as shown in row 22.
- Secondly, type the following formula to get the beginning balance.
- Next, type this formula to get the payment number, and use the Fill Handle to autofill the formula.
- 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)))))
- Output: FALSE.
- Output: 43467.
- Output: 43497.
- Output: 43466.
- 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.
- Then, type this formula to input the recurring extra payments.
- Output: 0.
- Output: 199800.898949694.
- 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.
- Next, type another formula and fill it down. We will get the interest paid amount from this.
- Then, type this formula to get the principal amount.
- Lastly, type this formula and fill it in downward. This formula will return the ending balance amount.
- After doing everything, the final snapshot of this second method will be similar to this.
Step 3: Finding Summary Amount
- Firstly, type this formula to get the total amount to be paid.
- Secondly, type this formula to get the total amount of interest to be paid.
- Thirdly, type the following formula to get the value of the estimated interest savings.
- After that, type this formula to find the total number of payments.
- 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"
- Output: 49796.
- 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.
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.
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
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.
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.
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.