Financial Modeling Tutorials
- Financial Modeling Basics
- What is Financial Modeling?
- Financial Modeling in Excel
- Types of Financial Models
- Financial Modeling Interview Questions
- Alibaba IPO Financial and Valuation Model
- Box IPO Modeling Details
- Box IPO Valuation Model
- Download Alibaba IPO Financial Model
- Financial Modeling Books
- Financial Modeling Templates
- Financial Modeling Course
- Excel Modeling
- Financial Functions in Excel
- Sensitivity Analysis in Excel
- Time Value of Money
- Future Value Formula
- Present Value Factor
- Perpetuity Formula
- Annuity vs Perpetuity
- Internal Rate of Return (IRR)
- NPV vs XNPV
- NPV vs IRR
- NPV Formula
- IRR vs ROI
- Break Even Point
- Payback Period & Discounted Payback Period
- Payback period Formula
- Discounted Payback Period Formula
- Profitability Index
- Cash Burn Rate
- Simple Interest
- Effective Interest Rate
- Loan Amortization Schedule
- Rule of 72
- Geometric Mean Return
- Real Rate of Return Formula
- Continuous compounding Formula
- Weighted average Formula
- Holding Period Return Formula
- Cost Benefit Analysis
- Mortgage APR vs Interest Rate
What is Loan Amortization Schedule?
Loan Amortization refers to the paying back off a loan or debt amount in terms of instalments spread over a particular period of time. We can get a clear understanding on 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 instalments, 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 instalments that are paid out will go as the interest to be paid. Later, eventually, the instalment amount paid will start to compensate 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.
Schedule of Loan Amortization in Excel – Step by Step
Let us take 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.
Download this – Loan Amortization Schedule in Excel
Step 1 – Put the inputs in this standard format given below
The first step to creating loan amortization in excel is to input the data in a standard format.
Step 2 – Find the Monthly Payment or the EMI (Equal Monthly instalments)
We use the PMT function given in Excel to easily calculate the monthly instalments here.
- rate = interest rate (in this example, it is 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 done at the end of the month.
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 in loan amortization in excel is to prepare the table as given below. Each payment of instalments 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 instalment is calculated on the basis of the principal and interest amount.
Step 4 – Calculate the Interest on the Beginning Balance.
Step 5 – Calculate the Balance before the Monthly Payments
Step 6 – Calculate the Principal repaid in the month
Step 7 – Find the month end loan balance
Step 8 – Link the Beginning balance of next month
Step 9 – Complete the Loan Amortization Schedule Excel Table
For the initial few instalments, a greater portion of the instalments comprises of the interest part that is to be paid while the principal payment comprises the remaining smaller portion of the instalment. With this, your loan amortization in excel is almost complete.
Step 10 – Check the balance at the end of the period
As we reach the end of the payback period, the portion of the interest part becomes smaller and smaller while that of the principal part becomes larger and larger.
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 of Loan Amortization Schedule
The practice of amortization has a lot of advantages and can benefit a business organisation 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 done in terms, there is no need to spend a lot of capital at once
A fully 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.
This is a guide to Loan Amortization Schedule along with step by step approach for preparing loan amortization in excel. Also, download free excel template. To learn more, you may refer to the following recommended articles –