Coupon rate used as yield
Coupon controls the payment. Yield discounts it; a quoted clean price can imply a different rate.
Reconcile clean price, accrued interest and settlement cash
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Build G2:G9 from the actual terms in B2:B9. Find the previous/next coupon dates and remaining payment count, the coupon per 100 face, clean price, accrued coupon interest, dirty price and the cash amount for the stated face amount. Rates are annual decimals and prices are per 100 face. Keep full internal precision.
Edit the bond terms
Apply one numeric value to the actual worksheet. Dates use calendar inputs; rates are decimal numbers. The complete regular-coupon report is validated before an edit replaces a valid input.
Applied value:
Follow the highlighted cells. Switch to Your turn to edit. Answer cells: G2, G3, G4, G5, G6, G7, G8, G9. 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 G2:G9 from the actual terms in B2:B9.
THE QUICK ANSWER01
Reconcile a per-100 quote with its coupon schedule and actual settlement cash.
G2
=COUPPCD($B$2,$B$3,$B$7,$B$8)G3
=COUPNCD($B$2,$B$3,$B$7,$B$8)G4
=COUPNUM($B$2,$B$3,$B$7,$B$8)G5
=100*$B$4/$B$7G6
=PRICE($B$2,$B$3,$B$4,$B$5,$B$6,$B$7,$B$8)G7
=100*$B$4/$B$7*COUPDAYBS($B$2,$B$3,$B$7,$B$8)/COUPDAYS($B$2,$B$3,$B$7,$B$8)G8
=G6+G7G9
=G8*$B$9/100For the example data
G2 2026-03-15G3 2026-09-15G4 11G5 2.625G6 96.0814146521568G7 0.029166666666666667G8 96.11058131882346G9 24027.64532970587
Enter each formula in its labeled answer cell. These report formulas perform different jobs; do not fill the first formula down.
How it works
Keep units attached to each input
Rates and yield changes are annual decimals. Frequency is 1, 2 or 4, and basis is a whole number from 0 through 4. All quotes and accrued amounts are per 100 face until explicitly scaled. Numeric dates remain dates in the worksheet and downloads.
Use the stated regular coupon model
These fictional sources assume regular coupons anchored on maturity, fixed coupon amounts and no default, call, tax, fee or changing cash-flow schedule. PRICE, YIELD and the duration measures do not establish a market value or an investment outcome.
Input scope
Use whole Excel date serials from 2000 through 2099, settlement before maturity and at most thirty years apart; coupon 0–0.25, redemption 50–150, frequency 1/2/4, basis 0–4, and face greater than zero through 10000000. Yield is 0–0.50. The yield report requires a quote with an implied rate in that interval. Duration retains redemption 100 and a yield change within ±0.02 that keeps the changed yield in range. Unsupported inputs preserve the applied worksheet.
Read the full step-by-step walkthrough
- Locate the coupon window
The maturity date anchors regular coupon dates. Settlement on a coupon date begins a new accrual period; the next payment is later than settlement. Use the selected frequency and day-count basis.
- Express the coupon per 100 face
The annual coupon rate applies to 100 face. Divide by payments per year; use the separate face amount only when calculating settlement cash.
- Calculate the quoted clean price
PRICE excludes accrued interest. It discounts the remaining regular payments using the quoted annual yield. Its one-payment branch uses simple remaining-period discounting.
- Reconcile the accrued amount
Multiply the coupon by accrued days divided by coupon-period days under the same basis. An actual/360 period denominator need not equal actual calendar days.
- Scale the settlement cash
Dirty price equals clean plus accrued. Multiply that per-100 price by face/100 once. Compare the zero-coupon and coupon-date sources, where accrued is zero.
Things to watch
Face amount scaled twice
PRICE already returns the price for 100 face. Multiply dirty price by face/100 once.
Mismatched day-count inputs
Accrued interest and the quoted price must use the same basis and coupon schedule.
About this example
Original regular-coupon reports checked with the actual formula engine and independent calendar/cash-flow rules. Native recalculation/edit/save remains separate.
Content version 1. Practice checks compare calculated results across different datasets; they do not certify every possible formula.