Coupon rate used as yield
Coupon controls the payment. Yield discounts it; a quoted clean price can imply a different rate.
Infer yield from a coupon bond quote
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Build G2:G7 from B2:B9. Solve the annual yield from the clean quote, reprice at that solved yield, report price minus quote, compare annual coupon/clean quote (current yield), then report accrued interest and settlement cash for the stated face amount. Keep full internal precision; YIELD and current yield answer different questions.
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. 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:G7 from B2:B9.
THE QUICK ANSWER01
Recover an annual rate from the clean quote, then check it by repricing.
G2
=YIELD($B$2,$B$3,$B$4,$B$5,$B$6,$B$7,$B$8)G3
=PRICE($B$2,$B$3,$B$4,G2,$B$6,$B$7,$B$8)G4
=G3-$B$5G5
=100*$B$4/$B$5G6
=100*$B$4/$B$7*COUPDAYBS($B$2,$B$3,$B$7,$B$8)/COUPDAYS($B$2,$B$3,$B$7,$B$8)G7
=($B$5+G6)*$B$9/100For the example data
G2 0.056978592310324094G3 96.24999999999996G4 -4.263256414560601e-14G5 0.05194805194805195G6 2.4722222222222223G7 24680.555555555555
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
- Read the quotation units
B5 is a clean price per 100 face, not the cash paid for the full face amount. The annual coupon rate stays separate.
- Solve the annual yield
YIELD finds the rate consistent with the regular coupon schedule and quote. This course accepts positive prices with an implied nonnegative yield no greater than 0.50.
- Reprice to check the inverse
PRICE at the solved yield should recover the original clean quote. Report the residual without rounding the rate before repricing.
- Compare the current yield
Annual coupon divided by clean price excludes the gain or loss between purchase and redemption and does not discount future coupons. Retain it as its own measure.
- Reconcile the settlement amount
Add accrued coupon interest to the clean quote, then scale by the face amount. A discount quote can still have positive accrued interest.
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.