Excel PRICE function: How does it compute Issue Date of the Security

[PJ Hooker]

In reading the description of Excel's PRICE function at Microsoft's site, here at http://office.microsoft.com/en-us/excel-help/price-HP005209219.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 usea 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 andMaturity Date of Jan 1, 2038

So how would Excel come up with Jan 1, 2008 as the Issue Date as nowhere tothe 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,

 

[PJ Hooker]

In reading the description of Excel's PRICE function at Microsoft's site,here at http://office.microsoft.com/en-us/excel-help/price-HP005209219.aspx

It would seem that function call does not require an input for Issue Dateof 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 theyfed 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

 

[joeu2004]

In reading the description of Excel's PRICE function
at Microsoft's site, here at
http://office.microsoft.com/en-us/excel-help/price-HP005209219.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.

It would seem that function call does not require an
input for Issue Date of the security

Correct.

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.

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.

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.

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

 

[joeu2004]

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.

 

[PJ Hooker]

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

[]

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 linkto 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

 

[joeu2004]

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.