Build a loan amortization schedule

Split every payment into interest and principal with IPMT and PPMT, track the balance month by month, and prove the schedule adds up. Advanced · 7 min · IPMT, PPMT, Running balance, $ references, Checks

An amortization schedule shows where every loan payment goes. The payment is the same each month, but the split changes. Early on, most of it is interest, because interest is charged on a large balance. As the balance shrinks, more of each payment goes on the loan itself, the principal.

You are borrowing G1 to buy a kiln and a wheel for your pottery studio, repaid monthly over a year. Two functions split each payment:

IPMT(rate, per, nper, pv): the interest part of payment number per
PPMT(rate, per, nper, pv): the principal part of the same payment

> Like PMT, both return negative numbers for a loan you receive. This schedule shows positive amounts, so each formula starts with a minus: =-IPMT(…).

Steps

Number the payments first. Month 0 in A2 is the day the money arrives; fill A3:A14 with the months 1 to 12. Type 1 in A3, put =A3+1 in A4, then fill that down to A14.
In B3, find the interest in month 1: =-IPMT($G$2/12, A3, $G$3, $G$1). That is the monthly rate, which payment this is, how many payments there are, and the loan amount. Fill it down to B14: the $ signs keep the loan details fixed while A3 moves down to each month.
In C3:C14, use PPMT in the same way to find how much of each payment pays off the loan itself.
Now track what is still owed. In D2, bring in the loan amount from G1. Then in D3, take that month's principal off the balance in the row above, and fill down to D14.
Finish with totals and a built-in check. In B15:C15, total the interest and the principal. Then in G5, write a formula that shows TRUE when the principal total matches the loan amount.

Remember

IPMT and PPMT split payment number per into interest and principal; together they always equal PMT.
Lock the loan inputs with $ and let the period number move with each row.
A running balance subtracts each month's principal from the row above.
Build in checks: the principal should total the loan, and the last balance should be zero.

Previous: Payoff time, true rates and savings targets · Next: Compare investments with NPV and IRR