Problem with Yield() function

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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


.

Relevant Pages

  • Investment Possibilities For Retirees
    ... Cusip: 989701AX5 ... Current Price: $24.66 ... PFF, yield 7.6% ... Div Yield: 6.95% ...
    (soc.retirement)
  • Re: Legality of not using return portion of airline tickets?
    ... anyway into higher prices fares it would appear to be an effective yield ... to the low cost carrier who don't have those artificial price barriers. ... They need to offer competitive fares. ... travellers and charge more to casual travellers. ...
    (rec.travel.air)
  • Re: house prices - always and forever upward?
    ... history of government attempts to hyperinflate out of the problem show a ... in their desperate search for a decent yield. ... There's nothing wrong with "savings" as a concept per se, ... What else is a decade of>10% house price inflation but targeted ...
    (uk.finance)
  • Re: Problem with Yield() function
    ... return value, if used as the yld argument in Price, and using all ... the same other arguments in Price() as used in Yield, ... Yield() returns: 3.406186157 ... The reason I ask is because I have custom Price() and Yield ...
    (microsoft.public.excel.worksheet.functions)