ACC2000: How to Use the PPmt Function

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.

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

1. 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]
   					

2. Set the OnClick property of the cmdPmtRpt command button to the following event procedure:

   Private Sub cmdPmtRpt_Click()
       DoCmd.OpenReport "rptPPMT", acViewPreview
   End Sub
   					

3. Close and save the frmPPMT form.
4. 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
   					

5. Set the following properties and create the following controls in the report Header section:

      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
   
   					

6. Set the following properties in the report footer section:

      Report Footer:
      --------------
      Height: 0"
   					

7. Create the following controls in the detail section:

      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"
   					

8. 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
   					

9. Close and save the rptPPMT report, and then open the frmPPMT form in Form view.
10. Type the following information in the appropriate text boxes:
    • How much do you want to borrow? $50,000.00
    • What is the annual percentage rate of your loan? 7.00%
    • How many monthly payments will you have to make? 48
    • Do you make payments at the beginning/end of month? END
11. Click Payment Report to open rptPPMT report and display the amortization table of payments.

For more information about the PPmt function, click Microsoft Access Help on the Help menu, type ppmt worksheet function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.