G
Guest
I have the following code written in Access 97. It stops dead in its' tracks
on the last line with an Error 5. Any help would be appreciated. What the
code is supposed to do is calculate the IRR for 10 years (13 periods per
year) for capital projects.
Public Function fncCalcIRR(dblPlanAmt As Double, dblYr0 As Double, _
dblYr1 As Double, dblYr2 As Double, dblYr3 As Double, dblYr4 As Double, _
dblYr5 As Double, dblYr6 As Double, dblYr7 As Double, dblYr8 As Double, _
dblYr9 As Double, dblYr10 As Double) As Double
Dim dblGuess As Double
Dim cntYrs As Byte
Dim cntPer As Byte
Dim dblCurYr As Double
Dim test As Double
' Static Per130NPV(130) As Double
Static Per130IRR(131) As Double
dblGuess = 0.1
' Populate array
For cntYrs = 0 To 9
Select Case cntYrs
Case 0
dblCurYr = Nz(dblYr1 / 13, 0)
Case 1
dblCurYr = Nz(dblYr2 / 13, 0)
Case 2
dblCurYr = Nz(dblYr3 / 13, 0)
Case 3
dblCurYr = Nz(dblYr4 / 13, 0)
Case 4
dblCurYr = Nz(dblYr5 / 13, 0)
Case 5
dblCurYr = Nz(dblYr6 / 13, 0)
Case 6
dblCurYr = Nz(dblYr7 / 13, 0)
Case 7
dblCurYr = Nz(dblYr8 / 13, 0)
Case 8
dblCurYr = Nz(dblYr9 / 13, 0)
Case 9
dblCurYr = Nz(dblYr10 / 13, 0)
Case Else
End Select
' Populate array for current year
For cntPer = 1 To 13
test = cntPer + (cntYrs * 13)
Per130IRR(test) = dblCurYr
Next cntPer
Next cntYrs
Per130IRR(0) = (Nz(dblPlanAmt, 0) * -1) + Nz(dblYr0, 0)
' Calculate IRR
fncCalcIRR = IRR(Per130IRR(), dblGuess)
on the last line with an Error 5. Any help would be appreciated. What the
code is supposed to do is calculate the IRR for 10 years (13 periods per
year) for capital projects.
Public Function fncCalcIRR(dblPlanAmt As Double, dblYr0 As Double, _
dblYr1 As Double, dblYr2 As Double, dblYr3 As Double, dblYr4 As Double, _
dblYr5 As Double, dblYr6 As Double, dblYr7 As Double, dblYr8 As Double, _
dblYr9 As Double, dblYr10 As Double) As Double
Dim dblGuess As Double
Dim cntYrs As Byte
Dim cntPer As Byte
Dim dblCurYr As Double
Dim test As Double
' Static Per130NPV(130) As Double
Static Per130IRR(131) As Double
dblGuess = 0.1
' Populate array
For cntYrs = 0 To 9
Select Case cntYrs
Case 0
dblCurYr = Nz(dblYr1 / 13, 0)
Case 1
dblCurYr = Nz(dblYr2 / 13, 0)
Case 2
dblCurYr = Nz(dblYr3 / 13, 0)
Case 3
dblCurYr = Nz(dblYr4 / 13, 0)
Case 4
dblCurYr = Nz(dblYr5 / 13, 0)
Case 5
dblCurYr = Nz(dblYr6 / 13, 0)
Case 6
dblCurYr = Nz(dblYr7 / 13, 0)
Case 7
dblCurYr = Nz(dblYr8 / 13, 0)
Case 8
dblCurYr = Nz(dblYr9 / 13, 0)
Case 9
dblCurYr = Nz(dblYr10 / 13, 0)
Case Else
End Select
' Populate array for current year
For cntPer = 1 To 13
test = cntPer + (cntYrs * 13)
Per130IRR(test) = dblCurYr
Next cntPer
Next cntYrs
Per130IRR(0) = (Nz(dblPlanAmt, 0) * -1) + Nz(dblYr0, 0)
' Calculate IRR
fncCalcIRR = IRR(Per130IRR(), dblGuess)