Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

# Recurring Deposit Calculation VBA Code

Copper Contributor

# 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.

Thank you for your valuable time and support.

VBA Code:
Private Sub CommandButton1_Click()
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

10 Replies

# Re: Recurring Deposit Calculation VBA Code

@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 frequency

N= 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 \ 3

REMAININGPAYMENT = (N MOD 3) * MONTHLY

MONTHLY = MONTHLY * 3

ElseIf OptionButton3.Value Then ' Half-Yearly mode
R= 1 + R / 2

N= N \ 6

REMAININGPAYMENT = (N MOD 6) * MONTHLY

MONTHLY = MONTHLY * 6

ElseIf OptionButton5.Value Then ' Yearly mode
R= 1 + R / 1

N= 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 + REMAININGPAYMENT

# Re: Recurring Deposit Calculation VBA Code

If 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.

# Re: Recurring Deposit Calculation VBA Code

Still not working.

# Re: Recurring Deposit Calculation VBA Code

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...

# Re: Recurring Deposit Calculation VBA Code

Please clarify: is the interest rate the yearly rate, or is it the rate for each compounding period?

# Re: Recurring Deposit Calculation VBA Code

The given interest rate is yearly. The monthly deposit amount shall be equal for the each month and its interest capitalization and interest rates shall be compounding based on the user selection i.e. monthly, quarterly, half-yearly and yearly.
For example:

Monthly Deposit 12000
Interest 12%
Tenure (in Months) 12
For Quarterly compouding
Month Deposit Interest Earned Quarterly Interest Ending Balance
1 12000 120.00
2 24,000.00 240.00
3 36,000.00 360.00 720.00 36,720.00
4 48,720.00 487.20
5 60,720.00 607.20
6 72,720.00 727.20 1,821.60 74541.6
7 86,541.60 865.42
8 98,541.60 985.42
9 110,541.60 1,105.42 2956.248 113497.848
10 125,497.85 1,254.98
11 137,497.85 1,374.98
12 149,497.85 1,494.98 4124.93544 153622.7834

For Monthly Compounding
Month Deposit Interest Earned Ending Balance
1 12000 120.00 12,120.00
2 24,120.00 241.20 24,361.20
3 36,361.20 363.61 36,724.81
4 48,724.81 487.25 49,212.06
5 61,212.06 612.12 61,824.18
6 73,824.18 738.24 74,562.42
7 86,562.42 865.62 87,428.05
8 99,428.05 994.28 100,422.33
9 112,422.33 1,124.22 113,546.55
10 125,546.55 1,255.47 126,802.02
11 138,802.02 1,388.02 140,190.04
12 152,190.04 1,521.90 153,711.94

Thanks

# Re: Recurring Deposit Calculation VBA Code

maybe try this:
PendingInt = 0
For i = 1 To N
Maturity = (Maturity + Monthly)
PendingInt = PendingInt + Maturity * (1 + R / CompoundingFactor)
IF NOT (i MOD CompoundingFactor) THEN
Maturity = Maturity + PendingInt
PendingInt = 0
END IF
Next i

# Re: Recurring Deposit Calculation VBA Code

@SP_Pandey, note the following corrections....

@mtarler  wrote:  PendingInt = PendingInt + Maturity * (1 + R / CompoundingFactor)

Should be Maturity * R / 12.  Interest is calculated monthly.

In @SP_Pandey's example, with an annual rate of 12%, the monthly rate is correctly 1% (12% / 12) when compounded quarterly, not 3% (12% / 4).

Aside....  Since that is the only use of R, you might consider calculating R as

R = CDbl(Replace(TextBox16.Value, "%", "")) / 100 / 12

in the first place, and simply using Maturity * R in the loop.

-----

@mtarler  wrote:  IF NOT (i MOD CompoundingFactor) THEN

Should be i MOD (12 / CompoundingFactor).

For example, for semiannual compounding, CompoundingFactor is 2, but interest is compounded every 6 months (12 / 2), not every 2 months.

In fact, with these corrections, I see no purpose in having CompoundingFactor at all.

I would have CompoundingFreq, which is (every) 12, 6, 3, or 1 (months) for annual, semiannual, quarterly or monthly.

Then I would write

If i MOD CompoundingFreq = 0 Then

Aside....  This presumes that N is a multiple of CompoundingFreq.  In case it is not, you might want to add the statement

Maturity = Maturity + PendingInt

after Next for the For-loop.

Note that PendingInt = 0 when N is a multiple of CompoundingFreq.  So the added statement does not cause any problem normally.

# Re: Recurring Deposit Calculation VBA Code

Ah yes thank you Joe for catching those corrections. I also agree with everything you noted for improvements and apologize to the OP for not catching my errors before posting.

# Re: Recurring Deposit Calculation VBA Code

Thank you Joe for your effort. However, the issues are still pertaining. The maturity value for monthly is calculating perfectly and rest is producing wrong value. I hereby sharing you my working file. Could you please resolve it and share it back to me?

Thank you