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