Dec 13 2023 06:07 AM
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.
Dec 13 2023 07:04 AM
@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
Dec 13 2023 07:13 AM
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.
Dec 13 2023 08:21 AM
Dec 13 2023 08:54 AM
Dec 13 2023 11:51 AM
Please clarify: is the interest rate the yearly rate, or is it the rate for each compounding period?
Dec 14 2023 05:39 AM
Dec 14 2023 07:50 AM
Dec 14 2023 09:27 AM - edited Dec 14 2023 09:50 AM
@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.
Dec 14 2023 09:55 AM
Dec 15 2023 08:53 AM
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