compounded return for each loop problem

[Chronicles84]

Hi,

I'm trying to create a UDF function to calculate a return on a principal amount, with different interest rates for different periods. I know there are built in excel functions that can do this, but I'm trying to learn more about vba through this simple example.

Here is what I've written so far:

Function compounded_returns(Principal As Double, Rets As Range) As Double

Dim vbCell As Range
compounded_returns = 0


    For Each vbCell In Rets
        compounded_returns = Principal * (1 + vbCell.Value)
    Next vbCell


compounded_returns = compounded_returns


End Function

The problem is when executed, the UDF only returns the principal * the last range. The difference between the UDF and Excel's function is shown below:

[TABLE="width: 500"]
<tbody>[TR]
[TD]Principal[/TD]
[TD]Rets[/TD]
[/TR]
[TR]
[TD]10,000[/TD]
[TD]5%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]6%[/TD]
[/TR]
[TR]
[TD][/TD]
[TD]7%[/TD]
[/TR]
[TR]
[TD]Excel's FVSchedule[/TD]
[TD]11909.1[/TD]
[/TR]
[TR]
[TD]UDF[/TD]
[TD]10700[/TD]
[/TR]
</tbody>[/TABLE]

Can anyone point out how i need to modify the UDF? your help/advice would be appreciated

 

[Chronicles84]

argh, the copy and paste did not go so well. Obviously, the full code includes the original function call

Function compounded_returns(Principal As Double, Rets As Range) As Double

Dim vbCell As Range
compounded_returns = 0




    For Each vbCell In Rets
        compounded_returns = Principal * (1 + vbCell.Value)
    Next vbCell


compounded_returns = compounded_returns

 

[Marcelo Branco]

Welcome to Mr Excel

See if this does what you need

Function compounded_returns(Principal As Double, Rets As Range) As Double
    Dim vbCell As Range, dbMult As Double
    
    dbMult = 1
    For Each vbCell In Rets
        dbMult = dbMult * (1 + vbCell.Value)
    Next vbCell
    compounded_returns = Principal * dbMult
End Function

M.

 

[Chronicles84]

Hi Marcelo

Thank you very much. Yes, this has the desired effect - I will have to this solution more to know for the future - thank you again!

 

[Marcelo Branco]

You are welcome. Thanks for the feedback.

M.