YIELD

Microsoft publishes =YIELD(A2,A3,A4,A5,A6,A7,A8) with the description "The yield, for the bond with the terms above (0.065 or 6.5%)" and a Result cell formatted as "6.5%". Settlement 2008-02-15 (serial 39493), maturity 2016-11-15 (serial 42689), 5.75% coupon, price 95.04287, redemption 100, semiannual, 30/360 basis. DERIVATION: the page documents the >1-period case as "calculated through a hundred iterations... the Newton method, based on the formula used for the function PRICE", so the yield was obtained by ROOT-FINDING the batch-F clean-room PRICE implementation -- price = redemption/(1+y/f)^(N-1+DSC/E) + sum over k of 100*(rate/f)/(1+y/f)^(k-1+DSC/E) - 100*(rate/f)*(A/E), evaluated cash flow by cash flow, with N = 18 semiannual coupons, A = DSC = 90 and E = 180 on basis 0. Day counts come from a clean-room implementation of OpenFormula 1.3 section 4.11.7 (Procedure A for US 30/360, Procedure B for actual days, Procedure C for European 30/360, D/E/F for days-in-year) written for this batch and used by no engine; the bond arithmetic is evaluated over exact rational day-count ratios at 50 digits with mpmath. The root is 0.06500000688075461063326 -- NOT exactly 0.065, because the published price 95.04287 is itself a five-decimal rounding of the true 95.04287439939201656339.

THE POINT OF THIS CASE IS THAT THE ANSWER IS NOT 0.065. Microsoft's published price is rounded to five decimals, so the yield that reproduces it is 0.0650000068807546, which is 0.06500001 at eight places. An engine asserted against a naive 0.06500000 would be recorded as diverging when it is in fact correct; the derivation is what establishes which of the two is right. Carried no further than eight places because YIELD is documented as an ITERATIVE Newton solve with a hundred-iteration cap, so its last few digits are a property of the solver's tolerance rather than of the mathematics -- see the round-trip case below, which tests the solver against the engine's own PRICE instead.

YIELD and PRICE are documented inverses -- the YIELD page says its solver is "based on the formula used for the function PRICE". Feeding the engine's OWN unrounded price back into YIELD must return the yield it started from, exactly 0.065, with no five-decimal rounding in the way. A structural assertion with no derived constant, and the sharpest test available of an iterative function: it is independent of the solver's absolute accuracy and tests only that the two functions agree with each other. The clean-room derivation confirms the composition is well posed -- PRICE at yield 0.065 is 95.04287439939201656339 and root-finding that price returns 0.065 to 50 digits.

The Remarks publish: "If settlement >= maturity, YIELD returns the #NUM! error value."

The Remarks publish: "If frequency is any number other than 1, 2, or 4, YIELD returns the #NUM! error value."

The Remarks publish: "If basis < 0 or if basis > 4, YIELD returns the #NUM! error value." The basis table runs 0 = US (NASD) 30/360, 1 = Actual/actual, 2 = Actual/360, 3 = Actual/365, 4 = European 30/360.

The Remarks publish: "If pr <= 0 or if redemption <= 0, YIELD returns the #NUM! error value."

Microsoft publishes =YIELD(A2,A3,A4,A5,A6,A7,A8) with the description "The yield, for the bond with the terms above (0.065 or 6.5%)" and a Result cell formatted as "6.5%". Settlement 2008-02-15 (serial 39493), maturity 2016-11-15 (serial 42689), 5.75% coupon, price 95.04287, redemption 100, semiannual, 30/360 basis. DERIVATION: the page documents the >1-period case as "calculated through a hundred iterations... the Newton method, based on the formula used for the function PRICE", so the yield was obtained by ROOT-FINDING the batch-F clean-room PRICE implementation -- price = redemption/(1+y/f)^(N-1+DSC/E) + sum over k of 100*(rate/f)/(1+y/f)^(k-1+DSC/E) - 100*(rate/f)*(A/E), evaluated cash flow by cash flow, with N = 18 semiannual coupons, A = DSC = 90 and E = 180 on basis 0. Day counts come from a clean-room implementation of OpenFormula 1.3 section 4.11.7 (Procedure A for US 30/360, Procedure B for actual days, Procedure C for European 30/360, D/E/F for days-in-year) written for this batch and used by no engine; the bond arithmetic is evaluated over exact rational day-count ratios at 50 digits with mpmath. The root is 0.06500000688075461063326 -- NOT exactly 0.065, because the published price 95.04287 is itself a five-decimal rounding of the true 95.04287439939201656339.

THE POINT OF THIS CASE IS THAT THE ANSWER IS NOT 0.065. Microsoft's published price is rounded to five decimals, so the yield that reproduces it is 0.0650000068807546, which is 0.06500001 at eight places. An engine asserted against a naive 0.06500000 would be recorded as diverging when it is in fact correct; the derivation is what establishes which of the two is right. Carried no further than eight places because YIELD is documented as an ITERATIVE Newton solve with a hundred-iteration cap, so its last few digits are a property of the solver's tolerance rather than of the mathematics -- see the round-trip case below, which tests the solver against the engine's own PRICE instead.

YIELD and PRICE are documented inverses -- the YIELD page says its solver is "based on the formula used for the function PRICE". Feeding the engine's OWN unrounded price back into YIELD must return the yield it started from, exactly 0.065, with no five-decimal rounding in the way. A structural assertion with no derived constant, and the sharpest test available of an iterative function: it is independent of the solver's absolute accuracy and tests only that the two functions agree with each other. The clean-room derivation confirms the composition is well posed -- PRICE at yield 0.065 is 95.04287439939201656339 and root-finding that price returns 0.065 to 50 digits.

The Remarks publish: "If settlement >= maturity, YIELD returns the #NUM! error value."

The Remarks publish: "If frequency is any number other than 1, 2, or 4, YIELD returns the #NUM! error value."

The Remarks publish: "If basis < 0 or if basis > 4, YIELD returns the #NUM! error value." The basis table runs 0 = US (NASD) 30/360, 1 = Actual/actual, 2 = Actual/360, 3 = Actual/365, 4 = European 30/360.

The Remarks publish: "If pr <= 0 or if redemption <= 0, YIELD returns the #NUM! error value."

Microsoft publishes =YIELD(A2,A3,A4,A5,A6,A7,A8) with the description "The yield, for the bond with the terms above (0.065 or 6.5%)" and a Result cell formatted as "6.5%". Settlement 2008-02-15 (serial 39493), maturity 2016-11-15 (serial 42689), 5.75% coupon, price 95.04287, redemption 100, semiannual, 30/360 basis. DERIVATION: the page documents the >1-period case as "calculated through a hundred iterations... the Newton method, based on the formula used for the function PRICE", so the yield was obtained by ROOT-FINDING the batch-F clean-room PRICE implementation -- price = redemption/(1+y/f)^(N-1+DSC/E) + sum over k of 100*(rate/f)/(1+y/f)^(k-1+DSC/E) - 100*(rate/f)*(A/E), evaluated cash flow by cash flow, with N = 18 semiannual coupons, A = DSC = 90 and E = 180 on basis 0. Day counts come from a clean-room implementation of OpenFormula 1.3 section 4.11.7 (Procedure A for US 30/360, Procedure B for actual days, Procedure C for European 30/360, D/E/F for days-in-year) written for this batch and used by no engine; the bond arithmetic is evaluated over exact rational day-count ratios at 50 digits with mpmath. The root is 0.06500000688075461063326 -- NOT exactly 0.065, because the published price 95.04287 is itself a five-decimal rounding of the true 95.04287439939201656339.

THE POINT OF THIS CASE IS THAT THE ANSWER IS NOT 0.065. Microsoft's published price is rounded to five decimals, so the yield that reproduces it is 0.0650000068807546, which is 0.06500001 at eight places. An engine asserted against a naive 0.06500000 would be recorded as diverging when it is in fact correct; the derivation is what establishes which of the two is right. Carried no further than eight places because YIELD is documented as an ITERATIVE Newton solve with a hundred-iteration cap, so its last few digits are a property of the solver's tolerance rather than of the mathematics -- see the round-trip case below, which tests the solver against the engine's own PRICE instead.

YIELD and PRICE are documented inverses -- the YIELD page says its solver is "based on the formula used for the function PRICE". Feeding the engine's OWN unrounded price back into YIELD must return the yield it started from, exactly 0.065, with no five-decimal rounding in the way. A structural assertion with no derived constant, and the sharpest test available of an iterative function: it is independent of the solver's absolute accuracy and tests only that the two functions agree with each other. The clean-room derivation confirms the composition is well posed -- PRICE at yield 0.065 is 95.04287439939201656339 and root-finding that price returns 0.065 to 50 digits.

The Remarks publish: "If settlement >= maturity, YIELD returns the #NUM! error value."

The Remarks publish: "If frequency is any number other than 1, 2, or 4, YIELD returns the #NUM! error value."

The Remarks publish: "If basis < 0 or if basis > 4, YIELD returns the #NUM! error value." The basis table runs 0 = US (NASD) 30/360, 1 = Actual/actual, 2 = Actual/360, 3 = Actual/365, 4 = European 30/360.

The Remarks publish: "If pr <= 0 or if redemption <= 0, YIELD returns the #NUM! error value."