IRR in a Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Dane Cooper - 25 Feb 2007 19:46 GMT

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

Reply to this Message

Douglas J. Steele - 25 Feb 2007 20:21 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

>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
[quoted text clipped - 7 lines]
>
> Dane

Reply to this Message

Dane Cooper - 25 Feb 2007 20:46 GMT

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

> IRR isn't intended to be used in a query against a table: it's expecting
> an array of values as its first element.
[quoted text clipped - 35 lines]
>>
>> Dane

Reply to this Message

Douglas J. Steele - 25 Feb 2007 22:39 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Thanks, Doug:
>
[quoted text clipped - 26 lines]
>>>
>>> Dane

Reply to this Message

Tom Wickerath - 25 Feb 2007 23:24 GMT

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
__________________________________________

> Make it a function. The following is a bit more generic. You'd call it as:
>
[quoted text clipped - 41 lines]
>
> End Function

Reply to this Message

Douglas J. Steele - 26 Feb 2007 01:05 GMT

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

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

> Hi Doug,
>
[quoted text clipped - 61 lines]
>>
>> End Function

Reply to this Message

Dane Cooper - 26 Feb 2007 00:46 GMT

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

>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
[quoted text clipped - 7 lines]
>
> Dane

Reply to this Message