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.
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.
- 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.
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.
- 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.
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.
- 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.
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.
Download the Practice Workbook
You can download the practice workbook from the link below, which you can use as a template.
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.
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?
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?
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.