Free Loan Amortization Schedule Templates [Excel]

Loan amortization schedule Excel with variable interest rate

A loan is the amount of money borrowed from someone with defined terms and conditions. So, it means the money has to be returned within the defined period. There are many types of loan repayment. However, if a loan is taken with scheduled and periodic payments. It is commonly known as an amortized loan. Most commonly this type of loan is taken for a vehicle loan, personal loan, study loan, and small project loan.

Another common example of an amortization loan for employees is the P.F loan. Most public sector companies deduct the fixed percentage of the amount from employee’s salaries and collect it in the name of employee provident fund. This provident fund is released on the retirement or final settlement of the employee. So, an employee may ask to take a loan from its collection of provident amount. However, it has to been paid back. Most companies have 2 – 3 year scheduled payment plans. Thus, it’s also a type of amortized loan that every employee has to deal with. You may also like the Payment Agreement Template.

The payment of the loan is distributed over the defined period of time. Thus, an loan amortization schedule is a share with the debtor. It allows the debtor to understand repayment terms. However, the debtor can request to take a grace period of few months in which no payment will be made.

What is a loan amortization schedule?

A loan amortization schedule is a schedule that has a complete table of periodic loan payments. It shows the amount of principal and amount of interest that contains each payment till the loan is paid off at the end of its term. Every periodic payment is a similar amount in total for every period. Moreover, if the schedule is early then the majority of each payment is what is owed in interest and if the schedule is later then the majority of each payment covers the loan’s principal.

How many types of amortization are there?

After knowing your full amortization amount then there are four general categories that you can fall into, it depends on your loan.

  1. Full amortization: If you pay the full amortization amount it will result in the outstanding balance of a loan. It reduces to zero at the end of the loan term. It is the most common type of loan available.
  2. Partial amortization: if you pay a portion of your amortization amount then it will reduce your outstanding principal on the loan each month. And, paying a partial amount will lead to an outstanding balance at the end of the loan term.
  3. Interest-only: As the name implies, if you pay interest only then there would be no need to include any amortization payments.
  4. Negative amortization: Negative amortization loans need fewer monthly payments than interest-only loans. However, monthly payments enhance the outstanding principal on the loan.

How do I create a loan amortization schedule in excel?

For creating a loan amortization schedule in Excel you will need to use the following functions;

  • PMT function (find the total amount of a periodic payment)
  • PPMT function (takes the principal part of every payment that leads to the loan principal i.e. the amount you borrowed)
  • IPMT function (find the interesting part of every payment that leads to the interest).

Now, let us discuss step-by-step how to create a loan amortization in excel;

Step#1: Firstly, set up the amortization table. You have to identify the input cells where you will enter the known components of a loan such as C2 (annual interest rate), C3 (loan term in years), C4 (number of payments per year), and C5 (loan amount).

Step#2: The next step is to calculate the total payment amount by using the PMT function.

Step#3: Then, calculate the interesting part of each periodic payment by using the IPMT function.

Step#4: After that, calculate the principal part of each periodic payment by using the PPMT formula.

Step#5: The last step is to get the remaining balance.

How can a loan amortization schedule help the borrowers?

The loan amortization schedule helps the borrowers to know how much interest and principal they have to pay as part of each monthly payment. It also gives an outstanding balance after each payment.

Furthermore, a loan amortization table assists the borrowers to find how much total interest they can save by making additional payments. You should also check the Car Lease Agreement Template.

How does the Loan Amortization Schedule Template Works?

The amortization of loans is sometimes a complex process to understand. So, a well-structured amortization schedule is useful in this regard. Also, agencies that work on providing loans have a printed amortization schedule. It is presented to clients for their understanding.

This loan amortization schedule includes all details about the number of payments, each payment amount, any special payment, and total capital payment.

Further, if someone has a very basic skill over MS Excel. A very useful loan amortization schedule can be created using a template. These templates are readily available on the internet and the users have to just edit the basic information.

There are many types of loan amortization schedule templates depending on payment terms. The most common and simple type of loan amortization schedule template just includes 3 input parameters for the whole calculation.

It is the most useable template as many times the government announces interest-free loans for students and poor people. So, many people who don’t even have a basic education in loan accounting take such loans. Thus, the use of an interest-free loan payment schedule is also common for P.F fund loans. So, the following are the components of an interest-free loan amortization schedule with a grace period.

  1. Capital Amount (Total Loan)
  2. Payment Duration
  3. Payment Frequency
  4. Borrowing Date
  5. Grace Period
  6. Borrowing Date
  7. Payments Calendar
  8. Payment Amount

The template just needs the first five entries from the debtor and the whole schedule is automatically created. Capital amount is the total amount of money the debtor has borrowed. You may also see Bill Pay Checklist & Calendars Template.

The payment duration is the allowed period in which all the money has to be returned e.g. 2 years’ payment plan loan. While the payment frequency specifies the payment cycles as if it’s monthly, bimonthly, quarterly, semi-annually, or annually payments.

The borrowing date is the date when the debtor has borrowed the loan. While the grace period is the time between borrowed date and the first payment date.

You Might Also Like