Create labels in column A. Create labels for your data in the first column to keep things organized.
Enter the information pertaining to your loan in column B. Fill out cells B1-B3 with information about your loan.
Calculate your payment in cell B4. To do this,click cell B4,and then type the following formula into the formula (fx) bar at the top of the sheet
Create column headers in row 7. You’ll be adding some additional data to the sheet,which requires a second chart area.
Populate the Period column. This column will contain your payment dates. Type the month and year of the first loan payment in cell A8.
Fill out the other entries in cells B8 through H8. The beginning balance of your loan into cell B8. In cell C8,type =$B$4 and press Enter or Return.
Continue the schedule by creating the entries in B9 through H9. Cell B9 should include a relative reference to the ending balance of the prior period.
Highlight cells B9 through H9. When you rest the mouse cursor over the bottom-right part of the highlighted area,the cursor will turn to a crosshair.
Drag the crosshair all the way down to row 367. This populates all the cells through row 367 with the amortization schedule.
How do I create a loan amortization table in Excel?
Open Excel and click on “File” tab on the left hand side. Then click “New” tab on the dropdown. You will see on the right all the templates available. Click on the “Sample Templates”, and you will see the “Loan Amortization Template” there.
How do I make an amortization schedule in Excel?
Complete the amortization schedule. Highlight cells B9 through H9, mouse over the bottom right corner of the selection to receive a crosshair cursor and then click and drag the selection down to row 367. Release the mouse button.
How to calculate amortization table?
Step 1: Calculate Periodic Payment. The first step is to calculate periodic payment.
Step 2: Calculate Starting Balance.
Step 3: Calculate Periodic Interest.
Step 4: Calculate New Balance.
Step 5: Apply Payment and Calculate Ending Balance.
