XL: Maturity Date at Month End may Produce Incorrect Result

• 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.

WORKAROUNDS

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.

REFERENCES

"Function Reference," version 4.0, pages 73-74