Forum Discussion
Recurring Deposit Calculation VBA Code
Hi There!
I need your help with a VBA code used to calculate the maturity value of a recurring deposit. Despite several attempts in the chat GPT, I couldn't solve it. So, I'm seeking your support for the same. The given code is not calculating the exact maturity value for quarterly, half-yearly, and yearly compounding frequencies.
Calculate_MaturityValue
End Sub
Private Sub TextBox16_Change()
' Interest Rate Set up with %'
Dim Temp As String
Temp = Replace(TextBox16.Text, ",", "")
If Len(Temp) > 0 And Right(Temp, 1) = "%" Then
Temp = Left(Temp, Len(Temp) - 1)
End If
If IsNumeric(Temp) Then
If Val(Temp) >= 1 Then
Temp = Format(Val(Temp), "#,##0.00") & "%"
Else
Temp = Format(Val(Temp), "0.00%")
End If
End If
TextBox16.Text = Temp
If InStr(Temp, ".") > 0 Then
TextBox16.SelStart = InStr(Temp, ".") - 1
Else
TextBox16.SelStart = Len(Temp)
End If
End Sub
Private Sub Calculate_MaturityValue()
' Declare variables
Dim Monthly As Double ' Monthly Deposit Amount
Dim R As Double ' Annual interest rate
Dim N As Double ' Number of periods (in months)
Dim Maturity As Double ' Maturity Value
Dim CompoundingFactor As Double ' Compounding factor
Dim i As Double ' Counter for compounding periods
' Get input values from textboxes and option buttons
Monthly = CDbl(TextBox17.Value)
R = CDbl(Replace(TextBox16.Value, "%", "")) / 100 ' Convert percentage to decimal
N = CDbl(TextBox15.Value)
' Calculate maturity value based on selected option button
If OptionButton1.Value Then ' Monthly mode
CompoundingFactor = 12 ' Number of compounding periods per year
ElseIf OptionButton2.Value Then ' Quarterly mode
CompoundingFactor = 4
ElseIf OptionButton3.Value Then ' Half-Yearly mode
CompoundingFactor = 2
ElseIf OptionButton5.Value Then ' Yearly mode
CompoundingFactor = 1
End If
Maturity = 0 ' Initial maturity value
' Calculate maturity value using the appropriate compounding formula
For i = 1 To N
Maturity = (Maturity + Monthly) * (1 + R / CompoundingFactor)
Next i
' Display final results in TextBox14
TextBox14.Value = Format(Maturity, "Currency")
End Sub
SP_Pandey The problem is that you are still compounding monthly in each case. Here is my suggestion to try:
' Calculate maturity value based on selected option button
If OptionButton1.Value Then ' Monthly mode
R= 1 + R / 12 ' calculate new rate based on compounding frequencyN= N \ 1 ' calculates the number of updates to be done base on frequency
REMAININGPAYMENT = 0 ' calculates any additional payment when frequency has left over
MONTHLY = MONTHLY * 1 ' calculates the sum of payments for the frequency of interest
ElseIf OptionButton2.Value Then ' Quarterly mode
R= 1 + R / 4
N= N \ 3REMAININGPAYMENT = (N MOD 3) * MONTHLY
MONTHLY = MONTHLY * 3
ElseIf OptionButton3.Value Then ' Half-Yearly mode
R= 1 + R / 2N= N \ 6
REMAININGPAYMENT = (N MOD 6) * MONTHLY
MONTHLY = MONTHLY * 6
ElseIf OptionButton5.Value Then ' Yearly mode
R= 1 + R / 1N= N \ 12
REMAININGPAYMENT = (N MOD 3) * MONTHLY
MONTHLY = MONTHLY * 12
End If
Maturity = 0 ' Initial maturity value
' Calculate maturity value using the appropriate compounding formula
For i = 1 To N
Maturity = (Maturity + Monthly) * R
Next i
Maturity = Maturity + REMAININGPAYMENTIf TextBox15 is the number of months, insert the following line just above the line Maturity = 0:
N = N * CompoundingFactor \ 12
For example, if the number of months is 24 and deposits are made quarterly, the number of periods is 24 * 4 \ 12 = 8.
Please clarify: is the interest rate the yearly rate, or is it the rate for each compounding period?
- note that if Hans is correct that the "monthly" deposit is actually a deposit at the same rate as the compounding factor, then yes it becomes significantly more simple as noted. My solution above was assuming a monthly deposit but different compounding rates. These equation also do not take into account ACTUAL deposit times vs ACTUAL interest times and if the deposit occurs just before/after the interest accrual or missed altogether...
