How to Create an Amortization Schedule in Excel

By Jose Leiva

An amortization schedule is a table that outlines a payment plan for an amortization loan such as a mortgage loan. The payment plan is determined using an amortization calculator. Creating an amortization schedule in Microsoft's Excel spreadsheet program is one way of making sure that you know when, and how much, you have to pay on your amortization loan.

Step 1

Open the Excel spreadsheet program on your computer by clicking the "Start" button on the left side of the tool bar, selecting "All Programs," selecting "Microsoft Office" and clicking on "Excel."

Step 2

Click on the box located on column "A" row "2" (A2) and type in "Start:" and the starting date of the amortization loan for which you are creating this schedule.

Step 3

Click on the box located on A3 and type in the date of the first payment for that loan. Continue in this fashion down all the rows of column A until you've typed in the date of the final payment for your amortization loan. For example, if the loan is to be paid off in 60 months, you'll have 61 different dates on column A, including the start date of the loan.

Step 4

Click on the box located on B1 and type in the word "Amount."

Step 5

Use an amortization calculator to figure out what your payment amount will be every month. Usually you have to type in the amount of the loan, the length of the loan in either years or months, the yearly interest rate and then click "Calculate" to get the monthly amount you'll have to pay to finish paying the loan in the designated time.

Step 6

Click on the box located on B2 and type in the total amount of the loan.

Step 7

Click on the box located on B3 and type in the monthly amount do as was calculated by the amortization calculator. Select the amount, right-click on it and click "Copy."

Step 8

Click on B4 and drag the cursor all the way down column B until you get to the row containing the date on column A and release the mouse. Right-click anywhere on the selected section of column B and select "Paste."

Step 9

Save the file by clicking "File," selecting "Save," typing in the name you want for this file and clicking "Save."