|
|
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.
|
|
