I'm having problems calling the Excel Price (bond) function within VBA.
I'm trying to build a manual yield function that needs to call excel'
PRICE function. But i get #VALUE

Application.Price(...) does not work.
Application.WorksheetFunction.Price(...) does not work either.

_below_is_my_code:_
Function YIELDMANUAL(vSettlement, vMaturity, vCoupon, vPrice
vRedemption, iFrequency)

Dim vGuess As Variant
Dim vGap As Variant

vGuess = vCoupon.Value 'set Guess rate to coupon
vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity
vCoupon, vGuess, vRedemption, iFrequency) - vPrice

'----------
If vGap 0 Then

Do
vGuess = vGuess + 0.000001
vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity
vCoupon, vGuess, vRedemption, iFrequency) - vPrice
Loop While vGap 0

Do
vGuess = vGuess - 0.0000001
vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity
vCoupon, vGuess, vRedemption, iFrequency) - vPrice
Loop While vGap < 0

Do
vGuess = vGuess + 0.00000001
vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity
vCoupon, vGuess, vRedemption, iFrequency) - vPrice
Loop While vGap 0

Do
vGuess = vGuess - 0.000000001
vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity
vCoupon, vGuess, vRedemption, iFrequency) - vPrice
Loop While vGap < 0

Do
vGuess = vGuess + 0.0000000001
vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity
vCoupon, vGuess, vRedemption, iFrequency) - vPrice
Loop While vGap 0

Do
vGuess = vGuess - 0.00000000001
vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity
vCoupon, vGuess, vRedemption, iFrequency) - vPrice
Loop While vGap < 0

'----------
ElseIf vGap < 0 Then

Do
vGuess = vGuess - 0.000001
vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity
vCoupon, vGuess, vRedemption, iFrequency) - vPrice
Loop While vGap < 0

Do
vGuess = vGuess + 0.0000001
vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity
vCoupon, vGuess, vRedemption, iFrequency) - vPrice
Loop While vGap 0

Do
vGuess = vGuess - 0.00000001
vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity
vCoupon, vGuess, vRedemption, iFrequency) - vPrice
Loop While vGap < 0

Do
vGuess = vGuess + 0.000000001
vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity
vCoupon, vGuess, vRedemption, iFrequency) - vPrice
Loop While vGap 0

Do
vGuess = vGuess - 0.0000000001
vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity
vCoupon, vGuess, vRedemption, iFrequency) - vPrice
Loop While vGap < 0

Do
vGuess = vGuess + 0.00000000001
vGap = Application.WorksheetFunction.Price(vSettlement, vMaturity
vCoupon, vGuess, vRedemption, iFrequency) - vPrice
Loop While vGap 0
'----------
End If

YIELDMANUAL = vGuess

End Functio

--
jomn
-----------------------------------------------------------------------
jomni's Profile: http://www.excelforum.com/member.php...nfo&userid=774
View this thread: http://www.excelforum.com/showthread.php?threadid=52467

Hi Jomni,

I am just wondering if, in Tools Add Ins, you have ticked off both
Analysis ToolPack, and
Analysis TooPack - VBA ...

HTH
Cheers
Carim

Yes, the add-in is installed.
I can also call the PRICE function in the worksheet cell.

Must be something wrong in my code

--
jomn
-----------------------------------------------------------------------
jomni's Profile: http://www.excelforum.com/member.php...nfo&userid=774
View this thread: http://www.excelforum.com/showthread.php?threadid=52467

Jomni,

May be I did not express myself clearly enough ...
In addition to the standard addin, there is another addin, dedicated to
VBA
named "Analysis TooPack - VBA "
which needs to be activated for VBA to be operational ...

HTH
Carim

I have that installed as well :confused

--
jomn
-----------------------------------------------------------------------
jomni's Profile: http://www.excelforum.com/member.php...nfo&userid=774
View this thread: http://www.excelforum.com/showthread.php?threadid=52467

jomni -

1. Load Analysis Tookpak - VBA

2. In your VBA project, use Tools | References to create a reference to the
ToolPak file.

3. In your project, call it like a VBA function (without Application and
without Application.WorksheetFunction).

- Mike
www.mikemiddleton.com

Mike,

Great! That surely fixed my problem. I soon found out that my code i
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 b
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 Functio

--
jomn
-----------------------------------------------------------------------
jomni's Profile: http://www.excelforum.com/member.php...nfo&userid=774
View this thread: http://www.excelforum.com/showthread.php?threadid=52467

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