How to Build a Loan Amortization Schedule in Excel & Google Sheets

Whether you are analyzing a commercial mortgage, an equipment financing lease, or a personal auto loan, understanding how much of each payment goes toward principal reduction versus interest expense is crucial for accurate financial accounting and tax deductions.

This guide walks you through the core financial functions—PMT, PPMT, and IPMT—and shows you how to build a dynamic, self-calculating loan amortization table from scratch.

---

The Core Formulas: PMT, PPMT, and IPMT

Every fixed-rate loan payment consists of two parts:

1. Principal Payment (PPMT): Reduces the outstanding loan balance.
2. Interest Payment (IPMT): The cost of borrowing money for that period.

$$\text{Total Payment (PMT)} = \text{Principal (PPMT)} + \text{Interest (IPMT)}$$

=PMT(annual_rate / 12, total_years * 12, -loan_amount)
=PPMT(annual_rate / 12, current_period, total_years * 12, -loan_amount)
=IPMT(annual_rate / 12, current_period, total_years * 12, -loan_amount)

---

Step 1: Set Up the Loan Summary Inputs

In your spreadsheet, create an input block for your loan terms. Using standard US financial assumptions:

Cell	Parameter	Example Value	Description
B1	Loan Amount (PV)	$350,000.00	Principal borrowed
B2	Annual Interest Rate	6.50%	Stated annual percentage rate (APR)
B3	Loan Term (Years)	30	Number of years to maturity
B4	Payments Per Year	12	Monthly frequency
B5	Monthly Payment (PMT)	=PMT(B2/B4, B3*B4, -B1)	Returns $2,212.24

[!TIP] Pro Tip: Always place a minus sign (-) in front of the loan amount (-B1). Excel and Google Sheets treat the loan amount as cash received (positive) and monthly payments as cash paid out (negative). Adding the minus sign ensures your output is a clean, positive number.

---

Step 2: Build the Amortization Schedule Table

Create a table starting at row 8 with the following headers:

• Column A: Period / Month (1, 2, 3... 360)
• Column B: Beginning Balance
• Column C: Total Payment (PMT)
• Column D: Principal Paid (PPMT)
• Column E: Interest Paid (IPMT)
• Column F: Ending Balance

Row 1 Formulas (Month 1):

Assuming Month 1 starts on row 9:

1. Beginning Balance (B9):

   =B1

2. Total Payment (C9):

   =$B$5

3. Principal Paid (D9):

   =PPMT($B$2/$B$4, A9, $B$3*$B$4, -$B$1)

   Result: $316.41
4. Interest Paid (E9):

   =IPMT($B$2/$B$4, A9, $B$3*$B$4, -$B$1)

   Result: $1,895.83
5. Ending Balance (F9):

   =B9 - D9

   Result: $349,683.59

---

Row 2 Formulas (Month 2 and Downward):

For row 10:

• Beginning Balance (B10): =F9 (References previous month’s ending balance)
• Total Payment (C10): =$B$5
• Principal Paid (D10): =PPMT($B$2/$B$4, A10, $B$3*$B$4, -$B$1)
• Interest Paid (E10): =IPMT($B$2/$B$4, A10, $B$3*$B$4, -$B$1)
• Ending Balance (F10): =B10 - D10

Drag these formulas down to Period 360. In period 360, your ending balance will hit exactly $0.00.

---

Schedule Preview Table (First 5 Months)

Period (A)	Beginning Balance (B)	Payment (C)	Principal (D)	Interest (E)	Ending Balance (F)
1	$350,000.00	$2,212.24	$316.41	$1,895.83	$349,683.59
2	$349,683.59	$2,212.24	$318.12	$1,894.12	$349,365.47
3	$349,365.47	$2,212.24	$319.85	$1,892.39	$349,045.62
4	$349,045.62	$2,212.24	$321.58	$1,890.66	$348,724.04
5	$348,724.04	$2,212.24	$323.32	$1,888.92	$348,400.72

Notice how each month, the Principal portion increases and the Interest portion decreases, while the total payment remains constant at $2,212.24.

---

Advanced: Calculating Cumulative Interest with CUMIPMT

If you need to know total interest paid in Year 1 (Periods 1 through 12) for tax reporting, you don’t need to sum 12 cells manually. Use CUMIPMT:

=CUMIPMT(Rate/12, Total_Periods, Loan_Amount, Start_Period, End_Period, 0)

Example for Year 1 Interest:

=CUMIPMT(6.5%/12, 360, 350000, 1, 12, 0)

Returns: -$22,642.50 (Interest paid in Year 1)

---

Troubleshooting Common Errors

• #NUM! Error: Occurs when current_period is less than 1 or greater than total_periods ($A9 > 360$). Ensure your period counter does not exceed the total loan term.
• #VALUE! Error: Occurs when text strings (such as "6.5%" written as plain text without percentage formatting) are supplied to numeric arguments.
• Payment calculation does not match bank statement: Banks may calculate daily interest using an Actual/360 or Actual/365 convention. For standard US 30/360 amortization, PMT is exact.