Problem with Yield() function

[Greg Lovern]

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

 

[Greg Lovern]

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

 

[Ron Rosenfeld]

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

 

[Greg Lovern]

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

 

[Ron Rosenfeld]

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

 

[rahul k]

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/popup/calculator.htm?mode=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:

Re: Problem with Yield() function
29-Dec-08

Thanks, apparently the error only happens at unrealistically hig
rates (such as 500% in this case..), and therefore doesn't matter

I was testing with randomly generated input values, not limiting th
test to realistic values. I changed the test to keep the rate down
and at those rates the error doesn't seem to occur

Gre

rote
n th
ion

EggHeadCafe - Software Developer Portal of Choice
C# MSMQ - Send and Receive XML
http://www.eggheadcafe.com/tutorial...08b-9332ed9da498/c-msmq--send-and-receiv.aspx