How to use FV function in VBA?

The FV function in VBA, or Visual Basic for Applications, is used to calculate the future value of an investment based on periodic, constant payments and a constant interest rate. This is the same FV function that is available in Excel. To use it in VBA, you will typically be within the environment of an Office application like Excel. Here’s the syntax for the FV function:

FV(rate, nper, pmt, [pv], [type])
Where: rate is the interest rate for each period. nper is the total number of payment periods. pmt is the payment made each period; it remains constant throughout the annuity. pv is the present value, or the total amount that a series of future payments is worth now; if omitted, pv is assumed to be 0. type is an optional argument that indicates when payments are due: 0 means at the end of the period and 1 means at the beginning of the period. If omitted, it is assumed to be 0. Here is an example of how to use the FV function in VBA to calculate the future value of an investment:

Sub CalculateFutureValue()

    Dim InterestRate As Double
    Dim NumberOfPeriods As Integer
    Dim PaymentPerPeriod As Double
    Dim PresentValue As Double
    Dim PaymentType As Integer
    Dim FutureValue As Double
    ' Example values
    InterestRate = 0.05 ' 5% annual interest rate
    NumberOfPeriods = 10 ' Invest for 10 years
    PaymentPerPeriod = -1000 ' Payment of $1000 each year (negative because it's an outlay)
    PresentValue = -10000 ' Initial investment of $10,000 (negative because it's an outlay)
    PaymentType = 0 ' Payments are made at the end of each period
    ' Calculate future value
    FutureValue = FV(InterestRate / 12, NumberOfPeriods * 12, PaymentPerPeriod, PresentValue, PaymentType)
    ' Output the result
    MsgBox "The future value of the investment is: $" & Format(FutureValue, "Standard")

End Sub
Please note that payments (PaymentPerPeriod and PresentValue) are typically entered as negative numbers in financial functions to represent cash outflows (money you are paying out). If you receive money from the investment, the value should be positive, representing cash inflows. When you run this macro, it will calculate the future value of the investment and show a message box with the formatted result. Adjust the example values (InterestRate, NumberOfPeriods, PaymentPerPeriod, PresentValue, and PaymentType) to fit your specific scenario before running the macro.

Unlock Your Potential


Basic - Advanced


Access Basic - Advanced

Power BI

Power BI Basic - Advanced

Help us grow the project

Leave a Reply

Your email address will not be published. Required fields are marked *