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:
Comments
Post a Comment