Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel PRICE function: How does it compute Issue Date of the Security
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, |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel PRICE function: How does it compute Issue Date of the Security
"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 That is your first mistake: reading Microsoft documentation. :-) :-) Just kidding! But the point is: don't try to read too much into Microsoft documentation. Often they are full of errors, irrelevancies and poor examples. "PJ Hooker" wrote: It would seem that function call does not require an input for Issue Date of the security Correct. "PJ Hooker" wrote: 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. Read it again. The definition of "A" does not refer to the issue date, nor should it. It does refer to the "beginning of the coupon period". That is not necessarily the issue date. In the design of the PRICE function, the assumption appears to be: we purchased the security in the secondary market some time after the issue date. Ergo, the interest earned from the previous coupon date to the settlement date is due to the previous owner, thereby reducing the imputed price. In order to determine "beginning of the coupon period", presumably the PRICE function counts back from the maturity date parameter, using the frequency parameter to determine coupon dates. "PJ Hooker" wrote: 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 I cannot really speak for the non-finance English major who wrote the Microsoft documentation. :-) I think the point of that paragraph is simply to define terminology; in this case, to make it clear that the settlement date is not necessarily the issue date. "PJ Hooker" wrote: 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 It doesn't. The paragraph is stating facts that we might know a priori, not necessarily information that the PRICE needs to know or determines. "PJ Hooker" wrote: 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 As explained, the issue date is not a factor; only the last coupon date. Based on a semiannual coupon frequency (as stated in the example), the PRICE function would determine that the last coupon date is 15-Nov-2007, thus (conceptually): A1: =EDATE(A3,-ROUNDUP(DATEDIF(A2,A3,"m")/6,0)*6) A2: 15-Feb-2008 A3: 15-Nov-2017 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel PRICE function: How does it compute Issue Date of the Security
"PJ Hooker" wrote:
Never mind, sorry to have bothered you all. [....] It would seem that Issue date was just mentioned as a reference and is not required for PRICE calculation of a security You got it! Hmm, did it really take me an hour to write my lengthier explanation of the same conclusions? Or is you computer clock off? Or did it just take that long for your follow-up posting to propagate to my news server? Rhetorical questions. Just wanted to explain that the only reason I seemed to have posted the same conclusions an hour later was because your follow-up had not appeared on my news server when I started writing. I did not intend to post a "me, too" response. :-) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel PRICE function: How does it compute Issue Date of the Security
On Wednesday, June 20, 2012 8:01:24 PM UTC+5, joeu2004 wrote:
"PJ Hooker" wrote: Never mind, sorry to have bothered you all. [....] It would seem that Issue date was just mentioned as a reference and is not required for PRICE calculation of a security You got it! Hmm, did it really take me an hour to write my lengthier explanation of the same conclusions? Or is you computer clock off? Or did it just take that long for your follow-up posting to propagate to my news server? I posted my original question at around 5AM pacific daylight time then wandered out to a local market to have a daily meal of a hamburger. Whilst having my meal, I realized that issue date had no effect on the price and the wording of the article on Microsoft was bit confusing Then got back home at around 7AM pacific daylight time and posted my observations. I use Google Groups to post on NewsGroups so I see my posts show up within a 5 to 10 minutes delay Rhetorical questions. Just wanted to explain that the only reason I seemed to have posted the same conclusions an hour later was because your follow-up had not appeared on my news server when I started writing. I did not intend to post a "me, too" response. :-) No worries Joe, your answers were right on the point :) Joe, do you work for Microsoft??? As I noticed you contribute a lot on Microsoft Answers and Microsoft's NewsGroups [ That is your first mistake: reading Microsoft documentation. :-) :-) Just kidding! But the point is: don't try to read too much into Microsoft documentation. Often they are full of errors, irrelevancies and poor examples. I cannot really speak for the non-finance English major who wrote the Microsoft documentation. :-) ] A while ago I landed on one of Microsoft's documentation page for Excel. The article was revision of on older article yet the revisionist didn't bother to do the investigation of the topic. I am not going to show you the link to it, it will look very bad for Microsoft for someone to read that and knowing that it is plain wrong. In any case if you are curious Google this phrase "compound amount excel" without the quotes and follow the link to Microsoft Support site that is the first search result BTW, I am building my own financial functions library for Excel (you can say reinventing the wheel but it is fun learning) but I am really short on time and it is very hot here so I only get to develop one function in four days after understanding the formula (if one exists) or the numerical method as is the case with Excel's YIELD function for which one has to understand Excel's PRICE function thus my original query Thanks Joe :) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Excel PRICE function: How does it compute Issue Date of the Security
"PJ Hooker" wrote:
Joe, do you work for Microsoft??? As I noticed you contribute a lot on Microsoft Answers and Microsoft's NewsGroups No. These are so-called peer-to-peer forums: users helping users. I don't believe you will find any Microsoft employees in the newsgroups, especially since MS abandoned the newsgroups a year or two ago. As for the Answers Forum, the only MS "employees" that participate (on rare occassion) are contractors identified as "support engineers" (no "MICROSOFT" identifier). For the most part, they seem to know very little about Excel in general; and I am quite sure they know nothing about specific functions. Their responses are the typical boilerplate "assistance" (I use the term advisedly) that you would expect from online and telephone help-line folks. Useless at best, IMHO; often a misdirection. Just ignore them. Arguing with their useless information is pointless: they don't seem to know enough to offer an educated counter-point. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Security Issue with Excel 2003 | Excel Programming | |||
base price list compute on to another price list? on excel work sh | Excel Discussion (Misc queries) | |||
Date function/Text issue | Excel Worksheet Functions | |||
Excel 2002 Security Issue | New Users to Excel | |||
COMPUTE CHANGE IN PRICE BETWEEN CELLS IN A ROW | Excel Programming |