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:

Period Irregular 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 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 Compounded Payment Made After Payment Frequency
Weekly 7 Days 52
Bi-weekly 14 Days 26
Semi-monthly 15 Days 24
Monthly 1 Month 12
Bi-monthly 2 Months 6
Quarterly 3 Months 4
Semi-annually 6 Months 2
Yearly 12 Months 1
  • 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.



Hello! 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 them here. Not only how-to guide on Excel, but you will get also topics on Finance, Statistics, Data Analysis, and BI. Stay tuned! You can checkout my courses at Udemy: https://www.udemy.com/user/exceldemy/

  1. 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

    • 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.

      • 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

    • 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. 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. Love it. Perfect for adding irregular payments whenever you want in the payment schedule. Thank you.

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

      • Would like to download michael cooper’s request for change in program for 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. The key here is the user can enter any payment amount into the payment schedule.

        thanks KL Mayfield

        [email protected]

  4. Option for no compounding interest.

  5. 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. 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. 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.

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

  9. 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?

    • 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.

      • Hi! I’m looking for one like this that can handle multiple debts for a snowball/avalanche affect, but with regular and irregular payments. Have anything like that yet?

  10. Hi Kawser,

    I am struggling with the below HP schedule:

    Loan amount: 993,516.60
    Interest rate 3.3% pa
    Interest charge 87,124.37
    No of payments 59
    Monthly 4 3,260.00
    Irregular 1 93,579.69
    Monthly 6 3,260.00
    Monthly 48 19,884.61

  11. Awesome template. Had do do a bit of tweaking to get the regular payments to align (I’m doing lump payments a the beginning of each year). Once I got everything aligned it worked like a charm!

  12. I can’t tell you how helpful this is and exactly what I’ve been looking for all these years. I’ve got a number of notes/loans that I collect off of and having an easy way to throw in an extra irregular payment when one is sent my way to help organize the new principal balance is perfect.

  13. Very nice. Will this handle multiple skipped payments or multiple partial payments or multiple higher payments from the original amount due and show the resulting balance due with interest?

  14. I agree with the suggestion of Michael Cooper, January 31, 2020. This template is great and what I’ve been looking for, but Would you DESIGN A TEMPLATE FOR ME (and Michael) that allows 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 user can enter any payment amount into the payment schedule.

  15. Hello, I was wondering if you could help me tweak this document template for a loan that I have that makes sporadic repayments towards interest / principle not within the normal repayment schedule? I have a loan that I’m trying to understand retroactively with the transactions made previously.

  16. Do any of your templates handle a loan with no regular payments, just a six month balloon payment but also some irrregular payments/pre-payments, and daily compounding? Thank you so much.

  17. Reply
    Elizabeth d Jones Feb 5, 2022 at 10:49 PM

    despite promise of template for irregular payments of a loan.. none available for me to create…..pfffft

  18. THANK YOU! This is a true gift! Excellent.

  19. Kawser – I agree with Michael Cooper’s comment above. What Michael describes is exactly what I’m looking for. Essentially, we know that we have to repay a loan for $x over the next 5 years with a set interest rate. The lender, however, has given us the option to pay whatever we choose whenever we choose over the next 5 years. We could skip some monthly payments or we could increase our monthly payments randomly if we have the ability. At the end of the 5 years we would be required to pay any remaining balance.

    Does anything like this exist?

Leave a reply