How To Build An Amortization Table

An amortization table is the financial roadmap for any installment loan, revealing exactly how each payment chips away at debt over time. Think about it: whether you are a student learning the mechanics of the time value of money, a homeowner dissecting a mortgage statement, or a developer building a loan calculator, understanding how to construct this schedule from the ground up is an essential skill. It transforms a mysterious monthly obligation into a transparent series of mathematical events, showing the precise moment your equity begins to outpace the interest expense.

Understanding the Core Components

Before diving into the construction process, you must identify the four variables that define every amortizing loan. Missing or miscalculating just one of these will render the entire table inaccurate Practical, not theoretical..

• Principal (P): The original amount borrowed or the current outstanding balance.
• Annual Interest Rate (r): The nominal yearly rate charged by the lender. This must be converted to a periodic rate for calculations.
• Loan Term (n): The total number of payment periods (usually months) over the life of the loan.
• Payment Frequency: How often payments are made (monthly, bi-weekly, quarterly). This dictates the periodic rate and total period count.

The magic number that ties these together is the Periodic Payment Amount (PMT). This fixed amount remains constant in a standard fixed-rate loan, but the internal composition—how much goes to interest versus principal—shifts dramatically with every row.

The Mathematical Foundation: Calculating the Fixed Payment

You cannot build the table until you know the payment amount. The formula derives from the present value of an annuity formula. For a standard loan where payments are made at the end of each period (ordinary annuity), the formula is:

$PMT = P \times \frac{i(1+i)^n}{(1+i)^n - 1}$

Where:

• $i$ = Periodic Interest Rate (Annual Rate / Number of Periods per Year)
• $n$ = Total Number of Payments (Years $\times$ Periods per Year)

Example Setup: Imagine a $200,000 mortgage at a 6% annual rate for 30 years, paid monthly.

1. Periodic Rate ($i$): $0.06 / 12 = 0.005$ (0.5% per month).
2. Total Periods ($n$): $30 \times 12 = 360$ months.
3. Payment Calculation: $PMT = 200,000 \times \frac{0.005(1.005)^{360}}{(1.005)^{360} - 1} \approx \textbf{$1,199.10}$

This $1,199.10 is the anchor for every single row in your table.

Step-by-Step Construction: The Iterative Method

The most intuitive way to build an amortization table is the iterative (row-by-row) method. This mimics exactly how a bank processes your payment each month. You can do this by hand for a few periods, but it is designed for spreadsheets (Excel, Google Sheets) or code (Python, JavaScript) Which is the point..

Counterintuitive, but true Not complicated — just consistent..

Column Structure

Set up your spreadsheet or data structure with these column headers:

1. Period (Month #)
2. Beginning Balance
3. Scheduled Payment (PMT)
4. Interest Portion
5. Principal Portion
6. Ending Balance
7. (Optional) Cumulative Interest Paid
8. (Optional) Cumulative Principal Paid

The Algorithm for Every Row

For Period 1 through Period N, execute this logic loop:

1. Beginning Balance: For Period 1, this is the Original Principal ($P$). For subsequent periods, it equals the previous period's Ending Balance.
2. Interest Portion: Beginning Balance $\times$ Periodic Rate ($i$).
   • This is the lender's cut. It is calculated on the debt outstanding before the current payment is applied.
3. Principal Portion: Scheduled Payment (PMT) - Interest Portion.
   • This is your equity gain. Because the payment is fixed, as interest drops, this number rises.
4. Ending Balance: Beginning Balance - Principal Portion.
   • Check: This should never go negative. In the final period, rounding differences often require a slight adjustment to the payment or principal portion to bring the balance exactly to zero.
5. Cumulative Trackers: Add the current period's Interest and Principal to their respective running totals.

Walkthrough: The First Three Months

Using our $200k / 6% / 30yr example ($1,199.10 PMT):

Observe the pattern: The interest charge drops by roughly $1.00 each month, while the principal reduction increases by that same amount. This is the "amortization curve" in action Most people skip this — try not to..

Building in Spreadsheets (Excel / Google Sheets)

While understanding the math is vital, professionals use built-in financial functions to generate tables instantly and error-free.

The PMT Function

Calculates the fixed payment. Syntax: =PMT(rate, nper, pv, [fv], [type])

• rate: Periodic rate (e.g., 0.06/12 or B1/12).
• nper: Total periods (e.g., 30*12 or 360).
• pv: Present Value (Loan Amount). Enter as negative (e.g., -200000) to get a positive payment result.
• Formula: =PMT(0.06/12, 360, -200000) $\rightarrow$ $1,199.10

The IPMT and PPMT Functions

These calculate the Interest Payment and Principal Payment for a specific period without building the prior rows The details matter here..

• =IPMT(rate, per, nper, pv) $\rightarrow$ Interest for period per.
• =PPMT(rate, per, nper, pv) $\rightarrow$ Principal for period per.

The CUMPRINC and CUMIPMT Functions

Essential for tax reporting or refinancing analysis. They sum the principal or interest paid between a start and end period Not complicated — just consistent..

• =CUMPRINC(rate, nper, pv, start_period, end_period, type)

The CUMPRINC and CUMIPMT functions are particularly handy when you need the aggregate amount paid toward principal or interest over a range of periods—say, the first five years of a mortgage or the interest that will be deductible on a tax return. Their syntax mirrors that of IPMT/PPMT but adds two extra arguments to define the window:

• start_period – the first period to include in the sum (1‑based).
• end_period – the last period to include in the sum.
• type (optional) – 0 for payments due at the end of the period (typical for loans), 1 for payments due at the beginning.

Example – cumulative interest for the first 24 months

=CUMIPMT(0.06/12, 360, -200000, 1, 24, 0)

Result: ≈ $23,018.45 – the total interest you would have paid after two years.

Example – cumulative principal for months 13 through 36

=CUMPRINC(0.06/12, 360, -200000, 13, 36, 0)

Result: ≈ $5,938.12 – the amount of loan balance reduced during that two‑year slice Small thing, real impact..

Because these functions return a single number, you can place them in a summary panel alongside the loan’s key metrics (original amount, rate, term, payment) to answer questions such as:

• “How much equity will I have after 7 years?” → = -CUMPRINC(rate, nper, pv, 1, 84) (note the minus sign to flip the negative output to a positive equity gain).
• “What is the tax‑deductible interest for the first 12 months?” → =CUMIPMT(rate, nper, pv, 1, 12).

Building a Full Amortization Schedule with Modern Excel

While dragging IPMT/PPMT down a column works, Excel 365 and Google Sheets now let you generate the entire table with a single spill formula using SEQUENCE, LET, and LAMBDA. This approach eliminates manual copying, reduces errors, and makes the schedule dynamically responsive to any input change Not complicated — just consistent..

Step‑by‑step (Excel 365)

Assume the following input cells:

1. Derive the periodic inputs (named for clarity):

rate      = B2/12
nper      = B4*12
pv        = -B3          // negative for PMT sign convention
pmt       = PMT(rate, nper, pv)

2. Create a period counter that spills from 1 to nper:

Period = SEQUENCE(nper)

3. Calculate beginning balance for each period. The first period starts with the original loan amount; every subsequent period begins with the prior period’s ending balance:

BegBal = LET(
    prev, VSTACK(0, DROP(BegBal, -1)),   // shift the column down one, prepend 0
    IF(Period=1, -pv, prev - Principal)  // Principal will be defined next
)

4. Interest and principal for each period using IPMT/PPMT (vectorized):

Interest = IPMT(rate, Period, nper, pv)
Principal= PPMT(rate, Period, nper, pv)

5. Ending balance:

EndBal = BegBal - Principal

6. Assemble the table with HSTACK (or CHOOSE in older versions):

AmortTable = HSTACK(
    Period,
    BegBal,
    pmt