XIRR function in vba

ENOBYZARC

New member
Local time
Today, 09:46
Joined
Sep 18, 2014
Messages
4
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
 
What is the function supposed to be doing?
 
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
 
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.
 
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.
 
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.
 
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.
 
Last edited:
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)
 
Last edited:
FYI, post 3 had been moderated so mods would have been the only ones seeing it.
 
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
Code:
dblXIRR = objExcel.XIRR(strValues, varDates)

hth
Chris
 
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
 

Users who are viewing this thread

Back
Top Bottom