## 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 13^{th}and 17^{th}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

**Read More: **Early Mortgage Payoff Calculator in Excel

## 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
and*ALT, M,*(one after the other) to see the*N*dialog box.*Name Manager*

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

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

- Two items will be displayed in the dropdown list.

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

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

`=-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
functions return a value from the third column and match it with the lookup range.*VLOOKUP* - 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
to copy the formula.*the Fill Handle*

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

**Download Practice Workbook**

Download the Excel file here.

## Related Articles

- 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

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

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)

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.

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)

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

PMTdoes include tax or insurance. Therefore, you will provide $1308as thePMTin your spreadsheet.Let us know if you have more queries.

Regards,

Guria

ExcelDemy.

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/12and later 6 months’ with=8/12. As a result, you will get the values of monthly interests.I hope this solution will help you. Let us know your feedback.

Regards,

Guria

ExcelDemy

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.