ACC2000: How to Use the PPmt Function (256200)


The information in this article applies to:

  • Microsoft Access 2000
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

  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.

REFERENCES

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.
Modification Type:MajorLast Reviewed:12/12/2002
Keywords:kbdta kbhowto KB256200
©2002 Microsoft Corporation. All rights reserved.