J
James
What I'm attempting to do: calculate IRR (internal rate of return) and
display the results in a textbox based on the input/calculations.
I'm not a seasoned coder, just picking this up new over the past couple of
weeks so my thoughts as to what should be right could be completely off.
Here is the code I came up with:
--------------------------------------
Private Sub CalcIRR()
'Declare counters and array (up to 240 payments + initial
negative value)
Dim CounterOne As Integer
Dim CounterTwo As Integer
Static Values(241) As Double
'Assign values to the Value Array
Values(0) = (Me.CapitalizedCost * (-1)) + Me.AdminFee +
Me.txtCIFinalBaseMonthlyPayment _
+ Me.SecurityDeposit + Me.txtCIProRataTotal
For CounterOne = 1 To (Me.Term - 1)
Values(Counter) = Me.txtCIFinalBaseMonthlyPayment
Next
Values(Me.Term) = Me.Residual - Me.SecurityDeposit
'Calculate the Internal Rate of Return
For CounterTwo = 0 To Me.Term
Me.txtGENIRR = IRR(Values()) * 12
Next
End Sub
--------------------------------------
Just for argument sake here are some test values:
Me.CapitalizedCost = 20000.00
Me.AdminFee = 100.00
Me.txtCIFinalBaseMonthlyPayment = 572.64
Me.txtCIProRataTotal = 283.22
Me.Term = 42
Me.Residual = 100.00
Me.SecurityDeposit = 0.00
Additional thoughts: It is known that the first value of the array has to be
negative, the rest have to be positive. Thus what should be Value(0) is the
sum of most of the variables (textboxes on a form) which ends up being
-19044.14. Values(1) through Values(41) are each 572.64, the amount of each
cashflow. The final cashflow, Value(Me.Term) which is the same as Value(42),
equals the residual minus the security deposit which would be 100.00.
In Excel, I can easily calculate this using the formula =IRR(H2:H74,)*12
which yields an answer of 12.66%, which I know is correct, however,
Access/VBA is causing me problems and it errors out.
If anyone could please help I'd be greatly appreciative. Searching here and
on the net has not yielded any solutions so far.
Thank you!
James
display the results in a textbox based on the input/calculations.
I'm not a seasoned coder, just picking this up new over the past couple of
weeks so my thoughts as to what should be right could be completely off.
Here is the code I came up with:
--------------------------------------
Private Sub CalcIRR()
'Declare counters and array (up to 240 payments + initial
negative value)
Dim CounterOne As Integer
Dim CounterTwo As Integer
Static Values(241) As Double
'Assign values to the Value Array
Values(0) = (Me.CapitalizedCost * (-1)) + Me.AdminFee +
Me.txtCIFinalBaseMonthlyPayment _
+ Me.SecurityDeposit + Me.txtCIProRataTotal
For CounterOne = 1 To (Me.Term - 1)
Values(Counter) = Me.txtCIFinalBaseMonthlyPayment
Next
Values(Me.Term) = Me.Residual - Me.SecurityDeposit
'Calculate the Internal Rate of Return
For CounterTwo = 0 To Me.Term
Me.txtGENIRR = IRR(Values()) * 12
Next
End Sub
--------------------------------------
Just for argument sake here are some test values:
Me.CapitalizedCost = 20000.00
Me.AdminFee = 100.00
Me.txtCIFinalBaseMonthlyPayment = 572.64
Me.txtCIProRataTotal = 283.22
Me.Term = 42
Me.Residual = 100.00
Me.SecurityDeposit = 0.00
Additional thoughts: It is known that the first value of the array has to be
negative, the rest have to be positive. Thus what should be Value(0) is the
sum of most of the variables (textboxes on a form) which ends up being
-19044.14. Values(1) through Values(41) are each 572.64, the amount of each
cashflow. The final cashflow, Value(Me.Term) which is the same as Value(42),
equals the residual minus the security deposit which would be 100.00.
In Excel, I can easily calculate this using the formula =IRR(H2:H74,)*12
which yields an answer of 12.66%, which I know is correct, however,
Access/VBA is causing me problems and it errors out.
If anyone could please help I'd be greatly appreciative. Searching here and
on the net has not yielded any solutions so far.
Thank you!
James