Microsoft KB Archive/256200: Difference between revisions
(importing KB archive) |
m (Text replacement - "<" to "<") |
||
Line 313: | Line 313: | ||
'Calculate payments and interest for each payment period. | 'Calculate payments and interest for each payment period. | ||
If intPeriod | If intPeriod <= intTotPmts Then | ||
Me.NextRecord = False | Me.NextRecord = False | ||
End If | End If |
Revision as of 09:01, 21 July 2020
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:
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
Set the following properties in the report footer section:
Report Footer: -------------- Height: 0"
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"
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
- Close and save the rptPPMT report, and then open the frmPPMT form in Form view.
- 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
- 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.
Additional query words: print unbound report
Keywords: kbhowto kbdta KB256200