View Single Post
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
PJ Hooker PJ Hooker is offline
external usenet poster
 
Posts: 15
Default Excel PRICE function: How does it compute Issue Date of the Security

On Wednesday, June 20, 2012 4:53:19 PM UTC+5, PJ Hooker wrote:
In reading the description of Excel's PRICE function at Microsoft's site, here at http://office.microsoft.com/en-us/ex...005209219.aspx

It would seem that function call does not require an input for Issue Date of the security, yet in its calculation of PRICE of the security it does use a value for Issue Date of the security as it is inferred from the description of variable "A" in the PRICE formula as stated below

A = number of days from beginning of coupon period to settlement date.

On the same page it is stated

The settlement date is the date a buyer purchases a coupon, such as a bond. The maturity date is the date when a coupon expires. For example, suppose a 30-year bond is issued on January 1, 2008, and is purchased by a buyer six months later. The issue date would be January 1, 2008, the settlement date would be July 1, 2008, and the maturity date would be January 1, 2038, which is 30 years after the January 1, 2008, issue date.

Why is it referring to an issue date of Jan 1, 2008 when all you are asked to provide to the PRICE function is the settlement date if July 1, 2008 and Maturity Date of Jan 1, 2038

So how would Excel come up with Jan 1, 2008 as the Issue Date as nowhere to the function have we stated that the bonds life is 30 years

And on the same page there is an example calculation, what Issue Date is assumed here, is it 15-Nov-2007 or 15-Nov-1997 or 15-Nov-1987

Data ######## Description
15-Feb-2008 #### Settlement date
15-Nov-2017 #### Maturity date
5.75% ######## Percent semiannual coupon
6.50% ######## Percent yield
$100 ######## Redemption value
2 ############ Frequency is semiannual (see above)
0 ############ 30/360 basis (see above)
Formula ##### Description (Result)
94.63436162 ## The bond price,


Never mind, sorry to have bothered you all.

It's just that half my brain is dead due to long exposure to chemicals they fed me

It would seem that Issue date was just mentioned as a reference and is not required for PRICE calculation of a security

The variable "A" I referred to is the number of days prior to settlement date and the previous coupon payment date in case these two are different, the part of the interest, paid to the former owner of the security, is deducted from the price of the security

And the rest of the formula is no different than the sum of discounted interest payments and discounted maturity value of the bond. Albeit it ensures that if the settlement date did not coincide with coupon payment date then any remaining part of the interest is added to the final price of the security

Now let me go back to my den and program the bond functions for my own collection