Creating a Financial Calculator

[Back to Excel VBA Sample Code]

We can write Excel VBA code to perform complex financial calculations. Here we have created a financial calculator to calculate the periodic payment for a loan taken from the bank. Below is the Excel VBA code for the financial calculator and its 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 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. The runtime interface is as shown in Figure 14.1

Figure 14.1

[Back to Excel VBA Sample Code]

Contact: admin@excelvbatutor.com [Privacy Policy]