For i = 1 To 99

As an alternative to a fixed number of loops, here's one idea:

Do
vGap = vPrice - Price(vSettlement, vMaturity, vCoupon, vGuess,
vRedemption, iFrequency)

vDerivative = Price(vSettlement, vMaturity, vCoupon, vGuess,
vRedemption, iFrequency)
vDerivative = (vDerivative - Price(vSettlement, vMaturity, vCoupon,
vGuess + 0.001, vRedemption, iFrequency)) / 0.001

vGuess = vGuess - (vGap / vDerivative)
Loop While Abs(vGap) 0.0000000000001
YIELD_MANUAL = vGuess

--
HTH. :)
Dana DeLouis
Windows XP, Office 2003


"jomni" wrote in
message ...

Mike,

Great! That surely fixed my problem. I soon found out that my code is
quite cumbersome so I chaged it to use the *Newton-Raphson* method.

The resulting code is shorter and with less iteratons.


Function YIELD_MANUAL(vSettlement, vMaturity, vCoupon, vPrice,
vRedemption, iFrequency)

Dim vGuess As Variant
Dim vGap As Variant
Dim vDerivative As Variant

'Set vGuess to coupon
vGuess = vCoupon

'I used For Next so that it stops after 99 tries (but it shouldn't be
that long in theory)
For i = 1 To 99
'vPrice - Price (vGuess) - vPrice
vGap = vPrice - Price(vSettlement, vMaturity, vCoupon, vGuess,
vRedemption, iFrequency)
'(Price (vGuess) - Price (vGuess + .001))/ .001
vDerivative = (Price(vSettlement, vMaturity, vCoupon, vGuess,
vRedemption, iFrequency) _
- Price(vSettlement, vMaturity, vCoupon, vGuess + 0.001,
vRedemption, iFrequency)) / 0.001
'This is the Newton-Raphson formula
vGuess = vGuess - (vGap / vDerivative)
'If resulting vGap is already 0, then end the function
'If vGap = 0 Then GoTo 10
Next i

10 YIELD_MANUAL = vGuess

End Function


--
jomni
------------------------------------------------------------------------
jomni's Profile:
http://www.excelforum.com/member.php...fo&userid=7744
View this thread: http://www.excelforum.com/showthread...hreadid=524671