The VBA FV function calculates the future value of a loan or investment that has periodic fixed payments and a fixed interest rate.
The syntax of the function is:
Where the function arguments are:
Rate | - | The interest rate, per period. |
Nper | - | The number of periods over which the loan or investment is to be paid. |
Pmt | - | The (fixed) payment amount per period. |
[Pv] | - |
An optional argument that specifies the present value of the loan / investment. If omitted, [Pv] uses the default value 0. |
[Due] | - |
An optional argument that defines whether the payment is due at the start or the end of the period. The [Due] argument can have the value 0 or 1, meaning:
0 - the payment is due at the end of the period; If the [Due] argument is omitted, it uses the default value 0 (denoting payments due at the end of the period). |
Cash Flow Sign Convention:
Note that, in line with the general cash flow sign convention, cash outflows are represented by negative numbers and cash inflows are represented by positive numbers.In the following VBA code, the VBA FV function is used to calculate the future value of an investment of $1,000 per month for a period of 5 years. The present value is 0 and interest is 5% per year. Each payment is made at the end of the month.
' Calculate the future value of an investment of $1,000 per month over 5 years.
Dim fv1 As Double' Interest is 5% per year and each payment is made at the end of the month. fv1 = FV( 0.05/12, 60, -1000 ) ' fv1 is calculated to be 68006.0828408428. |
The above VBA code calculates the future value of the investment to be $68,006.08.
Note that: