I continue to not quite understand the difference between YIELD() and
YIELDMAT(). 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()?

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?

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

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

--
W

"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".

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".

On May 5, 12:42*am, "joeu2004" wrote:
[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".

If that someone else you referred to is me then let me explain what I
mean when I say IRR may be defined in more than one way.

A) At an interest rate that equals the IRR internal rate of return,
the NPV or net present value of the cash flows is ZERO

B) If the NPV of cash flows is ZERO, then it follows that NFV or net
future value is also ZERO.

This is so as NFV is nothing more than the product of NPV and future
value interest factor that compounds the net present value to reflect
its future worth at i% interest rate for N periods.

C) NPV may be defined as the difference of discounted costs from
discounted benefits (B-C=0) thus we can rearrange this fact to define
profitability index such that PI is the ratio of discounted benefits
over discounted costs (B/C=1) given that C<0. This holds true as long
as the costs are not ZERO at which point the function is undefined due
to division by zero

NPV = discounted_benfits - discounted_costs
1) At IRR, NPV = 0
discounted_benfits - discounted_costs = 0

NFV = NPV x FVIF(i%,N)
2) At IRR, NFV = 0
NFV = NPV x (1+i)^N
NPV x (1+i)^N = 0

PI = discounted_benfits / discounted_costs
This follows from definition of NPV
NPV = discounted_benfits - discounted_costs
discounted_benfits = discounted_costs
discounted_benfits / discounted_costs = 1 [given that discounted_costs
< 0]

3) At IRR, PI = 1
discounted_benfits / discounted_costs = 1
[discounted_benfits / discounted_costs] - 1 = 0

On May 5, 12:42*am, "joeu2004" wrote:
"W" wrote:
-----
[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".

Adding to my last reply, it wouldn't matter which of three underlying
IRR Equations we use, we will still come away with the same internal
rate of return

I will now show you IRR calculation for the cash flows of

-10000 3000 3000 3000 3000

using the three different IRR equations to find internal rate of
return with Newton Raphson method

Newton Raphson Method IRR Calculation with NPV equation = 0
http://finance.thinkanddone.com/find... _method.html

f(x) = -10000(1+i)^0 +3000(1+i)^-1 +3000(1+i)^-2 +3000(1+i)^-3
+3000(1+i)^-4

f'(x) = -3000(1+i)^-2 -6000(1+i)^-3 -9000(1+i)^-4 -12000(1+i)^-5

x0 = 0.1
f(x0) = -490.4037
f'(x0) = -20585.4046
x1 = 0.1 - -490.4037/-20585.4046 = 0.0761771182095
Error Bound = 0.0761771182095 - 0.1 = 0.023823 0.000001

x1 = 0.0761771182095
f(x1) = 21.5227
f'(x1) = -22427.1121
x2 = 0.0761771182095 - 21.5227/-22427.1121 = 0.0771367901805
Error Bound = 0.0771367901805 - 0.0761771182095 = 0.00096 0.000001

x2 = 0.0771367901805
f(x2) = 0.0376
f'(x2) = -22348.7961
x3 = 0.0771367901805 - 0.0376/-22348.7961 = 0.0771384729469
Error Bound = 0.0771384729469 - 0.0771367901805 = 2.0E-6 0.000001

x3 = 0.0771384729469
f(x3) = 0
f'(x3) = -22348.6591
x4 = 0.0771384729469 - 0/-22348.6591 = 0.0771384729521
Error Bound = 0.0771384729521 - 0.0771384729469 = 0 < 0.000001
IRR = x4 = 0.0771384729521 or 7.71%

Newton Raphson Method IRR Calculation with NFV equation = 0
http://finance.thinkanddone.com/find... _method.html

f(x) = -10000(1+i)^5 +3000(1+i)^4 +3000(1+i)^3 +3000(1+i)^2
+3000(1+i)^1

f'(x) = -50000(1+i)^4 +12000(1+i)^3 +9000(1+i)^2 +6000(1+i)^1
+3000(1+i)^0

x0 = 0.1
f(x0) = -789.8
f'(x0) = -36743
x1 = 0.1 - -789.8/-36743 = 0.0785047492039
Error Bound = 0.0785047492039 - 0.1 = 0.021495 0.000001

x1 = 0.0785047492039
f(x1) = -44.4448
f'(x1) = -32655.0869
x2 = 0.0785047492039 - -44.4448/-32655.0869 = 0.0771437131056
Error Bound = 0.0771437131056 - 0.0785047492039 = 0.001361 0.000001

x2 = 0.0771437131056
f(x2) = -0.1698
f'(x2) = -32405.7442
x3 = 0.0771437131056 - -0.1698/-32405.7442 = 0.0771384730295
Error Bound = 0.0771384730295 - 0.0771437131056 = 5.0E-6 0.000001

x3 = 0.0771384730295
f(x3) = -0
f'(x3) = -32404.7864
x4 = 0.0771384730295 - -0/-32404.7864 = 0.0771384729521
Error Bound = 0.0771384729521 - 0.0771384730295 = 0 < 0.000001
IRR = x4 = 0.0771384729521 or 7.71%

Newton Raphson Method IRR Calculation with profitability index
equation = 1
http://finance.thinkanddone.com/find...on_method.html

b(x) = +3000(1+i)^-1 +3000(1+i)^-2 +3000(1+i)^-3 +3000(1+i)^-4

c(x) = +10000(1+i)^0

b'(x) = -3000(1+i)^-2 -6000(1+i)^-3 -9000(1+i)^-4 -12000(1+i)^-5

c'(x) = 0(1+i)^-1

pi(x) = b(x)/|c(x)| - 1
pi'(x) = [c(x) b'(x) - b(x) * c'(x)] / c(x)2
x0 = 0.1

b(0.1) = 9509.59633905
c(0.1) = 10000
pi(0.1) = -0.0490403660952
b'(0.1) = -20585.4046234
c'(0.1) = 0
pi'(0.1) = -2.05854046234
x1 = 0.1 - -0.0490403660952/-2.05854046234 = 0.0761771182095
x1 = 0.0761771182095

b(0.0761771182095) = 10021.5226709
c(0.0761771182095) = 10000
pi(0.0761771182095) = 0.00215226708963
b'(0.0761771182095) = -22427.1121246
c'(0.0761771182095) = 0
pi'(0.0761771182095) = -2.24271121246
x2 = 0.0761771182095 - 0.00215226708963/-2.24271121246 =
0.0771367901805
x2 = 0.0771367901805

b(0.0771367901805) = 10000.0376078
c(0.0771367901805) = 10000
pi(0.0771367901805) = 3.76078036624E-6
b'(0.0771367901805) = -22348.7961359
c'(0.0771367901805) = 0
pi'(0.0771367901805) = -2.23487961359
x3 = 0.0771367901805 - 3.76078036624E-6/-2.23487961359 =
0.0771384729469
x3 = 0.0771384729469

b(0.0771384729469) = 10000.0000001
c(0.0771384729469) = 10000
pi(0.0771384729469) = 1.15274456647E-11
b'(0.0771384729469) = -22348.6591282
c'(0.0771384729469) = 0
pi'(0.0771384729469) = -2.23486591282
x4 = 0.0771384729469 - 1.15274456647E-11/-2.23486591282 =
0.0771384729521
x4 = 0.0771384729521
IRR = 7.71%
Annualized IRR = 7.71%

On May 5, 12:42*am, "joeu2004" wrote:
-----
[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".

You only learn new things when you do some of your own thinking rather
than accepting what is being taught to you.

I dunno, I don't have a college education as the State of NY told me
they didn't owe me a dime and the Feds were more concerned about the
welfare of historically disadvantaged rather than likes of me who are
contemporary disadvantaged

So with a high school diploma in hand there weren't any employers
willing to hire me and I recall applying for a programming job at
Microsoft Ireland a few years ago and they gave me two programs to
write of which I knew one or at least I thought. The task was to
program a function that converted a decimal number to its binary
equivalent. Upon submission I never heard back from them. And when I
showed the code I wrote to pundits at Java Programming newsgroup I was
made fun of and was called stupid

I don't have a job, never had one and chances that I will ever have
one are slim

So I make my living writing financial software, it makes me some
decent change but then the country that I was sent to by the State
department is of the view that anything I ever created in terms of
software belongs to them and I have no rights to anything at all.

Here Microsoft Office 2010 sells for equivalent of $1 on pirated DVDs

I really haven't figured what is it that I was punished for by the
Thought Police that are hell bent of enforcing political correctness

And as for giving ideas to corporations about new products, I do
recall what Microsoft said to me in 1995 when I sent them my idea for
a World Clock for Windows. The letter read "Your idea is interesting
but doesn't fit our business needs"

Yes that is correct and few months after I sent the letter I noticed
Windows CE had a World Clock

"PJ Hooker" wrote:
You only learn new things when you do some of your own
thinking rather than accepting what is being taught to you.

I'm not sure why you are still posting responses to month-old comments,
adding no real substance. Perhaps you are just looking for closure. I hope
the following provides that for you.

I admit that I had misread your comments posted in a different thread 2
months ago, as well as a webpage that you referred to.

I thought that you were saying that there are different __definitions__ of
IRR. But in fact, you did say only: "It doesn't have to be a net present
value equation that is set to zero to find IRR". Implicitly, you said "the"
IRR, not "different IRRs".

Indeed, all of the other equations are algebraically equivalent (given the
same conditions). So as you point out in another response in this thread:
"it wouldn't matter which of [... the ...] IRR Equations we use, we will
still come away with the same internal rate of return", except for some
infinitesimal numerical differences due to binary floating-point
computation.

It's like saying that there are 4 equations for defining a straight line:
y=ax+b, x=(y-b)/a, b=y-ax, and a=(y-b)/x. Since they are algebraically the
same, I would not bother complicating matters by mentioning them unless it
is relevant to the discussion.

However, that's just me. Of course, you are free to say whatever you want.

My real concern was with the impression that there are "different"
definitions of IRR.

But that was my mistake in reading, as I said. Mea culpa!