Excel amortization schedule with irregular payments (Free Template)

excel amortization schedule with irregular payments

Excel amortization schedule with irregular payments (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.

Financial freedom is actually becoming debt-free. Do you want to pay off your loan or mortgage faster with some extra and irregular payments?

My Excel template (Amortization schedule with irregular payments) will solve your mortgage paying off problem in different ways:

  • Amortization schedule with regular payment (PMT)
  • Amortization schedule with regular extra payment (Recurring Extra Payment)
  • Amortization schedule with irregular extra payment (Irregular Extra Payments)

Let me show you how you will use this template.

Amortization schedule with regular payment (PMT)

Suppose you took a loan for your home (or for any other purposes) with the following details:

  • Loan Amount: $250,000
  • Loan Terms: 20 Years
  • Annual Percentage Rate (APR): 6%
  • Payment Type: End of the Period
  • Payment Frequency: Monthly

You want to know what your monthly payment will be.

See the image below (filled with the above data).

excel amortization schedule with irregular payments Image 1

Observer the image carefully.

1) Your monthly payment will be $1791.08.

2) On the right side of the image, you will find your loan details. You will pay total amount (principal + interest) = $429,858.64. Total interest payment = $179,858.64. And you’re not saving any as you did not pay any extra money except the regular monthly payment.

3) You are seeing the amortization table at the lower part of the image. Amortization table’s green numbers denote those periods for which you have (/should have) cleared your payments.

Amortization schedule with regular extra payment (Recurring Extra Payment)

Now you have made 20 payments and your monthly income has increased. So, you want to add some extra recurring payment to your monthly payment from the 21st period.

Suppose, your plan is to pay $500 extra every two months and you want to do so for the rest of the loan period.

I just poured these data into the template and here are the results (image below).

excel amortization schedule with irregular payments Image 2

1) We have added the above data here. Extra payment = $500. Extra payment will be paid every two months. So, I have selected Bi-monthly from the drop down. Extra payment will be paid from the 21st period.

2) Check out the loan summary on the right. Your total payment (principal + interest) has decreased to $395,178.84. Total interest payment = $145,178.84 and you will save total $34,679.80. Total time has reduced from 20 years to 16 years and 5 months.

3) The amortization table is now showing the extra recurring payment every two months from the 21st period.

Amortization schedule with irregular extra payment (Irregular Extra Payments)

Now you’re in a situation that you can pay some extra payments but this is not regular. Say in one month, you can make a payment of $10,000 extra and in another month, you can add $5000 to your regular payments.

Say you made the following payments:

PeriodIrregular Extra Payment
29$10,000
42$10,000
55$25,000
60$15,000
70$10,000

Now check out your loan summary.

excel amortization schedule with irregular payments Image 3

1) Irregular extra payments are manually inputted in the amortization table.

2) Loan summary has dramatically changed. You are now paying total (principal + interest) = $341,999.37. Total interest paid is $91,999.37 and estimated interest savings is $87,859.27. And loan total loan term has reduced drastically from 20 years to 11 years and 4 months. That is really a good saving of money and time.

Terms Used in this Template

  • Original Loan Terms (Years): Total time to pay off the loan. For home mortgages, this time ranges from 15 to 30 years. For car loans, this time ranges from 3-5 years.
  • Original Loan Amount: The principal amount you are borrowing from a bank.
  • Annual Percentage Rate (APR): This is also called nominal/stated interest rate. This is the rate you will see (stated) at your loan papers. But your effective interest rate is different.
  • Payment Type: Payment types can be either at the End of the Period (mostly used) or at the Beginning of the Period. You can use both in this template.
  • Payment Due: It is actually payment frequency. How many payments will you make in a year? In the most mortgage loan, it is monthly. But you can choose other payment frequencies (table below). All these payment frequencies can be selected from the drop-down.
Interest CompoundedPayment Made AfterPayment Frequency
Weekly7 Days52
Bi-weekly14 Days26
Semi-monthly15 Days24
Monthly1 Month12
Bi-monthly2 Months6
Quarterly3 Months4
Semi-annually6 Months2
Yearly12 Months1
  • Interest Compounded: Normally it is equal to the payment frequency. I mean if your payment frequency is monthly, interest is also compounded monthly. But in some countries like in Canada, though payment is monthly, interest compounding could be semi-annually. Read this article to deal with that kind of situation. Check out this title of the article: What if payment is done monthly but the interest rate is compounded semi-annually?
  • Other options are self-explanatory. They are Extra Amount You Plan to Add, Extra Payment (Recurring) Pay, Extra Payment Starts from Payment No.

Factors you should consider before paying your mortgage early

1) Does your bank apply a pre-payment penalty?

Some banks might apply a pre-payment penalty. In that case, you have to pay some penalty for paying off the mortgage loan early. So, I advise you to check carefully the terms and conditions of the loan before taking it.

2) Are you bearing any high paying CREDIT card / Car Loan?

Interest rate of mortgage loan is the lowest. So, if you have any high paying credit card or car loan or any high paying loan, please pay off them at first. Then consider paying off your home loan.

Normally Credit card and Car Loan’s interest are higher than a mortgage loan. So, pay them at first before paying off your mortgage loan.

3) Saved enough in your emergency fund?

Did you save 3-6 months’ expenditures at your emergency fund? A fully funded emergency fund will be able to bear your 3-6 months’ expenditures. If your emergency fund is not that sufficient, save for your emergency fund at first.

Related Excel Templates

Conclusion

I hope you will find our Excel template very helpful in your mortgage amortization calculation. If you have any feedback or you want some other features in this template, let us know in the comment box. Wish you a debt-free life.


Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

We will be happy to hear your thoughts

      Leave a reply