ODDFPRICE function solved mathematically

Hi,

I am trying to solve the ODDFPRICE function mathematically based on a set of parameters shown below in particular with the basis parameter set as US 30/360. I am able to solve the formula mathematically with frequency = 1 which when solved outputs the answer of 90.93640046. However for Frequency = 2 I am off by approx. .40 cents. Would anyone please provide the arithmetic with the actual numbers input to solve for the below function parameters. Output should be 88.91159635. This formula calculates the clean price of a bond with an odd first short/long coupon period. In this instance the first odd period is short.

Settlement Date 2010-10-15
Maturity Date 2017-11-30
Issue Date 2009-12-01
First Coupon Date 2010-11-30
Percent Coupon 2.75%
Yield 4.25%
Redemption Value 100.00
Frequency 2
Basis 0

Thank you.

Hello Streamer. Welcome to the forum.

Please post your actual formula ... or better yet a sample workbook with representative data and the formula in it.

Please bear in mind we are good at formulas, but not many of us are knowledgeable in finance.

Then I am confident someone can work out a solution.

Yes. Unfortunately I do not understand enough to contribute.

As of this moment there have been 7 views of the attachment. So others are looking at this as well. There is interest.

I will call for community help on this one as it appears to be highly specialized.

Sorry about the delay. Life happened!

The answer is very simple: Streamer made several mistakes.

The most serious mistake is: by choosing the same issue date, specifically 359 days before the first coupon, the semiannual example is a long first period, not a short first period like the annual example. As the ODDFPERIOD help page explains, a different formula applies to a long first period.

Another significant mistake is: using N=16 (and N-1=15) for the semiannual example. N=15 is correct. The semiannual formulas should be similar to the annual formulas, to wit:

C12: =COUPNUM(I1,I2,I8,I9)
C13: =C12-1

I did not verify that the long-first-period formula works with Streamer's semiannual example. But the following model demonstrates the documented formula for a short first period does indeed work for a short-first-period semiannual example as well as for a short-first-period annual example.

Unfortunately, I cannot post a table in this forum. Refer to the "ODDFPRICE 30360 (2)" worksheet in the attached Excel file.

The following formula (settlement date) ensures that the requirement settlement > issue date is adhered to, in case we experiment with other issue dates.

E1: =MAX(DATE(2010,10,15),E3+1)

The following formula (issue date) ensures that the issue date is at least 1 day after the quasi-coupon date (in J4) before the first coupon.

I3: =MAX(E3, J4+1+ISERROR(ODDFPRICE(I1,I2,J4+1,I4,I5,I6,I7,I8,I9)))
J4: =COUPPCD(I4-1,I4,I8,I9)

The ISERROR expression avoids an error that I discovered when freq=4 and basis=2. In that case, the issue date must be at least 2 days, not just 1 day, after the quasi-coupon date before the first coupon. Off-hand, I don't know why.

The PV(...,0) formula in E14 is mathematically equivalent to the NPV of the regular coupon payments, to wit:

E14: =PV(E6/E8,B13,-100*E5/E8,0,0) / (1+E6/E8)^B16

Alternatively, the following formula might be a more intuitive equivalent:

E14: =PV(B26,B24-1,-100*B25,0,1) / (1+B26)^(1+B28)

Either PV formula is more flexible than the SUM of the periodic PVs, which Streamer constructed -- although the latter could be made to be just as flexible.

The many other formula corrections are too numerious to explain. Generally, they are more versatile, allowing for changes in frequency and day-count basis.

With all of the changes, generally, my own "oddfprice" calculation matches the ODDFPRICE result within 15 significant digits for a variety of settlement/issue date and frequency/basis combinations.

With freq=4, generally, the different methods match up to 14 significant digits. Perhaps the difference is due to my use of PV in E14 instead of the sum of periodic PVs. (Speculation.)

However, with freq=4 and basis=1, the difference is in the 3rd decimal place(!). Off-hand, I don't know why.

Similarly large differences arise when the settlement or issue date is the end of a month, but not day 30, and the day-count basis is zero (30/360). Obviously, a defect in counting days. But I don't know if the error is in the various COUP* functions that I use or in ODDFPRICE.

joeu2004 my apologies. I thought I was responding to Streamer ... the thread owner.

My apologies. I got distracted by life; then so much time had elapased that I didn't know if you were still interested.

I cannot offer a good solution; I think it is best implemented in VBA.

What I provide is a proof of concept with some explanation.

For the discussion below, download the file "oddlprice excelforum.xlsx" from https://app.box.com/s/wyb64fte7i211fjqsxxm2h09sjpr08r2 . Ignore any preview errors, and just download. Also, you are not required to log into box.net/files first.

I tried to test the formulas extensively. Their results seem to be consistent with Excel ODDLPRICE. If you find an example that does not work, I would be happy to investigate it.

[Update: I do find differences with some combinations of dates, frequency and day-count basis. I believe the disparity has to do with date calculations involving leap years in the Excel ODDLPRICE and COUP* functions. When I manipulate the dates to avoid leap years and quasi-coup dates in or at the end of Feb, my results agree with Excel ODDLPRICE for the same frequency and day-count basis combinations.]

-----

[I have never been able to get the HTML tool to work. So I cannot create tables. Refer to the "ODDLPRICE (2)" worksheet in the Excel file.]

First, it is important to understand an arbitrary limitation of ODDLPRICE, namely: maturity > settlement > last coupon (paid). This simplifies the PV calculation insofar as there is only one payment (at maturity). That is why there is only one discount term in the mathematical formula that you found.

In contrast, if we allow for last coupon > settlement, there would be multiple discount terms, similar to NPV. For example, see http://westclintech.com/Blog/tabid/1...st-Coupon.aspx .

Second, the following is an improved description of the variables in the mathematical formula that you found:

Please Login or Register  to view this content.

The "i" refers to the quasi-coup period number following the last coupon (paid).

However, I cannot see any reason for NLi. I believe it should be the same for all periods. Therefore, I use a constant "NL".

[Update: In hindsight, that might be the reason why my calculation does not agree with Excel ODDLPRICE when leap years are involved. Arguably, NLi might be different for periods including Feb for some combinations of frequency and day-count basis. "The refinement is left as an exercise for the student". (wink)]

The following discussion is for your Example #1. Similar forumlas apply to your Example #2.

You had set up the following input values:

Please Login or Register  to view this content.

I set up the following additional helper values:

Please Login or Register  to view this content.

Note the "creative" interpretation of the "settlement" and "maturity" parameters for the COUP* functions. That is the key to the correct calculations of those values and dates for all combinations of frequency and day-count basis.

An important difference with ODDLPRICE calculations is: coup dates are determined forward from last coup date, not back from maturity date. This is because with an odd last period, the maturity date is typically not a regular coupon date (unlike your example).

In contrast, with other bond calculations and with Excel bond functions, the maturity date is assumed to be a regular coupon date. Consequently, other coupon dates can be calculated by counting back from the maturity date, depending on the day-count basis.

That difference is reflected in B13: we need to determine the end of the quasi-coupon period that contains the maturity date. Ostensibly, that is determined by treating the maturity date as the "settlement" date in the COUPNCD function, and by creating a fictitious "maturity" date, which is the last-coup date in the year following the maturity date. The COUPPCD(COUPNCD)=B4 logic handles the special case when the maturity date is a regular coupon date.

The following demonstrates my ODDLPRICE calculation. It relies on a table of helper cells in A25:E57, described below.

Please Login or Register  to view this content.

"Num" (numerator), "denom" (denominator) and "accr int" (accrued interest) refer to the terms of the mathematical formula that you found.

The real "magic" is performed in the table below, which determines Ai, DCi and DSCi for each quasi-coup period.

Please Login or Register  to view this content.

Ai is the number of days of accrued interest between the last coup (paid) date and the settlement date. For the quasi-coup period that contains the settlement date, it is the number of days between the previous quasi-coup date and the settlement date. Otherwise, Ai is the full number of days in quasi-coup periods before the settlement period; and it is zero for quasi-coup periods after the settlement period.

DCi is the total number of days of interest (including accrued interest) between the last coup (paid) date and the maturity date. For the quasi-coup period that contains the maturity date, it is the number of days between the previous quasi-coup date and the maturity date. (We must make a special case for when the maturity date is a quasi-coup date, due to an arbitrary limitation of COUPDAYBS.) Otherwise, DCi is the full number of days in each quasi-coup period.

DSCI is the number of days that the payment at maturity is discounted back to the settlement date. Ostensibly, it is the full number of days in quasi-coup periods between the settlement and maturity periods. For the settlement period, DCi is the number of days between the settlement date and the next quasi-coup date. For the maturity period, it is the number of days between the previous quasi-coup date and the maturity date. A special case arises when the settlement and maturity dates are in the same quasi-coup period. The construction of the DCi calculation handles both the normal and the special cases automagically.

SIGN(...)+2 results in the CHOOSE indexes 1, 2 and 3 for the conditions B25<B14, B25=B14 and B25>B14 respectively.

I hope that helps to understand how the Excel ODDLPRICE implements the mathematical formula, at least conceptually.

(I do not have any knowledge about how Excel ODDLPRICE is actually implemented.)

Streamer, thank you for alerting us about possible cross posts.

If you do so please posts links to those cross posts back here in this thread as well as in the other forums. It is a forum rule here as well as in other forums.

Please understand that contributors knowledgeable may not be currently logged on. It is the downside of free forums "staffed" by volunteers. So please check back from time to time.
When you do find solution as a courtesy please post back to all forums with link to the solution and mark threads Solved in all the cross posts.

I'm sure you understand. Thank you in advance.