A user form with labels, text boxes and a command button is created to display the compound interest calculations. The data is then transferred to an Excel worksheet for further analysis. The code for the command button is given below:

Private Sub CommandButton1_Click()

Dim amount, rate, period, interest As Single

‘if the amount of data is beyond 33000 rows then use Long as the data type

Dim erow As Integer

erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

‘If the user enters 100$ or 100 Rupees the amount will be 100

amount = Val(TextBox1.Text)

Cells(erow, 1).Value = amount

‘if the user enters 10% the value of rate will be 10

rate = (Val(TextBox2.Text)) / 100

Cells(erow, 2).Value = rate

‘we wish to display the ineterest rate per year as % like 10%

Cells(erow, 2).Value = Cells(erow, 2) * 100 & “%”

‘if the user enters 5 years the value of period will be 5

period = Val(TextBox3.Text)

Cells(erow, 3).Value = period

interest = ((amount) * (1 + rate) ^ period) – amount

Cells(erow, 4).Value = interest

End Sub

The important thing to note is the use of the Val function to extract only the numerical part of the data placed in the text boxes. Also the compound interest calculation mathematical formula is used to calculate the final value of the investment or loan and the total compound interest.

Watch the video:

Further reading:

MS Excel: VAL Function (VBA)

Compound interest formula