Financial Mathematics in VBA

This page gives an overview of the VBA financial functions with example code showing how they are used. A link is provided for each function to its documentation on microsoft learn VBA site.

Code VBA IntelliSense makes these function accessible under submenu VBA, see screenshot. intellisense menu with financial functions.

Hovering a menu you see an explanation what a procedure does, and what the inserted code will look like, depending on changes you make in the Function Call Builder dialog.

DDB - Depreciation of an asset double-declining balance

The DDB function returns a Double specifying the depreciation of an asset for a specific time period by using the double-declining balance method or some other method you specify.


Dim Fmt, InitCost, SalvageVal, MonthLife, LifeTime, DepYear, Depr
Const YRMOS = 12    ' Number of months in a year.'
Fmt = "###,##0.00"
InitCost = InputBox("What's the initial cost of the asset?")'
SalvageVal = InputBox("Enter the asset's value at end of its life.")'
MonthLife = InputBox("What's the asset's useful life in months?")'
Do While MonthLife < YRMOS    ' Ensure period is >= 1 year.'
    MsgBox "Asset life must be a year or more."
    MonthLife = InputBox("What's the asset's useful life in months?")'
Loop
LifeTime = MonthLife / YRMOS    ' Convert months to years.'
If LifeTime <> Int(MonthLife / YRMOS) Then
    LifeTime = Int(LifeTime + 1)    ' Round up to nearest year.'
End If 
DepYear = CInt(InputBox("Enter year for depreciation calculation."))
Do While DepYear < 1 Or DepYear > LifeTime
    MsgBox "You must enter at least 1 but not more than " & LifeTime
    DepYear = InputBox("Enter year for depreciation calculation.")
Loop
Depr = DDB(InitCost, SalvageVal, LifeTime, DepYear)
MsgBox "The depreciation for year " & DepYear & " is " & _
Format(Depr, Fmt) & "."

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/ddb-function

FV - Future value of an annuity

The FV function returns a Double specifying the future value of an annuity based on periodic fixed payments and a fixed interest rate.


Dim Fmt, Payment, APR, TotPmts, PayType, PVal, FVal
Const ENDPERIOD = 0, BEGINPERIOD = 1    ' When payments are made.'
Fmt = "###,###,##0.00"    ' Define money format.'
Payment = InputBox("How much do you plan to save each month?")
APR = InputBox("Enter the expected interest annual percentage rate.")
If APR > 1 Then APR = APR / 100    ' Ensure proper form.'
TotPmts = InputBox("For how many months do you expect to save?")
PayType = MsgBox("Do you make payments at the end of month?", vbYesNo)
If PayType = vbNo Then PayType = BEGINPERIOD Else PayType = ENDPERIOD
PVal = InputBox("How much is in this savings account now?")
FVal = FV(APR / 12, TotPmts, -Payment, -PVal, PayType)
MsgBox "Your savings will be worth " & Format(FVal, Fmt) & "."

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/fv-function

Ipmt - Interest payment

The Ipmt function returns a Double specifying the interest payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.


Dim FVal, Fmt, PVal, APR, TotPmts, PayType, Period, IntPmt, TotInt, Msg
Const ENDPERIOD = 0, BEGINPERIOD = 1    ' When payments are made.'
FVal = 0    ' Usually 0 for a loan.'
Fmt = "###,###,##0.00"    ' Define money format.'
PVal = InputBox("How much do you want to borrow?")
APR = InputBox("What is the annual percentage rate of your loan?")
If APR > 1 Then APR = APR / 100    ' Ensure proper form.'
TotPmts = InputBox("How many monthly payments?")
PayType = MsgBox("Do you make payments at end of the month?", vbYesNo)
If PayType = vbNo Then PayType = BEGINPERIOD Else PayType = ENDPERIOD
For Period = 1 To TotPmts    ' Total all interest.'
    IntPmt = IPmt(APR / 12, Period, TotPmts, -PVal, FVal, PayType)
    TotInt = TotInt + IntPmt
Next Period
Msg = "You'll pay a total of " & Format(TotInt, Fmt) '
Msg = Msg & " in interest for this loan."
MsgBox Msg    ' Display results.'

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/ipmt-function

IRR - Internal rate of return

The IRR function returns a Double specifying the internal rate of return for a series of periodic cash flows (payments and receipts).


Dim Guess, Fmt, RetRate, Msg
Static Values(5) As Double    ' Set up array.'
Guess = .1    ' Guess starts at 10 percent.'
Fmt = "#0.00"    ' Define percentage format.'
Values(0) = -70000    ' Business start-up costs.'
' Positive cash flows reflecting income for four successive years.'
Values(1) = 22000 : Values(2) = 25000
Values(3) = 28000 : Values(4) = 31000
RetRate = IRR(Values(), Guess) * 100    ' Calculate internal rate.'
Msg = "The internal rate of return for these five cash flows is "
Msg = Msg & Format(RetRate, Fmt) & " percent."
MsgBox Msg    ' Display internal return rate.'

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/irr-function

MIRR - Modified internal rate of return

The MIRR function returns a Double specifying the modified internal rate of return for a series of periodic cash flows (payments and receipts).


Dim LoanAPR, InvAPR, Fmt, RetRate, Msg
Static Values(5) As Double    ' Set up array.'
LoanAPR = .1    ' Loan rate.'
InvAPR = .12    ' Reinvestment rate.'
Fmt = "#0.00"    ' Define money format.'
Values(0) = -70000    ' Business start-up costs.'
' Positive cash flows reflecting income for four successive years.'
Values(1) = 22000 : Values(2) = 25000
Values(3) = 28000 : Values(4) = 31000
RetRate = MIRR(Values(), LoanAPR, InvAPR)    ' Calculate internal rate.'
Msg = "The modified internal rate of return for these five cash flows is"
Msg = Msg & Format(Abs(RetRate) * 100, Fmt) & "%."
MsgBox Msg    ' Display internal return rate.'

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/mirr-function

Nper - Number of periods for an annuity

The Nper function returns a Double specifying the number of periods for an annuity based on periodic, fixed payments and a fixed interest rate.


Dim FVal, PVal, APR, Payment, PayType, TotPmts
Const ENDPERIOD = 0, BEGINPERIOD = 1    ' When payments are made.'
FVal = 0    ' Usually 0 for a loan.'
PVal = InputBox("How much do you want to borrow?")
APR = InputBox("What is the annual percentage rate of your loan?")
If APR > 1 Then APR = APR / 100    ' Ensure proper form.'
Payment = InputBox("How much do you want to pay each month?")
PayType = MsgBox("Do you make payments at the end of month?", vbYesNo)
If PayType = vbNo Then PayType = BEGINPERIOD Else PayType = ENDPERIOD
TotPmts = NPer(APR / 12, -Payment, PVal, FVal, PayType)
If Int(TotPmts) <> TotPmts Then TotPmts = Int(TotPmts) + 1
MsgBox "It will take you " & TotPmts & " months to pay off your loan."

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/nper-function

Net present value of an investment

The NPV function returns a Double specifying the net present value of an investment based on a series of periodic cash flows (payments and receipts) and a discount rate.


Dim Fmt, Guess, RetRate, NetPVal, Msg
Static Values(5) As Double    ' Set up array.'
Fmt = "###,##0.00"    ' Define money format.'
Guess = .1    ' Guess starts at 10 percent.'
RetRate = .0625    ' Set fixed internal rate.'
Values(0) = -70000    ' Business start-up costs.'
' Positive cash flows reflecting income for four successive years.'
Values(1) = 22000 : Values(2) = 25000
Values(3) = 28000 : Values(4) = 31000
NetPVal = NPV(RetRate, Values())    ' Calculate net present value.'
Msg = "The net present value of these cash flows is "
Msg = Msg & Format(NetPVal, Fmt) & "."
MsgBox Msg    ' Display net present value.'

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/npv-function

Pmt -Payment for an annuity

The Pmt function returns a Double specifying the payment for an annuity based on periodic, fixed payments and a fixed interest rate.


Dim Fmt, FVal, PVal, APR, TotPmts, PayType, Payment
Const ENDPERIOD = 0, BEGINPERIOD = 1    ' When payments are made.'
Fmt = "###,###,##0.00"    ' Define money format.'
FVal = 0    ' Usually 0 for a loan.'
PVal = InputBox("How much do you want to borrow?")
APR = InputBox("What is the annual percentage rate of your loan?")
If APR > 1 Then APR = APR / 100    ' Ensure proper form.'
TotPmts = InputBox("How many monthly payments will you make?")
PayType = MsgBox("Do you make payments at the end of month?", vbYesNo)
If PayType = vbNo Then PayType = BEGINPERIOD Else PayType = ENDPERIOD
Payment = Pmt(APR / 12, TotPmts, -PVal, FVal, PayType)
MsgBox "Your payment will be " & Format(Payment, Fmt) & " per month."

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/pmt-function

PPmt - Principal payment for an annuity

The PPmt function returns a Double specifying the principal payment for a given period of an annuity based on periodic, fixed payments and a fixed interest rate.


Dim NL, TB, Fmt, FVal, PVal, APR, TotPmts, PayType, Payment, Msg, MakeChart, Period, P, I
Const ENDPERIOD = 0, BEGINPERIOD = 1    ' When payments are made.'
NL = Chr(13) & Chr(10)    ' Define new line.'
TB = Chr(9)    ' Define tab.'
Fmt = "###,###,##0.00"    ' Define money format.'
FVal = 0    ' Usually 0 for a loan.'
PVal = InputBox("How much do you want to borrow?")
APR = InputBox("What is the annual percentage rate of your loan?")
If APR > 1 Then APR = APR / 100    ' Ensure proper form.'
TotPmts = InputBox("How many monthly payments do you have to make?")
PayType = MsgBox("Do you make payments at the end of month?", vbYesNo)
If PayType = vbNo Then PayType = BEGINPERIOD Else PayType = ENDPERIOD
Payment = Abs(-Pmt(APR / 12, TotPmts, PVal, FVal, PayType))
Msg = "Your monthly payment is " & Format(Payment, Fmt) & ". "
Msg = Msg & "Would you like a breakdown of your principal and "
Msg = Msg & "interest per period?"
MakeChart = MsgBox(Msg, vbYesNo)    ' See if chart is desired.'
If MakeChart <> vbNo Then
    If TotPmts > 12 Then MsgBox "Only first year will be shown."
    Msg = "Month  Payment  Principal  Interest" & NL
    For Period = 1 To TotPmts
        If Period > 12 Then Exit For    ' Show only first 12.'
        P = PPmt(APR / 12, Period, TotPmts, -PVal, FVal, PayType)
        P = (Int((P + .005) * 100) / 100)    ' Round principal.'
        I = Payment - P
        I = (Int((I + .005) * 100) / 100)    ' Round interest.'
        Msg = Msg & Period & TB & Format(Payment, Fmt)
        Msg = Msg & TB & Format(P, Fmt) & TB & Format(I, Fmt) & NL
    Next Period
    MsgBox Msg    ' Display amortization table.'
End If

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/ppmt-function

PV - Present value of an annuity

The PV function returns a Double specifying the present value of an annuity based on periodic, fixed payments to be paid in the future and a fixed interest rate.


Dim Fmt, APR, TotPmts, YrIncome, FVal, PayType, PVal
Const ENDPERIOD = 0, BEGINPERIOD = 1    ' When payments are made.'
Fmt = "###,##0.00"    ' Define money format.'
APR = .0825    ' Annual percentage rate.'
TotPmts = 20    ' Total number of payments.'
YrIncome = 50000    ' Yearly income.'
FVal = 1000000    ' Future value.'
PayType = BEGINPERIOD    ' Payment at beginning of month.'
PVal = PV(APR, TotPmts, -YrIncome, FVal, PayType)
MsgBox "The present value is " & Format(PVal, Fmt) & "."

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/pv-function

Rate - Interest rate for an annuity

The Rate function returns a Double specifying the interest rate per period for an annuity.


Dim Fmt, FVal, Guess, PVal, Payment, TotPmts, PayType, APR
Const ENDPERIOD = 0, BEGINPERIOD = 1    ' When payments are made.'
Fmt = "##0.00"    ' Define percentage format.'
FVal = 0    ' Usually 0 for a loan.'
Guess = .1    ' Guess of 10 percent.'
PVal = InputBox("How much did you borrow?")
Payment = InputBox("What's your monthly payment?")'
TotPmts = InputBox("How many monthly payments do you have to make?")
PayType = MsgBox("Do you make payments at the end of the month?", _
vbYesNo)
If PayType = vbNo Then PayType = BEGINPERIOD Else PayType = ENDPERIOD
APR = (Rate(TotPmts, -Payment, PVal, FVal, PayType, Guess) * 12) * 100
MsgBox "Your interest rate is " & Format(CInt(APR), Fmt) & " percent."

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/rate-function

SLN - Straight-line depreciation of an asset

The SLN function returns a Double specifying the straight-line depreciation of an asset for a single period.


Dim Fmt, InitCost, SalvageVal, MonthLife, LifeTime, PDepr
Const YEARMONTHS = 12    ' Number of months in a year.'
Fmt = "###,##0.00"    ' Define money format.'
InitCost = InputBox("What's the initial cost of the asset?")'
SalvageVal = InputBox("What's the asset's value at the end of its useful life?")'
MonthLife = InputBox("What's the asset's useful life in months?")'
Do While MonthLife < YEARMONTHS    ' Ensure period is >= 1 year.'
    MsgBox "Asset life must be a year or more."
    MonthLife = InputBox("What's the asset's useful life in months?")'
Loop
LifeTime = MonthLife / YEARMONTHS    ' Convert months to years.'
If LifeTime <> Int(MonthLife / YEARMONTHS) Then
    LifeTime = Int(LifeTime + 1)    ' Round up to nearest year.'
End If
PDepr = SLN(InitCost, SalvageVal, LifeTime)
MsgBox "The depreciation is " & Format(PDepr, Fmt) & " per year."

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/sln-function

SYD - Sum-of-years' digits depreciation of an asset

The SYD function returns a Double specifying the sum-of-years' digits depreciation of an asset for a specified period.


Dim Fmt, InitCost, SalvageVal, MonthLife, LifeTime, DepYear, PDepr
Const YEARMONTHS = 12    ' Number of months in a year.'
Fmt = "###,##0.00"    ' Define money format.'
InitCost = InputBox("What's the initial cost of the asset?")'
SalvageVal = InputBox("What's the asset's value at the end of its life?")'
MonthLife = InputBox("What's the asset's useful life in months?")'
Do While MonthLife < YEARMONTHS    ' Ensure period is >= 1 year.'
    MsgBox "Asset life must be a year or more."
    MonthLife = InputBox("What's the asset's useful life in months?")'
Loop
LifeTime = MonthLife / YEARMONTHS    ' Convert months to years.'
If LifeTime <> Int(MonthLife / YEARMONTHS) Then
    LifeTime = Int(LifeTime + 1)    ' Round up to nearest year.'
End If 
DepYear = CInt(InputBox("For which year do you want depreciation?"))
Do While DepYear < 1 Or DepYear > LifeTime
    MsgBox "You must enter at least 1 but not more than " & LifeTime
    DepYear = CInt(InputBox("For what year do you want depreciation?"))
Loop
PDepr = SYD(InitCost, SalvageVal, LifeTime, DepYear)
MsgBox "The depreciation for year " & DepYear & " is " & Format(PDepr, Fmt) & "."

See https://learn.microsoft.com/en-us/office/vba/language/reference/user-interface-help/syd-function