Amortization Schedule with Irregular Payments in Excel (3 Cases)

Get FREE Advanced Excel Exercises with Solutions!

Financial freedom is becoming debt-free. Do you want to pay off your loan or mortgage faster with some extra and irregular payments? Then you’ve come to the right place. In this article, we’ll demonstrate 3 handy cases to calculate amortization schedules with irregular payments in Excel.


Amortization Terms Used in This Article

1. Original Loan Terms (Years): The total time taken to pay off the loan. For instance, this time ranges from 15 to 30 years in the case of home mortgages, while for car loans, this time ranges between 3-5 years.

2. Original Loan Amount: The principal amount that you’re borrowing from the bank.

3. Annual Percentage Rate (APR): This is the interest rate that you’ll see (stated) on your loan papers. Additionally, it is also known as nominal/stated interest rate, however, the effective interest rate is different.

4. Payment Type: Payment types can be either at the End of the Period (mostly used) or at the Beginning of the Period.

5. Payment Due: This represents the frequency of payment i.e. how many payments you’ll have to make in a year. Generally speaking, payments are commonly made at the end of the month (monthly), however, you can choose other payment frequencies as shown in the table below.

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

6. Interest Compounded: In general, it is equal to the payment frequency. Simply put, if your payment frequency is monthly, then your interest is also compounded monthly. In contrast, in some countries like Canada, though payment is monthly, interest compounding may be semi-annually.

Lastly, other terms such as the Extra Amount You Plan to Add, Extra Payment (Recurring) Pay, and Extra Payment Starts from Payment No. are self-explanatory.


3 Ways to Calculate Amortization Schedule with Irregular Payments

Indeed, this article will help you to pay off your mortgage in 3 diverse 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)

Therefore, without further delay, let’s explore them one by one.

excel amortization schedule with irregular payments


Case-1: Amortization Schedule with Regular Payment (PMT)

Now, let us consider the following scenario, where you took a home loan (or for any other purpose) with the following details:

  • First, the Loan Amount is $250,000.
  • Second, the Loan Term spans over 20 years.
  • Third, the Annual Percentage Rate (APR) consists of  6%.
  • Fourth, the Payment Type involves paying at the End of the Period.
  • Finally, the Payment Frequency is Monthly.

Furthermore, you want to know what your monthly payment will be. So, just enter the above information in their respective cells and the amortization schedule will be generated, as shown in the picture below.

Amortization Schedule with Regular Payment (PMT)

Here, the monthly payment is $1791.08 and you’ll find the additional loan details in the Summary table.

  • First and foremost, the Total Payment (principal + interest) is $429,858.64.
  • Next, the Total Interest Paid over the maturity of the loan is $179,858.64.
  • Then, the Total Period of the loan consists of 20 years or 240 months.

📃 Note: In addition, the orange numbers denote those periods for which you should have cleared your payments.

excel amortization schedule with irregular payments

Just like that, your amortization schedule is complete, it’s that simple!

Read More: Multiple Loan Amortization Schedule Excel Template


Case-2: Amortization Schedule with Regular Extra Payment (Recurring Extra Payment)

Now, for the second case, you’ve already made 20 payments, moreover, your monthly income has gone up. So, you want to add an extra bi-monthly recurring payment starting from the 24th period. In this case, you’ve chosen to pay $500 for the rest of the loan period. Therefore, let’s see it in action.

Amortization Schedule with Regular Extra Payment (Recurring Extra Payment)

At this time, the monthly payment remains the same at $1791.08 while the extra, recurring payments and the loan details are shown in the Summary table.

  • In the first place, the Total Payment (principal + interest) now decreases to $396,277.94.
  • Following this, the Total Interest Paid drops to $146,277.94 meanwhile, there is an Interest Savings of $33,630.69.
  • Eventually, the Total Period falls to 16 years 5 months, or 197 months.

Amortization Schedule with Regular Extra Payment (Recurring Extra Payment)

Read More: Excel Car Loan Amortization Schedule with Extra Payments Template


Case-3: Amortization Schedule with Irregular Extra Payment (Irregular Extra Payments)

Our third case considers the Excel amortization schedule with irregular payments i.e. you can pay some extra, irregular payments on certain months. Here, we are assuming that you can make the following payments as given below.

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

Hence, let us see the process in detail.

Amortization Schedule with Irregular Extra Payment (Irregular Extra Payments)

Likewise, the monthly payment is the same at $1791.08 while the extra, irregular payments and the loan details are given in the Summary table.

  • First of all, the Total Payment (principal + interest) decreases further to $342,580.08.
  • In turn, the Total Interest Paid also decreases to $92,580.08 meanwhile, the Interest Savings increases to $87,278.56.
  • Subsequently, the Total Period falls to 11 years, 5 months, or 137 months.

excel amortization schedule with irregular payments

Read More: Amortization Schedule Excel Template with Extra Payments


Factors to Consider Before Paying Your Mortgage Early

Here, we’ll discuss a few important factors to keep in mind when considering paying off the mortgage earlier than the loan term.

1. Does your Bank Apply a Pre-payment Penalty?

Some banks might apply a pre-payment penalty for paying off the mortgage loan early. So, it is advisable to carefully check the terms and conditions of the loan before taking it.

2. Are you Bearing any High-paying Credit Card / Car Loans?

Generally, mortgage loans have the lowest interest rate hence, if you have any high-paying loans, please pay off them first and then consider paying off your home loan.

3. Have you Saved Enough in your Emergency Fund?

Now, a fully funded emergency fund will be able to bear your 3-6 months’ expenditures, therefore, if your emergency fund is insufficient, save for your emergency fund first.


Practice Section

We have provided a Practice Section on the right side of each sheet so you can practice yourself. Please make sure to do it by yourself.

Practice Section


Download Practice Workbook

You can download the practice workbook from the link below.


Conclusion

I hope this article helps you understand how to calculate the amortization schedule with irregular payments in Excel. If you have any queries, please leave a comment below.


Related Articles


<< Go Back to Amortization Schedule | Finance Template | Excel Templates

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Kawser Ahmed
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

51 Comments
  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.

    • Hello Gerry Candeloro, this works perfectly on our end.

      Comment Solution

      Here the interest is compounded annually, and the payment due is Monthly and Bi Weekly.

  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?

    • Hello Andrew, you can download this Excel file which allows users to enter any amounts (including zero payments). Here, you need to input your value in Total Periods cell.

      Zero Payments

      • what about additional loan within the period

        • Hello ALEXANDER,
          Thanks for your comment.

          First, calculate the amortization schedule for the original loan. Then, calculate the amortization schedule for the additional loan separately. Now that you have the monthly payments for both loans, you can combine them to create a single amortization schedule. You’ll need to add the monthly payments together for each month and subtract this total from the outstanding balance.

          Repeat this process for each month until both loans are fully paid off. The combined amortization schedule will show the monthly payments and remaining balances as you pay off both loans simultaneously.

          If you have other queries let me know in the comment.
          Regards,
          Sajid Ahmed
          Exceldemy

  20. Thanks a lot. This is exactly what I was looking for.

  21. Will your Excel Amortization Schedule with Irregular Payments handle the following:

    Amount Financed $5,280,000
    Interest Rate 4%
    Term 10Years
    At beginning of Year2 20% Down payment
    At beginning of Year3 20% Down payment on Remaining Balance
    At beginning of Year4 20% Down payment on Remaining Balance
    At beginning of Year5 20% Down payment on Remaining Balance
    At beginning of Year6 20% Down payment on Remaining Balance
    At beginning of Year7 20% Down payment on Remaining Balance
    At beginning of Year8 33% Down payment on Remaining Balance
    At beginning of Year9 33% Down payment on Remaining Balance
    At beginning of Year10 33% Down payment on Remaining Balance

    Thank you,
    Jack

    • Hello, JACK LONETTO. Thank you for your query. After calculating the loan installment using Amortization Schedule with your given criteria, it shows the loan will be dismissed after the 6th installment if you go with a 20% down payment at the beginning of the year.

      If you want to complete this process in 10 years then the criteria will be-

      Amount Financed $5,280,000
      Interest Rate 4%
      Term 10Years
      At the beginning of Year2 2% Down payment
      At the beginning of Year3 2% Down payment on the Remaining Balance
      At the beginning of Year4 2% Down payment on the Remaining Balance
      At the beginning of Year5 2% Down payment on the Remaining Balance
      At the beginning of Year6 2% Down payment on the Remaining Balance
      At the beginning of Year7 2% Down payment on the Remaining Balance
      At the beginning of Year8 3% Down payment on the Remaining Balance
      At the beginning of Year9 3% Down payment on the Remaining Balance
      Remaining balance at the beginning of year10

      For a better understanding, you can check Amortization-Schedule-with-Irregular-Payments as well.

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo