Keep coupon quotes, settlement cash and duration in their own units

Follow a regular coupon schedule from a clean quotation to accrued interest, an implied yield and a fixed-date rate scenario.

Original worked example
Quantity	Value	Unit
Face amount	10000	face units
Clean quote	98	per 100 face
Accrued interest	1.5	per 100 face
Dirty price	99.5	per 100 face
Settlement cash	9950	cash units

A clean quote needs an accrual adjustment

PRICE returns a quote per 100 face. The coupon rate sets each regular payment: 100 × annual coupon / payments per year. The buyer also pays the coupon accrued since the previous coupon date. Clean plus accrued is dirty price; dirty price × face/100 is the settlement cash amount.

The maturity date anchors the regular coupon schedule. A settlement exactly on a coupon date starts a new period with zero accrued interest. Frequency is annual, semiannual or quarterly; rates remain annual decimals.

Use the same day-count basis throughout. The regular PRICE/YIELD discount fraction uses 1 minus accrued days divided by coupon-period days. For an actual/360 or actual/365 denominator, this cannot always be replaced with actual days until the next coupon divided by that denominator.

=PRICE(B2,B3,B4,B5,B6,B7,B8)

A quoted price can imply a different yield

YIELD solves the rate consistent with the clean quote and future regular payments. Repricing with PRICE at that full-precision rate should recover the quote. The residual gives a useful check on units, schedule and rounding.

Current yield is annual coupon divided by clean price. It omits the gain or loss between the purchase quote and redemption and does not discount the individual future coupons. Keep it separate from the solved yield.

When only one coupon payment remains, PRICE uses its simple remaining-period discount branch. Replacing it with a fractional power changes that convention. These exercises keep that last-period case visible.

=YIELD(B2,B3,B4,B5,B6,B7,B8)

Duration describes a local rate response

DURATION is the present-value-weighted time of the remaining cash flows, expressed in years. MDURATION divides it by 1+annual yield/frequency. Both functions assume 100 redemption.

For a multi-payment regular bond at a fixed settlement date, -modified duration × decimal yield change × dirty price approximates the absolute price change per 100 face. A basis point is 0.0001. Accrued interest stays fixed, so the dirty and clean price changes are equal.

Calculate PRICE at the changed yield to see the exact model scenario and its difference from the approximation. Larger changes expose curvature. In the one-payment branch, the standard duration convention also differs from the simple-price discount convention. The exercise retains the residual rather than forcing equality.

=-MDURATION(B2,B3,B4,B5,B7,B8)*B10*($G$2+$G$3)

Keep the stated cash-flow model attached to the answer

These original fictional sources assume fixed regular coupons, unchanged settlement and no default, call, tax or fee. The reports calculate that model; they do not establish a market price, realized return or a change in credit conditions.

Use numeric dates from 2000 through 2099, a horizon of at most thirty years, the declared nonnegative rate range and a positive remaining discount fraction. The editor validates the complete report before applying an input. Browser checks and workbook caches remain separate from native Excel or Sheets recalculation.

Check the idea

At a fixed settlement date, which price base belongs in the multi-payment modified-duration estimate of the absolute price change?

Try the idea

References

Examples and explanations are original. Source links describe the related Excel features; native Excel workbook testing is separate from browser learning checks.