|
|
MS Excel is very good in doing complex
calculations, so it is very easy to create VBA that can do all kinds of
calculations. Here I have created a program that can perform financial
calculation, it is the same as the financial calculator that I have shown you in
December's VB Today. Below are the codes and the output interface.
Private Sub CommandButton1_Click()
Dim N As Integer
Dim p, pmt, rate, I, PVIFA As Double
p = Cells(2, 2)
rate = Cells(3, 2)
N = Cells(4, 2) * 12
I = (rate / 100) / 12
PVIFA = 1 / I - 1 / (I * (1 + I) ^ N)
pmt = p / PVIFA
Cells(5, 2) = Format(pmt, "$#,##0.00")
End Sub
|
The formula to calculate periodic payment is
payment=Initial Principal/PVIFA, where PVIFA is known as
present value interest factor for an annuity . The formula to compute
PVIFA is 1/i - 1/i(1+i)n
where n is the number of payments. Normally you can check up a financial
table for the value of PVIFA and then calculate
the payments manually.
The function Format
is to determine the number of decimal places and the use of the $
sign.
|
|
