Help with Excel Macro to computer investment return

[XHELL]

Beginning


Claim Payments


Expenses


Return Premium


Profit Share


Taxes


End of Period


Avg Annual Balance


Interest


Investment Return

I have the above and want to compute the investment return which is intereest x avg annual balance.

Wrote the following UDF:

Sub udf_investreturn()


End Sub

Function investreturn(rate, beginning)


For i = 1 To 10
average_bal = beginning + ending / 2
investreturn = average_bal * rate
Next i
End Function


But my investment return is slighly off. Why?

[JackPaul]

I noticed that this thread is a couple of years old, but no worries, I'm still here to help you out with your Excel macro conundrum! First, in your ""For"" loop, I see that you're iterating from 1 to 10, but it seems like you're not actually using the loop index ""i"" within the loop. Make sure to incorporate the loop index into your calculations to ensure you're considering all the relevant values. Additionally, it looks like you're missing the ""ending"" value in your formula for calculating the average balance. You'll need to include that value in the calculation.

[Aussiebear]

@JackPaul, Yes the thread is getting a little longer in the tooth than we would prefer.

The result may well be because the initial formula was missing some brackets


Function investreturn(rate, beginning)
For i = 1 To 10
average_bal = (beginning + ending) / 2
investreturn = average_bal * rate
Next i
End Function