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 schedule with irregular payments in **Excel**.

**Table of Contents**hide

## Download Practice Workbook

You can download the practice workbook from the link below.

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

**Read More: How to Create Reverse Mortgage Calculator in Excel**

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

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

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

Just like that, your amortization schedule is complete, itâ€™s that simple!

**Read More:** **How to Calculate Monthly Payment on a Loan in Excel (2 Ways)**

__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 **24**th period. In this case, youâ€™ve chosen to pay **$500** for the rest of the loan period. Therefore, letâ€™s see it in action.

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.

**Read More: Mortgage payoff calculator with extra principal payment (Free 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.

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.

**Read More:** **Mortgage Calculator with Extra Payments and Lump Sum in Excel**

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

## 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. Also, if you want to read more articles like this, you can visit our website **ExcelDemy**.

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?

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.

Hello, Michael Cooper. you can

download this Excel fileÂ which allows users to enter any amounts (including zero payments).when i open this, it comes up as a read only file. Is there a way to open it so I can adjust the amounts?

Lori

Hello, Lori. The file is working correctly on our end. I think you need to “Enable Editing” from the protected view feature of Excel.

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]

Hello Kenneth Mayfield. you can

download this Excel fileÂ which allows users to enter any amounts (including zero payments).Option for no compounding interest.

Hey Buck, you can follow

this articlefor no compounding interest.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?

you can

download this Excel fileÂ which allows users to enter any amounts (including zero payments). Moreover, you can see amount to be paid at the last balance, which is 11,368.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.

Here the interest is compounded annually, and the payment due is

MonthlyandBi Weekly.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.

Hi,

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

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

I am taking a note of your suggestion. Thanks.

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?

Hello, if you give us your complete requirements, we can build it for you. Thanks.

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

Hi Sohel! Your problem is not clear to us. You can email us the problem [email protected]

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!

Hi Don,

Thanks. If you need anything you can let me know, I will tweak the Template for you.

Best regards

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.

We are glad that this article helped you.

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?

I think it will handle those criteria. Thanks. If it does not, let us know your problem, we shall make a template on the basis of the criteria.

HI, I tried entering 0 for a skipped payment and it didn’t work.

Any suggestions?

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.

OK, Nancy, I am keeping at our note, we shall develop a template as you require. Thanks for your feedback.

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.

No problem. Send the file to this email: [email protected]

Thanks.

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.

I have to check, Glen. Thanks.

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

Hello Elizabeth D Jones, the template was available on the top of the article. If you still cannot find the link, you can

download from here.THANK YOU! This is a true gift! Excellent.

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

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

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.