Amortization Schedule with Irregular Payments in Excel (3 Cases)

 

Amortization Terms Used in This Article

1. Original Loan Terms (Years): The total time taken to pay off the loan.

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

3. Annual Percentage Rate (APR): This is the interest rate. It is also known as nominal/stated interest rate.

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

5. Payment Due: Number of payments in a year. Payments are commonly made monthly but 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: When is interest applied to the current outstanding loan. It typically follows the payment frequency but can be different.

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


3 Ways to Calculate Amortization Schedule with Irregular Payments

We’ll cover three of the most common ways to pay off a loan:

  • Amortization Schedule with Regular Payment (PMT)
  • Amortization Schedule with Regular Extra Payment (Recurring Extra Payment)
  • Amortization Schedule with Irregular Extra Payment (Irregular Extra Payments)

The template provided below will have information fields you can use for every case.

excel amortization schedule with irregular payments


Case 1 – Amortization Schedule with Regular Payment (PMT)

Consider the following scenario:

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

You want to know what your monthly payment will be.

  • Enter the information in its respective cells and the amortization schedule will be generated as shown in the picture below.

Amortization Schedule with Regular Payment (PMT)

  • The monthly payment is $1,791.08 and you’ll find the additional loan details in the Summary table.
  • The Total Payment (principal + interest) is $429,858.64.
  • The Total Interest Paid over the maturity of the loan is $179,858.64.
  • The Total Period is 240 months.

Note: The orange numbers denote those periods for which you should have cleared your payments.

excel amortization schedule with irregular payments

Read More: Multiple Loan Amortization Schedule Excel Template


Case 2 – Amortization Schedule with Regular Extra Payment (Recurring Extra Payment)

Let’s say your monthly income has gone up and you want to add an extra bi-monthly recurring payment starting from the 24th period to pay off the loan faster. In this case, you’ve chosen to pay $500 for the rest of the loan period.

Amortization Schedule with Regular Extra Payment (Recurring Extra Payment)

  • The regular monthly payment remains the same at $1,791.08.
  • The extra, recurring payments and the loan details are shown in the Summary table.
  • The Total Payment (principal + interest) now decreases to $396,277.94.
  • The Total Interest Paid drops to $146,277.94, creating an Interest Savings of $33,630.69.
  • The Total Period is reduced 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 Payments

Let’s assume that you’ll 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
  • Input that information in the provided template and see the results.

Amortization Schedule with Irregular Extra Payment (Irregular Extra Payments)

  • The monthly payment remains $1,791.08.
  • The extra, irregular payments and the loan details are given in the Summary table.
  • The Total Payment (principal + interest) decreases further to $342,580.08.
  • The Total Interest Paid also decreases to $92,580.08 with the Interest Savings increased to $87,278.56.
  • The Total Period drops to 11 years, 5 months, or 137 months.

excel amortization schedule with irregular payments

Read More: Amortization Schedule Excel Template with Extra Payments

 


Practice Section

We have provided a Practice Section on the right side of each sheet so you can practice yourself.

Practice Section


Download the Practice Workbook

You can download the practice workbook from the link below, which you can use as a template.


Related Articles


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

Get FREE Advanced Excel Exercises with Solutions!
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

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

      • I saw your reply from 2021 regarding making new templates in coming months – just curious if one has been created that recalculates interest and principle based on the irregular date of payments?

        • Avatar photo
          Shamima Sultana Apr 29, 2024 at 4:25 PM

          Hello T Althoff,

          Our updated article indeed includes three templates of different cases, each addressing different scenarios. You can modify these templates to recalculate interest and principal based on irregular payment dates, they offer versatile solutions for various other scenarios.

          Regards
          ExcelDemy

      • 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