Building Loan Amortization Schedules with PMT, PPMT, and IPMT

Question

A borrower takes a $500,000 mortgage (cell `B1`) at a 6.0% annual interest rate (cell `B2`) for a 30-year term (cell `B3`). In cell `B4`, write a PMT formula to calculate the monthly payment. In an amortization table, write the formulas for Month 1's Interest Portion (using IPMT) and Principal Portion (using PPMT).

Accepted Answer

Optimal Solution:
// Monthly PMT (cell B4):
=PMT(B2/12, B3*12, -B1)

// Month 1 Interest (IPMT):
=IPMT(B2/12, 1, B3*12, -B1)

// Month 1 Principal (PPMT):
=PPMT(B2/12, 1, B3*12, -B1)

Technical Explanation:
Rate is converted to monthly (B2/12), periods to total months (B3*12), and loan amount is entered as negative (-B1) so payments display as positive cash inflows. In IPMT and PPMT, the second argument (1) represents the specific period being analyzed.

Parameter	Value	Cell
Loan Amount (Principal)	$500,000	B1
Annual Interest Rate	6.0%	B2
Loan Term (Years)	30 years	B3

Month	Total Monthly Payment (PMT)	Interest Paid (IPMT)	Principal Paid (PPMT)
Month 1	$2,997.75	$2,500.00	$497.75
Month 2	$2,997.75	$2,497.51	$500.24

	A	B	C	E
1	Parameter	Value	Cell
2	Loan Amount (Principal)	$500,000	B1	Target [Enter Formula]
3	Annual Interest Rate	6.0%	B2
4	Loan Term (Years)	30 years	B3
5
6
7