7.4. Creating Amortization Schedules

An amortization schedule is a financial table that shows the breakdown of each loan payment over time, highlighting how much of each payment goes toward the principal and how much goes toward the interest. This is commonly used for loans like mortgages, car loans, or any type of installment loan. In Excel, you can easily create amortization schedules using built-in functions and formulas to calculate loan payments, interest, and principal repayment over the life of the loan.

Key Components of an Amortization Schedule

1. Loan Amount (Principal): The total amount borrowed from the lender. This is the initial value of the loan and is used to calculate the interest paid over time.
2. Interest Rate: The annual interest rate charged by the lender on the loan. This rate is used to determine how much interest is paid during each period.
3. Loan Term: The length of time over which the loan will be repaid. The term is typically expressed in months or years and is used to calculate the payment schedule.
4. Payment Frequency: Whether the loan payments are made monthly, quarterly, or annually. Monthly payments are the most common, but this can vary based on the loan terms.
5. Payment Amount: The fixed amount that is paid regularly toward the loan. This is typically calculated using the PMT function in Excel, which uses the loan amount, interest rate, and loan term to determine the payment.
6. Principal Payment: The portion of each payment that goes toward reducing the original loan amount (principal).
7. Interest Payment: The portion of each payment that goes toward paying the interest on the loan balance.
8. Remaining Balance: The remaining balance of the loan after each payment. This reduces over time as principal payments are made.

Steps to Create an Amortization Schedule in Excel

1. Set Up the Loan Details: First, you need to set up the basic loan details such as loan amount, interest rate, loan term, and payment frequency.
   Example:
   • Loan Amount (Principal): $10,000
   • Interest Rate: 5% annually
   • Loan Term: 5 years
   • Payment Frequency: Monthly
2. Use the PMT Function to Calculate the Payment: Excel's PMT function can be used to calculate the fixed payment amount for a loan based on the interest rate, number of periods, and loan amount. The formula is:

   =PMT(rate, nper, pv)
   

   Where:
   • rate = Interest rate per period (annual rate divided by the number of periods per year)
   • nper = Total number of payments (loan term multiplied by the number of periods per year)
   • pv = Present value, or loan amount (negative number, as it's an outflow)
   For the above example, if the interest rate is 5% annually and you are making monthly payments over 5 years:

   =PMT(5%/12, 5*12, -10000)
   

   This will give you the monthly payment amount.
3. Set Up the Amortization Table: Create columns for the following details:
   • Period (Month)
   • Payment
   • Interest Paid
   • Principal Paid
   • Remaining Balance
4. Calculate Interest and Principal Paid for Each Period:
   • Interest Paid for each period is calculated by multiplying the remaining balance by the interest rate per period. For example:

     =Remaining Balance * (Interest Rate / Number of Periods per Year)
     

   • Principal Paid is calculated by subtracting the interest paid from the total payment:

     =Payment - Interest Paid
     

5. Update the Remaining Balance: The remaining balance after each payment is calculated by subtracting the principal paid from the previous remaining balance:

   =Previous Remaining Balance - Principal Paid
   

6. Fill in the Table: For each period, repeat the calculations until the remaining balance is reduced to zero.

Example of an Amortization Schedule for a $10,000 Loan

Assume the following loan details:

• Loan Amount: $10,000
• Interest Rate: 5% annually
• Loan Term: 5 years (60 months)
• Monthly Payment: $188.71 (calculated using the PMT function)

Best Practices for Creating Amortization Schedules

• Check for Errors: Make sure your formulas are correct, especially for calculating interest and principal payments. A common mistake is mixing up the interest rate per period or incorrectly linking the principal and interest.
• Format the Schedule: Use Excel’s formatting tools to make your schedule clear and easy to read. You can color-code the principal and interest payments, and highlight the last row where the balance reaches zero.
• Extend to Different Loan Types: If you have loans with different interest rates, payment structures (e.g., interest-only periods), or irregular payment schedules, adjust the model accordingly.
• Sensitivity Analysis: To understand how different interest rates or loan terms affect your payments, you can create multiple amortization schedules for different scenarios. This will help you better plan and make informed decisions.

Conclusion

Creating an amortization schedule in Excel is a valuable tool for managing loans and understanding how debt is repaid over time. By using Excel's built-in functions like PMT, along with basic arithmetic and logical formulas, you can create accurate and customized amortization tables to track loan payments, interest, and principal reduction. This not only helps with personal finance planning but is also an essential skill for financial analysts, business owners, and anyone involved in managing debt or evaluating financing options.