Microsoft KB Archive/256200

= ACC2000: How to Use the PPmt Function =

Article ID: 256200

Article Last Modified on 12/12/2002

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q256200



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).



SUMMARY
The article shows you how to use the PPmt function to calculate the monthly payment of a loan, and to calculate how much of that payment is principal and how much is interest.

The PPmt Function calculates payments based on periodic (monthly) fixed payments and fixed interest rates.



MORE INFORMATION
This example first shows you how to create an Access form that the user will use to enter the data that is necessary to calculate the periodic payments. Then, the example shows you how to create an Access report that actually calculates the payments, principal, and interest.

The user opens the form, enters the necessary data, and then clicks a button to start the report. The report then calculates and displays the results of the PPmt function.

PPmt Function Example
  Open a new database, and then create the following form:   Form: frmPPMT Caption: Periodic Payment Calculator RecordSource: None

Label Caption: How much do you want to borrow? Left: 0.25"  Top: 0.25" Width: 3.25"  FontWeight: Bold   TextAlign: Right

Text box ---  Name: txtPresentVal Format: Currency DecimalPlaces: 2 Left: 3.5"  Top: 0.25"

Label -  Caption: What is the annual percentage rate of your loan? Left: 0.25"  Top: 0.5" Width: 3.25"  FontWeight: Bold   TextAlign: Right

Text box ---  Name: txtRate Format: Percent DecimalPlaces: Auto Left: 3.5"  Top: 0.5"

Label -  Caption: How many monthly payments will you have to make? Left: 0.25"  Top: 0.75" Width: 3.25"  FontWeight: Bold   TextAlign: Right

Text box --  Name: txtTotPmts Format: General Number DecimalPlaces: 0 Left: 3.5"  Top: 0.75"

Label Caption: Do you make payments at the beginning/end of month? Left: 0.25"  Top: 1" Width: 3.25"  FontWeight: Bold   TextAlign: Right

Combo box -  Name: cmdPmtMade RowSourceType: Value List RowSource: BEGIN;END LimitToList: Yes Left: 3.5"  Top: 1"

Command button -  Name: cmdPmtRpt Caption: Payment Report Left: 1.75"  Top: 1.5" Width: 1.5"  FontWeight: Bold   OnClick: [Event Procedure]                      Set the OnClick property of the cmdPmtRpt command button to the following event procedure:  Private Sub cmdPmtRpt_Click    DoCmd.OpenReport "rptPPMT", acViewPreview End Sub                     Close and save the frmPPMT form.  Create the following report, and then on the View menu, turn on the Report Header/Footer command and turn off the Page Header/Footer command.    Report: rptPPMT   Caption: Periodic Payment Report   Recordsource: None                      Set the following properties and create the following controls in the report Header section: <pre class="fixed_text">   Report Header   -   Height: 1"

Label -  Caption: Your monthly payment is: Left: 0.25"  Top: 0.25" Width: 2"  FontWeight: Bold   TextAlign: Right

Textbox ---  Name: txtMonthlyPmt Format: Currency DecimalPlaces: 2 Left: 2.25"  Top: 0.25"

Label --  Caption: Principal and Interest Amortization Table Left: 0"  Top: 0.65" Width: 4"  FontWeight: Bold   FontUnderline: Yes   TextAlign: Center

Label Caption: Month Left: 0"  Top: 0.85" Width: 1"  FontWeight: Bold   TextAlign: Right

Label Caption: Payment Left: 1"  Top: 0.85" Width: 1"  FontWeight: Bold   TextAlign: Right

Label --  Caption: Principal Left: 2"  Top: 0.85" Width: 1"  FontWeight: Bold   TextAlign: Right

Label -  Caption: Interest Left: 3"  Top: 0.85" Width: 1"  FontWeight: Bold   TextAlign: Right

</li>  Set the following properties in the report footer section: <pre class="fixed_text">  Report Footer: --  Height: 0"                    </li>  Create the following controls in the detail section: <pre class="fixed_text">   Text box   --   Name: txtMonth   Format: General Number   DecimalPlaces: 0   Left: 0" Top: 0"

Text box Name: txtPayment Format: Currency DecimalPlaces: 2 Left: 1"  Top: 0"

Text box -  Name: txtPrincipalPmt Format: Currency DecimalPlaces: 2 Left: 2"  Top: 0"

Text box -  Name: txtInterest Format: Currency DecimalPlaces: 2 Left: 3"  Top: 0" </li>  On the View menu, click Code, and then type the following code: Option Compare Database Option Explicit

'When payments can be made. Const ENDPERIOD = 0 Const BEGINPERIOD = 1

'Define variables. Dim intTotPmts As Integer Dim intPeriod As Integer Dim curMonthlyPmt As Currency Dim curInterest As Currency Dim dblRate As Double Dim dblPresentVal As Double Dim dblPrincipalPmt As Double Dim varFutureVal As Variant Dim varPmtMade As Variant

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer) 'Calculate and round the amount of payment that goes toward Principal. dblPrincipalPmt = PPMT(dblRate / 12, intPeriod, _     intTotPmts, -dblPresentVal, varFutureVal, varPmtMade) dblPrincipalPmt = (Int((dblPrincipalPmt + 0.005) * 100) / 100) 'Calculate and round the amount of payment that goes toward Interest. curInterest = Me!txtMonthlyPmt - dblPrincipalPmt curInterest = (Int((curInterest + 0.005) * 100) / 100) 'Print the payments, principal, and interest. Me!txtMonth = intPeriod Me!txtPayment = Me!txtMonthlyPmt Me!txtPrincipalPmt = dblPrincipalPmt Me!txtInterest = curInterest End Sub

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer) 'Continue calculating until counter equals number of payments. intPeriod = intPeriod + 1 'Calculate payments and interest for each payment period. If intPeriod <= intTotPmts Then Me.NextRecord = False End If End Sub

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer) 'Initialize the variables. intPeriod = 1 varFutureVal = 0 dblPresentVal = Forms!frmPPMT!txtPresentVal dblRate = Forms!frmPPMT!txtRate intTotPmts = Forms!frmPPMT!txtTotPmts 'Ensure APR is in decimal format. If dblRate > 1 Then dblRate = dblRate / 100 End If  'Determine if payment will be made at beginning/end of month. If Forms!frmPPMT!cmdPmtMade = "BEGIN" Then varPmtMade = BEGINPERIOD Else varPmtMade = ENDPERIOD End If  'Calculate monthly payment. Me!txtMonthlyPmt = Abs(-Pmt(dblRate / 12, intTotPmts, dblPresentVal, _ varFutureVal, varPmtMade)) End Sub </li> Close and save the rptPPMT report, and then open the frmPPMT form in Form view.</li> Type the following information in the appropriate text boxes: <ul> How much do you want to borrow? $50,000.00 </li> What is the annual percentage rate of your loan? 7.00% </li> How many monthly payments will you have to make? 48 </li> Do you make payments at the beginning/end of month? END </li></ul> </li> Click Payment Report to open rptPPMT report and display the amortization table of payments.</li></ol>

<div class="references_section">