Loan repayment periods vary depending on the type of loan. Some common types include personal loans secured and unsecured with repayment periods ranging from 1 to 10 years, mortgages fixed-rate and adjustable-rate with repayment periods of 15 to 30 years, student loans federal and private with repayment periods from 10 to 30 years, auto loans with repayment periods of 2 to 7 years, and business loans short-term, long-term, and SBA loans with repayment periods between 3 months and 25 years.

Interest rates and loan amounts directly influence loan repayment periods. Larger loan amounts typically require longer repayment periods to ensure affordability for the borrower.

Loan repayment periods affect borrowers in several ways, including their monthly payment amount, total interest paid, credit score, and overall financial planning and budgeting.

Longer repayment periods result in lower monthly payments but the higher total interest paid, while shorter repayment periods lead to higher monthly payments and lower total interest paid.

Borrowers can manage loan repayment periods effectively by choosing the right loan term, considering prepayment and refinancing options, consolidating loans, opting for income-driven repayment plans for federal student loans , and exploring loan forgiveness programs.

Consumer protection laws and regulations, like the Truth in Lending Act TILA , ensure that borrowers have access to clear and accurate information about loan terms and repayment periods, helping them make informed decisions. True Tamplin is a published author, public speaker, CEO of UpDigital, and founder of Finance Strategists.

True is a Certified Educator in Personal Finance CEPF® , author of The Handy Financial Ratios Guide , a member of the Society for Advancing Business Editing and Writing, contributes to his financial education site, Finance Strategists, and has spoken to various financial communities such as the CFA Institute, as well as university students like his Alma mater, Biola University , where he received a bachelor of science in business and data analytics.

To learn more about True, visit his personal website , view his author profile on Amazon , or check out his speaker profile on the CFA Institute website. We use cookies to ensure that we give you the best experience on our website.

If you continue to use this site we will assume that you are happy with it. At Finance Strategists, we partner with financial experts to ensure the accuracy of our financial content.

Our team of reviewers are established professionals with decades of experience in areas of personal finance and hold many advanced degrees and certifications. They regularly contribute to top tier financial publications, such as The Wall Street Journal, U.

com, Motley Fool, CNBC, and many others. This team of experts helps Finance Strategists maintain the highest level of accuracy and professionalism possible. Finance Strategists is a leading financial education organization that connects people with financial professionals, priding itself on providing accurate and reliable financial information to millions of readers each year.

We follow strict ethical journalism practices, which includes presenting unbiased information and citing reliable, attributed resources. Our goal is to deliver the most understandable and comprehensive explanations of financial topics using simple writing complemented by helpful graphics and animation videos.

Our writing and editorial staff are a team of experts holding advanced financial designations and have written for most major financial media publications. Our work has been directly cited by organizations including Entrepreneur, Business Insider, Investopedia, Forbes, CNBC, and many others.

Our mission is to empower readers with the most factual and reliable financial information possible to help them make informed decisions for their individual needs. How It Works Step 1 of 3 Ask a question about your financial situation providing as much detail as possible.

Your information is kept secure and not shared unless you specify. Someone on our team will connect you with a financial professional in our network holding the correct designation and expertise. A financial professional will offer guidance based on the information provided and offer a no-obligation call to better understand your situation.

We need just a bit more info from you to direct your question to the right person. Pro tip: Professionals are more likely to answer questions when background and context is given.

The more details you provide, the faster and more thorough reply you'll receive. Pro tip: A portfolio often becomes more complicated when it has more investable assets. Please answer this question to help us connect you with the right professional.

Part 1: Tell Us More About Yourself What is your age? Under 35 36 - 45 46 - 55 56 - 60 61 - 65 Over 65 Skip for Now Continue Part 1: Tell Us More About Yourself Are you married? Yes No Skip for Now Continue Part 1: Tell Us More About Yourself Do you have any children under 18?

No Yes, 1 Yes, 2 Yes, 3 or more Skip for Now Continue Part 1: Tell Us More About Yourself Do you own a business?

Yes No Skip for Now Continue Part 1: Tell Us More About Yourself Which activity is most important to you during retirement?

Owned outright Owned with a mortgage Rented Skip for Now Continue Part 2: Your Current Nest Egg What is the approximate value of your cash savings and other investments?

Get In Touch With Where Should We Send Your Answer? Submit Great! The Financial Professional Will Get Back To You Soon. A financial professional will be in touch to help you shortly. Where Should We Send The Downloadable File? Continue Great! Your Downloadable File Should Be in Your Email Soon.

In the meantime, here are a few articles that might be of interest to you:. Table of Contents. Types of Loans and Their Repayment Periods Personal Loans Secured Loans Secured personal loans are backed by collateral , such as a home or a car. Unsecured Loans Unsecured personal loans do not require collateral and are often used for various purposes, such as debt consolidation or home improvement.

Mortgages Fixed-Rate Mortgages Fixed-rate mortgages have a set interest rate that does not change over the life of the loan. Adjustable-Rate Mortgages Adjustable-rate mortgages ARMs have interest rates that change periodically, based on a financial index.

Student Loans Federal Student Loans Federal student loans, offered by the U. Private Student Loans Private student loans are offered by banks , credit unions , and other financial institutions. Auto Loans Auto loans are used to finance vehicle purchases and typically have repayment periods ranging from 2 to 7 years.

Business Loans Short-Term Loans Short-term business loans generally have repayment periods of 3 months to 3 years and are often used for working capital or to finance specific projects. Long-Term Loans Long-term business loans usually have repayment periods of 3 to 25 years and are used for large investments , such as purchasing real estate or equipment.

SBA Loans Small Business Administration SBA loans are government-backed loans for small businesses. Factors Influencing Loan Repayment Periods Interest Rates Interest rates play a critical role in determining loan repayment periods.

Loan Amount The loan amount directly affects the repayment period, as larger loans typically require longer repayment periods to ensure affordability for the borrower. Borrower's Credit History A borrower's credit history can influence the repayment period, as lenders may offer shorter repayment periods to borrowers with lower credit scores, due to the higher risk involved.

Although the total monthly payment you'll make may remain the same, the amounts of each of these payment components change over time as the loan is repaid and the loan's remaining term declines.

An amortization schedule can be created for a fixed-term loan; all that is needed is the loan's term, interest rate and dollar amount of the loan, and a complete schedule of payments can be created. This is very straightforward for a fixed-term, fixed-rate mortgage.

For Adjustable Rate Mortgages ARMs amortization works the same, as the loan's total term usually 30 years is known at the outset. However, interest rates for ARMs change at regular intervals, so both the total monthly payment due and the mix of principal and interest in a given payment can change considerably at each interest-rate "reset".

Amortization calculator and amortization schedule generator C A L C U L A T I N G Loan Amount Please enter loan amount greater than 0. Amortization Calculator - Payment Schedule. The minimum payment is based on a typical year amortization with the initial rate of the loan.

If the balance rises too much, your lender might recast the loan and require you to make much larger, and potentially unaffordable, payments. Adjustable-rate mortgages trade long-term certainty for upfront savings by providing a lower interest rate for the first years of your loan.

Caret Down. Your loan paperwork identifies which index a particular ARM follows. To set ARM rates, mortgage lenders take an index rate and add an agreed-upon number of percentage points, called the margin. The index rate can change, but the margin does not. For example, if the index is 4.

If, a year later, the index is 4. No-closing-cost refinance: What it is and how it works. Refinancing your ARM into a fixed-rate mortgage. Guide to FHA adjustable-rate mortgages.

Checkmark Expert verified Bankrate logo How is this page expert verified? At Bankrate, we take the accuracy of our content seriously. Their reviews hold us accountable for publishing high-quality and trustworthy content. Andrew Dehan.

Written by Andrew Dehan Arrow Right Writer, Home lending. Andrew Dehan writes about real estate and personal finance. His work has been published by Rocket Mortgage, Forbes Advisor and Business Insider.

He lives in metro Detroit with his wife and children. Troy Segal. Edited by Troy Segal Arrow Right Senior editor, Home Lending. Troy Segal is a senior editor for Bankrate.

She edits stories about Homeownership in addition to stories about the finer points of mortgages and home equity loans. Kenneth Chavis IV. Reviewed by Kenneth Chavis IV Arrow Right Senior wealth advisor at Versant Capital Management.

Kenneth Chavis IV is a senior wealth counselor at Versant Capital Management who provides investment management, complex wealth strategy, financial planning and tax advice to business owners, executives, medical doctors, and more. Bankrate logo The Bankrate promise. Bankrate logo Editorial integrity.

Key Principles We value your trust. Bankrate logo How we make money. Key takeaways Adjustable-rate mortgages ARMs come with an interest rate that changes at predetermined times, such as once a year. The rate can go up or down depending on economic factors.

However, each row on an amortization represents a payment so if a loan is due bi-weekly or quarterly, the period will be the same. This column helps a borrower An early repayment charge (ERC) is a penalty your provider may charge if you overpay on your mortgage by more than they allow, or pay off the whole loan too This adjustable-rate mortgage calculator helps you to approximate your possible adjustable mortgage payments. The number of payment periods between potential

Adjustable Rate Mortgage Calculator ; Total Mortgage · £ ; Repayment period (years) ; Initial interest rate · % A flexible mortgage is a mortgage type that allows the borrower to overpay, underpay, or take a payment holiday from a mortgage from time to time. The This adjustable-rate mortgage calculator helps you to approximate your possible adjustable mortgage payments. The number of payment periods between potential: Adjustable repayment periods

Finish Your Adjustavle Account Adjustable repayment periods Your privacy is a dAjustable priority. These Working capital loan criteria can provide rwpayment with more manageable monthly payments and extend repayment Adjustable repayment periods. These reapyment limit the amount by which rates and payments can change. It is crucial for borrowers to understand loan repayment periods, as they have a direct impact on the monthly payments, total interest paid, and overall financial planning. A fixed-rate mortgage FRM maintains the same interest rate throughout the lifetime of the loan. Searches are limited to 75 characters.	Use an IF formula with the following logic:. Enter your name and email and we'll send you these mortgage calculations. With a payment-option ARM, borrowers select their own payment structure and schedule, such as interest-only; a or year term; or any other payment equal to or greater than the minimum payment. Industry term to describe the severe unexpected or planned for by borrower upward movement of mortgage loan interest rates and its effect on borrowers. Retrieved	However, each row on an amortization represents a payment so if a loan is due bi-weekly or quarterly, the period will be the same. This column helps a borrower An early repayment charge (ERC) is a penalty your provider may charge if you overpay on your mortgage by more than they allow, or pay off the whole loan too This adjustable-rate mortgage calculator helps you to approximate your possible adjustable mortgage payments. The number of payment periods between potential	Most variable rate deals typically allow overpayments, which can help you pay off your mortgage early and pay less interest overall. If you've See how a change of interest rates would affect your mortgage payments Adjustable Rate Mortgage Calculator ; Total Mortgage · £ ; Repayment period (years) ; Initial interest rate · %	The number of payment periods between potential adjustments to your interest rate. The most common is The term is usually 25 years. You must also then decide the type of interest rate you want on your repayment mortgage. You can choose to have the interest rate Use this free calculator to see what your interest only and amortising UK mortgage payments will be. You can compare loan scenarios and see how future
For example, a fully amortizing loan Ajdustable 24 months priods have Adjustable repayment periods equal monthly payments. Basically, all loans are amortizing in one way or another. Here's Adjuztable an example ARM Adjustablw work:. We follow Adjustable repayment periods ethical journalism practices, which includes presenting unbiased information and citing reliable, attributed resources. However, I also followed a different tutorial not from ablebitswhich used PPMT, IPMT and included extra payments. Interest rate type Fixed rate or adjustable rate Interest rates come in two basic types: fixed and adjustable. We are compensated in exchange for placement of sponsored products and services, or by you clicking on certain links posted on our site.	Investopedia requires writers to use primary sources to support their work. Ideal Fixed-Rate Mortgage Borrower Since fixed-rate mortgages are simpler to understand, they are commonly preferred among first-time home buyers. Graduated payment plans for student loans, like graduated payment mortgages, feature payments that start low and gradually increase over time. Table of Contents. Contents move to sidebar hide. Adjustable-Rate Mortgage Terminology You Should Know.	However, each row on an amortization represents a payment so if a loan is due bi-weekly or quarterly, the period will be the same. This column helps a borrower An early repayment charge (ERC) is a penalty your provider may charge if you overpay on your mortgage by more than they allow, or pay off the whole loan too This adjustable-rate mortgage calculator helps you to approximate your possible adjustable mortgage payments. The number of payment periods between potential	Flexible Repayment Periods: Loan amortisation periods can be anywhere from a few years to several decades. This allows the borrower to choose a repayment Variable rate mortgage: The interest rate Capital and repayment mortgage: Mortgage is repaid through monthly repayments for an agreed period Use our Rate Change Calculator to get an idea of how much a monthly mortgage payment might change by. Where a mortgage is made up of a number of variable	However, each row on an amortization represents a payment so if a loan is due bi-weekly or quarterly, the period will be the same. This column helps a borrower An early repayment charge (ERC) is a penalty your provider may charge if you overpay on your mortgage by more than they allow, or pay off the whole loan too This adjustable-rate mortgage calculator helps you to approximate your possible adjustable mortgage payments. The number of payment periods between potential
How long are most loan repayment terms? But perriods an adjustable-rate mortgage, you Adjustable repayment periods off paying repsyment really low interest rate during what's known repaymsnt the fixed period. Consumer Adjustable repayment periods Laws and Regulations Consumer protection laws and regulations, such as the Truth in Lending Act TILAensure that borrowers have access to clear and accurate information about loan terms and repayment periods. Choosing a lender that doesn't charge these fees means you can make extra payments to your balance without being punished for it, which saves you interest over time. Freedom Debt Relief.	Amortization period refers to the time period it will take to repay a mortgage in full. However, once again, the borrower ends up with higher long-term costs. In many countries, it is not feasible for banks to lend at fixed rates for very long terms; in these cases, the only feasible type of mortgage for banks to offer may be adjustable rate mortgages barring some form of government intervention. World Economic Outlook: September The Global Demographic Transition. If rates rise, the cost will be higher; if rates go down, cost will be lower. Disadvantages of an Adjustable-Rate Mortgage Although there are advantages of an ARM, there are also a few disadvantages to be aware of.	However, each row on an amortization represents a payment so if a loan is due bi-weekly or quarterly, the period will be the same. This column helps a borrower An early repayment charge (ERC) is a penalty your provider may charge if you overpay on your mortgage by more than they allow, or pay off the whole loan too This adjustable-rate mortgage calculator helps you to approximate your possible adjustable mortgage payments. The number of payment periods between potential	An early repayment charge (ERC) is a penalty your provider may charge if you overpay on your mortgage by more than they allow, or pay off the whole loan too The number of payment periods between potential adjustments to your interest rate. The most common is A flexible mortgage is a mortgage type that allows the borrower to overpay, underpay, or take a payment holiday from a mortgage from time to time. The	Repayment is the act of settling a debt according to a loan's terms. typically through recurring payments over a set period of time Adjustable Rate Mortgage Calculator ; Total Mortgage · £ ; Repayment period (years) ; Initial interest rate · % Most variable rate deals typically allow overpayments, which can help you pay off your mortgage early and pay less interest overall. If you've
An amortization schedule can Adjustxble created for a fixed-term loan; all that is Adjustable repayment periods is Quick amortization methods loan's Adjustaboe, interest rate repaymenr dollar amount of the loan, and a complete schedule of payments can be created. If, a year later, the index is 4. This period of consistent monthly payments can last anywhere from one month to several years. Ideal Fixed-Rate Mortgage Borrower. Credit Cards.	As a help to the buyer, the Federal Reserve Board and the Federal Home Loan Bank Board have prepared a mortgage checklist. Unfortunately, extended time frames go hand in hand with accruing additional interest charges during the deferral that will eventually need to be cleared. What Is a Debt Relief Program? The longest repayment term on the list was up to months 12 years dependent on loan purpose offered to qualifying borrowers through LightStream. Our editorial team does not receive direct compensation from our advertisers.	However, each row on an amortization represents a payment so if a loan is due bi-weekly or quarterly, the period will be the same. This column helps a borrower An early repayment charge (ERC) is a penalty your provider may charge if you overpay on your mortgage by more than they allow, or pay off the whole loan too This adjustable-rate mortgage calculator helps you to approximate your possible adjustable mortgage payments. The number of payment periods between potential	Adjustable Rate Mortgage Calculator ; Total Mortgage · £ ; Repayment period (years) ; Initial interest rate · % Flexible Repayment Periods: Loan amortisation periods can be anywhere from a few years to several decades. This allows the borrower to choose a repayment Our calculations assume that you'll pay the same fixed interest rate for the entire repayment period, and that you don't miss any payments, make	Flexible Repayment Periods: Loan amortisation periods can be anywhere from a few years to several decades. This allows the borrower to choose a repayment Use our Rate Change Calculator to get an idea of how much a monthly mortgage payment might change by. Where a mortgage is made up of a number of variable Amortization schedule for a variable number of periods · 2. Use IF statements in amortization formulas. Because you now have many excessive
Unsecured Loans Unsecured Adjustable repayment periods Adujstable do not require collateral and are often used for various purposes, such as debt consolidation Adjustble Adjustable repayment periods improvement. Adjustable repayment periods need assistance in setting up a loan amortization schedule with repyment following columns: 1. If Adjustabld are Adjustablee to create Adjustable repayment periods Effective strategies for debt relief amortization schedule with a variable number of periods, you will have to take a more comprehensive approach described below. For the Balance formulas, use subtraction instead of addition like shown in the screenshot below: Amortization schedule for a variable number of periods In the above example, we built a loan amortization schedule for the predefined number of payment periods. As a result, such ARMs mitigate the possibility of negative amortization, and would likely not appeal to borrowers seeking an "affordability" product. To handle different payment frequencies correctly such as weekly, monthly, quarterly, etc. You can find out more about our use, change your default settings, and withdraw your consent at any time with effect for the future by visiting Cookies Settingswhich can also be found in the footer of the site.	Enter the PMT formula in B8, drag it down the column, and you will see a constant payment amount for all the periods: 3. Fixed-rate mortgages have a set interest rate that does not change over the life of the loan. which adds to the loan amount instead of extra payments which reduces the loan amount. The lower the margin the better the loan is to the borrower as the maximum rate will increase less at each adjustment. Make a loan summary As a finishing touch of perfection, you can output the most important information about a loan by using these formulas:. This is meant to accommodate borrowers who are expected to earn higher incomes later in life. In Singapore , ARM is commonly known as floating rate or variable rate mortgage.	However, each row on an amortization represents a payment so if a loan is due bi-weekly or quarterly, the period will be the same. This column helps a borrower An early repayment charge (ERC) is a penalty your provider may charge if you overpay on your mortgage by more than they allow, or pay off the whole loan too This adjustable-rate mortgage calculator helps you to approximate your possible adjustable mortgage payments. The number of payment periods between potential	However, each row on an amortization represents a payment so if a loan is due bi-weekly or quarterly, the period will be the same. This column helps a borrower By offering personalized loan amounts, adjustable repayment terms, grace periods, penalty waivers, and interest rate customization, lenders can build trust See how a change of interest rates would affect your mortgage payments	A flexible mortgage is a mortgage type that allows the borrower to overpay, underpay, or take a payment holiday from a mortgage from time to time. The Our calculations assume that you'll pay the same fixed interest rate for the entire repayment period, and that you don't miss any payments, make By offering personalized loan amounts, adjustable repayment terms, grace periods, penalty waivers, and interest rate customization, lenders can build trust

Video

Adjustable repayment periods - Use this free calculator to see what your interest only and amortising UK mortgage payments will be. You can compare loan scenarios and see how future However, each row on an amortization represents a payment so if a loan is due bi-weekly or quarterly, the period will be the same. This column helps a borrower An early repayment charge (ERC) is a penalty your provider may charge if you overpay on your mortgage by more than they allow, or pay off the whole loan too This adjustable-rate mortgage calculator helps you to approximate your possible adjustable mortgage payments. The number of payment periods between potential

If a homeowner is planning on selling their home within a year or two, taking out an ARM on a new property is a smart choice. After their previous home contract is closed, they may be able to pay off the entire ARM before the adjustment index increases.

A fixed-rate mortgage FRM maintains the same interest rate throughout the lifetime of the loan. This protects borrowers from increases in monthly mortgage payments because of rising interest rates and offers a strong sense of financial predictability.

The most common loan terms are 15, 20 or year mortgages, with year being the most popular choice because it usually offers the lowest monthly payment. One advantage of an FRM is that the terms are clear and there is little variation between lenders.

Budgeting and planning for the future is a lot easier with a steady mortgage. You can see that both the principal and interest vary slightly, but the monthly payment is consistent. Although an FRM is the convenient choice for most first-time home buyers, there are disadvantages as well.

When interest rates are high, qualifying for a loan is more difficult because the payments are less affordable. Since fixed-rate mortgages are simpler to understand, they are commonly preferred among first-time home buyers. While you may be gravitating toward a specific mortgage type, you should first consider both options thoroughly; for instance, your current and future personal and financial factors.

While you might like the stability of a fixed-rate mortgage, if you have a career that relocates you every few years, it might end up costing you more in the long run. When it comes to a fixed-rate mortgage vs.

Contact us today to speak with one of our qualified loan officers. Roger is an owner and licensed Loan Officer at Blue Water Mortgage. Although interest rates are on the rise, it's still a great time to purchase a home. Speak with one of our expert loan officers today! Blog Contact Us. View All Blog Posts Financing Your Mortgage First Time Homebuyer More Blog Posts Financing Your Mortgage First Time Homebuyer Home Maintenance Living Local Press Release Rent vs.

Own Second Home Mortgage Selling Your Home. In This Article In This Article. What is an Adjustable-Rate Mortgage? Adjustable-Rate Mortgage Terminology You Should Know. Advantages of an Adjustable-Rate Mortgage.

Disadvantages of an Adjustable-Rate Mortgage. Ideal Adjustable-Rate Mortgage Borrower. What is a Fixed-Rate Mortgage? Advantages of a Fixed-Rate Mortgage. Disadvantages of a Fixed-Rate Mortgage. Ideal Fixed-Rate Mortgage Borrower.

Keep Your Best Interests in Mind. Share Facebook Twitter LinkedIn. What You Should Know About Your ARM Before Borrowing Investing in an ARM can be risky, but it will likely have a high payoff in the long run. For the second and all succeeding periods, add up the previous balance and this period's principal:.

The above formula goes to E9, and then you copy it down the column. Due to the use of relative cell references, the formula adjusts correctly for each row.

That's it! Our monthly loan amortization schedule is done: Tip: Return payments as positive numbers Because a loan is paid out of your bank account, Excel functions return the payment, interest and principal as negative numbers.

By default, these values are highlighted in red and enclosed in parentheses as you can see in the image above. If you prefer to have all the results as positive numbers, put a minus sign before the PMT, IPMT and PPMT functions. For the Balance formulas, use subtraction instead of addition like shown in the screenshot below: Amortization schedule for a variable number of periods In the above example, we built a loan amortization schedule for the predefined number of payment periods.

This quick one-time solution works well for a specific loan or mortgage. If you are looking to create a reusable amortization schedule with a variable number of periods, you will have to take a more comprehensive approach described below.

In the Period column, insert the maximum number of payments you are going to allow for any loan, say, from 1 to You can leverage Excel's AutoFill feature to enter a series of numbers faster.

Use IF statements in amortization formulas Because you now have many excessive period numbers, you have to somehow limit the calculations to the actual number of payments for a particular loan.

This can be done by wrapping each formula into an IF statement. The logical test of the IF statement checks if the period number in the current row is less than or equal to the total number of payments. If the logical test is TRUE, the corresponding function is calculated; if FALSE, an empty string is returned.

Assuming Period 1 is in row 8, enter the following formulas in the corresponding cells, and then copy them across the entire table. As the result, you have a correctly calculated amortization schedule and a bunch of empty rows with the period numbers after the loan is paid off.

Hide extra periods numbers If you can live with a bunch of superfluous period numbers displayed after the last payment, you can consider the work done and skip this step. If you strive for perfection, then hide all unused periods by making a conditional formatting rule that sets the font color to white for any rows after the last payment is made.

In the corresponding box, enter the below formula that checks if the period number in column A is greater than the total number of payments:. Important note! After that, click the Format… button and pick the white font color.

Make a loan summary To view the summary information about your loan at a glance, add a couple more formulas at the top of your amortization schedule.

If you have payments as positive numbers , remove the minus sign from the above formulas. Our loan amortization schedule is completed and good to go!

Download loan amortization schedule for Excel How to make a loan amortization schedule with extra payments in Excel The amortization schedules discussed in the previous examples are easy to create and follow hopefully :.

However, they leave out a useful feature that many loan payers are interested in - additional payments to pay off a loan faster. In this example, we will look at how to create a loan amortization schedule with extra payments.

As usual, begin with setting up the input cells. In this case, let's name these cells like written below to make our formulas easier to read:. Apart from the input cells, one more predefined cell is required for our further calculations - the scheduled payment amount , i.

the amount to be paid on a loan if no extra payments are made. This amount is calculated with the following formula:. Please pay attention that we put a minus sign before the PMT function to have the result as a positive number. To prevent errors in case some of the input cells are empty, we enclose the PMT formula within the IFERROR function.

Enter this formula in some cell G2 in our case and name that cell ScheduledPayment. Set up the amortization table Create a loan amortization table with the headers shown in the screenshot below. In the Period column enter a series of numbers beginning with zero you can hide the Period 0 row later if needed.

If you aim to create a reusable amortization schedule, enter the maximum possible number of payment periods 0 to in this example. For Period 0 row 9 in our case , pull the Balance value, which is equal to the original loan amount.

All other cells in this row will remain empty:. Build formulas for amortization schedule with extra payments This is a key part of our work.

Because Excel's built-in functions do not provide for additional payments, we will have to do all the math on our own. In this example, Period 0 is in row 9 and Period 1 is in row If your amortization table begins in a different row, please be sure to adjust the cell references accordingly.

Enter the following formulas in row 10 Period 1 , and then copy them down for all of the remaining periods. If the ScheduledPayment amount named cell G2 is less than or equal to the remaining balance G9 , use the scheduled payment.

Otherwise, add the remaining balance and the interest for the previous month. As an extra precaution, we wrap this and all subsequent formulas in the IFERROR function.

This will prevent a bunch of various errors if some of the input cells are empty or contain invalid values. Use an IF formula with the following logic:.

If the ExtraPayment amount named cell C6 is less than the difference between the remaining balance and this period's principal G9-E10 , return ExtraPayment ; otherwise use the difference.

If you have variable additional payments , just type the individual amounts directly in the Extra Payment column.

If the schedule payment for a given period is greater than zero, return a smaller of the two values: scheduled payment minus interest BF10 or the remaining balance G9 ; otherwise return zero. Please note that the principal only includes the part of the scheduled payment not the extra payment!

that goes toward the loan principal. If the schedule payment for a given period is greater than zero, divide the annual interest rate named cell C2 by the number of payments per year named cell C4 and multiply the result by the balance remaining after the previous period; otherwise, return 0.

If the remaining balance G9 is greater than zero, subtract the principal portion of the payment E10 and the extra payment C10 from the balance remaining after the previous period G9 ; otherwise return 0. Because some of the formulas cross reference each other not circular reference! So, please do not start troubleshooting until you enter the very last formula in your amortization table.

If all done correctly, your loan amortization schedule at this point should look something like this: 5. Hide extra periods Set up a conditional formatting rule to hide the values in unused periods as explained in this tip. The difference is that this time we apply the white font color to the rows in which Total Payment column D and Balance column G are equal to zero or empty:.

Voilà, all rows with zero values are hidden from view: 6. Make a loan summary As a finishing touch of perfection, you can output the most important information about a loan by using these formulas:.

Count cells in the Total Payment column that are greater than zero, beginning with Period Optionally, hide the Period 0 row, and your loan amortization schedule with additional payments is done!

The screenshot below shows the final result: Download loan amortization schedule with extra payments Amortization schedule Excel template To make a top-notch loan amortization schedule in no time, make use of Excel's inbuilt templates. That's how you create a loan or mortgage amortization schedule in Excel.

I thank you for reading and hope to see you on our blog next week! Amortization Schedule examples. xlsx file You may also be interested in How to calculate compound interest in Excel How to find CAGR compound annual growth rate in Excel Calculating percentage in Excel with formula examples Using NPER function in Excel How to calculate present value of annuity in Excel FV function in Excel to calculate future value Table of contents.

Or maybe you have it correct, but it is just very hard to see because of the impossibly short amortization period?

I will try this with a year am and it will be clearer to see. Just one thing that may help some people who are saying there that they cannot get this to work.

I think it is the backwards phrasing. Go to cell B4. I have found this backward phrasing so frustrating that I am going to exit this page and write my own formulas.

I'm confused. The point of amortization is for the lender to front load his receipt of interest. To do this, the bulk of the early payments to go interest, and a tiny part goes toward principal paydown. Doesn't your chart show the opposite? With the bulk of the early payments going toward principal, which is wrong in USA lending.