Yield to Maturity

[W]

I am perplexed why does the Yield to Maturity function need to be fed an
"issue date" as a parameter? If I invest in a bond today with maturity of
2/1/2013 and the bond sell for 80% of par and has a 5% coupon, why would I
care about an issue date? The formula is:

YIELDMAT(settlement,maturity,issue,rate,pr,basis)

 

[W]

I am perplexed why does the Yield to Maturity function need to be fed an
"issue date" as a parameter? If I invest in a bond today with maturity of
2/1/2013 and the bond sell for 80% of par and has a 5% coupon, why would I
care about an issue date? The formula is:

YIELDMAT(settlement,maturity,issue,rate,pr,basis)

A follow on question is why doesn't Yield to Maturity function let you
specify whether the bond is paid quarterly, semi-annually, annually, etc?

 

[joeu2004]

A follow on question is why doesn't Yield to Maturity
function let you specify whether the bond is paid quarterly,
semi-annually, annually, etc?

Read the Help page for YIELDMAT. It says: "Returns the annual yield of a
security that pays interest __at_maturity__".

For bonds that pay interest quarterly etc, use the YIELD function.

 

[joeu2004]

I am perplexed why does the Yield to Maturity function
need to be fed an "issue date" as a parameter? If I
invest in a bond today with maturity of 2/1/2013 and the
bond sell for 80% of par and has a 5% coupon, why would I
care about an issue date? The formula is:
YIELDMAT(settlement,maturity,issue,rate,pr,basis)

I doubt that is the formula verbatim that you are using, unless those are
actually named references. In any case, you need to tell us the values of
each referenced cell.

And to answer your question....

Many bonds are purchased in a secondary market some time after the issue
date.

Although the Help page for YIELDMAT neglects to mention it, I believe that
YIELDMAT solves for "yld" in the PRICEMAT formula. See the Help page for
PRICEMAT [*].

The point is: note that PRICEMAT is reduced by the fraction of interest
applicable to the period between issue and settlement dates.

The reason is: that interest gets paid to the previous bond holder.

Note: YIELDMAT does not allow for settlement = issue, IIRC. It is a
defect, IMHO. If the settlement and issue dates are the same, enter issue+1
for the YIELDMAT settlement parameter. Usually, that causes only a very
small error in the result. YMMV.


-----
[*] Alternatively, see the OpenOffice description of how YIELDMAT is
computed at
http://wiki.services.openoffice.org...lc:_Derivation_of_Financial_Formulas#YIELDMAT.
Caveat: OpenOffice is a different product from Excel. I believe I verified
that the OpenOffice derivation is similar to solving PRICEMAT for "yld".
But I cannot confirm that now with impunity.

 

[W]

Read the Help page for YIELDMAT. It says: "Returns the annual yield of a
security that pays interest __at_maturity__".

For bonds that pay interest quarterly etc, use the YIELD function.

Pretty awful that they chose a name for that function that sounds similar to
"Yield to Maturity" which is a standardized financial concept.

In any case, you are right I need to use YIELD

 

[W]

I am perplexed why does the Yield to Maturity function
need to be fed an "issue date" as a parameter? If I
invest in a bond today with maturity of 2/1/2013 and the
bond sell for 80% of par and has a 5% coupon, why would I
care about an issue date? The formula is:
YIELDMAT(settlement,maturity,issue,rate,pr,basis)

I doubt that is the formula verbatim that you are using, unless those are
actually named references. In any case, you need to tell us the values of
each referenced cell.

And to answer your question....

Many bonds are purchased in a secondary market some time after the issue
date.

Although the Help page for YIELDMAT neglects to mention it, I believe that
YIELDMAT solves for "yld" in the PRICEMAT formula. See the Help page for
PRICEMAT [*].

The point is: note that PRICEMAT is reduced by the fraction of interest
applicable to the period between issue and settlement dates.

The reason is: that interest gets paid to the previous bond holder.

Note: YIELDMAT does not allow for settlement = issue, IIRC. It is a
defect, IMHO. If the settlement and issue dates are the same, enter issue+1
for the YIELDMAT settlement parameter. Usually, that causes only a very
small error in the result. YMMV.

Purchase Date = K20 = 4/9/2012
Maturity = L20 = 11/1/2017
Coupon = M20 = 8.63%
Percent of Par at Purchase = N20 = 94%
Par Value = D20 = $1000
Pays Bi Annually

Using the YIELD function I enter:

=YIELD(K20,L20,M20,N20*D20,D20,2)

This is giving back 2.01% which looks wrong. If we are buying the bond at
less than par, how can a yield to maturity be lower than the coupon value of
8.63%?

Where is error in this formula?

 

[Vacuum Sealed]

Purchase Date = K20 = 4/9/2012

Maturity = L20 = 11/1/2017
Coupon = M20 = 8.63%
Percent of Par at Purchase = N20 = 94%
Par Value = D20 = $1000
Pays Bi Annually

Using the YIELD function I enter:

=YIELD(K20,L20,M20,N20*D20,D20,2)

This is giving back 2.01% which looks wrong. If we are buying the bond at
less than par, how can a yield to maturity be lower than the coupon value of
8.63%?

Where is error in this formula?

Hi

Not sure about the error, but using your structure & equation I got this
answer: 2.32%

Cheers
Mick.

 

[joeu2004]

Purchase Date = K20 = 4/9/2012
Maturity = L20 = 11/1/2017
Coupon = M20 = 8.63%
Percent of Par at Purchase = N20 = 94%
Par Value = D20 = $1000
Pays Bi Annually

Using the YIELD function I enter:
=YIELD(K20,L20,M20,N20*D20,D20,2)
This is giving back 2.01% which looks wrong. [....]
Where is error in this formula?

Following bond industry convention, the price (pr) and redemption value must
be stated as an amount per units of $100 face value. See the YIELD Help
page.

(That is very confusing, IMHO. But it is similar to my HP-12C calculator
inputs.)

So you should write the equivalent of:

=YIELD(DATE(2012,4,9),DATE(2017,11,1),8.63%,94,100,2)

(Note: I ass-u-me that 4/9/2012 and 11/1/2017 are in the form m/d/yyyy, not
d/m/yyyy.)

That returns about 10.06%.

In your formula, you should use N20*D20/10 and D20/10 for the 4th and 5th
parameters. We divide by 10 because D20/100 = 10.

(Note: Although you use "par value" and "redemption value" interchangeably,
they might be different for a callable bond and you actually compute "yield
to call".)

As a double-check, note that YTM is the IRR of the cash flows. So we can
set up the following cash flows (forgive me if things do not align well in
your view):


B C
1 4/9/2012 -940.00
2 10/9/2012 43.15
3 4/9/2013 43.15
4 10/9/2013 43.15
5 4/9/2014 43.15
6 10/9/2014 43.15
7 4/9/2015 43.15
8 10/9/2015 43.15
9 4/9/2016 43.15
10 10/9/2016 43.15
11 4/9/2017 43.15
12 10/9/2017 43.15
13 11/1/2017 1010.91
14 4/9/2018

C13 is the redemption value (1000) plus interest for the fractional coupon
period ending on the date in C14.

XIRR(C1:C13,B1:B13) returns about 10.39%.

That is significantly different because XIRR assumes compounded interest,
whereas semiannual coupons are computed using simple interest. That is, the
annual rate is divided by 2.

We can compensate to some degree by computing the following instead:

2*(SQRT(1+XIRR(C1:C13,B1:B13))-1)

That returns about 10.14%, compared to about 10.06% for YIELD.

There are many possible reasons for the difference. But hopefully that is
close enough to demonstrate the correctness of the YIELD result [1].

 

[PJ Hooker]

I doubt that is the formula verbatim that you are using, unless those are
actually named references.  In any case, you need to tell us the values of
each referenced cell.

And to answer your question....

Many bonds are purchased in a secondary market some time after the issue
date.

Although the Help page for YIELDMAT neglects to mention it, I believe that
YIELDMAT solves for "yld" in the PRICEMAT formula.  See the Help pagefor
PRICEMAT [*].

The point is:  note that PRICEMAT is reduced by the fraction of interest
applicable to the period between issue and settlement dates.

The reason is:  that interest gets paid to the previous bond holder.

Note:  YIELDMAT does not allow for settlement = issue, IIRC.  It is a
defect, IMHO.  If the settlement and issue dates are the same, enter issue+1
for the YIELDMAT settlement parameter.  Usually, that causes only a very
small error in the result.  YMMV.

Purchase Date = K20 = 4/9/2012
Maturity = L20 = 11/1/2017
Coupon = M20 = 8.63%
Percent of Par at Purchase = N20 = 94%
Par Value = D20 = $1000
Pays Bi Annually

Using the YIELD function I enter:

    =YIELD(K20,L20,M20,N20*D20,D20,2)

This is giving back 2.01% which looks wrong.    If we are buying the bond at
less than par, how can a yield to maturity be lower than the coupon valueof
8.63%?

Where is error in this formula?

Years till maturity in cell K21 =(L20-K20)/365
YTM using Excel RATE function in cell L21 =RATE(K21*2,D20*M20/2,-
D20*N20,D20)*2

Results in annual YTM of 10.06%

Using an online YTM calculation tool at http://finance.thinkanddone.com/online-ytm-calculation.html
the periodic YTM (semi-annual) is 5.03% and Annual YTM is 10.06%

f(x) = 1000 + -940 * (1+x)^11.134246 + 43.15 [(1+x)^11.134246 - 1]/x

f'(x) = 11.134246 * -940 * (1+x)^10.134246 + 43.15 * (11.134246 x (1 +
x)^10.134246 - (1 + x)^11.134246 + 1) / (x^2)

x = 0.1
f(x) = -900.9922
f'(x) = -23028.9944
x1 = 0.1 - -900.9922/-23028.9944 = 0.0608757455677
Error Bound = 0.0608757455677 - 0.1 = 0.039124 > 0.000001

x1 = 0.0608757455677
f(x1) = -155.1998
f'(x1) = -15523.8033
x2 = 0.0608757455677 - -155.1998/-15523.8033 = 0.0508782080515
Error Bound = 0.0508782080515 - 0.0608757455677 = 0.009998 > 0.000001

x2 = 0.0508782080515
f(x2) = -7.7899
f'(x2) = -13988.4926
x3 = 0.0508782080515 - -7.7899/-13988.4926 = 0.0503213270149
Error Bound = 0.0503213270149 - 0.0508782080515 = 0.000557 > 0.000001

x3 = 0.0503213270149
f(x3) = -0.0227
f'(x3) = -13906.9818
x4 = 0.0503213270149 - -0.0227/-13906.9818 = 0.0503196936612
Error Bound = 0.0503196936612 - 0.0503213270149 = 2.0E-6 > 0.000001

x4 = 0.0503196936612
f(x4) = -0
f'(x4) = -13906.7433
x5 = 0.0503196936612 - -0/-13906.7433 = 0.0503196936472
Error Bound = 0.0503196936472 - 0.0503196936612 = 0 < 0.000001
YTM = 5.03%
Annual YTM = 10.06%

We can find bond price at YTM of 10.06% using PV function as follows

Bond price in Cell M21 =PV(L21/2,K21*2,D20*M20/2,D20)
results in bond price of ($940)

Using this online bond price tool at
http://finance.thinkanddone.com/online-non-zero-bond-calculation.html
we find the bond price as follows

Interest compounded semi annually
Par value of bond is 1000
Coupon rate on bond is 0.04315
YTM on bond is 0.0503
Years till maturity are 11.134246
Price = coupon rate x par value x PVIFA(ytm%, n) + par value x PVIF(ytm
%, n)
PVIFA(0.0503, 11.134246) = 8.36943192239
PVIF(0.0503, 11.134246) = 0.579017574304
Price = 0.04315 x 1000 x 8.36943192239 + 1000 x 0.579017574304
Price = 361.140987451 + 579.017574304
Price = $940.16

 

[W]

Purchase Date = K20 = 4/9/2012
Maturity = L20 = 11/1/2017
Coupon = M20 = 8.63%
Percent of Par at Purchase = N20 = 94%
Par Value = D20 = $1000
Pays Bi Annually

Using the YIELD function I enter:
=YIELD(K20,L20,M20,N20*D20,D20,2)
This is giving back 2.01% which looks wrong. [....]
Where is error in this formula?

Following bond industry convention, the price (pr) and redemption value must
be stated as an amount per units of $100 face value. See the YIELD Help
page.

(That is very confusing, IMHO. But it is similar to my HP-12C calculator
inputs.)

So you should write the equivalent of:

=YIELD(DATE(2012,4,9),DATE(2017,11,1),8.63%,94,100,2)

So you are telling me that YIELD requires you to normalize all values to
$100 par value? So a $1000 bond is normalized to a $100 par bond? A $25
preferred is normalized to a $100 par bond?

Wow, could they make this thing any more obscure?

I confirm that your version gives a correct result and the alternate using
real par values of:

=YIELD(DATE(2012,4,9),DATE(2017,11,1),8.63%,940,1000,2)

fails to give a correct result.

(Note: I ass-u-me that 4/9/2012 and 11/1/2017 are in the form m/d/yyyy, not
d/m/yyyy.)

I am using the US standard of MM/DD/YYYY.

Are you telling me if I have my Excel set for a date input of MM/DD/YYYY and
enter a date that YIELD will convert my date into a European date format of
DD/MM/YYYY?

That means I have to rewrite all of my cells with the incredibly verbose
format like:

=YIELD(DATE(YEAR(mydatecell),MONTH(mydatecell),DAY(mydatecell)),.....)

My God....

 

[joeu2004]

So you are telling me that YIELD requires you to
normalize all values to $100 par value? [....]
Wow, could they make this thing any more obscure?

I agree. However, this is a bond industry convention. The HP-12C
calculator works the same way.

I am using the US standard of MM/DD/YYYY.
Are you telling me if I have my Excel set for a date
input of MM/DD/YYYY and enter a date that YIELD will
convert my date into a European date format of DD/MM/YYYY?

No. I am telling you that if you want everyone in the world to understand
the dates that you write, it would behoove you to tell us what form they are
in. You might notice that one other respondent misunderstood your date
format and "corrected" your YIELD results.

As long as you are working in your own worksheet on your own computer, no
date conversion is needed.