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.
Calculate a periodic loan payment with consistent units
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Fill E9:E11 separately. E9 is the signed unrounded payment for principal E2, nominal annual rate E3, total payment count E4, payment frequency E5 and timing E6. E10 is positive total scheduled outlay; E11 is positive total scheduled interest. The remaining balance is zero and fees are excluded.
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: E9, E10, E11. 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
Build a signed fixed-payment model, then reconcile total outlay with the amount borrowed.
THE QUICK ANSWER01
Convert the annual nominal rate to a per-payment rate. Keep the positive amount received and negative payments consistent, then calculate total outlay before subtracting the original principal.
E9
=PMT(E3/E5,E4,E2,0,E6)E10
=-E9*E4E11
=E10-E2For the example data
E9 -425.4778584264656E10 10211.468602235174E11 611.4686022351743
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How it works
Annual nominal is an explicit input convention
Use the supplied nominal annual rate divided by the number of equal payment periods in a year. Twelve monthly payments and four quarterly payments require different periodic rates. E4 is the total number of payments already. The task does not convert an effective annual yield or derive a lender APR.
Beginning payments move the whole schedule
With type 1, the first payment is made immediately, so every payment is one period earlier than with type 0. A positive interest rate therefore gives a smaller payment magnitude for the same principal and payment count. At zero interest the timing does not change the payment.
Separate the three outputs
A loan receipt of 2,400 split into twelve zero-interest payments produces a signed payment of -200, positive outlay of 2,400 and zero interest. For a nonzero rate, total outlay exceeds principal. Enter each report formula independently; filling the first formula down would change its references.
Bound the learning model
Inputs are literal finite numbers: principal 0.01 through 1,000,000; annual nominal rate zero or 0.0001 through 1; 1 through 1,200 total payments; 1 through 366 payments per year; timing 0 or 1. Extremely large compounded growth is outside this task. Fees, taxes, varying rates, extra payments, balloons and cents-rounded repayment schedules need another model.
Read the full step-by-step walkthrough
- Align the time units
E3 is an annual nominal rate: 0.06 means 6%. Divide by E5. E4 already counts all payments, not years. This convention is not an effective annual yield conversion.
- Keep cash flows signed
E2 is positive money received. PMT returns the negative equal payment leaving the borrower. The model has zero remaining balance.
- Name the payment timing
Timing zero pays at each period end; one pays at each period beginning. The beginning schedule includes a payment immediately at origination.
- Reconcile total outlay
Enter E10 separately. Negate the signed payment before multiplying by the total number of payments.
- Separate interest from returned principal
Enter E11 separately. Total scheduled interest is total outlay minus the original principal, with no fees in this model. Keep unrounded payment values.
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.