Henrique Carvalho
New Member
Hi Guys!
I have some data like this:
Month | Asset
Jan/10 0.50%
feb/10 1.50%
... ...
In Excel, you could just calculate the compund interest using:
=SUMPRODUCT(PRODUCT(b2:b1000+1)-1) Where the data range is in b2 Down
This formula is equivalent of: (1+0.0050)*(1+0.0150)*(1+...)*...-1
Although, I'm having some problems with VBA.
I can't just type this:
Application.WorksheetFunction.SUMPRODUCT(PRODUCT(b2:b1000+1)-1)
or this
Application.WorksheetFunction.SUMPRODUCT(PRODUCT("b2:b1000"+1)-1)
or defining a range for this, like:
set rng = range("b2:b1000")
And then Application.WorksheetFunction.SUMPRODUCT(PRODUCT(rng + 1)-1)
What should I try to resolve this?
Thanks in Advance!
I have some data like this:
Month | Asset
Jan/10 0.50%
feb/10 1.50%
... ...
In Excel, you could just calculate the compund interest using:
=SUMPRODUCT(PRODUCT(b2:b1000+1)-1) Where the data range is in b2 Down
This formula is equivalent of: (1+0.0050)*(1+0.0150)*(1+...)*...-1
Although, I'm having some problems with VBA.
I can't just type this:
Application.WorksheetFunction.SUMPRODUCT(PRODUCT(b2:b1000+1)-1)
or this
Application.WorksheetFunction.SUMPRODUCT(PRODUCT("b2:b1000"+1)-1)
or defining a range for this, like:
set rng = range("b2:b1000")
And then Application.WorksheetFunction.SUMPRODUCT(PRODUCT(rng + 1)-1)
What should I try to resolve this?
Thanks in Advance!