How To Create an Amortization Table In Excel

Introduction

This tutorial will guide you through the process of creating an amortization table in Excel. An amortization table outlines the monthly payments on a loan, detailing the principal and interest components. This is particularly useful for understanding mortgage payments, loan schedules, and the impact of interest rates over time.

Step 1: Set Up Your Spreadsheet

• Open Excel and create a new spreadsheet.
• Label the first row with the following headers:
  • A1: Period
  • B1: Beginning Balance
  • C1: Monthly Payment
  • D1: Monthly Interest
  • E1: Principal Payment
  • F1: Ending Balance

Step 2: Input Loan Details

• In the cells below your headers, input the loan details:
  • Principal Amount: Enter the total loan amount (e.g., $100,000).
  • Annual Interest Rate: Enter the interest rate (e.g., 5%) in a separate cell for reference.
  • Loan Term: Specify the loan duration in years (e.g., 30 years).

Step 3: Calculate Monthly Payment

• Use the PMT function to calculate the monthly payment:
  • In cell C2, enter the formula:

    =PMT(annual_interest_rate/12, loan_term*12, -principal_amount)
    

  • Replace annual_interest_rate, loan_term, and principal_amount with the appropriate cell references.

Step 4: Fill in the First Row of the Amortization Table

• In cell A2, enter “1” to represent the first payment period.
• In cell B2, enter the principal amount (e.g., $100,000).
• In cell D2, calculate the monthly interest:
  • Enter the formula:

    =B2*(annual_interest_rate/12)
    

• In cell E2, calculate the principal payment:
  • Enter the formula:

    =C2-D2
    

• In cell F2, calculate the ending balance:
  • Enter the formula:

    =B2-E2
    

Step 5: Fill in Subsequent Rows

• For Periods 2 and beyond, follow these steps:
  • In cell A3, enter “2”.
  • In cell B3, reference the ending balance from the previous row (B2):

    =F2
    

  • Copy the formulas from D2, E2, and F2 into D3, E3, and F3, respectively, adjusting the references as needed.

Step 6: Drag Down the Formulas

• Select the first two rows of your amortization table (A2 to F3).
• Drag the fill handle (small square at the bottom-right corner of the selection) down to fill in the rest of the rows for the entire loan term (e.g., 360 rows for a 30-year loan).

Step 7: Format Your Table

• Select the entire table and apply formatting for clarity:
  • Use currency formatting for financial figures.
  • Adjust column widths to ensure all text is visible.
  • Optionally, add borders for a clean look.

Conclusion

You have successfully created an amortization table in Excel. This table provides an overview of your loan payments, helping you understand how much you will pay each month and how your balance decreases over time. Consider exploring additional Excel features such as charts to visualize your amortization schedule. For further learning, check out Excel tips or tutorials on specific functions like PMT.