<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta name="description" content="VB6Parse Library Reference - pmt - Financial">
<title>pmt - Financial - VB6Parse Library Reference</title>
<link rel="stylesheet" href="../../../assets/css/style.css">
<link rel="stylesheet" href="../../../assets/css/docs-style.css">
<link rel="stylesheet" href="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/11.9.0/styles/github-dark.min.css">
<script src="../../../assets/js/theme-switcher.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/11.9.0/highlight.min.js"></script>
<script src="https://cdnjs.cloudflare.com/ajax/libs/highlight.js/11.9.0/languages/vbnet.min.js"></script>
<script>hljs.highlightAll();</script>
</head>
<body>
<header class="docs-header">
<div class="container">
<h1><a href="../../../index.html">VB6Parse</a> / <a href="../../../library/index.html">Library</a> / <a href="../../../library/functions/financial/index.html">Financial</a> / pmt</h1>
<p class="tagline">VB6 Library Reference</p>
</div>
</header>
<nav class="docs-nav">
<div class="container">
<a href="../../../index.html">Home</a>
<a href="../../../library/index.html">Library Reference</a>
<a href="../../../documentation.html">Documentation</a>
<a href="https://docs.rs/vb6parse" target="_blank">API Docs</a>
<a href="https://github.com/scriptandcompile/vb6parse" target="_blank">GitHub</a>
<button id="theme-toggle" class="theme-toggle" aria-label="Toggle theme">
<span class="theme-icon">🌙</span>
</button>
</div>
</nav>
<main class="container">
<article class="library-item">
<h1 id="pmt-function">Pmt Function</h1>
<p>Returns a Double specifying the payment for an annuity based on periodic, fixed payments and a fixed interest rate.</p>
<h2 id="syntax">Syntax</h2>
<pre><code class="language-vbnet">Pmt(rate, nper, pv, [fv], [type])</code></pre>
<h2 id="parameters">Parameters</h2>
<ul>
<li><code>rate</code> - Required. Double specifying interest rate per period. For example, if you get a car loan at an annual percentage rate (APR) of 10% and make monthly payments, the rate per period is 0.1/12, or 0.0083.</li>
<li><code>nper</code> - Required. Integer specifying total number of payment periods in the annuity. For example, if you make monthly payments on a 4-year car loan, your loan has 4 * 12 (or 48) payment periods.</li>
<li><code>pv</code> - Required. Double specifying present value (or lump sum) that a series of payments to be paid in the future is worth now. For example, when you borrow money to buy a car, the loan amount is the present value to the lender of the monthly car payments you will make.</li>
<li><code>fv</code> - Optional. Variant specifying future value or cash balance you want after you've made the final payment. For example, the future value of a loan is $0 because that's its value after the final payment. However, if you want to save $50,000 over 18 years for your child's education, then $50,000 is the future value. If omitted, 0 is assumed.</li>
<li><code>type</code> - Optional. Variant specifying when payments are due. Use 0 if payments are due at the end of the payment period, or use 1 if payments are due at the beginning of the period. If omitted, 0 is assumed.</li>
</ul>
<h2 id="return-value">Return Value</h2>
<p>Returns a <code>Double</code> specifying the payment amount per period. The payment includes principal and interest but includes no taxes, reserve payments, or fees sometimes associated with loans.</p>
<h2 id="remarks">Remarks</h2>
<p>The <code>Pmt</code> function is one of the most commonly used financial functions in VB6. It calculates the periodic payment
required to pay off a loan or to accumulate a certain amount in a savings plan, given a fixed interest rate and
fixed payment periods.
An annuity is a series of fixed cash payments made over a period of time. An annuity can be a loan (such as a home mortgage)
or an investment (such as a monthly savings plan).
The <code>rate</code> and <code>nper</code> arguments must be calculated using payment periods expressed in the same units. For example, if <code>rate</code>
is calculated using months, <code>nper</code> must also be calculated using months.
For all arguments, cash paid out (such as deposits to savings) is represented by negative numbers; cash received
(such as dividend checks) is represented by positive numbers.
<strong>Important</strong>: The payment returned by <code>Pmt</code> is typically a negative number when calculating loan payments, because
it represents money flowing out. When calculating savings deposits, the payment is positive if you want to know
how much to deposit (money flowing out).</p>
<h2 id="typical-uses">Typical Uses</h2>
<ol>
<li><strong>Mortgage Calculation</strong>: Calculate monthly mortgage payments for home loans</li>
<li><strong>Auto Loan Payments</strong>: Determine monthly car loan payments</li>
<li><strong>Savings Goals</strong>: Calculate required monthly deposits to reach a savings target</li>
<li><strong>Retirement Planning</strong>: Determine periodic contributions needed for retirement funds</li>
<li><strong>Lease Payments</strong>: Calculate lease payment amounts for equipment or vehicles</li>
<li><strong>Student Loan Amortization</strong>: Compute monthly student loan payments</li>
<li><strong>Investment Planning</strong>: Determine periodic investment amounts to reach financial goals</li>
<li><strong>Loan Comparison</strong>: Compare different loan options by payment amount</li>
</ol>
<h2 id="basic-examples">Basic Examples</h2>
<h3 id="example-1-simple-loan-payment">Example 1: Simple Loan Payment</h3>
<pre><code class="language-vbnet">' Calculate monthly payment for a $20,000 loan at 6% APR for 5 years
Dim monthlyPayment As Double
monthlyPayment = Pmt(0.06 / 12, 5 * 12, 20000)
' Returns approximately -386.66 (negative because it's money paid out)</code></pre>
<h3 id="example-2-mortgage-payment">Example 2: Mortgage Payment</h3>
<pre><code class="language-vbnet">' $200,000 home loan, 30 years, 4.5% APR
Dim mortgagePayment As Double
mortgagePayment = Pmt(0.045 / 12, 30 * 12, 200000)
' Returns approximately -1,013.37 per month</code></pre>
<h3 id="example-3-savings-plan">Example 3: Savings Plan</h3>
<pre><code class="language-vbnet">' How much to save monthly to accumulate $50,000 in 10 years at 5% annual return?
Dim monthlyDeposit As Double
monthlyDeposit = Pmt(0.05 / 12, 10 * 12, 0, -50000)
' Returns approximately -322.67 per month (negative = deposit)</code></pre>
<h3 id="example-4-payment-due-at-beginning-of-period">Example 4: Payment Due at Beginning of Period</h3>
<pre><code class="language-vbnet">' Lease payment due at start of month
Dim leasePayment As Double
leasePayment = Pmt(0.08 / 12, 36, 25000, 0, 1)
' Returns slightly lower payment due to beginning-of-period timing</code></pre>
<h2 id="common-patterns">Common Patterns</h2>
<h3 id="pattern-1-calculateloanpayment">Pattern 1: <code>CalculateLoanPayment</code></h3>
<pre><code class="language-vbnet">Function CalculateLoanPayment(loanAmount As Double, _
annualRate As Double, _
years As Integer) As Double
Dim monthlyRate As Double
Dim numPayments As Integer
monthlyRate = annualRate / 12
numPayments = years * 12
' Return positive value (absolute value of payment)
CalculateLoanPayment = Abs(Pmt(monthlyRate, numPayments, loanAmount))
End Function</code></pre>
<h3 id="pattern-2-calculatetotalinterest">Pattern 2: <code>CalculateTotalInterest</code></h3>
<pre><code class="language-vbnet">Function CalculateTotalInterest(principal As Double, _
annualRate As Double, _
years As Integer) As Double
Dim payment As Double
Dim totalPaid As Double
Dim numPayments As Integer
numPayments = years * 12
payment = Abs(Pmt(annualRate / 12, numPayments, principal))
totalPaid = payment * numPayments
CalculateTotalInterest = totalPaid - principal
End Function</code></pre>
<h3 id="pattern-3-compareloanoptions">Pattern 3: <code>CompareLoanOptions</code></h3>
<pre><code class="language-vbnet">Sub CompareLoanOptions(amount As Double)
Dim payment15 As Double
Dim payment30 As Double
Dim totalInterest15 As Double
Dim totalInterest30 As Double
' 15-year loan at 3.5%
payment15 = Abs(Pmt(0.035 / 12, 15 * 12, amount))
totalInterest15 = (payment15 * 15 * 12) - amount
' 30-year loan at 4.0%
payment30 = Abs(Pmt(0.04 / 12, 30 * 12, amount))
totalInterest30 = (payment30 * 30 * 12) - amount
Debug.Print "15-year: $" & Format(payment15, "0.00") & "/mo, Total Interest: $" & Format(totalInterest15, "#,##0")
Debug.Print "30-year: $" & Format(payment30, "0.00") & "/mo, Total Interest: $" & Format(totalInterest30, "#,##0")
End Sub</code></pre>
<h3 id="pattern-4-calculateaffordableamount">Pattern 4: <code>CalculateAffordableAmount</code></h3>
<pre><code class="language-vbnet">Function CalculateAffordableAmount(monthlyPayment As Double, _
annualRate As Double, _
years As Integer) As Double
' Calculate how much you can borrow given a payment amount
Dim monthlyRate As Double
Dim numPayments As Integer
Dim principal As Double
monthlyRate = annualRate / 12
numPayments = years * 12
' Use negative payment because Pmt returns negative for outflows
principal = PV(monthlyRate, numPayments, -monthlyPayment)
CalculateAffordableAmount = principal
End Function</code></pre>
<h3 id="pattern-5-savingscalculator">Pattern 5: <code>SavingsCalculator</code></h3>
<pre><code class="language-vbnet">Function CalculateSavingsDeposit(targetAmount As Double, _
years As Integer, _
annualReturn As Double) As Double
Dim monthlyRate As Double
Dim numPayments As Integer
monthlyRate = annualReturn / 12
numPayments = years * 12
' Use negative FV because it's a goal (money we want)
' Return positive value (absolute value)
CalculateSavingsDeposit = Abs(Pmt(monthlyRate, numPayments, 0, -targetAmount))
End Function</code></pre>
<h3 id="pattern-6-validatepmtparameters">Pattern 6: <code>ValidatePmtParameters</code></h3>
<pre><code class="language-vbnet">Function ValidatePmtParameters(rate As Double, nper As Integer, _
pv As Double) As Boolean
ValidatePmtParameters = False
If nper <= 0 Then
MsgBox "Number of periods must be positive"
Exit Function
End If
If rate < 0 Then
MsgBox "Interest rate cannot be negative"
Exit Function
End If
If pv = 0 Then
MsgBox "Present value cannot be zero for loan calculations"
Exit Function
End If
ValidatePmtParameters = True
End Function</code></pre>
<h3 id="pattern-7-calculatewithextrapayment">Pattern 7: <code>CalculateWithExtraPayment</code></h3>
<pre><code class="language-vbnet">Function CalculatePayoffWithExtra(principal As Double, _
annualRate As Double, _
years As Integer, _
extraPayment As Double, _
ByRef periodsToPayoff As Integer) As Double
Dim regularPayment As Double
Dim totalPayment As Double
Dim balance As Double
Dim monthlyRate As Double
Dim period As Integer
monthlyRate = annualRate / 12
regularPayment = Abs(Pmt(monthlyRate, years * 12, principal))
totalPayment = regularPayment + extraPayment
balance = principal
period = 0
Do While balance > 0 And period < years * 12
balance = balance * (1 + monthlyRate) - totalPayment
period = period + 1
Loop
periodsToPayoff = period
CalculatePayoffWithExtra = totalPayment
End Function</code></pre>
<h3 id="pattern-8-amortizationschedule">Pattern 8: <code>AmortizationSchedule</code></h3>
<pre><code class="language-vbnet">Sub CreateAmortizationSchedule(principal As Double, _
annualRate As Double, _
years As Integer)
Dim payment As Double
Dim monthlyRate As Double
Dim numPayments As Integer
Dim balance As Double
Dim interestPaid As Double
Dim principalPaid As Double
Dim period As Integer
monthlyRate = annualRate / 12
numPayments = years * 12
payment = Abs(Pmt(monthlyRate, numPayments, principal))
balance = principal
Debug.Print "Period", "Payment", "Interest", "Principal", "Balance"
Debug.Print String(60, "-")
For period = 1 To numPayments
interestPaid = balance * monthlyRate
principalPaid = payment - interestPaid
balance = balance - principalPaid
Debug.Print period, _
Format(payment, "0.00"), _
Format(interestPaid, "0.00"), _
Format(principalPaid, "0.00"), _
Format(balance, "0.00")
Next period
End Sub</code></pre>
<h3 id="pattern-9-biweeklypaymentcalculator">Pattern 9: <code>BiweeklyPaymentCalculator</code></h3>
<pre><code class="language-vbnet">Function CalculateBiweeklyPayment(principal As Double, _
annualRate As Double, _
years As Integer) As Double
Dim monthlyPayment As Double
Dim biweeklyPayment As Double
' Calculate monthly payment
monthlyPayment = Abs(Pmt(annualRate / 12, years * 12, principal))
' Biweekly is half the monthly payment
' This results in 26 payments per year instead of 24, paying off faster
biweeklyPayment = monthlyPayment / 2
CalculateBiweeklyPayment = biweeklyPayment
End Function</code></pre>
<h3 id="pattern-10-refinanceanalysis">Pattern 10: <code>RefinanceAnalysis</code></h3>
<pre><code class="language-vbnet">Function ShouldRefinance(currentBalance As Double, _
currentRate As Double, _
currentYearsLeft As Integer, _
newRate As Double, _
newYears As Integer, _
closingCosts As Double) As Boolean
Dim currentPayment As Double
Dim newPayment As Double
Dim monthlySavings As Double
Dim breakEvenMonths As Double
currentPayment = Abs(Pmt(currentRate / 12, currentYearsLeft * 12, currentBalance))
newPayment = Abs(Pmt(newRate / 12, newYears * 12, currentBalance))
monthlySavings = currentPayment - newPayment
If monthlySavings <= 0 Then
ShouldRefinance = False
Exit Function
End If
breakEvenMonths = closingCosts / monthlySavings
' Refinance if break-even is less than 3 years and you'll stay that long
ShouldRefinance = (breakEvenMonths <= 36)
End Function</code></pre>
<h2 id="advanced-usage">Advanced Usage</h2>
<h3 id="example-1-comprehensive-loan-calculator-class">Example 1: Comprehensive Loan Calculator Class</h3>
<pre><code class="language-vbnet">' Complete loan calculator with full amortization
Class LoanCalculator
Private m_principal As Double
Private m_annualRate As Double
Private m_years As Integer
Private m_paymentType As Integer ' 0 = end of period, 1 = beginning
Public Property Let Principal(value As Double)
If value > 0 Then m_principal = value
End Property
Public Property Let AnnualRate(value As Double)
If value >= 0 Then m_annualRate = value
End Property
Public Property Let Years(value As Integer)
If value > 0 Then m_years = value
End Property
Public Property Let PaymentType(value As Integer)
If value = 0 Or value = 1 Then m_paymentType = value
End Property
Public Function GetMonthlyPayment() As Double
Dim monthlyRate As Double
Dim numPayments As Integer
monthlyRate = m_annualRate / 12
numPayments = m_years * 12
GetMonthlyPayment = Abs(Pmt(monthlyRate, numPayments, m_principal, 0, m_paymentType))
End Function
Public Function GetTotalInterest() As Double
Dim payment As Double
Dim totalPaid As Double
payment = GetMonthlyPayment()
totalPaid = payment * m_years * 12
GetTotalInterest = totalPaid - m_principal
End Function
Public Function GetTotalPaid() As Double
GetTotalPaid = GetMonthlyPayment() * m_years * 12
End Function
Public Function GetAmortizationSchedule() As Collection
Dim schedule As Collection
Dim payment As Double
Dim monthlyRate As Double
Dim balance As Double
Dim interestPaid As Double
Dim principalPaid As Double
Dim period As Integer
Dim entry As Object
Set schedule = New Collection
monthlyRate = m_annualRate / 12
payment = GetMonthlyPayment()
balance = m_principal
For period = 1 To m_years * 12
interestPaid = balance * monthlyRate
principalPaid = payment - interestPaid
balance = balance - principalPaid
Set entry = CreateObject("Scripting.Dictionary")
entry.Add "Period", period
entry.Add "Payment", payment
entry.Add "Interest", interestPaid
entry.Add "Principal", principalPaid
entry.Add "Balance", balance
schedule.Add entry
Next period
Set GetAmortizationSchedule = schedule
End Function
Public Function GenerateReport() As String
Dim report As String
report = "Loan Analysis Report" & vbCrLf
report = report & String(50, "=") & vbCrLf
report = report & "Loan Amount: $" & Format(m_principal, "#,##0.00") & vbCrLf
report = report & "Annual Rate: " & Format(m_annualRate * 100, "0.00") & "%" & vbCrLf
report = report & "Term: " & m_years & " years" & vbCrLf
report = report & "Payment Type: " & IIf(m_paymentType = 0, "End of Period", "Beginning of Period") & vbCrLf
report = report & String(50, "-") & vbCrLf
report = report & "Monthly Payment: $" & Format(GetMonthlyPayment(), "#,##0.00") & vbCrLf
report = report & "Total Paid: $" & Format(GetTotalPaid(), "#,##0.00") & vbCrLf
report = report & "Total Interest: $" & Format(GetTotalInterest(), "#,##0.00") & vbCrLf
report = report & "Interest as % of Principal: " & _
Format((GetTotalInterest() / m_principal) * 100, "0.0") & "%"
GenerateReport = report
End Function
Public Function CalculatePayoffDate(startDate As Date) As Date
CalculatePayoffDate = DateAdd("m", m_years * 12, startDate)
End Function
End Class</code></pre>
<h3 id="example-2-retirement-savings-planner">Example 2: Retirement Savings Planner</h3>
<pre><code class="language-vbnet">' Plan retirement savings with multiple scenarios
Class RetirementPlanner
Private m_currentAge As Integer
Private m_retirementAge As Integer
Private m_targetAmount As Double
Private m_expectedReturn As Double
Private m_currentSavings As Double
Public Sub Initialize(currentAge As Integer, retirementAge As Integer, _
targetAmount As Double, expectedReturn As Double, _
currentSavings As Double)
m_currentAge = currentAge
m_retirementAge = retirementAge
m_targetAmount = targetAmount
m_expectedReturn = expectedReturn
m_currentSavings = currentSavings
End Sub
Public Function GetRequiredMonthlyContribution() As Double
Dim yearsToRetirement As Integer
Dim monthlyRate As Double
Dim numPayments As Integer
Dim futureValueNeeded As Double
yearsToRetirement = m_retirementAge - m_currentAge
monthlyRate = m_expectedReturn / 12
numPayments = yearsToRetirement * 12
' Account for current savings growing
futureValueNeeded = m_targetAmount - (m_currentSavings * ((1 + monthlyRate) ^ numPayments))
' Calculate required payment
GetRequiredMonthlyContribution = Abs(Pmt(monthlyRate, numPayments, 0, -futureValueNeeded))
End Function
Public Function ProjectSavingsGrowth() As Collection
Dim projections As Collection
Dim monthlyContribution As Double
Dim balance As Double
Dim monthlyRate As Double
Dim year As Integer
Dim month As Integer
Dim entry As Object
Set projections = New Collection
monthlyContribution = GetRequiredMonthlyContribution()
monthlyRate = m_expectedReturn / 12
balance = m_currentSavings
For year = m_currentAge To m_retirementAge - 1
For month = 1 To 12
balance = balance * (1 + monthlyRate) + monthlyContribution
Next month
Set entry = CreateObject("Scripting.Dictionary")
entry.Add "Age", year + 1
entry.Add "Balance", balance
entry.Add "YearlyContribution", monthlyContribution * 12
projections.Add entry
Next year
Set ProjectSavingsGrowth = projections
End Function
Public Function AnalyzeReturnScenarios() As String
Dim report As String
Dim rates() As Double
Dim i As Integer
Dim payment As Double
ReDim rates(0 To 4)
rates(0) = 0.04
rates(1) = 0.06
rates(2) = 0.08
rates(3) = 0.10
rates(4) = 0.12
report = "Retirement Contribution Analysis" & vbCrLf
report = report & String(50, "=") & vbCrLf
report = report & "Target Amount: $" & Format(m_targetAmount, "#,##0") & vbCrLf
report = report & "Years to Retirement: " & (m_retirementAge - m_currentAge) & vbCrLf
report = report & String(50, "-") & vbCrLf
report = report & "Return Rate Monthly Contribution" & vbCrLf
report = report & String(50, "-") & vbCrLf
For i = 0 To 4
m_expectedReturn = rates(i)
payment = GetRequiredMonthlyContribution()
report = report & Format(rates(i) * 100, "00.0") & "% $" & _
Format(payment, "#,##0.00") & vbCrLf
Next i
AnalyzeReturnScenarios = report
End Function
Public Function GenerateRetirementPlan() As String
Dim plan As String
Dim monthlyContribution As Double
Dim totalContributions As Double
Dim yearsToRetirement As Integer
monthlyContribution = GetRequiredMonthlyContribution()
yearsToRetirement = m_retirementAge - m_currentAge
totalContributions = monthlyContribution * 12 * yearsToRetirement
plan = "Personalized Retirement Plan" & vbCrLf
plan = plan & String(50, "=") & vbCrLf
plan = plan & "Current Age: " & m_currentAge & vbCrLf
plan = plan & "Retirement Age: " & m_retirementAge & vbCrLf
plan = plan & "Current Savings: $" & Format(m_currentSavings, "#,##0") & vbCrLf
plan = plan & "Target Amount: $" & Format(m_targetAmount, "#,##0") & vbCrLf
plan = plan & "Expected Return: " & Format(m_expectedReturn * 100, "0.0") & "%" & vbCrLf
plan = plan & String(50, "-") & vbCrLf
plan = plan & "Required Monthly Contribution: $" & Format(monthlyContribution, "#,##0.00") & vbCrLf
plan = plan & "Total You'll Contribute: $" & Format(totalContributions, "#,##0") & vbCrLf
plan = plan & "Expected Investment Growth: $" & _
Format(m_targetAmount - totalContributions - m_currentSavings, "#,##0")
GenerateRetirementPlan = plan
End Function
End Class</code></pre>
<h3 id="example-3-auto-loan-comparison-tool">Example 3: Auto Loan Comparison Tool</h3>
<pre><code class="language-vbnet">' Compare multiple auto loan options
Module AutoLoanAnalyzer
Private Type LoanOption
LenderName As String
Rate As Double
Term As Integer
DownPayment As Double
TradeInValue As Double
Fees As Double
End Type
Public Function AnalyzeLoanOptions(carPrice As Double, _
options() As LoanOption) As String
Dim report As String
Dim i As Integer
Dim loanAmount As Double
Dim payment As Double
Dim totalCost As Double
Dim totalInterest As Double
Dim bestOption As Integer
Dim lowestCost As Double
report = "Auto Loan Comparison" & vbCrLf
report = report & "Vehicle Price: $" & Format(carPrice, "#,##0") & vbCrLf
report = report & String(70, "=") & vbCrLf
report = report & "Lender Rate Term Payment Total Cost Interest" & vbCrLf
report = report & String(70, "-") & vbCrLf
lowestCost = 999999999
For i = LBound(options) To UBound(options)
loanAmount = carPrice - options(i).DownPayment - options(i).TradeInValue + options(i).Fees
payment = Abs(Pmt(options(i).Rate / 12, options(i).Term, loanAmount))
totalCost = payment * options(i).Term + options(i).DownPayment - options(i).TradeInValue
totalInterest = (payment * options(i).Term) - loanAmount
report = report & Left(options(i).LenderName & Space(15), 15) & " "
report = report & Format(options(i).Rate * 100, "0.0") & "% "
report = report & Format(options(i).Term, "00") & "mo "
report = report & "$" & Format(payment, "000.00") & " "
report = report & "$" & Format(totalCost, "#,##0") & " "
report = report & "$" & Format(totalInterest, "#,##0")
If totalCost < lowestCost Then
lowestCost = totalCost
bestOption = i
report = report & " *BEST*"
End If
report = report & vbCrLf
Next i
report = report & String(70, "-") & vbCrLf
report = report & "Recommended: " & options(bestOption).LenderName
AnalyzeLoanOptions = report
End Function
Public Function CalculateBreakEvenTerm(price As Double, rate1 As Double, _
term1 As Integer, rate2 As Double, _
term2 As Integer) As String
Dim payment1 As Double
Dim payment2 As Double
Dim totalCost1 As Double
Dim totalCost2 As Double
Dim monthlySavings As Double
payment1 = Abs(Pmt(rate1 / 12, term1, price))
payment2 = Abs(Pmt(rate2 / 12, term2, price))
totalCost1 = payment1 * term1
totalCost2 = payment2 * term2
monthlySavings = payment1 - payment2
CalculateBreakEvenTerm = "Option 1: " & term1 & " months at " & _
Format(rate1 * 100, "0.0") & "% = $" & _
Format(payment1, "0.00") & "/mo (Total: $" & _
Format(totalCost1, "#,##0") & ")" & vbCrLf & _
"Option 2: " & term2 & " months at " & _
Format(rate2 * 100, "0.0") & "% = $" & _
Format(payment2, "0.00") & "/mo (Total: $" & _
Format(totalCost2, "#,##0") & ")"
End Function
End Module</code></pre>
<h3 id="example-4-mortgage-affordability-calculator">Example 4: Mortgage Affordability Calculator</h3>
<pre><code class="language-vbnet">' Calculate home affordability based on income
Class MortgageAffordabilityCalculator
Private m_monthlyIncome As Double
Private m_monthlyDebts As Double
Private m_downPaymentPercent As Double
Private m_annualRate As Double
Private m_loanTermYears As Integer
Private m_propertyTaxRate As Double
Private m_insuranceRate As Double
Public Sub SetIncome(monthlyIncome As Double)
m_monthlyIncome = monthlyIncome
End Sub
Public Sub SetDebts(monthlyDebts As Double)
m_monthlyDebts = monthlyDebts
End Sub
Public Sub SetLoanTerms(downPaymentPercent As Double, _
annualRate As Double, years As Integer)
m_downPaymentPercent = downPaymentPercent
m_annualRate = annualRate
m_loanTermYears = years
End Sub
Public Sub SetHousingCosts(propertyTaxRate As Double, insuranceRate As Double)
m_propertyTaxRate = propertyTaxRate
m_insuranceRate = insuranceRate
End Sub
Public Function GetMaxMonthlyPayment() As Double
' Use 28% front-end ratio (housing costs / gross income)
' and 36% back-end ratio (total debt / gross income)
Dim maxByFrontEnd As Double
Dim maxByBackEnd As Double
maxByFrontEnd = m_monthlyIncome * 0.28
maxByBackEnd = (m_monthlyIncome * 0.36) - m_monthlyDebts
' Use the more conservative (lower) value
If maxByFrontEnd < maxByBackEnd Then
GetMaxMonthlyPayment = maxByFrontEnd
Else
GetMaxMonthlyPayment = maxByBackEnd
End If
End Function
Public Function GetAffordableHomePrice() As Double
Dim maxPayment As Double
Dim principalAndInterest As Double
Dim loanAmount As Double
Dim homePrice As Double
maxPayment = GetMaxMonthlyPayment()
' Estimate property tax and insurance (rough approximation)
' Actual payment = P&I + taxes + insurance
' taxes ≈ (price × tax rate) / 12
' insurance ≈ (price × insurance rate) / 12
' P&I = maxPayment - (price × (taxRate + insuranceRate) / 12)
' For simplicity, allocate 80% of max payment to P&I
principalAndInterest = maxPayment * 0.8
' Use PV to find loan amount from payment
loanAmount = Abs(PV(m_annualRate / 12, m_loanTermYears * 12, -principalAndInterest))
' Calculate home price from loan amount
homePrice = loanAmount / (1 - m_downPaymentPercent)
GetAffordableHomePrice = homePrice
End Function
Public Function GenerateAffordabilityReport() As String
Dim report As String
Dim maxPayment As Double
Dim affordablePrice As Double
Dim downPayment As Double
Dim loanAmount As Double
Dim estimatedPayment As Double
maxPayment = GetMaxMonthlyPayment()
affordablePrice = GetAffordableHomePrice()
downPayment = affordablePrice * m_downPaymentPercent
loanAmount = affordablePrice - downPayment
estimatedPayment = Abs(Pmt(m_annualRate / 12, m_loanTermYears * 12, loanAmount))
report = "Mortgage Affordability Analysis" & vbCrLf
report = report & String(50, "=") & vbCrLf
report = report & "Income & Debt Information:" & vbCrLf
report = report & " Monthly Gross Income: $" & Format(m_monthlyIncome, "#,##0") & vbCrLf
report = report & " Monthly Debt Payments: $" & Format(m_monthlyDebts, "#,##0") & vbCrLf
report = report & String(50, "-") & vbCrLf
report = report & "Loan Parameters:" & vbCrLf
report = report & " Down Payment: " & Format(m_downPaymentPercent * 100, "0") & "%" & vbCrLf
report = report & " Interest Rate: " & Format(m_annualRate * 100, "0.00") & "%" & vbCrLf
report = report & " Loan Term: " & m_loanTermYears & " years" & vbCrLf
report = report & String(50, "-") & vbCrLf
report = report & "Affordability Results:" & vbCrLf
report = report & " Max Monthly Payment: $" & Format(maxPayment, "#,##0") & vbCrLf
report = report & " Affordable Home Price: $" & Format(affordablePrice, "#,##0") & vbCrLf
report = report & " Required Down Payment: $" & Format(downPayment, "#,##0") & vbCrLf
report = report & " Loan Amount: $" & Format(loanAmount, "#,##0") & vbCrLf
report = report & " Est. Monthly P&I: $" & Format(estimatedPayment, "#,##0")
GenerateAffordabilityReport = report
End Function
End Class</code></pre>
<h2 id="error-handling">Error Handling</h2>
<p>The <code>Pmt</code> function can raise errors in the following situations:
- <strong>Invalid Procedure Call (Error 5)</strong>: When:
- <code>nper</code> is 0 or negative
- <code>rate</code> is -1 (causes division by zero in the formula)
- <strong>Type Mismatch (Error 13)</strong>: When arguments cannot be converted to numeric values
- <strong>Overflow (Error 6)</strong>: When calculated payment exceeds Double range
Always validate input parameters:</p>
<pre><code class="language-vbnet">On Error Resume Next
payment = Pmt(rate, nper, pv, fv, type)
If Err.Number <> 0 Then
MsgBox "Error calculating payment: " & Err.Description
Err.Clear
End If
On Error GoTo 0</code></pre>
<h2 id="performance-considerations">Performance Considerations</h2>
<ul>
<li>The <code>Pmt</code> function is very fast for individual calculations</li>
<li>For amortization schedules with hundreds of periods, pre-calculate the payment once</li>
<li>Avoid calling <code>Pmt</code> repeatedly in tight loops with the same parameters</li>
<li>Use variables to store intermediate calculations (monthly rate, etc.)</li>
</ul>
<h2 id="best-practices">Best Practices</h2>
<ol>
<li><strong>Convert Rates Properly</strong>: Always divide annual rates by 12 for monthly payments</li>
<li><strong>Match Time Units</strong>: Ensure rate and nper use the same time period</li>
<li><strong>Use Absolute Value</strong>: Use <code>Abs()</code> to display positive payment amounts to users</li>
<li><strong>Validate Inputs</strong>: Check that nper > 0 and rate is reasonable before calling</li>
<li><strong>Handle Sign Conventions</strong>: Remember negative = outflow, positive = inflow</li>
<li><strong>Round for Display</strong>: Use <code>Format()</code> to display payments with 2 decimal places</li>
<li><strong>Consider Type Parameter</strong>: Use type=1 for beginning-of-period payments (leases, etc.)</li>
<li><strong>Document Assumptions</strong>: Clearly state what rate, term, and conditions are used</li>
<li><strong>Test Edge Cases</strong>: Verify behavior with 0% rate, very short/long terms</li>
<li><strong>Combine with Other Functions</strong>: Use with <code>IPmt</code>, <code>PPmt</code>, <code>PV</code>, <code>FV</code> for complete analysis</li>
</ol>
<h2 id="comparison-with-related-functions">Comparison with Related Functions</h2>
<table>
<thead>
<tr>
<th>Function</th>
<th>Purpose</th>
<th>Returns</th>
<th>Use Case</th>
</tr>
</thead>
<tbody>
<tr>
<td><strong>Pmt</strong></td>
<td>Calculate periodic payment</td>
<td>Double (payment amount)</td>
<td>Loan payments, savings deposits</td>
</tr>
<tr>
<td><strong><code>IPmt</code></strong></td>
<td>Interest portion of payment</td>
<td>Double (interest amount)</td>
<td>Amortization schedules, tax deductions</td>
</tr>
<tr>
<td><strong><code>PPmt</code></strong></td>
<td>Principal portion of payment</td>
<td>Double (principal amount)</td>
<td>Tracking loan balance reduction</td>
</tr>
<tr>
<td><strong>PV</strong></td>
<td>Present value</td>
<td>Double (current value)</td>
<td>Reverse calculation from payment</td>
</tr>
<tr>
<td><strong>FV</strong></td>
<td>Future value</td>
<td>Double (future value)</td>
<td>Investment growth projections</td>
</tr>
<tr>
<td><strong><code>NPer</code></strong></td>
<td>Number of periods</td>
<td>Double (period count)</td>
<td>How long to pay off debt</td>
</tr>
<tr>
<td><strong>Rate</strong></td>
<td>Interest rate</td>
<td>Double (rate per period)</td>
<td>Finding effective interest rate</td>
</tr>
</tbody>
</table>
<h2 id="platform-and-version-notes">Platform and Version Notes</h2>
<ul>
<li>Available in all versions of VBA and VB6</li>
<li>Behavior is consistent across Windows platforms</li>
<li>Uses standard annuity formulas from financial mathematics</li>
<li>For zero interest rates, payment is simply pv / nper</li>
<li>Maximum precision limited by Double data type</li>
</ul>
<h2 id="limitations">Limitations</h2>
<ul>
<li>Assumes constant interest rate over entire period</li>
<li>Assumes equal payment amounts (no variable payments)</li>
<li>Does not account for taxes, fees, or insurance directly</li>
<li>Cannot handle variable rate loans (ARMs) without recalculation</li>
<li>Does not consider payment frequency other than what you specify</li>
<li>Sign convention can be confusing (negative for outflows)</li>
</ul>
<h2 id="related-functions">Related Functions</h2>
<ul>
<li><code>IPmt</code>: Returns the interest payment for a specific period</li>
<li><code>PPmt</code>: Returns the principal payment for a specific period</li>
<li><code>PV</code>: Returns the present value of an investment</li>
<li><code>FV</code>: Returns the future value of an investment</li>
<li><code>NPer</code>: Returns the number of periods for an investment</li>
<li><code>Rate</code>: Returns the interest rate per period</li>
</ul>
</article>
<div style="margin-top: 3rem; padding-top: 2rem; border-top: 1px solid var(--border-color);">
<p>
<a href="index.html">← Back to Financial</a> |
<a href="../index.html">View all functions</a>
</p>
</div>
</main>
<footer>
<div class="container">
<p>© 2024-2026 VB6Parse Contributors. Licensed under the MIT License.</p>
</div>
</footer>
</body>
</html>