XL2000: ODDFYIELD Returns #NUM with Online Help Example (262339)


The information in this article applies to:

  • Microsoft Excel 2000
This article was previously published under Q262339

SYMPTOMS

When you view information about the ODDFYIELD worksheet function in Microsoft Excel Help, you see the following text: 
 The yield of a security that has an odd (short or long) first period is:

 ODDFYIELD("1/25/1999","1/1/2004","1/18/1999","7/15/1999",0.0575,084.50,100,2,0)
 equals .097581 or 9.76 percent
However, when you type this formula into Excel 2000, you receive the #NUM! error value.

NOTE: This same formula works as described in earlier versions of Excel.

CAUSE

ODDFYIELD cannot return a valid result when both the first and last coupon dates are odd.

RESOLUTION

Change the maturity argument in the ODDFYIELD worksheet function to "1/15/2004". This date change ensures that the last coupon date is not odd. The following formula equals 0.097581282, or 9.76 percent: 
=ODDFYIELD("1/25/1999","1/15/2004","1/18/1999","7/15/1999",0.0575,084.50,100,2,0)

MORE INFORMATION

Earlier versions of Excel incorrectly calculated a value for the ODDFYEILD function even when the last coupon date and the first coupon date were both odd. This problem was corrected in Excel 2000, but the example in Microsoft Excel Help was not corrected.

This behavior also applies to the following worksheet functions:
  • ODDFPRICE
  • ODDLPRICE
  • ODDLYIELD
For more information about the resources used to derive financial, statistical, and engineering methods, click Microsoft Excel Help on the Help menu, type a bibliography of financial, statistical, and engineering methods in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about ODDFYIELD, click Microsoft Excel Help on the Help menu, type ODDFYIELD Worksheet Function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

REFERENCES

Lynch, John J., Jr., and Jan H. Mayle. Standard Securities Calculation Methods, Fixed Income Securities Formulas. New York: Securities Industry Association, 1994.
Modification Type:MajorLast Reviewed:9/25/2003
Keywords:kbprb KB262339
©2003 Microsoft Corporation. All rights reserved.