WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 250+ Courses All In One Bundle
  • Login
Home » Financial Modeling Tutorials » Excel Modeling » Loan Amortization Schedule

Loan Amortization Schedule

What is the 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 till the end of the loan term or up to which full amount of loan is paid off.

We can get a clear understanding of this by taking 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.

  • Initially, the first few installments that are paid out will go as the interest to be paid. Later, eventually, the installment amount paid will start to compensate for the principal loan amount.
  • In this way, within the period of payback, both the interest and the principal amounts to be paid against the loan borrowed by the lender are covered.
  • This is the basic idea, and this also applies in the case of a business organization opting for debts to carry out certain of its operation, and this can help the firm run a smooth business with less risk and financial crisis.

Loan amortization schedule

Schedule of Loan Amortization 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

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

The first step is to input the data in a standard format.

Popular Course in this category
Sale
All in One Financial Analyst Bundle (250+ Courses, 40+ Projects)
4.9 (1,067 ratings)
250+ Courses | 40+ Projects | 1000+ Hours | Full Lifetime Access | Certificate of Completion
View Course

Loan Amortization in Excel - Step 1

Step 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.

Step 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

Step 4 – Calculate the Interest on the Beginning Balance.

Loan Amortization in Excel - Step 4a

Step 5 – Calculate the Balance before the Monthly Payments

Loan Amortization in Excel - Step 5

Step 6 – Calculate the Principal repaid in the month.

Step 6

Step 7 – Find the month-end loan balance.

Step 7

 

Step 8 – Link the Beginning balance of next month.

Step 8

Step 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 principal payment comprises the remaining smaller portion of the installment. With this, your loan amortization in excel is almost complete.

Step 9

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

As we reach the end of the payback period, the portion of the interesting part becomes smaller and smaller, while that of the principal part becomes larger and larger.

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.

Advantages

The practice of amortization has a lot of advantages and can benefit a business organization in many ways. 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

Conclusion

An amortized loan is always good and can give the borrower or the firm all the benefits, while a not fully amortized loan can be a burden for the borrower at times. The borrower, in turn, should follow the perfect discipline in paying off the interest due within the specified time. Otherwise, he may have to face the problem of negative amortization, making him pay more than what he actually had to.

Recommended Articles

This is a guide to Loan Amortization Schedule along with step by step approach for preparing loan amortization in excel. Also, download a free excel template. To learn more, you may refer to the following recommended articles –

  • Total Loan Cost Calculator
  • Amortization Schedule for Mortgage
  • Amortization Table
  • Recourse Loan
10 Shares
Share
Tweet
Share
All in One Financial Analyst Bundle (250+ Courses, 40+ Projects)
  • 250+ Courses
  • 40+ Projects
  • 1000+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

* Please provide your correct email id. Login details for this Free course will be emailed to you

Book Your One Instructor : One Learner Free Class
WallStreetMojo

Free Investment Banking Course

IB Excel Templates, Accounting, Valuation, Financial Modeling, Video Tutorials

* Please provide your correct email id. Login details for this Free course will be emailed to you

Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Login

Forgot Password?

WallStreetMojo

Download Loan Amortization Schedule in Excel

New Year Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More