#### Amortization Schedule in Excel

An amortization schedule is a table or chart that outlines the repayment of a loan over time. It provides a detailed breakdown of each payment, showing how much of it goes toward repaying the loan principal and how much is allocated to paying interest. Amortization schedules are commonly used for mortgages, car loans, and other installment loans to help borrowers understand their payment structure and track the progress of loan repayment.

## Amortization Formula

The monthly payment in an amortization schedule can be calculated using the following formula:

PMT = (P*r*(1 + r)^n) / ((1 + r)^n – 1)

where:

• PMT is the monthly payment
• P is the principal loan amount
• r is the monthly interest rate (annual interest rate divided by 12 and expressed as a decimal)
• n is the total number of monthly payments

## Steps to Create an Amortization Schedule in Excel

To create an amortization schedule in Excel, you can follow these steps:

Set up your worksheet by creating a new Excel worksheet and labeling the columns as follows: Payment Number, Payment, Principal Payment, Interest Payment, Total Payment, Remaining Balance, and Interest Rate.

Input your loan details by entering the loan amount (principal), annual interest rate, and loan term in months into separate cells.
Calculate the monthly payment using the loan amortization formula:

=PMT(D1, C1, -A1)

Where:

• D1 is the cell containing the monthly interest rate
• C1 is the cell containing the loan term in months
• A1 is the cell containing the loan amount