Compound interest calculations with user form

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

Leave a Reply

Your email address will not be published. Required fields are marked *