Outstanding post, Joe. That one should be in a hall of fame. I
understand the issue now.

YIELDMAT looks both somewhat misnamed and somewhat useless.

--
W


"joeu2004" wrote in message
...
"W" wrote:
I continue to not quite understand the difference between
YIELD() and YIELDMAT().

Asked and answered a month ago. As the Help pages explain, YIELDMAT
returns
"the annual yield of a security that pays interest __at_maturity__".

In contrast, YIELD returns the "the yield on a security that pays
__periodic_interest__".

You do not seem to understand the difference between paying __periodic__
interest and paying __all__ interest __at_maturity__.

See the explanation of the example below.


"W" wrote:
I also don't understand why YIELDMAT() omits a "Frequency"
whereas YIELD() does not.

Because the "frequency" is known for YIELDMAT, to wit: one time!


"W" wrote:
A related question: YIELDMAT() takes an "issue date"
but if you put in the actual issue date of the bond many
years ago, YIELDMAT() is making you pay accrued interest
on the ENTIRE TIME PERIOD since first issuance. Based
on that, it appears that what you actually need to put
into the Issue field for YIELDMAT() is the date that the
bond *last* paid interest, so that the correct amount of
accrued interest is extracted?

Non sequitur based on your inability to understand the fact that YIELDMAT
assumes that __all__ interest is paid __one_time__ __at_maturity__. There
is no previous "date the bond last paid interest".

If the issue and settlement dates are different, the assumption is that
you
purchased a bond in the secondary market. Ergo, some accrued interest is
due to the previous owner.

For a bond that pays __periodic__ interest, yes, that would be the
interest
accrued since that coupon date on or before the settlement date.

For a bond that pays interest __one_time__ __at_maturity__, that would be
the interest accrued since the "issue date", i.e. the previous-owner's
settlement date.


"W" wrote:
I am confused why YIELD() requires a "redemption value"
when YIELDMAT() does not.

Normally, the "redemption value" is 100 anyway, based on its definition.
(See the Help page.)

The only time it would be different is for the value of an earlier "call".
Apparently, YIELDMAT is not capable of handling that case.


"W" wrote:
I have a bond that I buy on 5/3/2012 that matures on 6/1/2032.
It pays a 7.25% coupon. I buy it for 50% of par value.
YIELD() gives me 15.3% using this formula:
=YIELD(DATE(2012,5,3),DATE(2032,6,1),7.25%,50%*100 ,100,2)
YIELDMAT() gives me 19.5% using this formula:
=YIELDMAT(DATE(2012,5,3),DATE(2032,6,1),DATE(2012, 5,2),7.25%,50%*100)
What I am trying to solve for is the yield until maturity.
What is it that YIELD() gives me and why is it so much lower
than YIELDMAT()?

Both functions return "yield to maturity" (YTM). The names are confusing,
especially considering the confusing way that the term "yield" is used in
the bond industry.

The YTM rates differ because of the different assumptions about the cash
flows, the interest payments. I will demonstrate below.

However, ironically, I think YIELDMAT returns the wrong YTM for bonds held
more than a year. For the same bond characteristics, YIELDMAT should
return
a __lower__ YTM rate than YIELD.

AFAIK, YTM is normally defined as the IRR of the cash flows based on the
NPV
formula [1]. By that definition, the __periodic__ YTM is a compounded
rate.

And that is indeed what YIELD returns -- almost. (See the details below.)

But YIELDMAT returns a __simple__ rate of return.

(Note: Perhaps that follows conventions of the bond industry. I cannot
say
one way or another with impunity because I am not a bond expert and I have
never held a long-term bond that pays interest only at maturity.)

To understand the differences and the implications, it would be better to
look at worksheet with actual calculations. Download "yield v
yieldmat.xls"
at https://www.box.com/s/2e6fb50bb49c254e1019.

(Aside.... It would be easier to discuss that example if we could post a
screen shot of the worksheet. In the future, you might consider posting
questions to http://answers.microsoft.com/en-us/office/forum/excel so that
we have that opportunity.)

For simplicity, assume the maturity date is DATE(2032,5,3), exactly 20
years
after the settlement date, DATE(2012,5,3).

For YIELD, the cash flows a
1. -50 on DATE(2012,5,3): the initial purchase price.
2. 3.625 every 6 months starting on DATE(2012,11,3).
3. 103.625 at maturity on DATE(2032,5,3): the semiannual interest plus
the
redemption value (100).

The YIELD function returns about 15.3417%. 2*IRR(...) returns about the
same amount.

(Note: 2 times IRR because IRR returns a __periodic__ rate, semiannual in
this case; but YTM is is usually an annual rate. We might think that it
should be (1+IRR(...))^2-1, an annual __compounded__ rate, since the IRR
computes a periodic compounded rate. There are differences of opinion on
that point.)

For YIELDMAT, the cash flows a
1. -50 on DATE(2012,5,3), the initial purchase price; ignore the accrued
interest for now.
2. 245 at maturity on DATE(2032,5,3): 20 times the annual interest of
7.25
(145 total) plus the redemption value (100).

The YIELDMAT function returns about 19.4921%. The simple rate of return
is
about 19.5000%, computed by (245/50 - 1)/20.

(Note: The small difference is due a number of factors. One of them is
the
fact that YIELDMAT does not permit us to enter the same value for the
issue
and settlement dates, a defect IMHO. So we must use DATE(2012,5,3)-1 for
issue date. Thus, YIELDMAT always calculates some amount accrued
interest,
which our IRR model ignores.)

But the IRR of those cash flows is about 8.2704%. That is the correct YTM
based on the YIELDMAT cash flows, IMHO.


-----
[1] AFAIK, IRR is only defined as the rate that causes the NPV to be zero.
Someone else in this forum claims that there are multiple definitions of
IRRs. IMHO, he is misusing the term IRR per se. The "other definitions"
are, in fact, different definitions of rate of return. To paraphrase a
familiar syllogism: "IRR is a rate of return, but not all rates of return
are an IRR".