VBA  XV- Investment Calculation

 

This is the one million dollar puzzle again but now we program  a VBA in Excel to solve the problem. In order to get one million dollars in the future, 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 program:

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.

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

　

　

　

 

 

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