Total return function, vba

[BJM23]

Basically, I am trying to create a function that returns the total return for an array of numbers. Here is an example of the formula;

((1+.25/100)*(1+.5/100)*(1+1.5/100)-1)*100
The forumula in excel would look something like this;
=(Product(A1:C1/100+1)-1)*100 given A1=.25,B1=.5,C1=1.5

Function Totaltreturn(rngRange As Range)
Dim vbCell As Range
For Each vbCell In rngRange
Totalreturn = (Product((Totalreturn * vbCell) / 100 + 1) - 1) * 100
Next vbCell
End Function

I started this code but am an amateur to vba so I am probably missing something quite basic. Please let me know, thank you in advance for your help.

 

[cb12]

Try:

Function Totalreturn(rngRange As Range)
Dim vbCell As Range

    For Each vbCell In rngRange
        Totalreturn = (Application.Product(vbCell.Value / 100 + 1) - 1) * 100
    Next vbCell
    
End Function

 

[shg]

      -A-- B --C--- -------------D-------------
  1   2.5%   11.17% C1: {=PRODUCT(1+A1:A3) - 1}
  2   3.1%   11.17% C2: =TotalReturn(A1:A3)    
  3   5.2%

Function TotalReturn(r As Range) As Double
    TotalReturn = Evaluate("product(1 + " & r.Address & ") - 1")
End Function

 

[cb12]

Might help if I took a breath and looked what the formula was doing rather than just mimicking what I think it says in VBA. Apologies.

 

[AlphaFrog]

Why don't you use your formula?
=(Product(A1:C1/100+1)-1)*100

I seems to work if you enter it as an array formula.
Enetr using Ctrl+Shift+Enter
Excel will automatically surround it with {braces}.

If you want a User Defined Function (UDF), then try this...

Function Totaltreturn(rngRange As Range) As Double
    Dim vbCell As Range
    Totaltreturn = 1
    For Each vbCell In rngRange
        Totaltreturn = Totaltreturn * (vbCell.Value / 100 + 1)
    Next vbCell
    Totaltreturn = (Totaltreturn - 1) * 100
End Function

 

[BJM23]

Thanks everyone.

Yes, the formula is essentially compounding returns by linking them.

Understood about activating the sell with control+shift+enter, now would I also need to do this with the function? For some reason Alpha, I still can't get to work with the code.

 

[AlphaFrog]

If you use your original formula
=(Product(A1:C1/100+1)-1)*100
...then enter it using Ctrl+Shift+Enter. You wouldn't need the VBA code.

If you want to have a User Defined Function, then enter in the code in a VBA module and use this formula on the worksheetsheet.
=Totaltreturn(A1:C1)
... do not enter this formula with Ctrl+Shift+Enter.

 

[BJM23]

Alpha, have you tried the code, and does the result reflect something other than zero?

 

[AlphaFrog]

All methods returned the same result.

<br /><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">0.25</td><td style="text-align: center;;">0.5</td><td style="text-align: center;;">1.5</td><td style="text-align: center;;"></td><td style="font-weight: bold;text-align: center;;">Method</td><td style="font-weight: bold;text-align: center;;">Result</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Formula</td><td style="text-align: center;;">2.26251875</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">Array Formula</td><td style="text-align: center;;">2.26251875</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;">TotalReturn UDF</td><td style="text-align: center;;">2.26251875</td></tr></tbody></table><br /><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F2</th><td style="text-align:left">=(<font color="Blue">(<font color="Red">1+0.25/100</font>)*(<font color="Red">1+0.5/100</font>)*(<font color="Red">1+1.5/100</font>)-1</font>)*100</td></tr><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F4</th><td style="text-align:left">=Totalreturn(<font color="Blue">A1:C1</font>)</td></tr></tbody></table></td></tr></table><br /><table cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">F3</th><td style="text-align:left">{=(<font color="Blue">PRODUCT(<font color="Red">A1:C1/100+1</font>)-1</font>)*100}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />
TotalReturn UDF code

Function TotalReturn(rngRange As Range) As Double
    Dim vbCell As Range
    TotalReturn = 1
    For Each vbCell In rngRange
        TotalReturn = TotalReturn * (vbCell.Value / 100 + 1)
    Next vbCell
    TotalReturn = (TotalReturn - 1) * 100
End Function

 

[BJM23]

Ok, lets focus on the vba code, because the other calculations I already completely understood from the get go as I am familiar with compounding returns. Why would it return zero if I have entered exactly what you have provided? It clearly recognizes the code or else I would have gotten a name error. Also, the code isn't breaking, does it matter if I am using excel 2007? Just not sure why zero would be the result...