Saturday, July 13, 2024

Loan Amortization Schedule With Extra Payments

Don't Miss

Interest Rate Compound Period And Payment Period

How to make a Loan Amortization Table with Extra Payments in Excel

Usually, the interest rate that you enter into an amortization calculator is the nominal annual rate. However, when creating an amortization schedule, it is the interest rate per period that you use in the calculations, labeled rate per period in the above spreadsheet.

Basic amortization calculators usually assume that the payment frequency matches the compounding period. In that case, the rate per period is simply the nominal annual interest rate divided by the number of periods per year. When the compound period and payment period are different , a more general formula is needed .

Some loans in the UK use an annual interest accrual period where a monthly payment is calculated by dividing the annual payment by 12. The interest portion of the payment is recalculated only at the start of each year. The way to simulate this using our Amortization Schedule is by setting both the compound period and the payment frequency to annual.

Understanding An Amortization Schedule

If you are taking out a mortgage or auto loan, your lender should provide you with a copy of your loan amortization schedule so you can see at a glance what the loan will cost and how the principal and interest will be broken down over its life.

In a loan amortization schedule, the percentage of each payment that goes toward interest diminishes a bit with each payment and the percentage that goes toward principal increases. Take, for example, a loan amortization schedule for a $165,000, 30-year fixed-rate mortgage with a 4.5% interest rate:

Amortization schedules can be customized based on your loan and your personal circumstances. With more sophisticated amortization calculators, like the templates you can find in Excel you can compare how making accelerated payments can accelerate your amortization. If for example, you are expecting an inheritance, or you get a set yearly bonus, you can use these tools to compare how applying that windfall to your debt can affect your loan’s maturity date and your interest cost over the life of the loan.

In addition to mortgages, car loans and personal loans are also amortizing for a term set in advance, at a fixed interest rate with a set monthly payment. The terms vary depending on the asset. Most conventional home loans are 15- or 30-year terms. Car owners often get an auto loan that will be repaid over five years or less. For personal loans, three years is a common term.

Exercising Additional Payment Options

When you sign on for a 30-year mortgage, you know you’re in it for the long haul. You might not even think about trying to pay off your mortgage early. After all, what’s the point? Unless you’re doubling up on your payments every month, you aren’t going to make a significant impact on your bottom line right? You’ll still be paying off your loan for decades right?

Not necessarily. Even making small extra payments over time can shave years off your loan and save you thousands of dollars in interest, depending on the terms of your loan.

Don’t Miss: Which Is The Best Student Loan Servicer

How Much Can You Save With Extra Payments

The amount of money that you can save with extra payments depends on a few variables, the interest rate, term, loan balance, number of extra payments, and the size of the extra payments.Let’s take a look at the following example.Mortgage Amount: $300,000Loan Terms: 30 yearInterest Rate: 5% FixedThe monthly payment for the above mortgage is $1,610.46, and it would take 30 years to pay off. The total costs of interest payment are $279,767.35, which means the borrower would pay a total of $579,767.35 in 30 years.Payoff: 30 YearsInterest Payment: $279,767.35Total Payment: $579,767.35Now, let’s see how much one can save if he makes $300 extra payments on his mortgage each month.Extra Payments: $300By making $300 extra each month on top of the regular $1,610.46, the borrower is essentially paying $1,910.46 monthly. With this extra payment, the borrower is able to pay off his mortgage in 21 years, with interest payments of only $188,026.59, which means he saves $91,740.76 in interest payments.Payoff: 21 YearsInterest Payment: $188,026.59Total Payment: $488,026.59Total Savings: $91,740.76As we can see from the above example, if the borrower makes a $300 or 18% extra payment of the original monthly payment, he reduces his term by 9 years and saves about 33% in interest payments.

Loan Calculator With Amortization Schedule

10 Free Amortization Schedule Excel Template

Enough with the foreplay already, wheres the amortization schedule calculator for mortgages?

Start fiddling and diddling until youre satisfied:

Note: This mortgage calculator shows the monthly principal and interest payment, but doesnt include mortgage insurance, homeowners insurance , property taxes, homeowners association fees, or other costs of owning a property.

As you can see, higher interest rates make a huge difference in how quickly you pay down your loan. The higher the interest rate, the longer you have to wait before seeing real progress in paying off your loan.

Unless, of course, you make extra mortgage payments.

Don’t Miss: How Long Does Car Loan Pre Approval Take

Considerations For Extra Payments

Pay Off Higher Interest Debts First

Paying off your mortgage early isn’t always a no-brainer. Though it can help many people save thousands of dollars, it’s not always the best way for most people to improve their finances.

Compare your potential savings to your other debts. For example, if you have , it makes more sense to pay it off before putting any extra money toward your mortgage that has only a 5 percent interest rate.

Further, unlike many other debts, mortgage debt can be deducted from income taxes for those who itemize their taxes.

Also consider what other investments you can make with the money that might give you a higher return. If you can make significantly more with an investment and have an emergency savings fund set aside, you can make a bigger financial impact investing than paying off your mortgage. It is worth noting volatilility is the price of admission for higher earning asset classes like equities & profits on equites can be taxed with either short-term or long-term capital gains taxes, so the hurdle rate for investments would be the interest rate on your mortgage plus the rate the investments are taxed at.

How Quickly Can I Pay Off My Mortgage By Making Additional Repayments

This will depend on how large your extra repayments are and how frequently you are making them. Lets look at another quick example to demonstrate how quickly your mortgage can be paid off with additional repayments. Lets say you have a $400,000 home loan with a 3% interest rate over a 30-year loan term. Without making extra or lump sum repayments, youd need to make $1,686.42 monthly repayments. By adding $300 extra monthly repayments from month one, and contributing a $20,000 lump sum amount after two years, you would slice eight years and two months off the life of your loan.

Also Check: What Is The Current Interest Rate For Student Loan Consolidation

Extra Loan Repayments Calculator

Making repayments above your minimums could make a big difference to how quickly you could pay off your home loan. Use our extra repayments calculator to see what this could look like for you.

Important Information

The comparison rate is based on a loan amount of $150,000 over a loan term of 25 years. WARNING: This comparison rate is true only for the example given and may not include all fees and charges. Different terms, fees or other loan amounts might result in a different comparison rate.

Shorter Loan Terms Less Total Interest

Create an Amortization Schedule for a Car Loan and Make Extra Payments

The shorter your loan term, the less total interest youll pay. Period.

Longer loans let your lender stuff more interest into the first half of the amortization schedule.

Use the amortization schedule calculator to run the numbers on just how much extra interest youll pay if you choose a 15-year mortgage versus a 30-year mortgage, for example. You probably wont like what you see.

Sure, shorter loan terms mean higher monthly mortgage payments and lower real estate cash flow. But if you run the numbers with a rental cash flow calculator and find you can still end up in the black each month, it can save you a ton on interest in the long term.

What short-term fix-and-flip loan options are available nowadays?

How about long-term rental property loans?

We compare several buy-and-rehab lenders and several long-term landlord loans on LTV, interest rates, closing costs, income requirements and more.

Don’t Miss: What Is The Maximum Conforming Loan Amount In California

Invoice Factoring And Invoice Financing

Unlike most loan products, the cost of invoice financing and accounts receivable factoring are calculated based on a set factor fee, rather than an interest rate. Invoice financing is typically charged at a fee of around 0.5%-1% of the advanced amount per week. With invoice factoring, the typical fee is 1% per month.

In either case, the principal does not amortize, and there are no set payments to be made. Rather, the lenderâs fee is deducted from the amount issued to the borrower.

Without amortization, compounding interest, or set payments, thereâs no need to use a schedule calculator in this case.

How Is An Amortization Schedule Calculated

When a borrower applies for a mortgage or loan to finance the purchase of his dream home. He needs to pay the bank back the loan amount plus interest over a number of years. For home mortgages, the 15-year and 30-year are common terms which means the loan will be paid off in 15 or 30 years.On a fixed rate mortgage, the borrower pays the same amount each month. The monthly payment is made up of two parts, the principal and interest. At the beginning of a mortgage term, most of the payment goes for interest and little is going towards paying down the principal.Each month the principal and interest payment is recalculated. The interest payment is calculated based on the remaining balance of the loan, the lower the balance, the less interest payment the borrower has to pay for that month.That means as time passes, the monthly payments will shift more towards the principal than the interest. Eventually, the mortgage is paid off in full when the balance reaches $0.To learn more about amortization schedules and how to create one, visit the amortization schedule calculator.

Also Check: How To Get Out Of Car Loan Early

Calculate Balance Of Loan Amortization

After completing the payment per month, the interest payment per month, and the principal payment per month, we are going to calculate the balance of the loan by using those values. Lets walk through the steps to calculate the balance of the loan.

  • First of all, you have to enter the original balance in cell H11.
  • First of all, to calculate the balance of the loan, we will use the following formula in the cell H12:


  • Then, press Enter.
  • As a result, you will get the balance for the first month as shown below.
  • Next, drag the Fill Handle icon to fill the rest of the cells in the column with the formula.
  • Therefore, you will get the balance of the loan for twelve months of the loan as shown below. After the 12th month, you will be able to pay the loan which has been given in the below screenshot. This is how you will be able to create a car loan amortization schedule with extra payments in Excel.

When Not To Use A Loan Amortization Schedule

Loan Amortization Schedule Excel with Extra Payment option

While the debt amortization schedule is a very useful tool for many loans, there are some loan products that donât amortize in a standard wayâmeaning the interest doesnât compound on a regular schedule, and borrowers donât make equal payments on a set schedule. Here are the three main instances where you likely wonât want to use one.

Recommended Reading: Itt Tech Loan Forgiveness 2021 Application

Help With Amortization And Extra Payments

The accelerated payment calculator will calculate the effect of making extra principal payments. A minimal extra principal payment made along with a regular payment can save the borrower a large amount of interest over a loan’s life, particularly if those payments start when the debt is relatively new.

For example, assume that you have taken out a loan for $260,386 for 360 monthly periods with an annual interest rate of 4.25%. If with the six months after the start date, you pay an extra $200, you will save over $50,000 in interest payments, and the schedule shows us that you’ll have paid off the loan in 272 payments instead of the original 360 payments.

It is straightforward to calculate many different scenarios quickly. Note that the higher the interest rate, the greater the savings for any extra payment amount. Also, for a standard amortizing loan, the interest savings will be more significant the sooner the additional payments start. That is, you will save a lot more in interest if you pay an extra $50 a month for the last 20 years than if you pay an extra $100 a month for the previous ten years.

As with many of our other calculators, this calculator will also solve for an unknown input. For example, if you want the calculator to calculate the regular monthly payment, enter ‘0’ for the “Periodic Payment Amount” and a non-zero value for “Loan Amount/Current Balance,””Number of Payments,” and “Annual Interest Rate.”

Formulas Used In Amortization Schedules

Borrowers and lenders use amortization schedules for installment loans that have payoff dates that are known at the time the loan is taken out, such as a mortgage or a car loan. There are specific formulas that are used to develop a loan amortization schedule. These formulas may be built into the software you are using, or you may need to set up your amortization schedule from scratch.

If you know the term of a loan and the total periodic payment amount, there is an easy way to calculate a loan amortization schedule without resorting to the use of an online amortization schedule or calculator. The formula to calculate the monthly principal due on an amortized loan is as follows:

Principal Payment =Total Monthly Payment

To illustrate, imagine a loan has a 30-year term, a 4.5% interest rate, and a monthly payment of $1,266.71. Starting in month one, multiply the loan balance by the periodic interest rate. The periodic interest rate is one-twelfth of 4.5% , so the resulting equation is $250,000 x 0.00375 = $937.50. The result is the first month’s interest payment. Subtract that amount from the periodic payment to calculate the portion of the loan payment allocated to the principal of the loan’s balance .

Recommended Reading: What Does Fixed Rate Loan Mean

Calculate Total Payment Amount

The payment amount is calculated with the PMT function.

To handle different payment frequencies correctly , you should be consistent with the values supplied for the rate and nper arguments:

  • Rate – divide the annual interest rate by the number of payment periods per year .
  • Nper – multiply the number of years by the number of payment periods per year .
  • For the pv argument, enter the loan amount .
  • The fv and type arguments can be omitted since their default values work just fine for us .

Putting the above arguments together, we get this formula:


Please pay attention, that we use absolute cell references because this formula should copy to the below cells without any changes.

Enter the PMT formula in B8, drag it down the column, and you will see a constant payment amount for all the periods:

How Extra Payments Change Your Amortization Schedule

Add Extra Payment Information to a Car Loan Amortization Schedule

Making additional payments can help you escape the early high-interest phase of your amortization table much faster.

For example, if you borrow a $200,000 loan at 7% interest for 30 years, and you pay just $100 extra each month, you can knock out the loan in around 24 years instead of 30. Instead of paying $279,016 in interest, you pay $215,709 a savings of $63,307.

Play around with the amortization calculator with extra payments to get a sense for how quickly you can pay down your loan. For some creative ideas, read up on ways to pay down your mortgage faster .

Just make sure you dont pay down your loan so fast that the lender hits you with a prepayment penalty! Check your mortgage terms to see if it comes with one.

Read Also: Is The Loan Forgiveness Program Worth It

You Become A Borrower Understands The True Cost Of A Loan

Let’s say you’re on a fairly tight budget, but your dream car costs $60,000. You realize that it’s more than you should spend, but if you stretch the loan term out to 10 years, the monthly payment looks as though it will be manageable. Yes, you convince yourself, you can afford to pay an of 6% and a monthly payment of $667.

And that’s where an amortization schedule comes in .

An amortization schedule is like that friend who tells you that you have salad stuck between your two front teeth or that your fly is down. It is brutally honest.

An amortization schedule shows more than how much of your monthly payment will go towards paying down your principal. If you scroll down to the bottom of the page, you’ll also see exactly how much you’ll pay in interest over the life of the loan. In the case of your dream $60,000 vehicle, it’s just shy of a whopping $20,000.

Diy Extra Payment To Prepay Mortgage

Lets say you want to budget an extra amount each month to prepay your principal. One tactic is to make one extra mortgage principal and interest payment per year. You could simply make a double payment during the month of your choosing or add one-twelfth of a principal and interest payment to each months payment. A year later, you will have made 13 payments.

Make sure you earmark any additional principal payments to go specifically toward your mortgage principal. Lenders typically have this option online or have a process for earmarking checks for principal payments only. Ask your lender for instructions. If you dont specify that the extra payments should go toward the mortgage principal, the extra money will go toward your next monthly mortgage payment, which wont help you achieve your goal of prepaying your mortgage.

Once you have built sufficient equity in your home , ask your lender to remove private mortgage insurance, or PMI. Paying down your mortgage principal at a faster rate helps eliminate PMI payments more quickly, which also saves you money in the long run. You can also refinance your mortgage to eliminate PMI altogether.

Also Check: Do You Have To Repay Ppp Loan

More articles

Popular Articles