XIRR function in vba

[ENOBYZARC]

Hola, I am trying to use XIRR function en Access vba but i received an 1004 error, this is my function. What can go wrong?

Public Function dblXIRR() As Double
Dim objExcel As Object
Dim strValues() As String
Dim varDates(2) As Variant


Set objExcel = CreateObject("Excel.Application")


strValues = Split("1000,500,-2500", ",")


varDates(0) = CDate("01/01/2010")
varDates(1) = CDate("01/06/2010")
varDates(2) = CDate("31/12/2010")


dblXIRR = objExcel.WorksheetFunction.XIRR(strValues, varDates)


End Function

 

[Uncle Gizmo]

What is the function supposed to be doing?

 

[ENOBYZARC]

What is the function supposed to be doing?

I forgot , I use Access 2010 in spanish.

I want to calculate annualized returns like this,

experiglot.com/2006/10/17/how-to-use-xirr-in-excel-to-calculate-annualized-returns

 

[ENOBYZARC]

What is the function supposed to be doing?

Calculate annualized returns, i have Access 2010 in spanish.

 

[Uncle Gizmo]

Please show an example of the data you have. Showing it as text in the forum would be best.

Also show an example of the results you want, again posted in the forum is best for everyone to see, so we are all working from the same song sheet as it were.

 

[ENOBYZARC]

Please show an example of the data you have. Showing it as text in the forum would be best.

Also show an example of the results you want, again posted in the forum is best for everyone to see, so we are all working from the same song sheet as it were.

I don´t know what you mean. Sorry i am spanish.

The data is in the first post.

 

[Uncle Gizmo]

Hola, I am trying to use XIRR function en Access vba but i received an 1004 error, this is my function. What can go wrong?

Public Function dblXIRR() As Double
Dim objExcel As Object
Dim strValues() As String
Dim varDates(2) As Variant


Set objExcel = CreateObject("Excel.Application")


strValues = Split("1000,500,-2500", ",")


varDates(0) = CDate("01/01/2010")
varDates(1) = CDate("01/06/2010")
varDates(2) = CDate("31/12/2010")


dblXIRR = objExcel.WorksheetFunction.XIRR(strValues, varDates)


End Function

The data is in the first post? ..
Well I can't see it it looks like I'm having a bad day and I think I ought to go back to bed.

 

[gemma-the-husky]

I do not know how to call the function in the excel library, but these financial functions are not hard to develop from first principles.

eg

http://en.wikipedia.org/wiki/Internal_rate_of_return

the IRR functions evaluate a rate of return that brings an interest-adjusted cashflow to zero, so some trial and error /feedback function is necessary for this one, I think. Try an approximation, and then adjust the interest rate until you get to zero.

for each cash flow element it's a compounding interest calculation.
so typically you start with an outflow, and then you get inflows, so that the total inflow exceeds the initial outflow. Eventually you finds a compounding rate that makes the compounded outflow equal to the sum of the compounded inflows.

 

[Uncle Gizmo]

Ah! Thanks Dave. I got "man flu" and I don't think I should be out in public today.
Edit:- ( metaphorically I mean. As in not answering questions specifically)

 

[pbaldy]

FYI, post 3 had been moderated so mods would have been the only ones seeing it.

 

[stopher]

Hola, I am trying to use XIRR function en Access vba but i received an 1004 error, this is my function. What can go wrong?

Public Function dblXIRR() As Double
Dim objExcel As Object
Dim strValues() As String
Dim varDates(2) As Variant


Set objExcel = CreateObject("Excel.Application")


strValues = Split("1000,500,-2500", ",")


varDates(0) = CDate("01/01/2010")
varDates(1) = CDate("01/06/2010")
varDates(2) = CDate("31/12/2010")


dblXIRR = objExcel.WorksheetFunction.XIRR(strValues, varDates)


End Function

Try

dblXIRR = objExcel.XIRR(strValues, varDates)

hth
Chris

 

[stopher]

Actually, I'm not sure you can submit an array as if it were an excel range (not 100% sure). But see here how to create a range and then use it in your function.

Chris