Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2005
    Posts
    45

    Unanswered: Determining IRR's on numerous records...

    I have a table with 700 loans, and each loan has 180 months worth of cash flows. I am trying to figure a way to calculate the IRR for each loan. I can generate an IRR with the code supplied by Microsoft:

    Dim guess, Fmt, RetRate, Msg
    Static Values(5) As Double
    guess = 0.1
    Fmt = "#0.00"
    Values(0) = -70000
    Values(1) = 22000: Values(2) = 25000
    Values(3) = 28000: Values(4) = 31000
    RetRate = irr(Values(), guess) * 100 ' Calculate internal rate.

    I think I need to loop through the recordset of each loan and assign the cash flow values to an array, where I then calculate the IRR.

    Any ideas would be greatly appreciated!

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    move the code from the microsoft site into a function, then call that function from a Query


    the function will have to be stored in a public module
    HTH
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2005
    Posts
    45
    Thanks, but my monthly cash flows are not in columns, they are each a different record.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so?
    do a select on the data within the function, or find another way of flattenting the data within a query before it gets passed to the fucntion.

    intrinscially there is no reason why you can't run a query within a function, subject to accepting that there will be a perfomrance hit if the query and the way it is accessed isn't carefully crafted.

    from what I rmember of IRR calcualtions they are failry naiv, after all they are a projection, often used by accoutnants to pretend that a project has a better return that another project.
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Dec 2005
    Posts
    45
    Thanks for the help!

    The problem I'm having is that I have a table with 388 loans, and each has between 355 and 360 cash flows, giving me @ 140000 records.

    I think my first step is looping through a recordset and assigning loan_ID to a variable, then using that variable in an SQL statement to set another recordset to include only that loan's cash flows. Lastly, use getrows to assign the cash flows to an array, and irr that array.

    Make sense?
    This may be more difficult than necessary, but its all I can come up with.

    Thanks again!

  6. #6
    Join Date
    Jul 2005
    Posts
    50
    Let's say you had a table like this:

    Code:
    ID	Customer	CashFlowAmount	FlowDate
    1	Hayes		-70000		1/1/2005
    2	Hayes		22000		2/1/2005
    3	Hayes		25000		3/1/2005
    4	Hayes		28000		4/1/2005
    5	Hayes		31000		5/1/2005
    Here is a function call and function to handle that:

    Code:
    Sub SomeAccountingSubRuoutine()
    
    Dim dIRR As Double
    
        dIRR = MyIRR("Hayes", 0.1)
    
    End Sub
    
    'The Function
    Function MyIRR(sCustomer As String, dMyGuess As Double) As Double
    
    Dim iLoop As Integer
    Dim rsCashFlows As DAO.Recordset
    Dim sSQL As String
    Dim lRecCt As Long
    
        sSQL = "Select CashFlowAmount From tb_CashFlow WHERE Customer = '" & sCustomer & "'"
        Set rsCashFlows = CurrentDb.OpenRecordset(sSQL)
        
        'Get accurate record count
        rsCashFlows.MoveLast
        rsCashFlows.MoveFirst
        lRecCt = rsCashFlows.RecordCount
        
        ReDim Values(lRecCt - 1) As Double ' an array with 15 elements
        
        For iLoop = 0 To lRecCt - 1
            Values(iLoop) = rsCashFlows(0)
            rsCashFlows.MoveNext
        Next iLoop
        ' Perform your Internal Rate of Return calculation
        MyIRR = IRR(Values(), dMyGuess) * 100
        
        rsCashFlows.Close
        Set rsCashFlows = Nothing
    
    End Function

  7. #7
    Join Date
    Jul 2005
    Posts
    50
    If that is too simplistic, I don't know how your data is set up.

    It might be more fun to try to return your answer in a query, with no VB code, but I thought I'd approach your problem from a VBA perspective, since that's where you were headed.

  8. #8
    Join Date
    Dec 2005
    Posts
    45
    mlbuie,

    Looks like a great way to do the calc.

    Now I just need it to pull the first customers cash flows, calc an IRR, and then move to the next Customers cash flows, until all customers are done.

    Thanks for the help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •