Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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 payments 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. The extra payment will be paid every two months. So, I have selected Bi-monthly from the drop-down. The 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

Now check out your loan summary.

excel amortization schedule with irregular payments Image 3

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

2) The 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 the 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 a 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 most mortgage loans, 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?

The interest rate of mortgage loans 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


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.

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 here. Not only how to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned!

  1. Reply Avatar
    Marie Schurter Oct 8, 2019 at 9:16 AM

    Hi, thank you for these resources they have been very helpful. I am running into trouble with the Loan Amortization Table for one of my loans. When I run the PMT function it comes back about $40 higher per month than I am currently paying – because of this my IPMT & PPMT functions are also off. I am not sure what I need to do to correct this. Thanks, Marie

    • Reply Avatar
      James Jackson Mar 3, 2021 at 1:32 AM

      Thanks for the Excel amortization schedule with irregular payments. It is one of the best. My wish for additional features: Add column for variable interest rates for the loan term, prepayment periods column(monthly, annual, semi-annual, and one-time entries), and perhaps a prepayment penalty feature calculation if possible.

      • Reply Avatar
        Kawser Mar 4, 2021 at 12:09 AM

        Hi James,
        Thanks for your input.
        I am taking note of your problem, and we shall make templates based on your criteria.
        Thanks and regards
        Kawser Ahmed

    • Reply Avatar
      Kawser Oct 8, 2019 at 12:37 PM

      Maybe it is due to the payment time. Do you pay at the beginning of the month or at the end of the month?

  2. Reply Avatar
    Michael Cooper Jan 31, 2020 at 5:20 AM

    It would be useful to allow the user to enter the amount of the payment being made and the program apply the payment to interest and principal. This should allow for zero payments in some payment periods,and variable payments in other pay periods. The key here is the user can enter any payment amount into the payment schedule.

  3. Reply Avatar
    Kelly Apr 18, 2020 at 10:43 PM

    Love it. Perfect for adding irregular payments whenever you want in the payment schedule. Thank you.

    • Reply Avatar
      Kawser Apr 21, 2020 at 9:44 PM

      Thanks, Kelly for your feedback. Glad to hear that it helped you.
      Best regards

  4. Reply Avatar
    Buck Jun 3, 2020 at 8:06 PM

    Option for no compounding interest.

  5. Reply Avatar
    Lionel Walsh Dec 25, 2020 at 2:40 AM

    IN the excel amortisation schedule with irregular payments, the term is in years. Sometimes there are, say, 60 monthly payments and then a balloon payment is made in the 61st month. How do I process that?

  6. Reply Avatar
    Gerry Candeloro Jan 27, 2021 at 8:36 AM

    I think there is something wrong with the calculation fir Bi-Weekly payments. It does not seem to be calculating the Total payment or interest savings correctly so it comes up with no interest savings and barley a month savings in the term. I hope you can fix it.

  7. Reply Avatar
    bhavnesh Feb 19, 2021 at 4:35 PM

    Dear Sir
    I ask Multiple people multiple type loans how to maintain in Excel ? like A B C Person tack loan Multiple type Gold loan person load like how to mateine in excel sheet. Multiple Loan Repayment Planning with Extra Principal.

    • Reply Avatar
      Kawser Feb 19, 2021 at 9:40 PM

      If we make a template for that problem, I will email you. Thanks.

  8. Reply Avatar
    Isagani Silva Mar 4, 2021 at 4:06 PM

    A wonderful template. How about a penalty charge for a missed payment template based on the number of days missed?

    • Reply Avatar
      Kawser Mar 5, 2021 at 10:46 PM

      I am taking a note of your suggestion. Thanks.

  9. Reply Avatar
    Walt Olsen Apr 14, 2021 at 5:29 AM

    I appreciate the irregular payment spreadsheet. Do you have a spreadsheet that recalculates interest and principle based on the irregular date of the irregular payment?

    • Reply Avatar
      Kawser Apr 14, 2021 at 11:33 PM

      Not yet, Walt!
      But we shall start making a lot of templates in the coming months. If you need any, you can let us know.

    Leave a reply