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