Yield Calculation Problem (2002)

Topic

New Reply

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

Reply | Quote

Replies

I don’t know why, but it seems to get an error for any maturity date between 4/9/2004 and 9/7/2006.

Reply | Quote

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 Microsoft Knowledge Base Article 215214.

Reply | Quote

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 Microsoft Knowledge Base Article 215214.

Reply | Quote

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 price-specified 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 SP-1.

Reply | Quote

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).

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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).

Reply | Quote

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 price-specified 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 SP-1.

Reply | Quote