Results 1 to 6 of 6
Thread: Yield Calculation Problem (2002)

20031008, 20:24 #1
 Join Date
 Sep 2003
 Location
 Toronto, Ontario, Canada
 Posts
 124
 Thanks
 0
 Thanked 0 Times in 0 Posts
Yield Calculation Problem (2002)
This is a real doozy. If anyone can tell me why Im getting an error in the cell F4, I will be extremely grateful.
Thanks,
naut

20031008, 21:20 #2
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Yield Calculation Problem (2002)
I don't know why, but it seems to get an error for any maturity date between 4/9/2004 and 9/7/2006.
Legare Coleman

20031008, 21:34 #3
 Join Date
 Dec 2000
 Location
 Salt Lake City, Utah, USA
 Posts
 9,508
 Thanks
 0
 Thanked 6 Times in 6 Posts
Re: Yield Calculation Problem (2002)
Cell F4 also ceases to error out if the rate is higher than 0.1% (All have zero interest rates.) I do not know the answer, though I suspect that Excel cannot calculate the result within the 100 iteration maximum for the function, but I'm not smart enough to prove it! FWIW see <!mskb=215214>Microsoft Knowledge Base Article 215214<!/mskb>.
John ... I float in liquid gardens
UTC 7ąDS

20031009, 02:58 #4
 Join Date
 Sep 2003
 Location
 Louisville, Kentucky, USA
 Posts
 134
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Yield Calculation Problem (2002)
If you multiply your price by 100 and set your redemption value to 100, you get a valid answer.
In the Excel help, it says the YIELD function uses Newtonian iterations to get its answer. Newtonian iterations operate by starting with an initial guess at the yield. It then calcs a price from the cashflows you've specified. If the calc'd price is different from your specified price, it determines whether to adjust its guess up or down. It will continue this process until the calc'd price is "sufficiently close" to the specified price. The criteria for sufficiently close is usually abs(calc'd pricespecified price)/specified price. So if specified price is small (as it is in your original example), the allowable error is very small and Excel may not be able to reach a satisfactory answer prior to 100 iterations.
BTW, because your examples do not have any coupon payments, the formula I've added in my attachment presents a closed form solution (i.e. can be directly calc'd without going through an iterative process which always introduces some error). While we're definitely splitting hairs, my formula is more accurate than yours.
This was done in Excel 2002 SP1.

20031009, 12:06 #5
 Join Date
 Sep 2003
 Location
 Toronto, Ontario, Canada
 Posts
 124
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Yield Calculation Problem (2002)
Wow, thanks a ton for the input guys.
Do you think it would be possible for this formula to work if Excel could perform more than 100 iterations?
The main thing that was wracking my brian was the fact that we have a specified price which is small (with a working yeild formula) and the specified price which is larger (again with a working yield formula).

20031009, 17:18 #6
 Join Date
 Sep 2003
 Location
 Louisville, Kentucky, USA
 Posts
 134
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: Yield Calculation Problem (2002)
Newtonian iterations typically reach an answer within very few iterations (i.e. 5). If Excel hasn't reached an answer in 100 iterations, it's never going to get one.
It's not that there's a threshhold of X at which point the iteration process fails. You have a "small" number and that makes the iteration process susceptible to failure.
If you really want to use the YIELD function, just change the formula in F3 from =YIELD(B3,C3,0,D3,1,2,1) to =YIELD(B3,C3,0,100*D3,100,2,1) and copy that down.