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 sup...
Forum Discussion
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.
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
- 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...
