Printable View
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
Code: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?
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.
@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
Code:Function investreturn(rate, beginning)
For i = 1 To 10
average_bal = (beginning + ending) / 2
investreturn = average_bal * rate
Next i
End Function