Amortization refers to the process of paying off a debt, such as a loan or mortgage, over time through regular payments. By calculating amortization in Excel, you can easily keep track of your payments and understand how each payment affects your loan balance. In this article, we will guide you through the process of creating an amortization schedule in Excel.

1. Set up the worksheet

Start by creating a new Excel workbook. Set up column headers for “Payment Number,” “Beginning Balance,” “Payment,” “Interest,” “Principal,” and “Ending Balance.” Your worksheet should look something like the table below:
Payment Number | Beginning Balance | Payment | Interest | Principal | Ending Balance

2. Input loan details

To begin calculating amortization, first input the main details of your loan: principal amount, annual interest rate, and the total number of payments over the life of the loan (loan term). You can place this data in separate cells on the top of your worksheet.

3. Calculate monthly payment

Next, calculate your monthly payment using the PMT function. The formula is:

=PMT(interest rate/12, total number of payments, -principal amount)

Assuming you placed interest rate in cell A1, total number of payments in cell B1, and principal amount in cell C1, this is what your formula would look like:

=PMT(A1/12,B1,-C1)

Format the result as a currency value and copy it into the first “Payment” row.

4. Populate Payment Numbers

In the first column under “Payment Number,” input 1 for your initial payment then continue down for subsequent payments until you reach the end of your loan term.

5. Fill out Begining Balance

Copy your initial principal value from Step 2 into cell B2(“Beginning Balance” for the first payment). Then use a formula to populate the rest of the column by referencing the previous row’s “Ending Balance” (cell F2).

=IF(F2=””, “”, F2)

6. Calculate Interest

In the “Interest” column, input a formula to calculate interest based on your loan’s annual interest rate and divide by 12 for the monthly interest rate.

=(A1/12)*B2

7. Compute Principal

To calculate the principal amount paid, subtract the interest payment from the total monthly payment:

=Payment column – Interest column

8. Determine Ending Balance

Finally, calculate the ending balance for each period by subtracting the principal amount from the beginning balance.

=Beginning Balance – Principal

9. Fill Down Columns

Using Excel’s fill down feature (bottom-right corner of a cell), carry down your formulas from Steps 5-8 through all the rows until you reach your last payment.

Now you have successfully set up an amortization schedule in Excel. This valuable tool can help you easily visualize your loan payments and monitor your progress toward becoming debt-free. Analyze how additional payments impact your loan balance and experiment with different repayment scenarios to help you reach your financial goals more efficiently.