Chapter 16 : Investment Calculation

 

This is a one-million-dollars puzzle  In order to get one million dollars in the future, how much money do we need to invest now?  To solve this puzzle, we need to calculate the initial investment based on the interest rate and the length of a period, usually in years. The formula is

         WorksheetFunction.PV(rate, N, periodic payment, amount, due)

where rate is the interest rate, N is the length of the period  and amount is the amount borrowed.

Here is the VBA code:

<

Private Sub CommandButton1_Click()
Dim F_Money, Int_Rate, Investment As Double
Dim numYear As Single
F_Money = Cells(2, 2)
Int_Rate = (Cells(3, 2) / 100)
numYear = Cells(4, 2)
Investment = PV(Int_Rate, numYear, 0, F_Money, 1)
Cells(5, 2) = Format(-Investment, "$##,###,##0.00")

End Sub

　

Explanation:

We have to divide the interest rate by 100 because it is expressed in percentage form so we have to convert it to decimal form. Annual payment is zero as we are interested only in the initial one-time payment in order to get one million dollars in the future.

We put a negative sign in front of the investment because this is the amount we have to pay.　

　

　

 

 

　　[Previous Chapter] [Back to VBA Tutorial] [Next Chapter]