Recurring Deposit Calculation VBA Code

[SP_Pandey]

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

[#2]

@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

[#3]

@SP_Pandey 

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.

[#4]

@HansVogelaar File 

Still not working.

[#5]

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

[#6]

@SP_Pandey 

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

[#7]

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

[#8]

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

[#9]

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

[#10]

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.

[#11]

 @JoeUser2004 

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?

Working File 

Thank you