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

- DDB - Depreciation of an asset double-declining balance
- Ipmt - Interest payment
- IRR - Internal rate of return
- MIRR - Modified internal rate of return
- Net present value of an investment
- Nper - Number of periods for an annuity
- Pmt -Payment for an annuity
- PPmt - Principal payment for an annuity
- PV - Present value of an annuity
- FV - Future value of an annuity
- Rate - Interest rate for an annuity
- SLN - Straight-line depreciation of an asset
- SYD - Sum-of-years' digits depreciation of an asset

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
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.'
```

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

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

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

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

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