On Apr 10, 5:56*am, "W" wrote:
"joeu2004" wrote in message
...
"W" wrote:
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?
--
W
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/onli...lculation.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/onli...lculation.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