a udf with many arguments

[lior03]

hello
i want tire you with financial analysis.the aim of the following function is to let the user know at a given interest rate ,and intial investment how much will he make per dollar invested.how can i use indifinate number of years.my limited vba knowhow enabled me to use only eight years.
Function investec(rate, inflow1, inflow2, inflow3, inflow4, inflow5, inflow6, inflow7, intialcost)
investec = Abs(1 + Abs(Application.WorksheetFunction.NPV(rate, inflow1, inflow2, inflow3, inflow4, inflow5, inflow6, inflow7) - intialcost) / intialcost)
End Function
thanks

[XLGibbs]

Aren't there already a number of ROI, IRR and built in financial functions that would work?

It seems you are looking at an IRR based on the X number of years of inflows at a given interest rate.

Allow input for i interest, x # of years, "amortize" the ROI, then apply an IRR formula to the result...

Writing a UDF with an infinite number of years might be a bit ambitious.

[Bob Phillips]

Function investec(rate, intialcost, ParamArray inflow())
Dim sNPV As String
Dim i As Long
Dim valNPV As Double

For i = LBound(inflow) To UBound(inflow)
sNPV = sNPV & "," & inflow(i)
Next i
sNPV = "NPV(" & rate & sNPV & ")"
valNPV = Application.Caller.Parent.Evaluate(sNPV)
investec = Abs(1 + Abs(valNPV - intialcost) / intialcost)
End Function