Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Financial functions (Win XP Access 2003)

    Can you guys point me in the right direction for this? I need to calculate the accumulated payments to capital for a loan, similar to the CUMPRINC function in Excel. In fact, I am making the calculation with an imported query from Access to Excel now, but it is a bit inconveniente, and I would like to do it in within Access. Does anyone know the formula or know if somebody has this function programmed in Access?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Financial functions (Win XP Access 2003)

    You can set a reference in the Visual Basic Editor, in Tools References..., to the Microsoft Office Web Components Function Library (C:Microsoft OfficeOffice11MSOWCF.DLL). This will make the CUMPRINC function available in VBA. You can write your own "wrapper" function for it.
    Alternatively, you could write a VBA function that emulates CUMPRINC by adding the appropriate PPMT values (PPMT is available in "generic" VBA):

    Function CUMPRINC(Rate, NPer, PV, Start_Period, End_Period, Type_Payment)
    Dim i As Integer
    For i = Start_Period To End_Period
    CUMPRINC = CUMPRINC + PPmt(Rate, i, NPer, PV, Type_Payment)
    Next i
    End Function

    You may want to add error handling.

  3. #3
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Financial functions (Win XP Access 2003)

    Thanks for the response. I can't find the file MSOWCF.DLL nor the reference Microsoft Office Web Components Function Library . There is a Microsoft Office Web Components Wizzard, though.

    We are for sure on the right track. Is this an Access installation problem of mine?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Financial functions (Win XP Access 2003)

    Can you find the file if you click Browse... in Tools | References... and select "All Files" in the Files of Type dropdown?

  5. #5
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Financial functions (Win XP Access 2003)

    No, Hans. I also ran a search in C:Program FilesMicrosoft OfficeOffice11 for MSOWCF.DLL with no results.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Financial functions (Win XP Access 2003)

    I'm on Access 2002 at the moment. I'll check the situation in Access 2003 tomorrow at work.

    In the meantime, you could use the custom VBA function I posted. It should work in all recent versions of Access.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Financial functions (Win XP Access 2003)

    You appear to be correct - my Office 2003 at work doesn't have it either. Does someone else know if Office 2003 has an equivalent of the Microsoft Office Web Components Function Library and if so, what is called?

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Financial functions (Win XP Access 2003)

    I found some files if you are interested. They were found in an old access version of mine.

    I have your email address so I will send them to you.

  9. #9
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Financial functions (Win XP Access 2003)

    Hans, why doesn't this work? I run the function from a command button on a form. the interest is 12%/12 = 1.

    Option Compare Database
    Option Explicit


    Public Function CUMPRINC(Rate, NPer, PV, Start_period, End_Period, Type_Payment) As Double
    Dim i As Integer
    For i = Start_period To End_Period
    CUMPRINC = CUMPRINC + PPmt(Rate, i, NPer, PV, Type_Payment)
    Next i
    End Function


    Private Sub Command16_Click()
    Dim Rate As Double, NPer As Integer, PV As Double, Start_period As Variant, End_Period As Variant
    Dim Type_Payment As Variant
    Dim AccumCap As Double
    AccumCap = CUMPRINC(1, 120, 100000, 1, 10, 0)
    End Sub

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Financial functions (Win XP Access 2003)

    You're specifying 100% interest rate instead of 1%. Remember, "percent" means "per 100", so 1% = 1/100 = 0.01
    You should use

    AccumCap = CUMPRINC(0.01, 120, 100000, 1, 10, 0)

  11. #11
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Financial functions (Win XP Access 2003)

    Pat - The immediate problem is solved, but I am interested in pursuing this further. I would appreciate the files you mentioned, if possible. Thanks.

  12. #12
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Financial functions (Win XP Access 2003)

    I sent them on the 27th Feb, I have just sent them again to your email address.

  13. #13
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Financial functions (Win XP Access 2003)

    Patt - Nothing yet on that e-mail. How can we verify the address?

  14. #14
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Financial functions (Win XP Access 2003)

    I am sending it to

    gsmith77063@intelnet.net.gt

  15. #15
    4 Star Lounger
    Join Date
    Apr 2001
    Location
    Guatemala City
    Posts
    515
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Financial functions (Win XP Access 2003)

    That address is correct. I have spoken with my Internet company about this. Maybe
    they are rejecting this as spam or something similar. When (and if) I get
    some response from my provider, I will let you know and we can try again.

    Thanks for the effort.

Page 1 of 2 12 LastLast

Posting Permissions

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