ODDFPRICE

What it does

ODDFPRICE(settlement, maturity, issue, first_coupon, rate, yld, redemption, frequency, [basis])

ODDFPRICE computes clean price per 100 face value for a security with an irregular first coupon. Its dates are settlement, maturity, issue and first_coupon. They are followed by coupon rate, yield, redemption, frequency and optional basis.

Try it

=ODDFPRICE(DATE(2027,2,1),DATE(2027,10,1),DATE(2027,1,1),DATE(2027,4,1),0.06,0,100,2,0) gives a price near 104. The first coupon spans January to April, a short half-year coupon. Under 30/360 its payment is 1.5; the regular maturity coupon is three; accrued interest at February settlement is 0.5. At zero yield the clean price is 100+1.5+3-0.5=104.

Quirks

Basis defaults to zero: 0 US 30/360, 1 actual/actual, 2 actual/360, 3 actual/365, 4 European 30/360. Dates and selectors are truncated under their documented rules. Use DATE values rather than locale-dependent date text. The required date order is issue < settlement < first_coupon < maturity. Frequency is one, two or four. Negative coupon rate, invalid selectors and invalid amount/domain cases produce errors. These schedules cannot be reduced to PRICE by changing one date.

How to implement

Generate quasi-coupon periods aligned to first_coupon and maturity. Prorate the odd first coupon across those periods, discount it and later coupons/redemption, and subtract accrued interest. Long first periods can contain multiple quasi-coupons. The worked zero-yield example isolates the coupon and accrual arithmetic from discount powers. Calendar seams, long/short-period branches and residual numerical differences remain open where the recorded reports say so. The family combines a credited Apache-licensed ExcelFinancialFunctions translation in its odd-first kernel and calendar helpers with project-owned kernels and changes guided by recorded Excel answers.

Rust code

23 oddfprice_fn "ODDFPRICE" (8, 9) [V "settlement", V "maturity", V "issue", V "first_coupon", V "rate", V "yld", V "redemption", V "frequency", V "basis"] |s, a, sv| {
    let dates = [date_1900(s, a, 0, sv)?, date_1900(s, a, 1, sv)?, date_1900(s, a, 2, sv)?, date_1900(s, a, 3, sv)?];
    odd_out(s, odd::oddfprice_kernel(dates[0], dates[1], dates[2], dates[3], num(s, a, 4)?, num(s, a, 5)?, num(s, a, 6)?, num(s, a, 7)?, basis(s, a, 8)?))
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No same-input comparison between named Excel builds is recorded for this function here. The documented behaviour below is not a measurement of every Excel release.

Alternatives

ODDFYIELD solves yield from the same first-coupon schedule. ODDLPRICE handles an irregular final coupon; PRICE handles regular coupons.

Named variants

No named variants are recorded yet.