Dynamic arrays And Financial Functions

[jamesbeau]

Hi guys,

I am trying to create a function which generates the required return on a savings account given its, current_value, annual_savings, target_value and n_years.

I have attempted to create a dynamic array cf() with current_value as cf(0), target as cf(n_year+1) and the value for annual_savings as all the other cashflows in the middle.

I haven't had much success and was wondering if someone could help me creating a structure like this.

The idea being once I have created the array I can just take the WorksheetFunction.IRR(cf()).

Any help would be greatly appreciated!

[Paul_Hossler]

Maybe something like this.

No error checking

I used 10 in my test



Option Explicit
Sub phh()
Dim cf() As Double
Dim n_years As Long

n_years = InputBox("How many Years?")

ReDim cf(0 To n_years + 1)

cf(0) = 100 ' current value
cf(n_years + 1) = 1000000 ' target

cf(1) = 50
cf(2) = 100
'etc.
End Sub





15767

[snb]

Savings 120, dividend 6%, 30 years


Sub M_snb()
ReDim sn(29)

sn(0) = 120
For j = 1 To UBound(sn)
sn(j) = sn(j - 1) * 1.06
Next

Cells(1).Resize(UBound(sn) + 1) = Application.Transpose(sn)
End Sub