I have been able to call the object before within a stored procedure and I had it setup as a Sub method and sent back the data via SqlContext.Pipe.Send. I changed it to a function and had it returning a string and now I'm having the problem. Here is my code please let me know if you see something.
Imports
Microsoft.SqlServer.Server
Public
Class Theoretical
Public Shared Function CalcValue(ByVal Spread As Double, ByVal TimeToMaturityQuarters As Int32, ByVal LGD As Double, ByVal OID As Double, _
ByVal Yr1 As Double, ByVal Yr2 As Double, ByVal Yr3 As Double, ByVal Yr4 As Double, ByVal Yr5 As Double, ByVal LiborSpread As Double) As String
Dim numberofquarters As Int32 = 161 '40 years max
Dim cdf(161), pdf(161), yr(4) As Double
Dim PayOffMid, PayOffLow, PayOffHigh, TheorecticalPrice, TheorecticalSpread As Double
Dim PriceMax As Double = 2
Dim PriceMin As Double = 0
Dim PriceMid As Double = 1
Dim PriceAnswer As Double = 100
Dim SpreadMax As Double = 0.5
Dim SpreadMin As Double = 0
Dim SpreadMid As Double = 0.25
Dim SpreadAnswer As Double = 100
Try
yr(0) = Yr1
yr(1) = Yr2
yr(2) = Yr3
yr(3) = Yr4
yr(4) = Yr5
'This section will calculate the Probability Density Function & the
'Cumulative Distribution Function
For counter As Int32 = 0 To numberofquarters
If counter < 4 Then
pdf(counter) = Math.Round((yr(0) / 4), 14)
ElseIf counter < 20 Then
pdf(counter) = Math.Round((yr(Math.Floor(counter / 4) - 1) * IIf(Math.Abs(((3 - counter)
Mod 4)) = 0, 0, (4 - Math.Abs(((3 - counter) Mod 4)))) * 0.25 + yr(Math.Floor(counter / 4)) * IIf(Math.Abs(((counter + 1) Mod 4)) = 0, 4, (Math.Abs(((counter + 1) Mod 4)))) * 0.25) / 4, 14)
Else
pdf(counter) = Math.Round(yr(4) / 4, 14)
End If
If counter = 0 Then
cdf(counter) = Math.Round(pdf(counter), 14)
Else
cdf(counter) = Math.Round(cdf(counter - 1) + pdf(counter), 14)
End If
Next
'End PDF & CDF
'Calculate Price
While (Math.Abs(PriceAnswer) > 0.001)
PayOffLow = Payofffind(Spread / 10000, TimeToMaturityQuarters, cdf, pdf, PriceMin, LGD, LiborSpread)
PayOffMid = Payofffind(Spread / 10000, TimeToMaturityQuarters, cdf, pdf, PriceMid, LGD, LiborSpread)
PayOffHigh = Payofffind(Spread / 10000, TimeToMaturityQuarters, cdf, pdf, PriceMax, LGD, LiborSpread)
TheorecticalPrice = PriceMid
If PayOffHigh > 0 Then
PriceAnswer = 0
TheorecticalPrice = 0
ElseIf (PayOffLow < 0) Then
PriceMax = PriceMax * 2
PriceMid = PriceMid * 2
PriceAnswer = PayOffMid
ElseIf (PayOffMid > 0) Then
PriceAnswer = PayOffMid
PriceMin = PriceMid
PriceMid = (PriceMid + PriceMax) / 2
Else
PriceAnswer = PayOffMid
PriceMax = PriceMid
PriceMid = (PriceMid + PriceMin) / 2
End If
End While
'Calculate Spread
While (Math.Abs(SpreadAnswer) > 0.001)
PayOffLow = Payofffind(SpreadMin, TimeToMaturityQuarters, cdf, pdf, OID, LGD, LiborSpread)
PayOffMid = Payofffind(SpreadMid, TimeToMaturityQuarters, cdf, pdf, OID, LGD, LiborSpread)
PayOffHigh = Payofffind(SpreadMax, TimeToMaturityQuarters, cdf, pdf, OID, LGD, LiborSpread)
TheorecticalSpread = SpreadMid
If PayOffLow > 0 Then
SpreadAnswer = 0
TheorecticalSpread = 0
ElseIf (PayOffHigh < 0) Then
SpreadMax = SpreadMax * 2
SpreadMid = SpreadMid * 2
SpreadAnswer = PayOffMid
ElseIf (PayOffMid < 0) Then
SpreadAnswer = PayOffMid
SpreadMin = SpreadMid
SpreadMid = (SpreadMid + SpreadMax) / 2
Else
SpreadAnswer = PayOffMid
SpreadMax = SpreadMid
SpreadMid = (SpreadMid + SpreadMin) / 2
End If
End While
Return (((TheorecticalPrice * 100) & "|" & (TheorecticalSpread * 100 * 100)))
Catch ex As Exception
SqlContext.Pipe.Send(
"Error - " & ex.Message)
End Try
End Function
Private Shared Function Payofffind(ByRef Spread As Double, ByRef QuarterCalc As Int32, ByRef cdf() As Double, ByRef pdf() As Double, _
ByRef OID As Double, ByRef LGD As Double, ByRef LiborSpread3m As Double) As Double
Dim Payoff As Double = 0
Dim counter As Int32 = 0
Try
While counter < QuarterCalc
If counter = 0 Then
Payoff += Math.Round((pdf(counter)) * (1 - LGD) * ((QuarterCalc - counter) / QuarterCalc), 14)
ElseIf counter < (QuarterCalc - 1) Then
Payoff += Math.Round((((counter) * (1 + LiborSpread3m + Spread) / (QuarterCalc)) + (1 - LGD) * ((QuarterCalc - counter) / QuarterCalc)) * (
CType(1, Double) - cdf(counter - 1)) * (pdf(counter)), 14)
Else
Payoff += Math.Round((1 - cdf(counter)) * (1 + LiborSpread3m + Spread), 14)
End If
counter += 1
End While
Return Payoff - (OID * (1 + LiborSpread3m))
Catch ex As Exception
Throw ex
End Try
End Function
End
Class