Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yield to Maturity
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yield to Maturity
"W" wrote in message
... 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? -- W |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yield to Maturity
"W" wrote:
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? 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. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yield to Maturity
"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. -----[*] Alternatively, see the OpenOffice description of how YIELDMAT is computed at http://wiki.services.openoffice.org/...mulas#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. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yield to Maturity
"joeu2004" wrote in message
... "W" wrote: 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? 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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yield to Maturity
"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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yield to Maturity
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. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yield to Maturity
"W" wrote:
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]. ----- [1] With my own formula for the NPV and using Solver to determine the YTM, I get about 10.04%. I am still investigating to try to understand the difference, albeit small. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yield to Maturity
"Vacuum Sealed" wrote:
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. [....] using your structure & equation I got this answer: 2.32% I suspect you interpreted 4/9/2012 and 11/1/2017 as in the form d/m/yyyy instead of m/d/yyyy. (Not your fault.) Note that =YIELD(DATE(2012,9,4),DATE(2017,1,11),8.63%,940,10 00,2,0) is about 2.32%, whereas =YIELD(DATE(2012,4,9),DATE(2017,11,1),8.63%,940,10 00,2,0) is indeed about 2.01%. In either case, you are both misusing the YIELD parameters. See my response to "W". |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yield to Maturity
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 |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yield to Maturity
"joeu2004" wrote in message
... "W" wrote: 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,10 00,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.... -- W |
#12
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yield to Maturity
"W" wrote:
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. "W" wrote: (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? 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Current Yield vs. Yield to Maturity at low prices | Excel Worksheet Functions | |||
How to calculate the maturity value | Excel Worksheet Functions | |||
Does anyone know how to calculate Yield to Maturity using Exel? | Excel Worksheet Functions | |||
How do I make a template for Yield to Maturity? | Excel Worksheet Functions |