Keep loan units, signs and observation times consistent
Explain what a constant-payment formula measures before interpreting its payment, interest or balance.
| Zero-rate model | After five of twelve payments |
|---|---|
| Principal received | 2400 |
| Signed payment | -200 |
| Outstanding balance | 1400 |
| Principal repaid | 1000 |
| Interest paid | 0 |
A rate belongs to a period
In this course, 0.06 is a nominal annual rate. Twelve monthly periods per year give 0.06/12 per payment. A quarterly schedule uses 0.06/4. If the contract gives 24 total payments, nper is 24 already; multiplying by twelve again creates a different loan.
Nominal annual divided by frequency is the declared convention here. An effective annual yield would require a different conversion. The model uses equal periods and constant rates, without fees, taxes or extra payments.
=PMT(E3/E5,E4,E2,0,E6)Preserve the borrower perspective
The positive principal is money received. PMT, IPMT and PPMT return negative outflows for that positive receipt. The signed interest and principal components add to the signed payment. Convert a payment to positive outlay deliberately when constructing a report.
For a zero-interest receipt of 2,400 over twelve payments, PMT is -200. Total outlay is 2,400, principal repaid after five payments is 1,000 and interest paid is zero. A zero component residual alone does not prove the allocation is correct; check each component too.
Name the instant at which a balance is observed
For end payments, zero completed payments means origination. One completed payment means immediately after the first payment. FV with the positive receipt and negative scheduled payment gives a signed future cash flow; negating it reports positive debt outstanding. Original principal equals remaining balance plus principal repaid.
The payment task also compares beginning payments: type 1 moves every payment a period earlier and includes a payment immediately. The component and balance tasks deliberately use type 0 only, so their period observations remain unambiguous.
=-FV(E3/E5,E7,PMT(E3/E5,E4,E2,0,E6),E2,E6)Keep calculation precision separate from display
Formatting a payment to two decimals preserves the stored number. ROUND(payment,2) changes every modeled payment and therefore the remaining balance. These tasks keep unrounded equal payments; a real cents-rounded schedule can require a final adjustment.
The datasets check zero rates, first and final payments, intermediate balances and changed frequencies. The browser results and workbook package checks do not certify native Excel or Google Sheets. The teaching model is not a payoff quote or a comparison of lending offers.
Check the idea
A nominal annual rate is 0.08, with four equal payment periods per year and twelve payments total. Which PMT inputs preserve those units?
Try the idea
References
Examples and explanations are original. Source links describe the related Excel features; native Excel workbook testing is separate from browser learning checks.