Cumlative Principal Function in Access (A2000)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

Hans,

Thanks, very much appreciated

Cheers
Tony

Reply | Quote

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:

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 

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 to write their own functions to attain this functionality.

Reply | Quote