• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Compound Interest Function in VBA - Define only a range of Monthly Data

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!
 
Hi Henrique,


It may be better to do the calculation in Excel, read the result into VBA and clear the calculation from Excel
 
Hi kchiba!


Thanks for your sugestion, but the range that I work is dynamic.


Besides that I really want to understand why the formula doesn't work.


Some thoughts:


1. For every function that I use in VBA I have to use Application.WorksheetFunction?


So, using 2 functions it would be like that:


Application.WorksheetFunction.SUMPRODUCT(Application.WorksheetFunction.PRODUCT(b2:b1000+1)-1)


I've tried this but got an error


2. I'm trying to create a custom function that uses this formula. So that I can only select the range (monthly values) and then it returns the compound interest of the period.


Any ideas?
 
Henrique


I'm experiencing a bit of Deja vu with this post taking me back 3 months


Try the following User defined Function

To use copy and paste into a Code Module in VBA

[pre]
Code:
Function CompInt(myRange As Range) As Double
Dim prod As Double

prod = 1

For Each c In myRange
prod = prod * (c.Value + 1)
Next
CompInt = prod - 1

End Function
[/pre]

To Use:

In a cell use =CompInt(A1:A100)

Set the cell format to % as the value will come back as 1.1333 instead of 113.33%


Please note that you can't use values or an array to store values only a Range


ie: =CompInt(0.7,1,1.1,0.5) or =CompInt({0.7,1,1.1,0.5}) will return errors
 
Back
Top