Excel amortization schedule with irregular payments (free Excel template)
“No Macro used. No installation is necessary. Just download and start using.” – Kawser Ahmed (Template Developer)
Created using Excel 2016 version
License: Personal Use (Not for resale or distribution)
Let us know (in the comment box) your criteria or the problems that you’re facing while using this template. We shall update the template.
Financial freedom is actually becoming debt-free. Do you want to pay off your loan or mortgage faster with some extra and irregular payments?
My Excel template (Amortization schedule with irregular payments) will solve your mortgage paying off problem in different 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)
Let me show you how you will use this template.
Amortization schedule with regular payment (PMT)
Suppose you took a loan for your home (or for any other purposes) with the following details:
- Loan Amount: $250,000
- Loan Terms: 20 Years
- Annual Percentage Rate (APR): 6%
- Payment Type: End of the Period
- Payment Frequency: Monthly
You want to know what your monthly payment will be.
See the image below (filled with the above data).
Observer the image carefully.
1) Your monthly payment will be $1791.08.
2) On the right side of the image, you will find your loan details. You will pay total amount (principal + interest) = $429,858.64. Total interest payment = $179,858.64. And you’re not saving any as you did not pay any extra money except the regular monthly payment.
3) You are seeing the amortization table at the lower part of the image. Amortization table’s green numbers denote those periods for which you have (/should have) cleared your payments.
Amortization schedule with regular extra payment (Recurring Extra Payment)
Now you have made 20 payments and your monthly income has increased. So, you want to add some extra recurring payments to your monthly payment from the 21st period.
Suppose, your plan is to pay $500 extra every two months and you want to do so for the rest of the loan period.
I just poured these data into the template and here are the results (image below).
1) We have added the above data here. Extra payment = $500. The extra payment will be paid every two months. So, I have selected Bi-monthly from the drop-down. The extra payment will be paid from the 21st period.
2) Check out the loan summary on the right. Your total payment (principal + interest) has decreased to $395,178.84. Total interest payment = $145,178.84 and you will save total $34,679.80. Total time has reduced from 20 years to 16 years and 5 months.
3) The amortization table is now showing the extra recurring payment every two months from the 21st period.
Amortization schedule with irregular extra payment (Irregular Extra Payments)
Now you’re in a situation that you can pay some extra payments but this is not regular. Say in one month, you can make a payment of $10,000 extra, and in another month, you can add $5000 to your regular payments.
Say you made the following payments:
|Period||Irregular Extra Payment|
Now check out your loan summary.
1) Irregular extra payments are manually inputted into the amortization table.
2) The loan summary has dramatically changed. You are now paying total (principal + interest) = $341,999.37. Total interest paid is $91,999.37 and estimated interest savings is $87,859.27. And the loan total loan term has reduced drastically from 20 years to 11 years and 4 months. That is really a good saving of money and time.
Terms Used in this Template
- Original Loan Terms (Years): Total time to pay off the loan. For home mortgages, this time ranges from 15 to 30 years. For car loans, this time ranges from 3-5 years.
- Original Loan Amount: The principal amount you are borrowing from a bank.
- Annual Percentage Rate (APR): This is also called a nominal/stated interest rate. This is the rate you will see (stated) at your loan papers. But your effective interest rate is different.
- Payment Type: Payment types can be either at the End of the Period (mostly used) or at the Beginning of the Period. You can use both in this template.
- Payment Due: It is actually payment frequency. How many payments will you make in a year? In most mortgage loans, it is monthly. But you can choose other payment frequencies (table below). All these payment frequencies can be selected from the drop-down.
|Interest Compounded||Payment Made After||Payment Frequency|
- Interest Compounded: Normally it is equal to the payment frequency. I mean if your payment frequency is monthly, interest is also compounded monthly. But in some countries like in Canada, though payment is monthly, interest compounding could be semi-annually. Read this article to deal with that kind of situation. Check out this title of the article: What if payment is done monthly but the interest rate is compounded semi-annually?
- Other options are self-explanatory. They are Extra Amount You Plan to Add, Extra Payment (Recurring) Pay, Extra Payment Starts from Payment No.
Factors you should consider before paying your mortgage early
1) Does your bank apply a pre-payment penalty?
Some banks might apply a pre-payment penalty. In that case, you have to pay some penalty for paying off the mortgage loan early. So, I advise you to check carefully the terms and conditions of the loan before taking it.
2) Are you bearing any high paying CREDIT card / Car Loan?
The interest rate of mortgage loans is the lowest. So, if you have any high paying credit card or car loan or any high paying loan, please pay off them at first. Then consider paying off your home loan.
Normally Credit card and Car Loan’s interest are higher than a mortgage loan. So, pay them at first before paying off your mortgage loan.
3) Saved enough in your emergency fund?
Did you save 3-6 months’ expenditures at your emergency fund? A fully-funded emergency fund will be able to bear your 3-6 months’ expenditures. If your emergency fund is not that sufficient, save for your emergency fund at first.
Related Excel Templates
- Mortgage Payoff Calculator with Extra Payment (Free Excel Template)
- Mortgage payoff calculator with extra principal payment (Excel Template)
- Mortgage calculator with extra payments and lump sum [Excel Template]
- Biweekly mortgage calculator with extra payments [Free Excel Template]
I hope you will find our Excel template very helpful in your mortgage amortization calculation. If you have any feedback or you want some other features in this template, let us know in the comment box. Wish you a debt-free life.