Microsoft KB Archive/119316

Microsoft Knowledge Base

Excel: YIELD Function Incorrectly Returns #NUM! Error Value

Last reviewed: December 17, 1996
Article ID: Q119316

The information in this article applies to:

• Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0, 5.0c
• Microsoft Excel for Windows 95, version 7.0
• Microsoft Excel 97 for Windows
• Microsoft Excel for the Macintosh, versions 4.0, 5.0, 5.0a

SYMPTOMS

In Microsoft Excel, when you use the YIELD() function, the #NUM! error value is returned, even if all of the values that are used in the formula are correct.

CAUSE

The Newton method, or iterative resolution of equations, is used in the YIELD() function calculation. It is possible that when you use this method a solution may not be found, even when a solution does exist.

This error occurs when the maturity date is just greater than one coupon period away from the settlement date.

For example, if the settlement date is 3/17/93, and the coupon period is one year (frequency=1), then the error value is returned if the maturity date is 3/18/94, or one day longer than a coupon period away from the settlement date.

STATUS

Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

The YIELD function is available only when you load or open the Analysis ToolPak add-in file, ANALYS32.XLL (in Microsoft Excel version 7.0 and 97), ANALYSIS.XLL (in Microsoft Excel version 5.0) or ANALYSF.XLA (Microsoft Excel version 4.0).

REFERENCES

"Standard Securities Calculation Methods, Fixed Income Securities Formulas," John J. Lynch, Jr.

For more information about YIELD, choose the Search button in Help and type:

    YIELD function

KBCategory: kbtool KBSubcategory: xladdins xlformula Additional reference words: 4.00 4.00a 5.00 7.00 97 atp addins THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY. Last reviewed: December 17, 1996 ©1997 Microsoft Corporation. All rights reserved. Legal Notices.