Rate and payment periods disagree
E3 is a nominal annual rate as a decimal. Divide it by E5, the number of equal payment periods per year. E4 is already the total count of payments; do not multiply it by E5 again.
Split a selected loan payment into interest and principal
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Fill E9:E12 separately for an end-of-period schedule (E6 must be 0). Use E7 as the selected one-based payment number. E9 is the signed payment, E10 its signed interest, E11 its signed principal and E12 the reconciliation residual E9-E10-E11, which should be zero.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: E9, E10, E11, E12. Browser calculations may differ from your Excel version.
Selection & formula search
Practice stays on this device. No worksheet data is uploaded.
Notes & examples
About this lesson
Inspect one end-of-period payment and prove that its signed components add to the fixed payment.
THE QUICK ANSWER01
Calculate the same fixed payment, then ask for the interest and principal at the selected one-based period. All three cash flows are negative; their reconciliation residual is zero.
E9
=PMT(E3/E5,E4,E2,0,E6)E10
=IPMT(E3/E5,E7,E4,E2,0,E6)E11
=PPMT(E3/E5,E7,E4,E2,0,E6)E12
=E9-E10-E11For the example data
E9 -425.4778584264656E10 -48E11 -377.4778584264656E12 0
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How it works
The first interest amount has a simple independent check
For the demo principal 9,600 and nominal annual rate 6% with twelve periods per year, the first period charges 48 of interest. IPMT reports -48 from the borrower perspective. The remainder of the negative fixed payment is principal repayment.
Changing period is not changing the original principal
Keep E2 as the original loan receipt for IPMT and PPMT, even when selecting a late period. These functions work out the preceding balance under the same constant-payment schedule. Entering an already-reduced balance as pv would construct a different contract.
A zero residual is necessary, not sufficient
Using zero for interest and the whole payment for principal also gives a zero residual but misstates a positive-rate loan. The checker grades both components independently across first, middle, final and zero-rate datasets. At zero interest the entire payment is principal.
Domain and timing
The numeric loan bounds match the preceding payment task, but timing E6 must be zero. E7 is a literal integer from 1 through the total payment count. These are end-of-period component examples; beginning-payment interest conventions, irregular dates, fees and actual-day accrual are outside this task.
Read the full step-by-step walkthrough
- Use one contract for every formula
The payment, interest and principal must use identical rates, counts, original principal and timing. These component cases use end-of-period payments only.
- Select a one-based payment
E7 is between 1 and E4 inclusive. Interest in period one belongs to the original principal; later interest belongs to the balance remaining before that payment.
- Calculate interest leaving the borrower
Enter the complete IPMT formula in E10. The original principal is positive, so this interest cash flow is negative.
- Calculate principal separately
Enter the PPMT formula in E11. With a constant positive rate the interest magnitude falls while the principal magnitude rises over the schedule.
- Reconcile every component
E12 subtracts both signed components from the signed payment. A zero residual alone cannot prove the components are correct; all four outputs are graded.
Things to watch
The cash-flow sign changed
The positive principal is money received by the borrower. Payments, interest payments and principal payments leave the borrower and are negative. Outstanding balance, total outlay and repaid principal are reported as positive amounts.
Rounding payments changes the model
Keep full precision inside the calculation. These tasks model equal unrounded payments and exclude fees, taxes, extra payments and a final cents adjustment. Formatting to two decimals is different from rounding each payment.
About this example
These original constant-rate teaching cases are checked with the local browser engine and independent arithmetic. Native Excel and Google Sheets recalculation, editing and saving remain unverified.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.