How to Prepare Amortization Schedule in Excel
Launch Microsoft Excel and open a new spreadsheet., Create labels in cells A1 down through A4 as follows: Loan Amount, Interest Rate, Months and Payments. , Include the information pertaining to your loan in the cells B1 down through B3. , Enter...
Step-by-Step Guide
-
Step 1: Launch Microsoft Excel and open a new spreadsheet.
The dollar signs in the formula are absolute references to make sure the formula will always look to those specific cells, even if it is copied elsewhere into the worksheet.
The loan interest rate must be divided by 12, since it is an annual rate that is calculated monthly.
For example, if your loan is for $150,000 at 6 percent interest for 30 years (360 months), your loan payment will calculate out to $899.33. ,, Enter the month and year of the first loan payment in cell A8.
You may need to format the column to show the month and year correctly.
Select the cell, click and drag down to fill the column to cell A367.
Make sure the Auto Fill Option is set to "Fill Months."
Key the beginning balance of your loan into cell B8.
In cell C8, type "=$B$4" and press "Enter." In cell E8, create a formula to calculate the loan interest amount on the beginning balance for that period.
The formula will look like "=ROUND($B8*($B$2/12), 2)".
The single dollar sign creates a relative reference.
The formula will look for the appropriate cell in the B column.
In cell D8, subtract the loan interest amount in cell E8 from the total payment in C8.
Use relative references so this cell will copy correctly.
The formula will look like " =$C8-$E8." In cell H8, create a formula to subtract the principal portion of the payment from the beginning balance for that period.
The formula will look like " =$B8-$D8."
Cell B9 should include a relative reference to the ending balance of the prior period.
Type "=$H8" in the cell and press the Enter key.
Copy cells C8, D8 and E8 and paste them into C9, D9 and E9.
Copy cell H8 and paste it into H9.
This is where the relative reference becomes helpful.
In cell F9, create a formula to tabulate cumulative principal paid.
The formula will look like this: " =$D9+$F8." Do the same for the cumulative interest cell in G9 which will look like this: " =$E9+$G8."
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.
Make sure the Auto Fill Option is set to "Copy Cells" and that the final ending balance is $0.00. -
Step 2: Create labels in cells A1 down through A4 as follows: Loan Amount
-
Step 3: Interest Rate
-
Step 4: Months and Payments.
-
Step 5: Include the information pertaining to your loan in the cells B1 down through B3.
-
Step 6: Enter your loan interest rate as a percentage.
-
Step 7: Calculate your payment in cell B4 by typing " =ROUND(PMT($B$2/12
-
Step 8: 2)" into the formula bar without the quotation marks and pressing the Enter key.
-
Step 9: Label the columns from cell A7 across through H7 as follows: Period
-
Step 10: Beginning Balance
-
Step 11: Payment
-
Step 12: Principal
-
Step 13: Interest
-
Step 14: Cumulative Principal
-
Step 15: Cumulative Interest and Ending Balance.
-
Step 16: Populate the Period column.
-
Step 17: Complete the other entries in cells B8 through H8.
-
Step 18: Continue the schedule by creating the following entries in cells B9 through H9.
-
Step 19: Complete the amortization schedule.
Detailed Guide
The dollar signs in the formula are absolute references to make sure the formula will always look to those specific cells, even if it is copied elsewhere into the worksheet.
The loan interest rate must be divided by 12, since it is an annual rate that is calculated monthly.
For example, if your loan is for $150,000 at 6 percent interest for 30 years (360 months), your loan payment will calculate out to $899.33. ,, Enter the month and year of the first loan payment in cell A8.
You may need to format the column to show the month and year correctly.
Select the cell, click and drag down to fill the column to cell A367.
Make sure the Auto Fill Option is set to "Fill Months."
Key the beginning balance of your loan into cell B8.
In cell C8, type "=$B$4" and press "Enter." In cell E8, create a formula to calculate the loan interest amount on the beginning balance for that period.
The formula will look like "=ROUND($B8*($B$2/12), 2)".
The single dollar sign creates a relative reference.
The formula will look for the appropriate cell in the B column.
In cell D8, subtract the loan interest amount in cell E8 from the total payment in C8.
Use relative references so this cell will copy correctly.
The formula will look like " =$C8-$E8." In cell H8, create a formula to subtract the principal portion of the payment from the beginning balance for that period.
The formula will look like " =$B8-$D8."
Cell B9 should include a relative reference to the ending balance of the prior period.
Type "=$H8" in the cell and press the Enter key.
Copy cells C8, D8 and E8 and paste them into C9, D9 and E9.
Copy cell H8 and paste it into H9.
This is where the relative reference becomes helpful.
In cell F9, create a formula to tabulate cumulative principal paid.
The formula will look like this: " =$D9+$F8." Do the same for the cumulative interest cell in G9 which will look like this: " =$E9+$G8."
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.
Make sure the Auto Fill Option is set to "Copy Cells" and that the final ending balance is $0.00.
About the Author
Elizabeth Hernandez
Experienced content creator specializing in organization guides and tutorials.
Rate This Guide
How helpful was this guide? Click to rate: