Coupon rate used as yield
Coupon controls the payment. Yield discounts it; a quoted clean price can imply a different rate.
Compare duration with exact bond repricing
Read-only
Step-by-step guide · read-only ·
Loading your interactive spreadsheet…
Preparing the worksheet and formulas…
Data & tools
Task requirements
Build G2:G9 using the fixed 100 redemption and terms in B2:B8 plus decimal yield change B10. Report clean, accrued and dirty prices, Macaulay duration in years, modified duration, the approximate change -modified duration × yield change × dirty price, exact clean-price repricing change, and exact minus approximate. Settlement stays fixed so accrued interest cancels from the price change.
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 using the fixed 100 redemption and terms in B2:B8 plus decimal yield change B10.
THE QUICK ANSWER01
Compare a small-change duration estimate with an exact yield scenario.
G2
=PRICE($B$2,$B$3,$B$4,$B$5,$B$6,$B$7,$B$8)G3
=100*$B$4/$B$7*COUPDAYBS($B$2,$B$3,$B$7,$B$8)/COUPDAYS($B$2,$B$3,$B$7,$B$8)G4
=G2+G3G5
=DURATION($B$2,$B$3,$B$4,$B$5,$B$7,$B$8)G6
=MDURATION($B$2,$B$3,$B$4,$B$5,$B$7,$B$8)G7
=-G6*$B$10*G4G8
=PRICE($B$2,$B$3,$B$4,$B$5+$B$10,$B$6,$B$7,$B$8)-G2G9
=G8-G7For the example data
G2 92.15491872995965G3 2.4444444444444446G4 94.59936317440409G5 6.781134649984196G6 6.577240203670414G7 -1.5555068367807723G8 -1.539798192190915G9 0.015708644589857323
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
- Keep the valuation date fixed
The only scenario change is B10 added to annual yield. Dates, coupon, payment frequency and 100 redemption remain unchanged.
- Reconcile the price base
Clean plus accrued gives dirty price. Duration weights the present values of future cash flows, whose total is dirty value for the multi-payment branch.
- Separate the two duration measures
DURATION returns present-value-weighted time in years. MDURATION divides by 1+annual yield/frequency. Frequency changes both coupon size and the per-period discount rate.
- Calculate a local approximation
Multiply modified duration by the decimal yield change and dirty price, then reverse the sign. A one-basis-point change is 0.0001. This is a local approximation, not a forecast.
- Reprice rather than hide the residual
Calculate PRICE at the changed yield and subtract the original clean price. Compare exact minus approximate. With one payment PRICE uses simple discounting, so the standard modified-duration approximation also has that convention boundary.
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.