XIRR for non-periodic dates and flows by investment

[Sanjay_S]

I currently have a query XIRR_Array, that returns three fields: Match Code (a concatenation of investor name and product code), CFlow, which is the cash flow (negative for outflow, positive for inflow) and TDate, which is the date of the cash flow.

I have been trying to derive the XIRR for each match code using my less-than-rudimentary VBA skills.

An extract of data from XIRR_Array (In the form of an eponymous table), the query I have used to get the XIRR and the VBA code I used in Module1 to define the XIRR function are all in the attached database.

As can be seen in the XIRR_Array table, the query has already sorted the data, and also supplied a month-end value (TDate=31/08/2016) for the XIRR evaluation.

The query I have used:

SELECT DISTINCT [Match Code] FROM XIRR_ARRAY, AXIRR([Match Code],0.1) AS CAGR

The VBA code for defining AXIRR:

Option Compare Database

Function AXIRR(MatchCode As String, Optional GuessRate As Double = 0.1) As Variant
Dim rsXIRR As dao.Recordset
Dim db As Database
Dim CFlow() As Currency
Dim TDate() As Date
Dim SelectSql As String
Dim I As Integer
  
SelectSql = "SELECT CFlow, TDate FROM XIRR_Array WHERE [Match Code]= 'MatchCode' ORDER BY TDate"
  'Debug.Print strSql
 Set db = CurrentDb
 Set rsXIRR = db.OpenRecordset(SelectSql, dbOpenDynaset)
  'Fill Payments and dates.
  ReDim CFlow(rs.RecordCount - 1)
  ReDim TDate(rs.RecordCount - 1)
  Do While Not rs.EOF
    CFlow(I) = rs.Fields(CFlow).Value
      TDate(I) = rs.Fields(TDate).Value
    Debug.Print I
    I = I + 1
    rs.MoveNext
  Loop
  For I = 0 To rs.RecordCount - 1
    Debug.Print CFlow(I) & " " & TDate(I)
  Next I
  AccessXIRR = XIRR_Wrapper(CFlow, TDate, GuessRate)
End Function

Public Function XIRR_Wrapper(Payments() As Currency, Dates() As Date, Optional GuessRate As Double = 0.1)
   'Must add a reference to the Xcel library. Use Tools, References, Microsoft Excel XX.X Object Library
   XIRR_Wrapper = Excel.WorksheetFunction.Xirr(Payments, Dates, GuessRate)
End Function

On running this, I get the following error: ‘run time error 424 object required’, and the debug highlights the line: ReDim CFlow(rs.RecordCount - 1)

Can someone guide me on this?

Sanjay

 

[JHB]

Because rs is unknown - you call your recordset rsXIRR

Dim rsXIRR As dao.Recordset
..
Set rsXIRR = db.OpenRecordset(SelectSql, dbOpenDynaset)
..
ReDim CFlow(rs.RecordCount - 1)
...

 

[Sanjay_S]

Because rs is unknown - you call your recordset rsXIRR

Ok, fixed that. My bad. Here is the new code:

Option Compare Database

Function AXIRR(MatchCode As String, Optional GuessRate As Double = 0.1) As Variant
Dim rsXIRR As DAO.Recordset
Dim db As Database
Dim CFlow() As Currency
Dim TDate() As Date
Dim SelectSql As String
Dim I As Integer
  
SelectSql = "SELECT CFlow, TDate FROM XIRR_Array WHERE [Match Code]= 'MatchCode' ORDER BY TDate"
  'Debug.Print strSql
 Set db = CurrentDb
 Set rsXIRR = db.OpenRecordset(SelectSql, dbOpenDynaset)
  'Fill Payments and dates.
  ReDim CFlow(rsXIRR.RecordCount - 1)
  ReDim TDate(rsXIRR.RecordCount - 1)
  Do While Not rsXIRR.EOF
    CFlow(I) = rsXIRR.Fields(CFlow).Value
      TDate(I) = rsXIRR.Fields(TDate).Value
    Debug.Print I
    I = I + 1
    rsXIRR.MoveNext
  Loop
  For I = 0 To rsXIRR.RecordCount - 1
    Debug.Print CFlow(I) & " " & TDate(I)
  Next I
  AXIRR = XIRR_Wrapper(CFlow, TDate, GuessRate)
End Function

Public Function XIRR_Wrapper(Payments() As Currency, Dates() As Date, Optional GuessRate As Double = 0.1)
   'Must add a reference to the Xcel library. Use Tools, References, Microsoft Excel XX.X Object Library
   XIRR_Wrapper = Excel.WorksheetFunction.Xirr(Payments, Dates, GuessRate)
End Function

Now I get an error "Run Time Error 9: Subscript out of Range", and the Debug stops on the line:

  ReDim CFlow(rsXIRR.RecordCount - 1)

 

[JHB]

Then check how many records rsXIRR returns.

 

[Sanjay_S]

Then check how many records rsXIRR returns.

It is not returning any records. I think there is a problem with the filter definition in the SelectSql statement, where I have specified

WHERE [Match Code]='MatchCode'

The idea was that MatchCode would be passed as an argument to the AXIRR function through the query I had shared, and I would get a neat XIRR for each matchcode. But this does not seem to work.

Would appreciate it if you could take a look at this Test_XIRR database I had attached. I must be making a rookie mistake somewhere.

Thanks,

Sanjay

 

[Cronk]

After a recordset is opened ie
Set rsXIRR = db.OpenRecordset(SelectSql, dbOpenDynaset)
the value of rst.recordcount is either 0 (no records in the set) or 1 (one or more records).

The only way to determine the number of records is to move to the end of the recordset with
rst.movelast

 

[Sanjay_S]

After a recordset is opened ie
Set rsXIRR = db.OpenRecordset(SelectSql, dbOpenDynaset)
the value of rst.recordcount is either 0 (no records in the set) or 1 (one or more records).

The only way to determine the number of records is to move to the end of the recordset with
rst.movelast

Tried that too, no dice- I tried putting a

rstXIRR.MoveLast

line before the problem line

ReDim CFlow(rs.RecordCount - 1)

 

[Cronk]

What is the value of
rsXIRR.RecordCount
after .movelast?

 

[JANR]

SelectSql = "SELECT CFlow, TDate FROM XIRR_Array WHERE [Match Code]= 'MatchCode' ORDER BY TDate"

This select statement is looking for records in your table XIRR_ARRAY where the field Match Code is equel to the literal string 'MatchCode' and yopu probably don not have any records there, so record count will always be zero.

If you want to pass along a sting value from your query, you need to do something like this:

SelectSql = "SELECT CFlow, TDate FROM XIRR_Array WHERE [Match Code]= '" & MatchCode & "' ORDER BY TDate"

Just my 2 cents

Jan R

 

[Sanjay_S]

SelectSql = "SELECT CFlow, TDate FROM XIRR_Array WHERE [Match Code]= 'MatchCode' ORDER BY TDate"

This select statement is looking for records in your table XIRR_ARRAY where the field Match Code is equel to the literal string 'MatchCode' and yopu probably don not have any records there, so record count will always be zero.

If you want to pass along a sting value from your query, you need to do something like this:

SelectSql = "SELECT CFlow, TDate FROM XIRR_Array WHERE [Match Code]= '" & MatchCode & "' ORDER BY TDate"

Just my 2 cents

Jan R

Jan, thanks for the observation. When I do

SelectSql = "SELECT CFlow, TDate FROM XIRR_Array WHERE [Match Code]= '" & MatchCode & "' ORDER BY TDate"

and I also do a

MsgBox(SelectSql)

I get the following as my SelectSql:

SELECT CFlow, TDate FROM XIRR_Array WHERE [Match Code]='[Match Code]' ORDER BY TDate

I should be getting the following on first execution:

SELECT CFlow, TDate FROM XIRR_Array WHERE [Match Code]='ArkaP1191' ORDER BY TDate

I am not able to figure out how to pass the [Match Code] variable in the table to the query to filter.

The Query is as follows:

SELECT DISTINCT XIRR_Array.[Match Code], AXIRR("[Match Code]",0.1) AS CAGR
FROM XIRR_Array;

 

[Cronk]

So [Match Code] is not a field but a control on the form with text data.

Use
"SELECT CFlow, TDate FROM XIRR_Array WHERE [Match Code]='" & Me.[Match Code] & "' ORDER BY TDate"
By the way, don't forget to rst.movefirst after beginning the loop.

 

[Sanjay_S]

So [Match Code] is not a field but a control on the form with text data.

Use
"SELECT CFlow, TDate FROM XIRR_Array WHERE [Match Code]='" & Me.[Match Code] & "' ORDER BY TDate"
By the way, don't forget to rst.movefirst after beginning the loop.

There is no form here; the database comprises a table XIRR_Array with three fields: [Match Code], [CFlow] and [TDate].

We need to get an XIRR function that can, for each [Match Code], give us an XIRR, based on the [CFlow] and [TDate] values associated with that [Match Code].

In this table, there are two distinct [Match Code] values: ARKAP1191 and ARKAPEDIRD.

I am trying to pass these values on to the SelectSql string to filter the TDate and CFlow fields, and then arrive at an XIRR for that [Match Code].

Hope I've clarified. Since the [Match Code] values is in the table XIRR_Array itself and not on a form, Me.[Match Code] does not work.

 

[Sanjay_S]

Ok, I've figured out the SelectSql query part, to get it to filter the recordset, referencing the two distinct values in the [Match Code] field of Table XIRR_Array.

I did this by modifying the function arguments to take another field called "MatchCode_Value", and by setting "MatchCode_Field=MatchCode_Value" in the SelectSql.

Since both are arguments, I entered "[Match Code]" in the AXIRR Function in the query for the MatchCode_Field and [Match Code] for the MatchCode_Value argument of the function.

I also brought in the Fields for Cash Flows and Dates into the function arguments, as it seems to reference better.

All good, and the Debug window prints the "I" values, "Payments (I)" and "Dates (I)" values correctly.

Now I get an error: "run time error 1004; unable to get the XIRR property of the WorksheetFunction class".

In my references, I have selected Microsoft Excel 15.0 Object Library and Mircosoft Office 15.0 Access Database Engine Object Library.

I have attached the revised Test_XIRR_Rev accdb here; the AXIRR function is in Module 1, and is launched through Query_XIRR, which uses the Table XIRR_Array.

Any help on this would be really appreciated. Thank you.

Sanjay