ODDLPRICE

Microsoft publishes =ODDLPRICE(A2, A3, A4, A5, A6, A7, A8, A9) = $99.88 for a bond settled 2008-02-07, maturing 2008-06-15, last interest 2007-10-15, 3.75% coupon, 4.05% yield, redemption 100, semiannual, 30/360 basis. DERIVATION, clean-room. Microsoft publishes NO formula for ODDLPRICE -- the page has no 'calculated as follows' section and no equation image at all, which is the single biggest documentation gap in this batch -- and OpenFormula 1.3 section 6.12.33 gives only a parameter glossary. So the price here is obtained by ALGEBRAICALLY INVERTING the closed form Microsoft DOES publish, on the ODDLYIELD page, solving that equation for the price: pr = (redemption + (sum DCi/NLi)*100*rate/f) / (1 + (sum DSCi/NLi)*yld/f) - (sum Ai/NLi)*100*rate/f. For this bond the odd last period runs from the last interest date 2007-10-15 to maturity 2008-06-15 and spans two quasi-coupon periods on the semiannual grid, so on basis 0 the ratios are sum DCi/NLi = 240/180, sum Ai/NLi = 112/180 (2007-10-15 to settlement 2008-02-07) and sum DSCi/NLi = 128/180 (settlement to maturity). With a coupon of 100*0.0375/2 = 1.875 that gives (100 + 2.5)/(1 + (128/180)*0.02025) - 1.1666... = 99.87828601472134... The day counts come from a clean-room implementation of OpenFormula 1.3 section 4.11.7 -- Procedure A for US (NASD) 30/360 with its four order-dependent endpoint adjustments, Procedure B for actual days, Procedure C for European 30/360, and Procedures D/E/F for days-in-year -- written for this batch and used by no engine; the arithmetic is mpmath at 50 digits over exact rational day-count ratios. At two decimals that is 99.88 -- the published figure, reproduced from an inverted formula rather than copied. (Microsoft's own prose on this page says the arguments come from "cells A2:A10" while the formula it prints takes eight arguments from A2:A9; the same off-by-one appears on the ODDLYIELD page. It is a typo in the prose, not in the figure.) Microsoft's page was re-read live on 2026-08-31 at https://support.microsoft.com/en-us/excel/functions/oddlprice-function (the /en-us/office/<name>-function-<guid> path was returning Microsoft's 'Sorry, the page you're looking for can't be found' body throughout this batch, and the working path serves an 87 KB stub about half the time, so pages were fetched with retries until the payload exceeded 150 KB).

Two published decimals cannot see a day-count error of a day or two, so the inverted-formula value is asserted at ten places: 99.8782860147.

A SECOND VENDOR'S PUBLISHED FIGURE, on a second set of dates. LibreOffice's help (help.libreoffice.org/latest/en-US/text/scalc/01/04060118.html) prints '=ODDLPRICE("1999-02-07";"1999-06-15";"1998-10-15"; 0.0375; 0.0405;100;2;0) returns 99.87829' -- Microsoft's example shifted back exactly nine years, same month-and-day, same rates. Running the clean-room derivation on the 1999 dates gives 99.87828601472134..., identical to the 2008 answer, because every 30/360 day count in the calculation depends only on the month-and-day differences: 99.87829 at five places. Two independent vendors' documentation and one independent derivation agreeing on the same number is stronger evidence than any of the three alone, and this case is here to make that agreement executable.

Microsoft documents: "If rate < 0 or if yld < 0, ODDLPRICE returns the #NUM! error value."

Microsoft documents: "If basis < 0 or if basis > 4, ODDLPRICE returns the #NUM! error value."

Microsoft documents: "The following date condition must be satisfied; otherwise, ODDLPRICE returns the #NUM! error value: maturity > settlement > last_interest." Swapping the two puts settlement (2007-10-15) before the last interest date (2008-02-07).

Microsoft documents: "If settlement, maturity, or last_interest is not a valid date, ODDLPRICE returns the #VALUE! error value."

Microsoft publishes =ODDLPRICE(A2, A3, A4, A5, A6, A7, A8, A9) = $99.88 for a bond settled 2008-02-07, maturing 2008-06-15, last interest 2007-10-15, 3.75% coupon, 4.05% yield, redemption 100, semiannual, 30/360 basis. DERIVATION, clean-room. Microsoft publishes NO formula for ODDLPRICE -- the page has no 'calculated as follows' section and no equation image at all, which is the single biggest documentation gap in this batch -- and OpenFormula 1.3 section 6.12.33 gives only a parameter glossary. So the price here is obtained by ALGEBRAICALLY INVERTING the closed form Microsoft DOES publish, on the ODDLYIELD page, solving that equation for the price: pr = (redemption + (sum DCi/NLi)*100*rate/f) / (1 + (sum DSCi/NLi)*yld/f) - (sum Ai/NLi)*100*rate/f. For this bond the odd last period runs from the last interest date 2007-10-15 to maturity 2008-06-15 and spans two quasi-coupon periods on the semiannual grid, so on basis 0 the ratios are sum DCi/NLi = 240/180, sum Ai/NLi = 112/180 (2007-10-15 to settlement 2008-02-07) and sum DSCi/NLi = 128/180 (settlement to maturity). With a coupon of 100*0.0375/2 = 1.875 that gives (100 + 2.5)/(1 + (128/180)*0.02025) - 1.1666... = 99.87828601472134... The day counts come from a clean-room implementation of OpenFormula 1.3 section 4.11.7 -- Procedure A for US (NASD) 30/360 with its four order-dependent endpoint adjustments, Procedure B for actual days, Procedure C for European 30/360, and Procedures D/E/F for days-in-year -- written for this batch and used by no engine; the arithmetic is mpmath at 50 digits over exact rational day-count ratios. At two decimals that is 99.88 -- the published figure, reproduced from an inverted formula rather than copied. (Microsoft's own prose on this page says the arguments come from "cells A2:A10" while the formula it prints takes eight arguments from A2:A9; the same off-by-one appears on the ODDLYIELD page. It is a typo in the prose, not in the figure.) Microsoft's page was re-read live on 2026-08-31 at https://support.microsoft.com/en-us/excel/functions/oddlprice-function (the /en-us/office/<name>-function-<guid> path was returning Microsoft's 'Sorry, the page you're looking for can't be found' body throughout this batch, and the working path serves an 87 KB stub about half the time, so pages were fetched with retries until the payload exceeded 150 KB).

Two published decimals cannot see a day-count error of a day or two, so the inverted-formula value is asserted at ten places: 99.8782860147.

A SECOND VENDOR'S PUBLISHED FIGURE, on a second set of dates. LibreOffice's help (help.libreoffice.org/latest/en-US/text/scalc/01/04060118.html) prints '=ODDLPRICE("1999-02-07";"1999-06-15";"1998-10-15"; 0.0375; 0.0405;100;2;0) returns 99.87829' -- Microsoft's example shifted back exactly nine years, same month-and-day, same rates. Running the clean-room derivation on the 1999 dates gives 99.87828601472134..., identical to the 2008 answer, because every 30/360 day count in the calculation depends only on the month-and-day differences: 99.87829 at five places. Two independent vendors' documentation and one independent derivation agreeing on the same number is stronger evidence than any of the three alone, and this case is here to make that agreement executable.

Microsoft documents: "If rate < 0 or if yld < 0, ODDLPRICE returns the #NUM! error value."

Microsoft documents: "If basis < 0 or if basis > 4, ODDLPRICE returns the #NUM! error value."

Microsoft documents: "The following date condition must be satisfied; otherwise, ODDLPRICE returns the #NUM! error value: maturity > settlement > last_interest." Swapping the two puts settlement (2007-10-15) before the last interest date (2008-02-07).

Microsoft documents: "If settlement, maturity, or last_interest is not a valid date, ODDLPRICE returns the #VALUE! error value."

Microsoft publishes =ODDLPRICE(A2, A3, A4, A5, A6, A7, A8, A9) = $99.88 for a bond settled 2008-02-07, maturing 2008-06-15, last interest 2007-10-15, 3.75% coupon, 4.05% yield, redemption 100, semiannual, 30/360 basis. DERIVATION, clean-room. Microsoft publishes NO formula for ODDLPRICE -- the page has no 'calculated as follows' section and no equation image at all, which is the single biggest documentation gap in this batch -- and OpenFormula 1.3 section 6.12.33 gives only a parameter glossary. So the price here is obtained by ALGEBRAICALLY INVERTING the closed form Microsoft DOES publish, on the ODDLYIELD page, solving that equation for the price: pr = (redemption + (sum DCi/NLi)*100*rate/f) / (1 + (sum DSCi/NLi)*yld/f) - (sum Ai/NLi)*100*rate/f. For this bond the odd last period runs from the last interest date 2007-10-15 to maturity 2008-06-15 and spans two quasi-coupon periods on the semiannual grid, so on basis 0 the ratios are sum DCi/NLi = 240/180, sum Ai/NLi = 112/180 (2007-10-15 to settlement 2008-02-07) and sum DSCi/NLi = 128/180 (settlement to maturity). With a coupon of 100*0.0375/2 = 1.875 that gives (100 + 2.5)/(1 + (128/180)*0.02025) - 1.1666... = 99.87828601472134... The day counts come from a clean-room implementation of OpenFormula 1.3 section 4.11.7 -- Procedure A for US (NASD) 30/360 with its four order-dependent endpoint adjustments, Procedure B for actual days, Procedure C for European 30/360, and Procedures D/E/F for days-in-year -- written for this batch and used by no engine; the arithmetic is mpmath at 50 digits over exact rational day-count ratios. At two decimals that is 99.88 -- the published figure, reproduced from an inverted formula rather than copied. (Microsoft's own prose on this page says the arguments come from "cells A2:A10" while the formula it prints takes eight arguments from A2:A9; the same off-by-one appears on the ODDLYIELD page. It is a typo in the prose, not in the figure.) Microsoft's page was re-read live on 2026-08-31 at https://support.microsoft.com/en-us/excel/functions/oddlprice-function (the /en-us/office/<name>-function-<guid> path was returning Microsoft's 'Sorry, the page you're looking for can't be found' body throughout this batch, and the working path serves an 87 KB stub about half the time, so pages were fetched with retries until the payload exceeded 150 KB).

Two published decimals cannot see a day-count error of a day or two, so the inverted-formula value is asserted at ten places: 99.8782860147.

A SECOND VENDOR'S PUBLISHED FIGURE, on a second set of dates. LibreOffice's help (help.libreoffice.org/latest/en-US/text/scalc/01/04060118.html) prints '=ODDLPRICE("1999-02-07";"1999-06-15";"1998-10-15"; 0.0375; 0.0405;100;2;0) returns 99.87829' -- Microsoft's example shifted back exactly nine years, same month-and-day, same rates. Running the clean-room derivation on the 1999 dates gives 99.87828601472134..., identical to the 2008 answer, because every 30/360 day count in the calculation depends only on the month-and-day differences: 99.87829 at five places. Two independent vendors' documentation and one independent derivation agreeing on the same number is stronger evidence than any of the three alone, and this case is here to make that agreement executable.

Microsoft documents: "If rate < 0 or if yld < 0, ODDLPRICE returns the #NUM! error value."

Microsoft documents: "If basis < 0 or if basis > 4, ODDLPRICE returns the #NUM! error value."

Microsoft documents: "The following date condition must be satisfied; otherwise, ODDLPRICE returns the #NUM! error value: maturity > settlement > last_interest." Swapping the two puts settlement (2007-10-15) before the last interest date (2008-02-07).

Microsoft documents: "If settlement, maturity, or last_interest is not a valid date, ODDLPRICE returns the #VALUE! error value."