Forum Discussion
Recurring Deposit Calculation VBA Code
HansVogelaar https://drive.google.com/file/d/1_FcGUNjGySC-DziN-Dyp9hk1y-1mY08e/view?usp=sharing
Still not working.
Please clarify: is the interest rate the yearly rate, or is it the rate for each compounding period?
- 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- 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 iSP_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.
