How It Works

An amortization schedule breaks down each payment into principal (reducing your balance) and interest (the cost of borrowing). Early payments are mostly interest; later payments are mostly principal.

Core Formulas

Monthly Payment:

=PMT(rate/12, total_months, -loan_amount)

Interest Portion:

=remaining_balance * (rate/12)

Principal Portion:

=payment - interest

New Balance:

=previous_balance - principal

Example: Building a Mortgage Schedule

Loan Details:

Amount: $300,000
Rate: 7% annual
Term: 30 years (360 months)

Monthly Payment: =PMT(7%/12, 360, -300000) = $1,995.91

Payment #	Payment	Interest	Principal	Balance
0				$300,000
1	$1,996	$1,750	$246	$299,754
2	$1,996	$1,749	$247	$299,507
3	$1,996	$1,747	$249	$299,258
…	…	…	…	…
360	$1,996	$12	$1,984	$0

Complete Spreadsheet Setup

Input Section

Field	Value
Loan Amount	$300,000
Annual Rate	7.0%
Term (Years)	30
Monthly Payment	=PMT(B2/12, B3*12, -B1)

Schedule Table

A	B	C	D	E
#	Payment	Interest	Principal	Balance
0				=loan_amount
1	=payment	=E2*rate/12	=B3-C3	=E2-D3
2	=payment	=E3*rate/12	=B4-C4	=E3-D4

Copy row 3 formulas down for all 360 payments.

Formulas Explained

Payment (B3): =$B$6 (reference the calculated payment)

Interest (C3): =E2*$B$2/12 (previous balance × monthly rate)

Principal (D3): =B3-C3 (what’s left after interest)

Balance (E3): =E2-D3 (previous balance minus principal)

Using Built-in Functions

Google Sheets has specialized functions:

Interest Portion for Payment N:

=IPMT(rate/12, payment_number, total_payments, -loan_amount)

Principal Portion for Payment N:

=PPMT(rate/12, payment_number, total_payments, -loan_amount)

Cumulative Interest (Payments 1-N):

=CUMIPMT(rate/12, total_payments, loan_amount, 1, N, 0)

Cumulative Principal (Payments 1-N):

=CUMPRINC(rate/12, total_payments, loan_amount, 1, N, 0)

Amortization Analysis

Total Interest Paid

=payment * total_months - loan_amount

$300K loan at 7% for 30 years:

=$1,995.91 * 360 - $300,000 = $418,527

You pay more in interest than the original loan!

Interest by Year

Year	Principal	Interest	Balance
1	$3,123	$20,828	$296,877
5	$17,496	$102,309	$282,504
10	$40,684	$199,026	$259,316
15	$72,619	$286,996	$227,381
20	$117,040	$361,478	$182,960
30	$300,000	$418,527	$0

Interest vs. Principal Crossover

When does principal exceed interest in each payment?

=MATCH(TRUE, Principal_Column > Interest_Column, 0)

For our example: Payment #194 (about 16 years in)

Extra Payments Impact

Add a column for extra principal:

#	Payment	Interest	Extra	Principal	Balance
1	$1,996	$1,750	$200	$446	$299,554

Modified Principal: =standard_principal + extra Modified Balance: =previous - principal - extra

$200 extra monthly on this loan:

Saves 7 years and 4 months
Saves $112,000 in interest

Different Loan Types

15-Year Mortgage

=PMT(rate/12, 180, -loan)

Higher payment, much less interest.

Auto Loan (5 years)

=PMT(rate/12, 60, -car_price)

Student Loan (10 years)

=PMT(rate/12, 120, -loan)

Biweekly Payment Schedule

Pay half the monthly payment every two weeks (26 payments/year = 13 monthly equivalents):

Payment	Amount	Frequency
Monthly	$1,996	12/year
Biweekly	$998	26/year

Annual payments: $23,948 vs $23,951 - essentially one extra payment per year.

Result: 30-year mortgage paid off in ~25 years.

Pro Tips

Print your schedule - seeing the full picture motivates extra payments
Compare loan options - build schedules for different rates/terms side by side
Track actual vs. scheduled - enter your real payments to see how extra payments affect the timeline
Refinance analysis - compare remaining interest on current loan vs. new loan schedule
Tax planning - interest paid may be deductible (mortgage interest)

Common Errors

Balance doesn’t reach zero: Rounding errors accumulate - adjust final payment slightly
#NUM! error: Check that rate, periods, and loan are all positive/valid
Wrong payment: Ensure rate is monthly (divide annual by 12) and periods are in months

Amortization Schedule