Why does Yieldmat include an issue date ?
The yield to maturity should be from the settlement date to the maturity
date, subject to the rate of interest and the price of the bond

It does not matter when in the past it was issued

However I see that changing the issue date changes the yield to maturity

Help please

Based on MS Help, the rate and term are based on date of issue (not date of
settlement). So, if the security was issues on 1/1/08 on a 30 year bond, the
maturity date is 1/1/38, regardless of settlement date.

"BarryHWhite" wrote:

Why does Yieldmat include an issue date ?
The yield to maturity should be from the settlement date to the maturity
date, subject to the rate of interest and the price of the bond

It does not matter when in the past it was issued

However I see that changing the issue date changes the yield to maturity

Help please

I think that the Date of Settlement was the date I purchased the bond.
Nothing to do with the original issue date, nor the maturity date

The parameters are
Syntax: YIELDMAT(S, M, I, R, PR[, B])

S = settlement date
M = maturity date
I = issue date
R = interest rate at date of issue
PR = the price of the security per $100 face value
B = (Optional) the day count basis to be used:

0 or omitted 30/360

So
My question remains
Example (dates UK format dd mm yy)

Yield to maturity 10.00%
Sett Date 31/12/2009
Maturity 31/12/2010
Issue Date 30/12/2009
Coupon yearly 10.000%
Price now 100.00
Days basis 3

But if the Issue date changes you get

Yield to maturity 9.09%
Sett Date 31/12/2009
Maturity 31/12/2010
Issue Date 31/12/2008
Coupon yearly 10.000%
Price now 100.00
Days basis 3

Which is wrong - you are paying out 100 now at 10% and will get 110 in one
year, so the yield to maturity should be 10% - does not matter when the bond
was issued







"Sean Timmons" wrote:

Based on MS Help, the rate and term are based on date of issue (not date of
settlement). So, if the security was issues on 1/1/08 on a 30 year bond, the
maturity date is 1/1/38, regardless of settlement date.

"BarryHWhite" wrote:

Why does Yieldmat include an issue date ?
The yield to maturity should be from the settlement date to the maturity
date, subject to the rate of interest and the price of the bond

It does not matter when in the past it was issued

However I see that changing the issue date changes the yield to maturity

Help please

I must disagree.

the bond has achieved a portion of maturity prior to settlement. In the
below examples, 1 day and 1 year respectively. The clear indication is that
those days you do not have the bond in "possession" do not accumulate
interest in your name. (Please note, in the first example above, the return
is not precisely 100%, though the change occurs at a third decimal). Hope
thsi helps!

"BarryHWhite" wrote:

I think that the Date of Settlement was the date I purchased the bond.
Nothing to do with the original issue date, nor the maturity date

The parameters are
Syntax: YIELDMAT(S, M, I, R, PR[, B])

S = settlement date
M = maturity date
I = issue date
R = interest rate at date of issue
PR = the price of the security per $100 face value
B = (Optional) the day count basis to be used:

0 or omitted 30/360

So
My question remains
Example (dates UK format dd mm yy)

Yield to maturity 10.00%
Sett Date 31/12/2009
Maturity 31/12/2010
Issue Date 30/12/2009
Coupon yearly 10.000%
Price now 100.00
Days basis 3

But if the Issue date changes you get

Yield to maturity 9.09%
Sett Date 31/12/2009
Maturity 31/12/2010
Issue Date 31/12/2008
Coupon yearly 10.000%
Price now 100.00
Days basis 3

Which is wrong - you are paying out 100 now at 10% and will get 110 in one
year, so the yield to maturity should be 10% - does not matter when the bond
was issued







"Sean Timmons" wrote:

Based on MS Help, the rate and term are based on date of issue (not date of
settlement). So, if the security was issues on 1/1/08 on a 30 year bond, the
maturity date is 1/1/38, regardless of settlement date.

"BarryHWhite" wrote:

Why does Yieldmat include an issue date ?
The yield to maturity should be from the settlement date to the maturity
date, subject to the rate of interest and the price of the bond

It does not matter when in the past it was issued

However I see that changing the issue date changes the yield to maturity

Help please

I wonder if I have misunderstood what the formula does

I am assuming that interest on the bond is paid out annually, so if I buy
the bond on the anniversery of the issue date, interest on the period before
my purchase is not
an issue

My first example involves cash flows as follows
-100 Sett Date (when I buy the bond) 31/12/2009
+110 Maturity (when the bond terminates and pays capital and one years
interest 31/12/2010
Clearly here the return is 10%
and the bond issue date is not relevant

The reason I showed issue date as 1 day before settlement date is the
formula will not accept the same issue date and settlement date

Is the formula assuming that no interest is paid out, but accumulates in the
bond - if so, I see that this will give a different result depending on issue
date

Can you tell me the underlying calculation for the YieldMat function?

If this is the case, is there a formula which assumes interest is paid out
and calculates the cash flows from settlement date forward - or should I use
the formula with issue date one day before settlement, which (apart from the
tiny interest difference) gives me an answer the same as I would get using
IRR with the cash flows.

Rgds


"Sean Timmons" wrote:

I must disagree.

the bond has achieved a portion of maturity prior to settlement. In the
below examples, 1 day and 1 year respectively. The clear indication is that
those days you do not have the bond in "possession" do not accumulate
interest in your name. (Please note, in the first example above, the return
is not precisely 100%, though the change occurs at a third decimal). Hope
thsi helps!

"BarryHWhite" wrote:

I think that the Date of Settlement was the date I purchased the bond.
Nothing to do with the original issue date, nor the maturity date

The parameters are
Syntax: YIELDMAT(S, M, I, R, PR[, B])

S = settlement date
M = maturity date
I = issue date
R = interest rate at date of issue
PR = the price of the security per $100 face value
B = (Optional) the day count basis to be used:

0 or omitted 30/360

So
My question remains
Example (dates UK format dd mm yy)

Yield to maturity 10.00%
Sett Date 31/12/2009
Maturity 31/12/2010
Issue Date 30/12/2009
Coupon yearly 10.000%
Price now 100.00
Days basis 3

But if the Issue date changes you get

Yield to maturity 9.09%
Sett Date 31/12/2009
Maturity 31/12/2010
Issue Date 31/12/2008
Coupon yearly 10.000%
Price now 100.00
Days basis 3

Which is wrong - you are paying out 100 now at 10% and will get 110 in one
year, so the yield to maturity should be 10% - does not matter when the bond
was issued







"Sean Timmons" wrote:

Based on MS Help, the rate and term are based on date of issue (not date of
settlement). So, if the security was issues on 1/1/08 on a 30 year bond, the
maturity date is 1/1/38, regardless of settlement date.

"BarryHWhite" wrote:

Why does Yieldmat include an issue date ?
The yield to maturity should be from the settlement date to the maturity
date, subject to the rate of interest and the price of the bond

It does not matter when in the past it was issued

However I see that changing the issue date changes the yield to maturity

Help please

Yeah, YIELDMAT is really for people purchasing a bond after it's been issues
to another. In these cases, a portion of the interest is held by the prior
owner of the bond, and the remaining goes to you. Bonds typically mature at
longer increments (10, 20, 30 years), so it's a bigger issue in those
situations.

you may want YIELDDISC()

"BarryHWhite" wrote:

I wonder if I have misunderstood what the formula does

I am assuming that interest on the bond is paid out annually, so if I buy
the bond on the anniversery of the issue date, interest on the period before
my purchase is not
an issue

My first example involves cash flows as follows
-100 Sett Date (when I buy the bond) 31/12/2009
+110 Maturity (when the bond terminates and pays capital and one years
interest 31/12/2010
Clearly here the return is 10%
and the bond issue date is not relevant

The reason I showed issue date as 1 day before settlement date is the
formula will not accept the same issue date and settlement date

Is the formula assuming that no interest is paid out, but accumulates in the
bond - if so, I see that this will give a different result depending on issue
date

Can you tell me the underlying calculation for the YieldMat function?

If this is the case, is there a formula which assumes interest is paid out
and calculates the cash flows from settlement date forward - or should I use
the formula with issue date one day before settlement, which (apart from the
tiny interest difference) gives me an answer the same as I would get using
IRR with the cash flows.

Rgds


"Sean Timmons" wrote:

I must disagree.

the bond has achieved a portion of maturity prior to settlement. In the
below examples, 1 day and 1 year respectively. The clear indication is that
those days you do not have the bond in "possession" do not accumulate
interest in your name. (Please note, in the first example above, the return
is not precisely 100%, though the change occurs at a third decimal). Hope
thsi helps!

"BarryHWhite" wrote:

I think that the Date of Settlement was the date I purchased the bond.
Nothing to do with the original issue date, nor the maturity date

The parameters are
Syntax: YIELDMAT(S, M, I, R, PR[, B])

S = settlement date
M = maturity date
I = issue date
R = interest rate at date of issue
PR = the price of the security per $100 face value
B = (Optional) the day count basis to be used:

0 or omitted 30/360

So
My question remains
Example (dates UK format dd mm yy)

Yield to maturity 10.00%
Sett Date 31/12/2009
Maturity 31/12/2010
Issue Date 30/12/2009
Coupon yearly 10.000%
Price now 100.00
Days basis 3

But if the Issue date changes you get

Yield to maturity 9.09%
Sett Date 31/12/2009
Maturity 31/12/2010
Issue Date 31/12/2008
Coupon yearly 10.000%
Price now 100.00
Days basis 3

Which is wrong - you are paying out 100 now at 10% and will get 110 in one
year, so the yield to maturity should be 10% - does not matter when the bond
was issued







"Sean Timmons" wrote:

Based on MS Help, the rate and term are based on date of issue (not date of
settlement). So, if the security was issues on 1/1/08 on a 30 year bond, the
maturity date is 1/1/38, regardless of settlement date.

"BarryHWhite" wrote:

Why does Yieldmat include an issue date ?
The yield to maturity should be from the settlement date to the maturity
date, subject to the rate of interest and the price of the bond

It does not matter when in the past it was issued

However I see that changing the issue date changes the yield to maturity

Help please

"BarryHWhite" wrote:
is there a formula which assumes interest is paid out
and calculates the cash flows from settlement date forward

Use YIELD().

Although in bond parlance, there is usually a difference between the terms
(current) "yield" and "yield to maturity", apparently the Excel YIELD()
returns YTM, at least when the bond is held for more than one coupon period.

This is self-evident if you compare the results of YIELD() and IRR() or
RATE() in some simple cases, for example a bond held from 1/1/2009 through
1/1/2012 priced at $90 (per $100 face value) with semi-annual coupons at the
rate of 5%.

Caveat emptor: I cannot vouch for the results of YIELD() for bonds held for
one coupon period or less, or for bonds with maturity dates that are not
multple of coupon periods (!). Moreover, I have not tried to use YIELD()
for bonds that pay all the interest at maturity, although I suspect we can
make it return something reasonable by fudging the "redemption" parameter.

PS: I have also not tried to use YIELD() for bonds that are purchased after
the issue date -- ergo, we usually need to pay some accrued unpaid interest
for part of the coupon period that includes the settlement date. But
similarly, I suspect we can make it return something reasonable by fudging
the "pr" (price) parameter.

Like you, I have not had much success with YIELDMAT(), and it is unclear
what its algorithm is, notwithstanding a formula that some participants
posted in response to my inquiry in another thread.

I not yet had a chance to fully experiment with YIELD(). But it looks
promising since it returns results that are consistent with IRR() and RATE()
in circumstances that are conducive to using those functions; and those
results are very different from the YIELDMAT() results, which I believe are
incorrect in those circumstances.

FYI, it is not surprising for any valid YTM function or formula to return
results that differ from XIRR(), even when the date count mode ("basis") is
actual/actual.

The differences are due, at least in part, to apparently widely-accepted
conventions for determining YTM when the coupon frequency is more than once
per year. Also, there seems to be a wide variety of interpretations or
"rules of thumbs" that affect the details of the YTM formulation, especially
when fractional coupon periods are involved. Even the HP 12C formula is
questionable, IMHO.

But I digress. I tried to initiate an academic discussion of all this in my
thread ("Please explain YIELDMAT function"), but to no avail so far.
However, last weekend was a holiday in both the US and Canada, and some of
the participants who have demonstrated financial wizardry in the past might
still be on vacations or just returning from them.

In the meantime, I hope my comments help.


----- original message -----

"BarryHWhite" wrote in message
...
I wonder if I have misunderstood what the formula does

I am assuming that interest on the bond is paid out annually, so if I buy
the bond on the anniversery of the issue date, interest on the period
before
my purchase is not
an issue

My first example involves cash flows as follows
-100 Sett Date (when I buy the bond) 31/12/2009
+110 Maturity (when the bond terminates and pays capital and one years
interest 31/12/2010
Clearly here the return is 10%
and the bond issue date is not relevant

The reason I showed issue date as 1 day before settlement date is the
formula will not accept the same issue date and settlement date

Is the formula assuming that no interest is paid out, but accumulates in
the
bond - if so, I see that this will give a different result depending on
issue
date

Can you tell me the underlying calculation for the YieldMat function?

If this is the case, is there a formula which assumes interest is paid out
and calculates the cash flows from settlement date forward - or should I
use
the formula with issue date one day before settlement, which (apart from
the
tiny interest difference) gives me an answer the same as I would get using
IRR with the cash flows.

Rgds


"Sean Timmons" wrote:

I must disagree.

the bond has achieved a portion of maturity prior to settlement. In the
below examples, 1 day and 1 year respectively. The clear indication is
that
those days you do not have the bond in "possession" do not accumulate
interest in your name. (Please note, in the first example above, the
return
is not precisely 100%, though the change occurs at a third decimal). Hope
thsi helps!

"BarryHWhite" wrote:

I think that the Date of Settlement was the date I purchased the bond.
Nothing to do with the original issue date, nor the maturity date

The parameters are
Syntax: YIELDMAT(S, M, I, R, PR[, B])

S = settlement date
M = maturity date
I = issue date
R = interest rate at date of issue
PR = the price of the security per $100 face value
B = (Optional) the day count basis to be used:

0 or omitted 30/360

So
My question remains
Example (dates UK format dd mm yy)

Yield to maturity 10.00%
Sett Date 31/12/2009
Maturity 31/12/2010
Issue Date 30/12/2009
Coupon yearly 10.000%
Price now 100.00
Days basis 3

But if the Issue date changes you get

Yield to maturity 9.09%
Sett Date 31/12/2009
Maturity 31/12/2010
Issue Date 31/12/2008
Coupon yearly 10.000%
Price now 100.00
Days basis 3

Which is wrong - you are paying out 100 now at 10% and will get 110 in
one
year, so the yield to maturity should be 10% - does not matter when the
bond
was issued







"Sean Timmons" wrote:

Based on MS Help, the rate and term are based on date of issue (not
date of
settlement). So, if the security was issues on 1/1/08 on a 30 year
bond, the
maturity date is 1/1/38, regardless of settlement date.

"BarryHWhite" wrote:

Why does Yieldmat include an issue date ?
The yield to maturity should be from the settlement date to the
maturity
date, subject to the rate of interest and the price of the bond

It does not matter when in the past it was issued

However I see that changing the issue date changes the yield to
maturity

Help please

Thanks, JoeU2004 I will play with this function and see if it helps
I am very surprised that this is so difficult
There must be many people like me who just want to know when they buy a bond
during its life, at a given price, with a given interest rate and maturity
date, which pays out interest each year, what is the Yield to Maturity (same
basis as an IRR calc)

Incidentally UK practise when you buy you pay in addition to the "clean"
price, the accumulated interest from the last payment date. When the first
interest payment is made, you get the total but have paid the seller for his
part. This I think should be ignored in the calc, and the price used should
be the clean price, although strictly you lose a bit on interest by paying
the price with interest up front, but get the first interst payment later in
the year

I will keep you posted, thanks for your thoughts so far



"JoeU2004" wrote:

"BarryHWhite" wrote:
is there a formula which assumes interest is paid out
and calculates the cash flows from settlement date forward

Use YIELD().

Although in bond parlance, there is usually a difference between the terms
(current) "yield" and "yield to maturity", apparently the Excel YIELD()
returns YTM, at least when the bond is held for more than one coupon period.

This is self-evident if you compare the results of YIELD() and IRR() or
RATE() in some simple cases, for example a bond held from 1/1/2009 through
1/1/2012 priced at $90 (per $100 face value) with semi-annual coupons at the
rate of 5%.

Caveat emptor: I cannot vouch for the results of YIELD() for bonds held for
one coupon period or less, or for bonds with maturity dates that are not
multple of coupon periods (!). Moreover, I have not tried to use YIELD()
for bonds that pay all the interest at maturity, although I suspect we can
make it return something reasonable by fudging the "redemption" parameter.

PS: I have also not tried to use YIELD() for bonds that are purchased after
the issue date -- ergo, we usually need to pay some accrued unpaid interest
for part of the coupon period that includes the settlement date. But
similarly, I suspect we can make it return something reasonable by fudging
the "pr" (price) parameter.

Like you, I have not had much success with YIELDMAT(), and it is unclear
what its algorithm is, notwithstanding a formula that some participants
posted in response to my inquiry in another thread.

I not yet had a chance to fully experiment with YIELD(). But it looks
promising since it returns results that are consistent with IRR() and RATE()
in circumstances that are conducive to using those functions; and those
results are very different from the YIELDMAT() results, which I believe are
incorrect in those circumstances.

FYI, it is not surprising for any valid YTM function or formula to return
results that differ from XIRR(), even when the date count mode ("basis") is
actual/actual.

The differences are due, at least in part, to apparently widely-accepted
conventions for determining YTM when the coupon frequency is more than once
per year. Also, there seems to be a wide variety of interpretations or
"rules of thumbs" that affect the details of the YTM formulation, especially
when fractional coupon periods are involved. Even the HP 12C formula is
questionable, IMHO.

But I digress. I tried to initiate an academic discussion of all this in my
thread ("Please explain YIELDMAT function"), but to no avail so far.
However, last weekend was a holiday in both the US and Canada, and some of
the participants who have demonstrated financial wizardry in the past might
still be on vacations or just returning from them.

In the meantime, I hope my comments help.


----- original message -----

"BarryHWhite" wrote in message
...
I wonder if I have misunderstood what the formula does

I am assuming that interest on the bond is paid out annually, so if I buy
the bond on the anniversery of the issue date, interest on the period
before
my purchase is not
an issue

My first example involves cash flows as follows
-100 Sett Date (when I buy the bond) 31/12/2009
+110 Maturity (when the bond terminates and pays capital and one years
interest 31/12/2010
Clearly here the return is 10%
and the bond issue date is not relevant

The reason I showed issue date as 1 day before settlement date is the
formula will not accept the same issue date and settlement date

Is the formula assuming that no interest is paid out, but accumulates in
the
bond - if so, I see that this will give a different result depending on
issue
date

Can you tell me the underlying calculation for the YieldMat function?

If this is the case, is there a formula which assumes interest is paid out
and calculates the cash flows from settlement date forward - or should I
use
the formula with issue date one day before settlement, which (apart from
the
tiny interest difference) gives me an answer the same as I would get using
IRR with the cash flows.

Rgds


"Sean Timmons" wrote:

I must disagree.

the bond has achieved a portion of maturity prior to settlement. In the
below examples, 1 day and 1 year respectively. The clear indication is
that
those days you do not have the bond in "possession" do not accumulate
interest in your name. (Please note, in the first example above, the
return
is not precisely 100%, though the change occurs at a third decimal). Hope
thsi helps!

"BarryHWhite" wrote:

I think that the Date of Settlement was the date I purchased the bond.
Nothing to do with the original issue date, nor the maturity date

The parameters are
Syntax: YIELDMAT(S, M, I, R, PR[, B])

S = settlement date
M = maturity date
I = issue date
R = interest rate at date of issue
PR = the price of the security per $100 face value
B = (Optional) the day count basis to be used:

0 or omitted 30/360

So
My question remains
Example (dates UK format dd mm yy)

Yield to maturity 10.00%
Sett Date 31/12/2009
Maturity 31/12/2010
Issue Date 30/12/2009
Coupon yearly 10.000%
Price now 100.00
Days basis 3

But if the Issue date changes you get

Yield to maturity 9.09%
Sett Date 31/12/2009
Maturity 31/12/2010
Issue Date 31/12/2008
Coupon yearly 10.000%
Price now 100.00
Days basis 3

Which is wrong - you are paying out 100 now at 10% and will get 110 in
one
year, so the yield to maturity should be 10% - does not matter when the
bond
was issued







"Sean Timmons" wrote:

Based on MS Help, the rate and term are based on date of issue (not
date of
settlement). So, if the security was issues on 1/1/08 on a 30 year
bond, the
maturity date is 1/1/38, regardless of settlement date.

"BarryHWhite" wrote:

Why does Yieldmat include an issue date ?
The yield to maturity should be from the settlement date to the
maturity
date, subject to the rate of interest and the price of the bond

It does not matter when in the past it was issued

However I see that changing the issue date changes the yield to
maturity

Help please

"BarryHWhite" wrote:
I am very surprised that this is so difficult[.]
There must be many people like me who just want to know when they
buy a bond during its life, at a given price, with a given interest rate
and maturity date, which pays out interest each year, what is the Yield
to Maturity

And in the States, we rely on the broker to tell us that.

Every text that I consulted state up-front that the YTM computation is
difficult and mysterious because some aspects are subject to interpretation.
There are many different "rules of thumbs". Most texts cover only the
simple cases.


Incidentally UK practise when you buy you pay in addition to the "clean"
price, the accumulated interest from the last payment date. When the first
interest payment is made, you get the total but have paid the seller for
his
part. This I think should be ignored in the calc

I disagree. For the purposes of computing YTM, I believe the unpaid accrued
interest that the buyer pays to the seller should be added to the price to
correctly reflect the cash flow.

In fact, returning to your original question, that is why I believe
YIELDMAT() requires the issue date in addition to the settlement date: to
compute the unpaid accrued interest before the settlement date. I expected
that was the added value of YIELDMAT() over YIELD().

However, I was unable to confirm that because I was never able to
reverse-engineer the YIELDMAT() computation and come close to its result.


although strictly you lose a bit on interest by paying the price with
interest up front, but get the first interst payment later in the year

..... Which impacts the cash flow model.

Remember: YTM is all about the time-value of money. Either you have to add
the unpaid accrued interest before settlement to the price, or you have to
subtract it from the first coupon payment after settlement. The latter does
not correctly reflect the cash flow timing, and it is difficult to formulate
"neatly". It is much easier to add it to the price; and in fact, that is
where it belongs in the timing of the cash flows.

FYI, the HP 12C YTM function does just that. And my broker accounts for
that when they quote the YTM.

But of course, that only matters when you are buying a "used" bond ;-).


----- original message -----

"BarryHWhite" wrote in message
...
Thanks, JoeU2004 I will play with this function and see if it helps
I am very surprised that this is so difficult
There must be many people like me who just want to know when they buy a
bond
during its life, at a given price, with a given interest rate and maturity
date, which pays out interest each year, what is the Yield to Maturity
(same
basis as an IRR calc)

Incidentally UK practise when you buy you pay in addition to the "clean"
price, the accumulated interest from the last payment date. When the first
interest payment is made, you get the total but have paid the seller for
his
part. This I think should be ignored in the calc, and the price used
should
be the clean price, although strictly you lose a bit on interest by paying
the price with interest up front, but get the first interst payment later
in
the year

I will keep you posted, thanks for your thoughts so far



"JoeU2004" wrote:

"BarryHWhite" wrote:
is there a formula which assumes interest is paid out
and calculates the cash flows from settlement date forward

Use YIELD().

Although in bond parlance, there is usually a difference between the
terms
(current) "yield" and "yield to maturity", apparently the Excel YIELD()
returns YTM, at least when the bond is held for more than one coupon
period.

This is self-evident if you compare the results of YIELD() and IRR() or
RATE() in some simple cases, for example a bond held from 1/1/2009
through
1/1/2012 priced at $90 (per $100 face value) with semi-annual coupons at
the
rate of 5%.

Caveat emptor: I cannot vouch for the results of YIELD() for bonds held
for
one coupon period or less, or for bonds with maturity dates that are not
multple of coupon periods (!). Moreover, I have not tried to use YIELD()
for bonds that pay all the interest at maturity, although I suspect we
can
make it return something reasonable by fudging the "redemption"
parameter.

PS: I have also not tried to use YIELD() for bonds that are purchased
after
the issue date -- ergo, we usually need to pay some accrued unpaid
interest
for part of the coupon period that includes the settlement date. But
similarly, I suspect we can make it return something reasonable by
fudging
the "pr" (price) parameter.

Like you, I have not had much success with YIELDMAT(), and it is unclear
what its algorithm is, notwithstanding a formula that some participants
posted in response to my inquiry in another thread.

I not yet had a chance to fully experiment with YIELD(). But it looks
promising since it returns results that are consistent with IRR() and
RATE()
in circumstances that are conducive to using those functions; and those
results are very different from the YIELDMAT() results, which I believe
are
incorrect in those circumstances.

FYI, it is not surprising for any valid YTM function or formula to return
results that differ from XIRR(), even when the date count mode ("basis")
is
actual/actual.

The differences are due, at least in part, to apparently widely-accepted
conventions for determining YTM when the coupon frequency is more than
once
per year. Also, there seems to be a wide variety of interpretations or
"rules of thumbs" that affect the details of the YTM formulation,
especially
when fractional coupon periods are involved. Even the HP 12C formula is
questionable, IMHO.

But I digress. I tried to initiate an academic discussion of all this in
my
thread ("Please explain YIELDMAT function"), but to no avail so far.
However, last weekend was a holiday in both the US and Canada, and some
of
the participants who have demonstrated financial wizardry in the past
might
still be on vacations or just returning from them.

In the meantime, I hope my comments help.


----- original message -----

"BarryHWhite" wrote in message
...
I wonder if I have misunderstood what the formula does

I am assuming that interest on the bond is paid out annually, so if I
buy
the bond on the anniversery of the issue date, interest on the period
before
my purchase is not
an issue

My first example involves cash flows as follows
-100 Sett Date (when I buy the bond) 31/12/2009
+110 Maturity (when the bond terminates and pays capital and one years
interest 31/12/2010
Clearly here the return is 10%
and the bond issue date is not relevant

The reason I showed issue date as 1 day before settlement date is the
formula will not accept the same issue date and settlement date

Is the formula assuming that no interest is paid out, but accumulates
in
the
bond - if so, I see that this will give a different result depending on
issue
date

Can you tell me the underlying calculation for the YieldMat function?

If this is the case, is there a formula which assumes interest is paid
out
and calculates the cash flows from settlement date forward - or should
I
use
the formula with issue date one day before settlement, which (apart
from
the
tiny interest difference) gives me an answer the same as I would get
using
IRR with the cash flows.

Rgds


"Sean Timmons" wrote:

I must disagree.

the bond has achieved a portion of maturity prior to settlement. In
the
below examples, 1 day and 1 year respectively. The clear indication is
that
those days you do not have the bond in "possession" do not accumulate
interest in your name. (Please note, in the first example above, the
return
is not precisely 100%, though the change occurs at a third decimal).
Hope
thsi helps!

"BarryHWhite" wrote:

I think that the Date of Settlement was the date I purchased the
bond.
Nothing to do with the original issue date, nor the maturity date

The parameters are
Syntax: YIELDMAT(S, M, I, R, PR[, B])

S = settlement date
M = maturity date
I = issue date
R = interest rate at date of issue
PR = the price of the security per $100 face value
B = (Optional) the day count basis to be used:

0 or omitted 30/360

So
My question remains
Example (dates UK format dd mm yy)

Yield to maturity 10.00%
Sett Date 31/12/2009
Maturity 31/12/2010
Issue Date 30/12/2009
Coupon yearly 10.000%
Price now 100.00
Days basis 3

But if the Issue date changes you get

Yield to maturity 9.09%
Sett Date 31/12/2009
Maturity 31/12/2010
Issue Date 31/12/2008
Coupon yearly 10.000%
Price now 100.00
Days basis 3

Which is wrong - you are paying out 100 now at 10% and will get 110
in
one
year, so the yield to maturity should be 10% - does not matter when
the
bond
was issued







"Sean Timmons" wrote:

Based on MS Help, the rate and term are based on date of issue
(not
date of
settlement). So, if the security was issues on 1/1/08 on a 30 year
bond, the
maturity date is 1/1/38, regardless of settlement date.

"BarryHWhite" wrote:

Why does Yieldmat include an issue date ?
The yield to maturity should be from the settlement date to the
maturity
date, subject to the rate of interest and the price of the bond

It does not matter when in the past it was issued

However I see that changing the issue date changes the yield to
maturity

Help please