Lesson 10: Fincancial Functions

Excel VBA offers a number of financial functions that can be used for accounting and financial calculations. In this lesson, we shall deal some of those functions that perform basic financial calculations. They are PV, FV and Pmt.

10.1 PV

PV returns the present value of a certain amount of money a person needs to invest in order to earn a certain amount of money in the future(future value), based on the interest rate and the number of years this amount of money is kept. Additionally, it can also return the present value of an annuity which means the present value of a series of payments in the future

The syntax of PV in Excel VBA is

   PV(Rate, Nper, Pmt, FV, Due)

The parameters in the parentheses are explained below:

Rate - Interest rate per period

Nper - Number of payment periods

Pmt - Amount of periodic payment for an annuity

FV - Future value

Due - Indicates when the payment is due. Its value is 1 for beginning of month and 0 for end of the month

Example 10.1

Do you know how much you need to invest today and how much you need to save monthly in order to obtain $1,000,000 thirty years from now? Let'assume a fixed deposit interest rate is 4% per annum and you are willing to save $100 monthly in the bank, you can write the following Excel VBA code to find out the initial investment you need to fork out.

Private Sub CommandButton1_Click()

Dim TheRate, FuVal, Payment As Single

Dim NPeriod As Integer

TheRate = InputBox("Enter the rate per annum")

FuVal = InputBox("Enter future value")

Payment = -InputBox("Enter amount of monthly payment")

NPeriod = InputBox("Enter number of years")

MsgBox ("The Initial Investment is " & Round(PV(TheRate / 12 / 100, NPeriod * 12, Payment, FuVal, 1), 2))

End Sub

Running the program will produce a series of input boxes where the user can enter various values. The answer is shown in Figure 10.1. The value is negative because this is the amount you need to pay.

Figure 10.1

>