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.
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(Rate, Nper, Pmt, FV, Due)
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
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(Rate, Nper, Pmt, PV, Due)
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
The Pmt function determines periodic loan payments - essential for mortgages, car loans, and other financing scenarios.
Pmt(Rate, Nper, PV, FV, Due)
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
Share this lesson:
Copyright ® 2008-2023 Dr. Liew Voon Kiong . All rights reserved [Privacy Policy]
Contact: Facebook Page