How to use Microsoft Excel IPMT and PPMT functions?

 

Here’s a step-by-step guide to creating a loan amortization table in Excel.

Step 1: Input Loan Details

Open a new Excel workbook.

Enter the loan details in the top rows:

Loan Amount (PV): 10000

Annual Interest Rate: 6%

Loan Term (Years): 5

Payments per Year: 12

Calculate derived values:

Monthly Interest Rate: =B2/B4

Total Periods (Nper): =B3*B4

Step 2: Set Up the Table

Create column headers: Period | Payment | Interest (IPMT) | Principal (PPMT) | Balance

Start with Period 0 and enter the loan amount as the initial balance.

Step 3: Calculate Payments

In the first row under Payment (e.g., cell C8), calculate the constant monthly payment using the PMT function: =PMT(B5, B6, -B2)

Step 4: Calculate Interest and Principal

In the Interest column (e.g., D9), use the IPMT function: =IPMT($B$5, A9, $B$6, -$B$2)

Copy this formula down the column for each period.

In the Principal column (e.g., E9), use the PPMT function: =PPMT($B$5, A9, $B$6, -$B$2)

Copy this formula down the column for each period.

Step 5: Calculate the Remaining Balance

In the Balance column (e.g., F9), calculate the remaining loan balance: =F8 - E9

Copy this formula down the column for each period.

Step 6: Fill the Table

Drag the formulas for Period, Interest, Principal, and Balance down until the balance reaches 0.

Check the totals:

Total interest paid = Sum of the Interest column.

Total principal paid = Loan amount.

Example Table: