Lesson 10

Excel VBA Lesson 10: Excel VBA Financial Functions - PV, FV, and Pmt

Continue learning classic Excel VBA with the same shared lesson template and cleaner visual style.

Classic Excel VBA Shared modern template Ad-free lesson layout

Excel VBA provides powerful financial functions for accounting and investment calculations. In this comprehensive lesson, you'll learn how to use the PV, FV, and Pmt functions with practical examples.

10.1 PV Function - Present Value Calculation

The PV function returns the present value of an investment - the amount you need to invest today to reach a future financial goal. This is essential for retirement planning, education savings, and other long-term financial strategies.

PV Function Syntax:

PV(Rate, Nper, Pmt, FV, Due)

Parameters Explained:

Rate - Interest rate per period (monthly for most calculations)
Nper - Total number of payment periods
Pmt - Periodic payment amount (additional savings)
FV - Desired future value
Due - Payment timing (0=end of period, 1=beginning)

Example 10.1: Retirement Savings Calculator

Calculate how much you need to invest today to reach $1,000,000 in 30 years with 4% annual interest and $100 monthly contributions:

Private Sub CommandButton1_Click()
  Dim TheRate, FuVal, Payment As Single
  Dim NPeriod As Integer
  TheRate = InputBox("Enter the annual interest rate (e.g., 4 for 4%)")
  FuVal = InputBox("Enter your target future value (e.g., 1000000)")
  Payment = -InputBox("Enter monthly savings amount (e.g., 100)")
  NPeriod = InputBox("Enter investment period in years (e.g., 30)")
  
  Dim InitialInvestment As Currency
  InitialInvestment = Round(PV(TheRate / 12 / 100, NPeriod * 12, Payment, FuVal, 1), 2)
  
  MsgBox "Initial investment needed: $" & InitialInvestment & vbCrLf & _
         "Monthly savings: $" & Abs(Payment) & vbCrLf & _
         "Total years: " & NPeriod & vbCrLf & _
         "Annual interest rate: " & TheRate & "%", _
         vbInformation, "Retirement Planning"
End Sub

Pro Tip: The negative value indicates an outgoing payment (money you need to invest). Convert to positive using the Abs() function when displaying results to users.

Excel VBA PV function example result — Figure 10.1: PV function calculation result

10.2 FV Function - Future Value Projection

The FV function calculates how much your investments will grow over time with regular contributions and compound interest - perfect for visualizing your financial future.

FV Function Syntax:

FV(Rate, Nper, Pmt, PV, Due)

Example 10.2: Investment Growth Calculator

Project the future value of a $100,000 investment with $100 monthly contributions at 5% annual interest over 30 years:

Private Sub CommandButton1_Click()
  Dim TheRate, PVal, Payment As Single
  Dim NPeriod As Integer
  
  TheRate = InputBox("Enter annual interest rate (e.g., 5 for 5%)")
  PVal = InputBox("Enter initial investment (e.g., 100000)")
  Payment = -InputBox("Enter monthly contribution (e.g., 100)")
  NPeriod = InputBox("Enter investment period in years (e.g., 30)")
  
  Dim FutureValue As Currency
  FutureValue = Round(FV(TheRate / 12 / 100, NPeriod * 12, Payment, -PVal, 0), 2)
  
  MsgBox "Projected future value: $" & FutureValue & vbCrLf & _
         "Initial investment: $" & PVal & vbCrLf & _
         "Monthly contributions: $" & Abs(Payment) & vbCrLf & _
         "Growth over " & NPeriod & " years at " & TheRate & "%", _
         vbInformation, "Investment Projection"
End Sub

Excel VBA FV function example result — Figure 10.2: FV function calculation result

10.3 Pmt Function - Loan Payment Calculation

The Pmt function determines periodic loan payments - essential for mortgages, car loans, and other financing scenarios.

Pmt Function Syntax:

Pmt(Rate, Nper, PV, FV, Due)

Example 10.3: Mortgage Payment Calculator

Calculate monthly mortgage payments for a $500,000 loan at 4% interest over 30 years:

Private Sub CommandButton1_Click()
  Dim TheRate, PVal As Single
  Dim NPeriod As Integer
  
  TheRate = InputBox("Enter annual interest rate (e.g., 4 for 4%)")
  PVal = InputBox("Enter loan amount (e.g., 500000)")
  NPeriod = InputBox("Enter loan term in years (e.g., 30)")
  
  Dim MonthlyPayment As Currency
  MonthlyPayment = Round(Pmt(TheRate / 12 / 100, NPeriod * 12, PVal, 0, 0), 2)
  
  MsgBox "Monthly payment: $" & Abs(MonthlyPayment) & vbCrLf & _
         "Loan amount: $" & PVal & vbCrLf & _
         "Loan term: " & NPeriod & " years" & vbCrLf & _
         "Interest rate: " & TheRate & "% APR", _
         vbInformation, "Mortgage Calculator"
End Sub

Excel VBA Pmt function example result — Figure 10.3: Pmt function calculation result

Practical Applications:

Personal Finance: Retirement planning, savings goals, loan management
Business: Investment analysis, capital budgeting, lease calculations
Education: Financial literacy programs, business course examples

Summary

✅ In This Lesson, You Learned:

The PV (Present Value) function for investment planning
The FV (Future Value) function for growth projections
The Pmt (Payment) function for loan calculations
How to apply these functions in real-world financial scenarios

🔗 Related Resources

Share this lesson:

❮ Previous Lesson Next Lesson ❯