How to Make an Amortization Schedule in Microsoft Excel
Microsoft Excel is an invaluable tool for financial planning, particularly for constructing detailed amortization schedules. Practice the method of creating an amortization schedule, allowing you to track loan repayments, including principal and interest, over the life of a loan.
How to Make an Amortization Schedule in Microsoft Excel
Crafting an amortization schedule in Microsoft Excel enables precise tracking and forecasting of loan payments. This guide outlines the essential steps to develop a custom schedule, helping you visualize your loan’s repayment trajectory over its entire term.
-
Step 1. Set Up Your Spreadsheet
Begin by opening a new Excel workbook. Label the first few rows with the following headings: ‘Loan Amount,’ ‘Interest Rate (annual),’ ‘Loan Term (years),’ ‘Monthly Payment,’ ‘Start Date of Loan,’ and ‘Total Interest Paid.’ These labels will help organize the data needed to create your amortization schedule.
-
Step 2. Enter Loan Details
Under each heading, input the details of your loan. For the monthly payment, you’ll use the PMT function: “=PMT(Interest Rate/12, Loan Term*12, -Loan Amount).” Ensure the interest rate is divided by 12 to convert it to a monthly rate, and the loan term is multiplied by 12 to convert it to months. The loan amount should be entered as a negative number to represent a cash outflow.
-
Step 3. Create the Amortization Table
Below your loan details, create a table with the headings ‘Payment Number,’ ‘Payment Date,’ ‘Payment Amount,’ ‘Interest Paid,’ ‘Principal Paid,’ and ‘Remaining Balance.’ The Payment Number column should start at 1 and increment down the column for the total number of payments (loan term * 12).
-
Step 4. Calculate Payment Details
For the Payment Date column, use the Start Date of the Loan and Excel’s DATE function to calculate each subsequent payment date. For Interest Paid, use the formula “=IPMT(Interest Rate/12, current period, Loan Term*12, -Loan Amount).
” For Principal Paid, use “=PPMT(Interest Rate/12, current period, Loan Term*12, -Loan Amount).”
The Remaining Balance can be calculated by subtracting the Principal Paid from the starting loan amount and accumulating this subtraction for each period down the column.
-
Step 5. Populate the Amortization Schedule
Copy the formulas down through the rows of your amortization table to fill in each period’s payment details until the loan is paid off. The last row should show a Remaining Balance of $0, indicating the loan is fully repaid.
You may also find valuable insights in the following articles offering tips for Microsoft Excel:
FAQs
What is an amortization schedule?
An amortization schedule is a table detailing each periodic payment on a loan, showing the amount of principal and the amount of interest that comprise each payment until the loan is paid off at the end of its term.
Can I create an amortization schedule for different types of loans?
Yes, you can use Excel to create amortization schedules for various types of loans, including fixed-rate mortgages, auto loans, and personal loans, by adjusting the loan details accordingly.
How do I adjust the schedule for extra payments?
To account for extra payments, you can modify the amortization schedule by adding the extra payment amount to the principal payment for the periods extra payments are made and adjust the remaining balance accordingly.
Why does the remaining balance not equal zero at the end of my schedule?
If the remaining balance does not equal zero, check for rounding errors in your payment calculations or ensure that all payments were correctly applied to both the principal and interest.
Can Excel calculate the total interest paid over the life of the loan?
Yes, by summing the ‘Interest Paid’ column in your amortization schedule, Excel can calculate the total interest paid over the life of the loan.
More in Excel
