Call PRICE function via VBA

[jomni]

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's 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 Function

[Carim]

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

[jomni]

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

Must be something wrong in my code.

[Carim]

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

[jomni]

I have that installed as well

[Mike Middleton]

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

[jomni]

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

[Dana DeLouis]

> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>