Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with Yield() function
The Yield() and Price() functions are reciprocal in that Yield's
return value, if used as the yld argument in Price(), and using all the same other arguments in Price() as used in Yield(), should return the same value (or very nearly the same value, allowing for very tiny rounding errors within the functions) as originally used for the pr argument in Yield(). I've tested that with several hundred random sets of arguments, and in most cases it works. But in some cases I get a very different value. For example: Settlement: Jan 4, 2009 Maturity: Sep 20, 2009 Rate: 5 Price: 2 Redemption: 3 Frequency: 1 Basis: 2 Yield() returns: 3.406186157 Price() returns: 0.577520039 DIFFERENCE FROM EXPECTED VALUE: 2 - 0.577520039 = 1.422479961 = 71.1% Is that a bug in Excel? The reason I ask is because I have custom Price() and Yield() functions that allow a frequency of 12 (Excel's Yield and Price functions allow only 1, 2, or 4 for the frequency argument), and they always return the expected value in this test. These custom functions are made from the formulas and descriptions shown in the function reference for Excel's Price() and Yield() functions. I believe the problem is in Excel's Yield function, and not its Price function, because the custom Price function always returns the same results as Excel's Price function, given the same arguments. I discovered this discrepancy when testing these custom Price and Yield functions against Excel's Price and Yield functions. Aside from this problem, they always return the same values as Excel's functions, given the same arguments. Thanks for any information. Greg |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with Yield() function
I forgot to add -- Crystal Reports also has a Yield function, and it agrees with the custom Yield function in this test, not Excel's Yield function. Greg On Dec 27, 2:40*pm, Greg Lovern wrote: The Yield() and Price() functions are reciprocal in that Yield's return value, if used as the yld argument in Price(), and using all the same other arguments in Price() as used in Yield(), should return the same value (or very nearly the same value, allowing for very tiny rounding errors within the functions) as originally used for the pr argument in Yield(). I've tested that with several hundred random sets of arguments, and in most cases it works. But in some cases I get a very different value. For example: Settlement: Jan 4, 2009 Maturity: Sep 20, 2009 Rate: 5 Price: 2 Redemption: 3 Frequency: 1 Basis: 2 Yield() returns: 3.406186157 Price() returns: 0.577520039 DIFFERENCE FROM EXPECTED VALUE: 2 - 0.577520039 = 1.422479961 = 71.1% Is that a bug in Excel? The reason I ask is because I have custom Price() and Yield() functions that allow a frequency of 12 (Excel's Yield and Price functions allow only 1, 2, or 4 for the frequency argument), and they always return the expected value in this test. These custom functions are made from the formulas and descriptions shown in the function reference for Excel's Price() and Yield() functions. I believe the problem is in Excel's Yield function, and not its Price function, because the custom Price function always returns the same results as Excel's Price function, given the same arguments. I discovered this discrepancy when testing these custom Price and Yield functions against Excel's Price and Yield functions. Aside from this problem, they always return the same values as Excel's functions, given the same arguments. Thanks for any information. Greg |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with Yield() function
On Sat, 27 Dec 2008 14:40:41 -0800 (PST), Greg Lovern wrote:
The Yield() and Price() functions are reciprocal in that Yield's return value, if used as the yld argument in Price(), and using all the same other arguments in Price() as used in Yield(), should return the same value (or very nearly the same value, allowing for very tiny rounding errors within the functions) as originally used for the pr argument in Yield(). I've tested that with several hundred random sets of arguments, and in most cases it works. But in some cases I get a very different value. For example: Settlement: Jan 4, 2009 Maturity: Sep 20, 2009 Rate: 5 Price: 2 Redemption: 3 Frequency: 1 Basis: 2 Yield() returns: 3.406186157 Price() returns: 0.577520039 DIFFERENCE FROM EXPECTED VALUE: 2 - 0.577520039 = 1.422479961 = 71.1% Is that a bug in Excel? The reason I ask is because I have custom Price() and Yield() functions that allow a frequency of 12 (Excel's Yield and Price functions allow only 1, 2, or 4 for the frequency argument), and they always return the expected value in this test. These custom functions are made from the formulas and descriptions shown in the function reference for Excel's Price() and Yield() functions. I believe the problem is in Excel's Yield function, and not its Price function, because the custom Price function always returns the same results as Excel's Price function, given the same arguments. I discovered this discrepancy when testing these custom Price and Yield functions against Excel's Price and Yield functions. Aside from this problem, they always return the same values as Excel's functions, given the same arguments. Thanks for any information. Greg I don't know what the error should be, but when I use <5% for the rate in the Yield and Price functions (and not <5 as you have), then the Price function returns 1.991023915. So there is an "error" of just under 1%. --ron |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with Yield() function
Thanks, apparently the error only happens at unrealistically high rates (such as 500% in this case..), and therefore doesn't matter. I was testing with randomly generated input values, not limiting the test to realistic values. I changed the test to keep the rate down, and at those rates the error doesn't seem to occur. Greg On Dec 27, 4:06*pm, Ron Rosenfeld wrote: On Sat, 27 Dec 2008 14:40:41 -0800 (PST), Greg Lovern wrote: The Yield() and Price() functions are reciprocal in that Yield's return value, if used as the yld argument in Price(), and using all the same other arguments in Price() as used in Yield(), should return the same value (or very nearly the same value, allowing for very tiny rounding errors within the functions) as originally used for the pr argument in Yield(). I've tested that with several hundred random sets of arguments, and in most cases it works. But in some cases I get a very different value. For example: Settlement: Jan 4, 2009 Maturity: Sep 20, 2009 Rate: 5 Price: 2 Redemption: 3 Frequency: 1 Basis: 2 Yield() returns: 3.406186157 Price() returns: 0.577520039 DIFFERENCE FROM EXPECTED VALUE: 2 - 0.577520039 = 1.422479961 = 71.1% Is that a bug in Excel? The reason I ask is because I have custom Price() and Yield() functions that allow a frequency of 12 (Excel's Yield and Price functions allow only 1, 2, or 4 for the frequency argument), and they always return the expected value in this test. These custom functions are made from the formulas and descriptions shown in the function reference for Excel's Price() and Yield() functions. I believe the problem is in Excel's Yield function, and not its Price function, because the custom Price function always returns the same results as Excel's Price function, given the same arguments. I discovered this discrepancy when testing these custom Price and Yield functions against Excel's Price and Yield functions. Aside from this problem, they always return the same values as Excel's functions, given the same arguments. Thanks for any information. Greg I don't know what the error should be, but when I use <5% for the rate in the Yield and Price functions (and not <5 as you have), then the Price function returns 1.991023915. *So there is an "error" of just under 1%. --ron- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Problem with Yield() function
On Sat, 27 Dec 2008 20:04:33 -0800 (PST), Greg Lovern wrote:
Thanks, apparently the error only happens at unrealistically high rates (such as 500% in this case..), and therefore doesn't matter. I was testing with randomly generated input values, not limiting the test to realistic values. I changed the test to keep the rate down, and at those rates the error doesn't seem to occur. Greg I am surprised that there is that large an error (1%) , but I don't have time to dig into the reasons. I'm glad it works well enough for you. --ron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Yield in Excel
Hi Greg,
The yield function in excel does not give the right value if I play around with the redemption value(% of par). I have checked this with other available online bond yield calculators such as http://www.moneychimp.com/calculator...e=calc_bondytm If I change the redemption value to 50 and the bond price to $50, ideally the coupon rate and the yield should be equal for any time period. While this happens in the calculator mentioned above, Excel gives me a different number. Is this actually a bug or am I missing the point? Test this in Excel... =YIELD("6/30/2009","6/30/2022",0.0958,50,50,2,0) Also test different redemption and price values and compare them with the calculator above. Greg Lovern wrote: Problem with Yield() function 29-Dec-08 Thanks, apparently the error only happens at unrealistically high rates (such as 500% in this case..), and therefore doesn't matter. I was testing with randomly generated input values, not limiting the test to realistic values. I changed the test to keep the rate down, and at those rates the error doesn't seem to occur. Greg On Dec 27, 4:06=A0pm, Ron Rosenfeld wrote: rote: n the ion EggHeadCafe - Software Developer Portal of Choice C# MSMQ - Send and Receive XML http://www.eggheadcafe.com/tutorials...nd-receiv.aspx |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Yield() function missing from XL2007 SP1 ATPVBAEN Analysis ToolPak | Excel Worksheet Functions | |||
what is PAR in the Yield Function | Excel Worksheet Functions | |||
In Excel, can a function yield a drop-down list? | Excel Worksheet Functions | |||
yield function | Excel Worksheet Functions | |||
yield to call | Excel Discussion (Misc queries) |