How to Make Loan Amortization Schedule in Excel

There is an inbuild auto loan amortization schedule in Microsoft Excel accessible for free. You can use it any time by opening the ready to use the template. Amortization Schedule is also known as a loan repayment schedule which shows the details of loan related information.

Loan Amortization Excel 2016

Open Excel Amortization Schedule

After opening the template you will be required to fill up the information to the designated fields. Then the loan amortization excel will display the results

Loan Amortization Schedule in Excel 2016

If you want to learn how to make a loan amortization schedule using Microsoft Excel 2016 then this article is for you.

How to Make Loan Amortization Schedule in Excel 2016

Suppose you will be required 1 Million USD for your garments machinery purchase so you are planning to take a loan from the bank. Debt is the cheapest source of finance you if there is an opportunity of taking a loan then you must take it. But before taking a loan you should do a comparative study of available loan alternatives. So that you can have a better financial decision for your business.

Bank Loan Related Information

The loan related information you have collected from a Bank are:

Term Loan Amount      1,000,000 USD
Loan Period5Year
Interest Rate10%Percent
Payment Terms1Times/Year
Total No. of Payment5Times
Periodic Interest Rate10%Percent

Then you need to calculate the periodic payment of your loan. To calculate this, you can use the formula and put it in your notebook and then calculate the payment.

But if you use excel then you will find an inbuilt function which will make your job easier. The function is the “PMT” function.

PMT function in excel

Calculation of Periodic payment is necessary because it will show you how much money you need to pay for each payment. This is definitely a cash outflow for you or your business. You must have to do a cash flow forecast so that you can check whether there is available money on your hand to do repayment of your loan.

Periodic Payment = $263,797 USD

  • Rate = Periodic Interest Rate
  • NPER = Total Number of Payment
  • Pv = Present Value of the Amount that is loan amount (should input as a negative value to get the positive result)
  • FV = Future Value of Loan
  • Type = Payment Mechanism (Beginning of the period or ending of the period) for beginning period payment the value is “1” (annuity due), and ending of the period payment the value is “0” (ordinary annuity)

Loan Amortization Schedule

PeriodOpening BalanceTotal PaymentInterest PaymentPrincipal PaymentClosing Balance
0                         1,000,000                           –                                       –                                         –                1,000,000
1                            1,000,000               263,797                         100,000                           163,797                 836,203
2                                836,203               263,797                            83,620                           180,177                 656,025
3                                656,025               263,797                            65,603                           198,195                 457,830
4                                457,830               263,797                            45,783                           218,014                 239,816
5                                239,816               263,797                            23,982                           239,816                            (0)

Steps of Making Loan Amortization Schedule in Excel

  • After calculating the periodic payment amount you can put the same value in all fields of payment column.
  • The zero period closing balance should be the loan amount and then the beginning balance of the first year should be the ending balance of the previous year that is zero years.
  • The interest amount can be calculated by multiplying the beginning balance of that period multiplies the periodic interest rate.
  • The principal amount is the difference between the payment amount in that period minus interest amount.
  • You will get the ending balance of a loan by subtracting the periodic principal amount from the beginning value of the unpaid loan amount.
  • After filling the first row you can copy the formulas to the following period then your loan repayment schedule will be there.
  • At the end of the last periodic payment, your ending loan amount will be zero and this confirms that you made the loan amortization schedule successfully.

I made a simple tutorial for your better understanding, you can follow the instruction described in this video. I also added a simple template that will be convenient for your calculation.

How to Make Loan Amortization Schedule in Excel

Leave a Reply

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

Scroll to top