Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cumlative Principal Function in Access (A2000)

    Hi all,


    I am in urgent need to locate a cumulative principal function that can be used in access. I am after the same function provided by Excel - CUMPRINC which returns the cumulative principal paid on a loan between the starting and ending periods


    Any help or pointers appreciated.

    Thanks
    Tony

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

    Re: Cumlative Principal Function in Access (A2000)

    Place the following function in a module. It computes the equivalent of the CUMPRINC function:

    Function CumPrinc(Rate As Double, NPer As Double, PV As Double, _
    Start_Period As Double, End_Period As Double, Type_Payment As Integer) As Double
    Dim i As Integer
    Dim dblRetVal As Double
    For i = Start_Period To End_Period
    dblRetVal = dblRetVal + PPmt(Rate, i, NPer, PV, 0, Type_Payment)
    Next i
    CumPrinc = dblRetVal
    End Function

    Note: the underscore character _ in the first line is the VB continuation character. There must be a space before it.

    Example usage:

    MsgBox CumPrinc(0.04, 25, 100000, 5, 8, 0)

    If you also want a CUMIPMT function, copy the above function and replace PPmt by IPmt.

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Location
    Christchurch, New Zealand
    Posts
    73
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cumlative Principal Function in Access (A2000)

    Hans,

    Thanks, very much appreciated

    Cheers
    Tony

  4. #4
    Bronze Lounger
    Join Date
    Nov 2001
    Location
    Arlington, Virginia, USA
    Posts
    1,394
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: Cumlative Principal Function in Access (A2000)

    Some additional info:

    If you have Office 2K or higher installed you can utilize many Excel functions in Access by setting a reference to the Microsoft Office Web Components (OWC) Function Library (MSOWCF.DLL) (in Office 2K typically located in C:Program FilesMicrosoft OfficeOffice folder, or ..Office10 in Office XP). According to MS, "The Office Web Components are included with Microsoft Office 2000 Premium, Microsoft Office 2000 Professional, Microsoft Office 2000 Standard, Microsoft Office 2000 Developer, and Microsoft Access 2000." They are also included in Office XP.

    The OWC are intended for displaying a functional worksheet, chart, etc on a web page, but you can subvert this functionality for other purposes. In Access, you can set a reference to Excel and then make use of the functions defined in the WorksheetFunction class; however, Access will not allow you to set a reference to an Excel .XLA add-in file. Therefore, you cannot set a reference to Excel and then use the functions defined in the Analysis ToolPak (ATP) add-in (ATPVBAEN.XLA). Setting a reference to the OWC function library allows you to circumvent this limitation to some extent.

    The MSOWCF.DLL type library includes two classes relevant here: OCATP, which includes many, but not all of, the functions included in the Excel ATP; and OCFunc, which includes many, but not all of, the worksheet functions available in the WorksheetFunction class. Not all functions are available since the Office web components lack the full functionality of the parent application.

    The CUMPRINC function is a member of the OCATP class. Example of use in Access:

    <pre>Public Function OWC_CUMPRINC(Rate As Double, Nper As Double, Pv As Double, _
    Start_period As Double, End_period As Double, dblType As Double)

    Dim atp As New MSOWCFLib.OCATP
    OWC_CUMPRINC = atp.CUMPRINC(Rate, Nper, Pv, Start_period, End_period, dblType)
    Set atp = Nothing

    End Function </pre>

    This may or may not be of use for anyone who needs to make extensive use of Excel functions from another program like Access, and who (like me) aren't clever enough <img src=/S/clever.gif border=0 alt=clever width=15 height=15> to write their own functions to attain this functionality.

Posting Permissions

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