Results 1 to 4 of 4

Thread: IRR Function

  1. #1
    Join Date
    Oct 2003
    Posts
    18

    Unhappy Unanswered: IRR Function

    I am trying to calculate the IRR using the built in function within access.

    I currently have a query with has 5 columns...

    1. Investment
    2. year 1 return
    3. year 2 return
    4. year 3 return
    5. year 4 return

    From what i can gather, i need to make these values in to an array so that i can use the IRR function but have no idea how to do this.

    Ideally i'd like to return the value off the IRR function in to a column in the same query.

    Any ideas how to acheive this ?

    It seems so simple in excel, this is really frustrating.

    Thanks

    Dave

  2. #2
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    From the access help:
    Dim Guess, Fmt, RetRate, Msg
    Static Values(5) As Double ' Set up array.
    Guess = .1 ' Guess starts at 10 percent.
    Fmt = "#0.00" ' Define percentage format.
    Values(0) = -70000 ' Business start-up costs.
    ' Positive cash flows reflecting income for four successive years.
    Values(1) = 22000 : Values(2) = 25000
    Values(3) = 28000 : Values(4) = 31000
    RetRate = IRR(Values(), Guess) * 100 ' Calculate internal rate.
    Msg = "The internal rate of return for these five cash flows is "
    Msg = Msg & Format(RetRate, Fmt) & " percent."
    MsgBox Msg ' Display internal return rate.

    So if you paste this into a module
    Code:
    Public Function myIRR(Investment, Return1, Return2, Return3, Return4, Percentage) As Double
        Dim x(4) As Double
        x(0) = Investment
        x(1) = Return1
        x(2) = Return2
        x(3) = Return3
        x(4) = Return4
        myIRR = IRR(x, Percentage)
    End Function
    Then from your query call this function with the needed fields, it should return your IRR.

    Regards

  3. #3
    Join Date
    Oct 2003
    Posts
    18
    I tried this but it returns a compile error:

    Compile error !!

    Expected variable or proceedure, not module

    Any idea whats going wrong ?

  4. #4
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    You have named your module the same as the function, DONT.

    Try keeping to a naming convention.

    mdl = module
    mdlIRR for instance

    frm = from
    tbl = table
    qry = query
    etc

    Regards

Posting Permissions

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