# 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. .

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
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
End If
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