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 -