I'm trying to create custom functions to do what Yield() and Price()
do except to also allow 12 as the fequency argument (Excel's built-in
Yield() and Price() accept only 1, 2, or 4 as the frequency argument;
for all other values they return an error).

The online help for Excel's Yield() and Price() functions show the
equations used to generate them.

I set up a worksheet to do randomized testing to find any differences
between the custom functions and Excel's functions. I'm finding that
although the equations usually return close to the same result, there
are sometimes large discrepancies.


For example:

Settlement: Aug 28, 6312
Maturity: Aug 30, 6360
Rate: 1.770369509
Pr: 4.972347127
Redemption: 1.900433108
Frequency: 4
Basis: 1

Excel's Yield returns: 28.31249092
Equation returns: 35.60430243


At first I assumed it was a problem with programming the equation, but
then I found this function here in this newsgroup:

http://groups.google.com/group/micro...0e0c2ffae4c225

That function, written by Myrna Larson, returns 35.60419324 for the
above example arguments, very close to what I had found.

I can provide more examples if desired. I've also found discrepancies
with Price, but I'll focus on Yield for now since two seperate efforts
by two people are giving similar results, both of them sometimes very
different than Excel's results.


So -- what is Excel's Yield() doing differently than what its online
help says it is doing???


Thanks,

Greg

Hi. I don't have an answer, but "in general" you usually buy (price)
that is "Lower" then the Redemption price. I may be wrong though.
Your Price is "Higher" than Redemption. You may want to double-check
your numbers. The fact that the year is in 6312 makes me question the
data also.
- - -
HTH :)
Dana DeLouis


Greg Lovern wrote:
I'm trying to create custom functions to do what Yield() and Price()
do except to also allow 12 as the fequency argument (Excel's built-in
Yield() and Price() accept only 1, 2, or 4 as the frequency argument;
for all other values they return an error).

The online help for Excel's Yield() and Price() functions show the
equations used to generate them.

I set up a worksheet to do randomized testing to find any differences
between the custom functions and Excel's functions. I'm finding that
although the equations usually return close to the same result, there
are sometimes large discrepancies.


For example:

Settlement: Aug 28, 6312
Maturity: Aug 30, 6360
Rate: 1.770369509
Pr: 4.972347127
Redemption: 1.900433108
Frequency: 4
Basis: 1

Excel's Yield returns: 28.31249092
Equation returns: 35.60430243


At first I assumed it was a problem with programming the equation, but
then I found this function here in this newsgroup:

http://groups.google.com/group/micro...0e0c2ffae4c225

That function, written by Myrna Larson, returns 35.60419324 for the
above example arguments, very close to what I had found.

I can provide more examples if desired. I've also found discrepancies
with Price, but I'll focus on Yield for now since two seperate efforts
by two people are giving similar results, both of them sometimes very
different than Excel's results.


So -- what is Excel's Yield() doing differently than what its online
help says it is doing???


Thanks,

Greg