# Mortgage Calculator with Extra Payments and Lump Sum in Excel – Easy Steps

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

This calculator can be used to:

• Calculate your regular payments (PMT).
• Deposit recurring extra payments.
• Deposit irregular / lump-sum payments.

The template will showcase 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
• Interest Compounding Frequency.
• A different payment frequency for your extra recurring payments.

Consider the following example:

• Original Loan Amount: \$200,000
• Original Loan Terms (Years): 30
• Remaining Years: 28.50
• Annual Percentage Rate: 6%
• Loan Date (mm/dd/yy): 1/1/2019
• Payment Type: End of the Period
• Regular Payment Frequency:  every month.
• Interest Compounding Frequency: Monthly. This is the normal case: the Regular Payment Frequency is 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.
• Extra Payment Frequency: \$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. You will enter these values directly in the column Extra Payment (Lump Sum).

Here is the loan summary:

We are saving:

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

## Mortgage Calculator with Extra Payments and Lump Sum in Excel – Easy Steps

### Step 1: Entering Loan Details

• Enter the values. Here, 28.5 in the remaining years field.

• Press ALT, M, and N (one after the other) to see the Name Manager dialog box.

• The “periodic_table” is in a hidden sheet to avoid accidentally changing the values.

• Select E11 and press ALT, A, V, and then V (one after the other) to open the data validation dialog box.
• In Allow:, select List.
• In Source, enter =payment_types.
• Click OK.

• Two items will be displayed in the dropdown list.

• Repeat the above steps for E12, E13, and E15. Source will be “=payment_due” for E12 and E13. For E15 it will be “=INDIRECT(payment_frequency)”. Remember, the extra payment frequency is only available for “weekly” and “monthly” payment frequencies.
• Enter the number of extra payments in E14. Here, \$200.

• Enter this formula to find the value of the installment amount. The PMT function is used, here.

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

• Enter 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`

Formula Breakdown

• The three VLOOKUP functions return a value from the third column and match it with the lookup range.
• VLOOKUP(interest_compounded,periodic_table,3,0)
• Output: 12.
• VLOOKUP(payment_frequency,periodic_table,3,0)
• Output: 12.
• The formula reduces to, (1+apr/12)^(12/12)-1
• Output: 0.00499999999999989.

### Step 2: Calculating the Payment Schedule

• Create the row headings as shown in row 22.
• Enter the following formula to see the beginning balance.

`=loan`

• Enter this formula to see the payment number, and use the Fill Handle to copy the formula.

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

• Enter this formula to see the due date and use the Fill Handle to copy the formula.

`=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.
• 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.
• February 01, 2019.
• Enter this formula to see the due payment and drag the Fill Handle to copy it.

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

• Enter this formula for 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.
• 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.
• Enter the lump sum amount manually.

• Enter this formula and drag down the Fill handle to copy it. The interest paid amount will be displayed.

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

• Enter this formula to see the principal amount.

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

• Enter this formula to see the ending balance amount.

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

• This is the final output.

### Step 3: Finding the Summary Amount

• Enter this formula to see the total amount to be paid.

`=SUM(interest_paid,principal_paid)`

• Enter this formula to see the total amount of interest to be paid.

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

• Enter the following formula to see the value of the estimated interest savings.

`=nper*payment-loan-I8`

• Enter this formula to see the total number of payments.

`=COUNTIF(array,">0")`

• Enter 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, “y” was used to return the number of years. By entering “ym” and “md” the function will return month and day.
• The formula reduces to, =17 & ” Years, ” & 4 & ” Months, ” & 0 & ” Days”
• Output: 17 Years, 4 Months, 0 Days.
• Enter this formula to return the value of the time saved.

`=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"`

• This is the final mortgage calculator with extra payments and the lump sum in Excel.

## Caution:

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

You see an error showing when the interest compounding frequency is Semi-monthly and the regular payment frequency is monthly:

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

2) The 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. The error message is displayed.

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

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

In The USA mortgage interests are tax-free.

Consider the following example: 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 a month, and you’re in the 25% tax bracket. For the next 30 years, you save \$300 in tax every month on your \$4000 income. The total savings is \$108,000.

How to Calculate an Early Paying Off Your Debt:

After 2 years, you pay \$300 every month. So, you save \$75 every month in tax exemptions. Total tax savings = \$75 x 19 years, 7 months = \$17,625 Total tax exemptions for regular monthly payments: \$300 x 19 years, 7 months = \$70,500

The loan is paid in 19 years and 7 months. Your total savings will be: (\$1200 – \$300) x 10 years 5 months = \$112,500.  \$300 in tax savings were deducted from your monthly payment of \$1200. Grand savings total will be: \$17,625 + \$70,500 + \$112,500 = \$200,625

## Things to Consider Before Paying Off Your Loan Earlier

• Are there any prepayment penalties?
• Do you have any high-paying credit cards or any other debts? Pay them first. Mortgage loan interest rates are the lowest in the debt world.

There’s a prepayment checklist in the template workbook.

## Auto Loan Calculator with Extra Payments and Lump Sum

The existing template can be used as an auto loan calculator with extra payments and a lump sum.

## Related Articles

<< Go Back to Mortgage CalculatorFinance TemplateExcel Templates

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

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.

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!

• Hi Mike,
I am taking note of your problem, we shall make templates on the basis of your needs.
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,
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)

4. Does the PMT include taxes and insurance? For example, I have a PMT of \$1308 that includes my taxes/insurance. Without it, the PMT is around \$1100. Which number do I put in the spreadsheet as the PMT?

• Hello Violet,
Thanks for sharing your query. Yes, the PMT does include tax or insurance. Therefore, you will provide \$1308 as the PMT in your spreadsheet.
Let us know if you have more queries.

Regards,
Guria
ExcelDemy.

5. Thanks for this spreadsheet. Can you please explain how I adjust the rate increases when the bank does. ie for the first 6 month I was on 7.5% interest per annum and now it is 8% Sorry, I am not very good with excel that is why I am using your preformulated one! Many thanks.

• Hello Nicola,
Thank you for sharing your problem. I assume you are getting difficulties to calculate interest rate as changed twice in a year. In that case, calculate first 6 months’ rate separately with this formula =7.5/12 and later 6 months’ with =8/12. As a result, you will get the values of monthly interests.

Regards,
Guria
ExcelDemy

6. Hi all, this calculator is GREAT! thank you. The only column missing is Early Repayment Charge (ERC) which changes through out the Fixed term is (5% in 1st year; 4% in the second; 3.5% in the third… etc)

I have tried adding the column ‘J’ manually and inserting the following formulas next to each line for months 2-11 (skipping the first month in each year for the 10% early repayment allowance) in this example J25=F25/100*5; J37=F37/100*4; J49=F49/100*3.5) which works fine for calculating the ERC value. However, it does not take into account that the remaining balance (column ‘I’ on which the interest and capital repayment calculations are based on) have in fact increased by the amount of the ERC column ‘J’ value calculated in the previous line.

I have worked it out by adding the value from previous line in ‘J’ column to the end of the formula in ‘I’ column:
I25 =IFERROR(IF(H25<=0,"",I24-H25),"")+J24
I26 =IFERROR(IF(H26<=0,"",I25-H26),"")+J25 etc…

The calculations seems to be correct. I wonder if there is any better way of achieving similar result?

• Hi Mohammed
Thank you for your appreciation. If you want to incorporate ERC in your final balance, I think your method is a nice one.
I would, however, suggest one thing. Keep the ERC in column I and calculate the final balance in column J.

Advanced Excel Exercises with Solutions PDF