IRR in a Query

  • Thread starter Thread starter Dane Cooper
  • Start date Start date
D

Dane Cooper

I am trying to use the IRR function in a query, but not succeeding. I have
a positive value (the loan amount) and a lot of negative values (payments
against the loan) in a single field called "CashFlow".

My formula looks like this:

IRR:IRR(([CashFlow]),0.1)

Any suggestions would be GREATLY appreciated.

Dane
 
IRR isn't intended to be used in a query against a table: it's expecting an
array of values as its first element.

It's intended to be used in VBA, along the lines of:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim intLoop As Integer
Dim dblValues() As Double
Dim strSQL As String

strSQL = "SELECT CashFlow FROM MyTable"
Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
rsCurr.MoveLast
rsCurr.MoveFirst
ReDim dblValues(0 to rsCurr.RecordCount - 1)
intLoop = 0
Do Until rsCurr.EOF
dblValues(intLoop) = rsCurr!CashFlow
intLoop = intLoop + 1
rsCurr.MoveNext
Loop
rsCurr.Close
MsgBox "Irr returned " & Irr(dblValues, 0.1)
Set rsCurr = Nothing
Set dbCurr = Nothing
 
Thanks, Doug:

Your code worked just fine. I got the message box, just like you wrote it.
However, I was hoping to put the function at the bottom of a form (like in
the form footer), so that it computes against values in the table and the
user will see the resluts without having to look at a message box. To do
this, do I simply reference this code in a form field? If so, do I make
this code a function or a sub?

I am kind of new to this programming stuff, so I really appreciate your
help.

Dane


Douglas J. Steele said:
IRR isn't intended to be used in a query against a table: it's expecting
an array of values as its first element.

It's intended to be used in VBA, along the lines of:

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim intLoop As Integer
Dim dblValues() As Double
Dim strSQL As String

strSQL = "SELECT CashFlow FROM MyTable"
Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
rsCurr.MoveLast
rsCurr.MoveFirst
ReDim dblValues(0 to rsCurr.RecordCount - 1)
intLoop = 0
Do Until rsCurr.EOF
dblValues(intLoop) = rsCurr!CashFlow
intLoop = intLoop + 1
rsCurr.MoveNext
Loop
rsCurr.Close
MsgBox "Irr returned " & Irr(dblValues, 0.1)
Set rsCurr = Nothing
Set dbCurr = Nothing

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dane Cooper said:
I am trying to use the IRR function in a query, but not succeeding. I
have a positive value (the loan amount) and a lot of negative values
(payments against the loan) in a single field called "CashFlow".

My formula looks like this:

IRR:IRR(([CashFlow]),0.1)

Any suggestions would be GREATLY appreciated.

Dane
Click to expand...
Click to expand...
 
Make it a function. The following is a bit more generic. You'd call it as:

MyIrr("CashFlow", "MyTable", 0.1)

Function MyIrr( _
FieldName As String, _
TableName As String, _
Optional Guess As Double = 0.1 _
) As Double

On Error GoTo Err_MyIrr

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim intLoop As Integer
Dim dblValues() As Double
Dim strSQL As String

strSQL = "SELECT [" & FieldName & "] " & _
"FROM [" & TableName & "]"
Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
rsCurr.MoveLast
rsCurr.MoveFirst
ReDim dblValues(0 to rsCurr.RecordCount - 1)
intLoop = 0
Do Until rsCurr.EOF
dblValues(intLoop) = rsCurr!CashFlow
intLoop = intLoop + 1
rsCurr.MoveNext
Loop
MyIrr = Irr(dblValues, Guess)

End_MyIrr:
On Error Resume Next
rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing
Exit Function

Err_MyIrr:
Err.Raise Err.Number, "MyIrr", Err.Description
Resume End_MyIrr

End Function

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dane Cooper said:
Thanks, Doug:

Your code worked just fine. I got the message box, just like you wrote
it. However, I was hoping to put the function at the bottom of a form
(like in the form footer), so that it computes against values in the table
and the user will see the resluts without having to look at a message box.
To do this, do I simply reference this code in a form field? If so, do I
make this code a function or a sub?

I am kind of new to this programming stuff, so I really appreciate your
help.

Dane


Douglas J. Steele said:
IRR isn't intended to be used in a query against a table: it's expecting
an array of values as its first element.

It's intended to be used in VBA, along the lines of:


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Dane Cooper said:
I am trying to use the IRR function in a query, but not succeeding. I
have a positive value (the loan amount) and a lot of negative values
(payments against the loan) in a single field called "CashFlow".

My formula looks like this:

IRR:IRR(([CashFlow]),0.1)

Any suggestions would be GREATLY appreciated.

Dane
Click to expand...
Click to expand...
Click to expand...
 
Hi Doug,

I think to make your function truly generic, you'll want to make the
indicated substitution, instead of hard-coding the field name "CashFlow":

Do Until rsCurr.EOF
dblValues(intLoop) = rsCurr(Eval("'[" & FieldName & "]'")) '<-----
' dblValues(intLoop) = rsCurr!CashFlow
intLoop = intLoop + 1
rsCurr.MoveNext
Loop


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
Thanks, Doug and Tom. Your suggestions were right on the money (pardon the
pun). This makes a big difference in my application. With your permission,
I would like to credit you for your assistance.

Again, thanks for your help.

Dane
 
Good catch, Tom.

dblValues(intLoop) = rsCurr.Fields(0)

will do as well, or you could use

strSQL = "SELECT [" & FieldName & "] AS Field1" & _
"FROM [" & TableName & "]"

and

dblValues(intLoop) = rsCurr!Field1

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


Tom Wickerath said:
Hi Doug,

I think to make your function truly generic, you'll want to make the
indicated substitution, instead of hard-coding the field name "CashFlow":

Do Until rsCurr.EOF
dblValues(intLoop) = rsCurr(Eval("'[" & FieldName & "]'")) '<-----
' dblValues(intLoop) = rsCurr!CashFlow
intLoop = intLoop + 1
rsCurr.MoveNext
Loop


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________

Douglas J. Steele said:
Make it a function. The following is a bit more generic. You'd call it
as:

MyIrr("CashFlow", "MyTable", 0.1)

Function MyIrr( _
FieldName As String, _
TableName As String, _
Optional Guess As Double = 0.1 _
) As Double

On Error GoTo Err_MyIrr

Dim dbCurr As DAO.Database
Dim rsCurr As DAO.Recordset
Dim intLoop As Integer
Dim dblValues() As Double
Dim strSQL As String

strSQL = "SELECT [" & FieldName & "] " & _
"FROM [" & TableName & "]"
Set dbCurr = CurrentDb()
Set rsCurr = dbCurr.OpenRecordset(strSQL)
rsCurr.MoveLast
rsCurr.MoveFirst
ReDim dblValues(0 to rsCurr.RecordCount - 1)
intLoop = 0
Do Until rsCurr.EOF
dblValues(intLoop) = rsCurr!CashFlow
intLoop = intLoop + 1
rsCurr.MoveNext
Loop
MyIrr = Irr(dblValues, Guess)

End_MyIrr:
On Error Resume Next
rsCurr.Close
Set rsCurr = Nothing
Set dbCurr = Nothing
Exit Function

Err_MyIrr:
Err.Raise Err.Number, "MyIrr", Err.Description
Resume End_MyIrr

End Function
Click to expand...
Click to expand...
 
Back
Top