Loan Amortization Schedule

Article byWallstreetmojo Team
Edited byAshish Kumar Srivastav
Reviewed byDheeraj Vaidya, CFA, FRM

What Is A Loan Amortization Schedule?

Loan amortization schedule refers to the schedule of repayment of the loan in terms of periodic payments or installments that comprise of principal amount and interest component until the end of the loan term or up to which full amount of loan is paid off.

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Loan Amortization Schedule (wallstreetmojo.com)

A loan amortization schedule is a complete summary of what one is supposed to pay to the lending party, including the principal amount and interest over time, as well as letting know users of the outstanding amount at the same time.

Loan Amortization Schedule Explained

A loan amortization schedule provides a list of payments to be made towards a mortgage or loan from time to time. It is a clear picture of how much a borrower has to pay at the end of every interval along with what is still left to be paid after the current repayment is made. There are interest-only loan amortization schedules as well that emphasize on the interest only mortgage repayments over time. These schedules help borrowers plan their installment payments properly, thereby helping them to not default on any scheduled repayments.

Loan-amortization-schedule

You are free to use this image on your website, templates, etc, Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Loan Amortization Schedule (wallstreetmojo.com)

Let us take an auto loan or home loan two of its examples. In case of an auto loan, or a home loan, the lender pays off the amount in several installments, which are divided into small amounts to be paid over a specific, much longer period of time by creating a loan amortization schedule.

–>> If you want to learn Financial Modeling & Valuation professionally , then do check this ​Financial Modeling & Valuation Course Bundle​ (25+ hours of video tutorials with step by step McDonald’s Financial Model). Unlock the art of financial modeling and valuation with a comprehensive course covering McDonald’s forecast methodologies, advanced valuation techniques, and financial statements.

How To Calculate?

Per the formula, the loan amortization figures are calculated and based on the same the schedule is prepared. Let us have a quick look at the steps to calculate the amortization:

  • Multiply the principal balance with the interest rate applicable.
  • Calculate the monthly interest amount by dividing the resultant by 12.
  • Find the difference between the interest fee from the total monthly payment.
  • This difference becomes the amount one pays towards the principal for that month.

The calculation is repeated for every month until the repayment is completely done. These are the steps on which the loan amortization schedule calculator works. Based on this calculation and figures obtained for each month, the loan amortization schedule is created, normally in Excel.

How To Create in Excel? (Step by Step)

Let us take a home loan example for preparing a schedule of Loan Amortization in Excel. Let us assume that a home loan is issued at the beginning of month 1.  The principal is $1,500,000, the interest rate is 1% per month, and the term is 60 months.

Repayments are to be made at the end of each month. The loan must be fully repaid by the end of the term.

You can download this Loan Amortization Schedule in Excel here – Loan Amortization Schedule in Excel

Follow the steps to calculate loan amortization schedule.

  1. Put the inputs in this standard format given below.

    The first step is to input the data in a standard format.
    Loan Amortization in Excel - Step 1

  2. Find the Monthly Payment or the EMI (Equal Monthly installments)

    We use the PMT function given in Excel to easily calculate the monthly installments here.
    Loan Amortization in Excel - Step 2 - PMT Function
    Here,
    – rate = interest rate (in this example, it is the monthly interest rate of 1%)
    – nper = period (in our example, this is 60)
    – PV = is the loan amount of $1.5 million
    – FV = is the future value of this loan amount ( in our case, we need to fully repay the amount. Therefore FV = 0)
    – type = 0 or 1; 0 is payment done at the end of the period (month),
    and 1 is for payments at the beginning of the period (month).
    Here we assume that the payment is made at the end of the month.
    Loan Amortization in Excel - Step 2a - PMT Function
    Note the negative sign in front of the PMT formula. It is because the repayments are cash outflows for us.

  3. Prepare the Loan Amortization Schedule table as given below. The third step is to prepare the table as given below. Each payment of installments throughout the payback period for the loan amortization comprises of two things, which are principal and interest. The period of time specified as a payback period and the payment per installment is calculated on the basis of the principal and interest amount.

    Loan Amortization in Excel - Step 3a

  4. Calculate the Interest on the Beginning Balance.

    Loan Amortization in Excel - Step 4a

  5. Calculate the Balance before the Monthly Payments

    Loan Amortization in Excel - Step 5

  6. Calculate the Principal repaid in the month.

    Loan Amortization in Excel - Step 6

  7. Find the month-end loan balance.

    Loan Amortization in Excel - Step 7a

  8. Link the Beginning balance of next month.

    Loan Amortization in Excel - Step 8

  9. Complete the Loan Amortization Schedule Excel Table

    For the initial few installments, a greater portion of the installments comprises of the interesting part that is to be paid while the comprises the remaining smaller portion of the installment. With this, your loan amortization in excel is almost complete. The standard loan amortization schedule format or template looks like:
    Loan Amortization in Excel - Step 9

  10. Check the balance at the end of the period.

    As we reach the end of the, the portion of the interesting part becomes smaller and smaller, while that of the principal part becomes larger and larger
    Loan Amortization in Excel - Step 10
    Since the loan in this example is fully amortized, the payment done at the end of the 60th month completes the payment of the entire loan amount. This completes your table of loan amortization in excel.

Example

Let us consider the following instance to understand how the loan amortization schedule is created and followed:

Suppose Mary decides to buy a house worth $500,000 for which she pays 25% of it as the downpayment. The remaining amount after paying the downpayment portion is $375,000. She obtains a mortgage loan for this remaining amount at a rate of interest of 3%. Using the calculator, the monthly payment including both principal and interest comes to $2,590.

An amortization schedule is to be prepared for the same. However, before that the calculation is to be made for the same too. Let us see how the calculation is undertaken:

Step 1

The principal balance is multiplied by the interest applicable.

= $375,000*3%

= $11,250

The current interest rate for a year = $11,250

Step 2

The annual interest amount is divided by 12.

= 11,250/12

= $937.50

This amount becomes the monthly interest fee to be paid by Mary.

Step 3

Subtract the monthly interest fee from the total monthly payment.

= 2,590 – 937.50

= $1,652.50

This $2590 is split into $1,652 (principal) and $937.50 (interest amount) for the first month.

Based on this calculation and information available, the excel loan amortization schedule template is prepared.

Advantages

The practice of amortizationAmortizationAmortization of Intangible Assets refers to the method by which the cost of the company's various intangible assets (such as trademarks, goodwill, and patents) is expensed over a specific time period. This time frame is typically the expected life of the asset.read more has a lot of advantages and can benefit a business organization in many ways.

Let us have a look at some of them below:

  • The method of splitting up a mortgage or debt possessed by the firm can help the firm to repay it having a less stressful time.
  • The loan amortization schedule also helps the borrower to have a good repayment model helping him to pay back the loan without affecting other operations of the firm.

As the repayment is made in terms, there is no need to spend a lot of capital at once

Disadvantages

An amortized loanAmortized LoanThe amortized loan formula is used to calculate the annual or monthly payments a borrower must make to the lender for the loan they have taken out. Annual interest payments plus the annual portion of the long-term debt make up the yearly payment.read more is always good and can give the borrower or the firm all the benefit

However, it does have flaws, which are mentioned below:

This has been a guide to what is a Loan Amortization Schedule. Here, we explain the concept along with how to calculate it, how to create it, and example. To learn more, you may refer to the following recommended articles –

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *