Create a loan amortization schedule in Excel (with extra payments)

This tutorial demonstrates how to create a detailed amortization schedule in Excel for amortizing loans or mortgages. An amortizing loan is repaid in installments over its term, with each payment covering principal and interest. An amortization schedule itemizes these payments, showing the principal and interest portions, and the remaining balance after each payment.

This guide covers:

• Creating a loan amortization schedule in Excel.
• Handling variable numbers of payment periods.
• Incorporating additional payments.
• Utilizing an Excel amortization template.

Creating a Loan Amortization Schedule in Excel

This requires the PMT, PPMT, and IPMT functions:

• PMT: Calculates the total periodic payment (constant throughout the loan).
• PPMT: Calculates the principal portion of each payment (increases over time).
• IPMT: Calculates the interest portion of each payment (decreases over time).

Step 1: Setting up the Amortization Table

First, define input cells:

• Cell C2: Annual interest rate
• Cell C3: Loan term (years)
• Cell C4: Number of payments per year
• Cell C5: Loan amount

Create a table with headers "Period," "Payment," "Interest," "Principal," and "Balance" (A7:E7). Populate the "Period" column with payment numbers (e.g., 1-24).

Step 2: Calculating Total Payment Amount (PMT Formula)

Use the PMT function: =PMT($C$2/$C$4, $C$3*$C$4, $C$5). This formula should be entered in B8 and copied down. Note the use of absolute references ($) for consistent calculations.

Step 3: Calculating Interest (IPMT Formula)

Use the IPMT function: =IPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5). Enter this in C8 and copy down. A8 is a relative reference, adjusting for each period.

Step 4: Calculating Principal (PPMT Formula)

Use the PPMT function: =PPMT($C$2/$C$4, A8, $C$3*$C$4, $C$5). Enter this in D8 and copy down.

(Verify: Principal Interest = Payment for each row)

Step 5: Calculating Remaining Balance

In E8: =C5 D8 (Loan amount Principal). In E9 and below: =E8 D9 (Previous balance Principal).

(To display positive numbers, negate the PMT, IPMT, and PPMT formulas and use subtraction for the balance.)

Amortization Schedule for a Variable Number of Periods

For a reusable schedule, input a maximum number of periods (e.g., 360) in the "Period" column. Wrap each formula in an IF statement to check if the period is within the actual loan term. If not, return an empty string. Conditional formatting can then hide extra rows.

Loan Amortization Schedule with Additional Payments

This requires more complex formulas to account for variable extra payments. Additional input cells are needed (e.g., for the extra payment amount). Formulas will need to adjust the principal and balance calculations accordingly. Detailed formulas are provided in the original document.

Amortization Schedule Excel Template

Excel offers built-in templates; search for "amortization schedule" when creating a new workbook.

The original document provides detailed formulas and screenshots for each step, including handling variable periods and additional payments. Remember to adapt cell references if your table layout differs.

The above is the detailed content of Create a loan amortization schedule in Excel (with extra payments). For more information, please follow other related articles on the PHP Chinese website!