How to Make an Amortization Schedule in Excel

By Shreya Mehta

When making a large purchase, it is wise to analyze the loan you are taking to determine if you are getting a good interest rate or term for the loan. Planning a payment schedule is also essential to ensure you can meet the required payments on the due date of each pay period. Follow the simple steps below to create a loan amortization schedule in Microsoft Excel.

Things You'll Need

  • Computer
  • Microsoft Excel

Step 1

Open a new workbook in Microsoft Excel. Click "New" in the "File" menu.

Step 2

Click on the "Schedules" category under the Microsoft Office Online section. Select the "Loan Amortization Schedule" template and click on "Download Template."

Step 3

Enter data in Cell D5 through Cell D10 for the following labels: loan amount, annual interest rate on the loan, loan period in years, number of payments that will be made per year, the start date of the loan and any extra payments made toward the principal each pay period. If only an occasional payment will be made, enter it in the Extra Payment column for the corresponding payment number.

Step 4

Press "Enter" on your keyboard. A full amortization schedule will be populated. You'll see the following information displayed: payment number, payment date, beginning balance, scheduled payment, any extra payments, total payments, principal, interest, ending balance and accumulated interest. The Loan Summary section will become populated with the total amounts of scheduled payments, number of payments, actual number of payments, early payments and total interest.