Microsoft KB Archive/98065

{|
 * width="100%"|

XL: Maturity Date at Month End may Produce Incorrect Result

 * }

-

The information in this article applies to:


 * Microsoft Excel for Windows, versions 4.0, 4.0a, 5.0
 * Microsoft Excel for the Macintosh, version 4.0

-

SYMPTOMS
The Analysis ToolPak add-in in Microsoft Excel contains many financial, statistical and engineering functions. Some of the financial functions provided for security analysis, such as the COUPNUM PRICE and YIELD functions, may return the incorrect result when the date for the maturity argument falls on the last day of the month.

STATUS
Microsoft has confirmed this to be a problem in the Microsoft products listed at the beginning of this article. This problem was corrected in Microsoft Excel for Windows, version 5.0c, and Microsoft Excel for the Macintosh, version 5.0.

COUPNUM
COUPNUM will return the correct result if the maturity date is anything other than the last day of a month. As a result, if you extend the maturity date by one day, for example from 6/30/93 to 7/1/93, the correct number of coupons will be returned.

PRICE
PRICE returns the incorrect result when the maturity date occurs on the last day of a month and the settlement date occurs in the same month or a coupon month.

The PRICE function returns the correct result if the maturity is any date other than the last day of a month. However, since the price will vary from day to day, if you extend the maturity by one day, you'll get the price for that day, not the day you want. In this case, there is no workaround.

A workaround for PRICE is to average day-before, day and day-after for End-of-month maturity dates. This provides a linear trend for the estimated price; accurate to about 5 decimal places with tested data.