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.
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: 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.
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: 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.
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: 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.
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
- Preparing Bond Amortization Schedule in Excel
- Excel Interest Only Amortization Schedule with Balloon Payment Calculator
- Amortization Schedule with Balloon Payment and Extra Payments in Excel
- Excel Car Loan Amortization Schedule Template
- Excel Student Loan Amortization Schedule
- ARM Amortization Schedule Excel TemplateÂ
<< Go Back to Amortization Schedule | Finance Template | Excel Templates
Get FREE Advanced Excel Exercises with Solutions!
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 article for 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 Monthly and Bi 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.