Santosh080
Board Regular
- Joined
- Jul 15, 2016
- Messages
- 73
- Office Version
- 2021
- Platform
- Windows
Hello Friends
Today i have faced some difficulties in calculating monthly compounding interest.
In my office i have some loan accounts in which interest calculation stopped by system due to some error. Now i have to calculate the monthly interest of these accounts.
Like this example,
This is the Client's account data, here client deposited certain amount regularly but interest have stopped from 30-07-2019
And this what i am doing manually to calculate monthly interest by using formula(written in table). 11= Interest Rate
Like this i have 57 accounts. Here i want some help to quickly do this. Can i do this automatically? By putting some information like last intrest stop date, clients deposits with date etc.. I want monthly interest in which i want to include clients deposit like below. If any non-vba solution possible? or vba? I have little bit knowledge in vba.
Today i have faced some difficulties in calculating monthly compounding interest.
In my office i have some loan accounts in which interest calculation stopped by system due to some error. Now i have to calculate the monthly interest of these accounts.
Like this example,
This is the Client's account data, here client deposited certain amount regularly but interest have stopped from 30-07-2019
| Date | Info | Debit Amount | Credit Amount | Outstanding |
30-07-2019 | 26,50,774.74 | |||
31-07-2019 | Deposit | 3,800.00 | ||
31-07-2019 | Deposit | 2,000.00 | ||
31-08-2019 | Deposit | 3,800.00 | ||
31-08-2019 | Deposit | 2,000.00 | ||
30-09-2019 | Deposit | 3,800.00 | ||
30-09-2019 | Deposit | 2,000.00 | ||
01-11-2019 | Deposit | 3,800.00 | ||
01-11-2019 | Deposit | 2,000.00 | ||
26-11-2019 | Deposit | 1,50,000.00 | ||
30-11-2019 | Deposit | 3,800.00 | ||
30-11-2019 | Deposit | 2,000.00 | ||
02-12-2019 | Deposit | 12,000.00 | ||
07-12-2019 | Deposit | 3,000.00 | ||
16-12-2019 | Deposit | 10,000.00 | ||
24-12-2019 | Deposit | 10,000.00 | ||
01-01-2020 | Deposit | 3,800.00 | ||
01-01-2020 | Deposit | 2,000.00 | ||
02-01-2020 | Deposit | 10,000.00 | ||
03-02-2020 | Deposit | 3,800.00 | ||
03-02-2020 | Deposit | 2,000.00 | ||
04-02-2020 | Deposit | 10,000.00 | ||
29-02-2020 | Deposit | 3,800.00 | ||
29-02-2020 | Deposit | 2,000.00 | ||
03-03-2020 | Deposit | 11,000.00 | ||
09-03-2020 | WITHDRAWALS | 2,360.00 | ||
11-03-2020 | WITHDRAWALS | 11,800.00 | ||
26-03-2020 | Deposit | 3,800.00 | ||
26-03-2020 | Deposit | 2,000.00 | ||
30-04-2020 | Deposit | 3,800.00 | ||
30-04-2020 | Deposit | 2,000.00 | ||
30-05-2020 | Deposit | 3,800.00 | ||
30-05-2020 | Deposit | 2,000.00 | ||
30-06-2020 | Deposit | 3,800.00 | ||
30-06-2020 | Deposit | 2,000.00 | ||
31-07-2020 | Deposit | 3,800.00 | ||
31-07-2020 | Deposit | 2,000.00 | ||
31-08-2020 | Deposit | 2,000.00 | ||
15-09-2020 | Deposit | 1,00,000.00 | ||
15-09-2020 | Deposit | 1,00,000.00 | ||
30-09-2020 | Deposit | 2,000.00 | ||
30-10-2020 | Deposit | 2,000.00 | ||
27-11-2020 | Deposit | 2,000.00 | ||
31-12-2020 | Deposit | 2,000.00 | ||
30-01-2021 | Deposit | 2,000.00 | ||
26-02-2021 | Deposit | 2,000.00 | ||
31-03-2021 | Deposit | 30,400.00 | ||
31-03-2021 | Deposit | 2,000.00 | ||
30-04-2021 | Deposit | 3,800.00 | ||
30-04-2021 | Deposit | 2,000.00 | ||
29-05-2021 | Deposit | 3,800.00 | ||
29-05-2021 | Deposit | 2,000.00 | ||
30-06-2021 | Deposit | 3,800.00 | ||
30-06-2021 | Deposit | 2,000.00 | ||
31-07-2021 | Deposit | 3,800.00 | ||
31-07-2021 | Deposit | 2,000.00 | ||
31-08-2021 | Deposit | 3,800.00 | ||
31-08-2021 | Deposit | 2,000.00 | ||
30-09-2021 | Deposit | 3,800.00 | ||
30-09-2021 | Deposit | 2,000.00 |
And this what i am doing manually to calculate monthly interest by using formula(written in table). 11= Interest Rate
Like this i have 57 accounts. Here i want some help to quickly do this. Can i do this automatically? By putting some information like last intrest stop date, clients deposits with date etc.. I want monthly interest in which i want to include clients deposit like below. If any non-vba solution possible? or vba? I have little bit knowledge in vba.
| Date | Interest | Credit | Balance | No of Days |
[A2] 30-07-2019 | 2650775 | |||
31-07-2019 | 3800 | 2646975 | ||
31-07-2019 | 2000 | 2644975 | ||
31-08-2019 | 3800 | 2641175 | ||
31-08-2019 | 2000 | 2639175 | ||
31-08-2019 | [=ROUND(E7*11*D6/36500,2)] 25451.77 | 2664627 | (=A7-A2)32 | |
30-09-2019 | 3800 | 2660827 | ||
30-09-2019 | 2000 | 2658827 | ||
30-09-2019 | 24038.71 | 2682865 | 30 | |
01-11-2019 | 0 | 3800 | 2679065 | |
01-11-2019 | 0 | 2000 | 2677065 | |
01-11-2019 | 25817.18 | 2702882 | 32 | |
26-11-2019 | 0 | 150000 | 2552882 | |
30-11-2019 | 0 | 3800 | 2549082 | |
30-11-2019 | 0 | 2000 | 2547082 | |
30-11-2019 | 22260.8 | 2569343 | 29 | |
02-12-2019 | 0 | 12000 | 2557343 | |
07-12-2019 | 0 | 3000 | 2554343 | |
16-12-2019 | 0 | 10000 | 2544343 | |
24-12-2019 | 0 | 10000 | 2534343 | |
31-12-2019 | 23677.01 | 2558020 | 31 | |
01-01-2020 | 0 | 3800 | 2554220 | |
01-01-2020 | 0 | 2000 | 2552220 | |
02-01-2020 | 0 | 10000 | 2542220 | |
31-01-2020 | 23750.61 | 2565971 | 31 | |
03-02-2020 | 0 | 3800 | 2562171 | |
03-02-2022 | 0 | 2000 | 2560171 | |
04-02-2020 | 0 | 10000 | 2550171 | |
29-02-2020 | 0 | 3800 | 2546371 | |
29-02-2020 | 0 | 2000 | 2544371 | |
29-02-2020 | 22237.1 | 2566608 | 29 | |
03-03-2020 | 11000 | 2555608 | ||
09-03-2020 | 2360 | 2557968 | ||
11-03-2020 | 11800 | 2569768 | ||
26-03-2020 | 3800 | 2565968 | ||
26-03-2020 | 2000 | 2563968 | ||
31-03-2020 | 23953.78 | 2587922 | 31 | |
30-04-2020 | 0 | 3800 | 2584122 | |
30-04-2020 | 0 | 2000 | 2582122 | |
30-04-2020 | 23345.21 | 2605467 | 30 | |
30-05-2020 | 0 | 3800 | 2601667 | |
30-05-2020 | 0 | 2000 | 2599667 | |
31-05-2020 | 24287.3 | 2623954 | 31 | |
30-06-2020 | 0 | 3800 | 2620154 | |
30-06-2020 | 0 | 2000 | 2618154 | |
30-06-2020 | 23670.98 | 2641825 | 30 | |
31-07-2020 | 0 | 3800 | 2638025 | |
31-07-2020 | 0 | 2000 | 2636025 | |
31-07-2020 | 24626.98 | 2660652 | 31 | |
31-08-2020 | 0 | 2000 | 2658652 | |
31-08-2020 | 24838.37 | 2683491 | 31 | |
15-09-2020 | 0 | 100000 | 2583491 | |
15-09-2020 | 0 | 100000 | 2483491 | |
30-09-2020 | 0 | 2000 | 2481491 | |
30-09-2020 | 22435.39 | 2503926 | 30 | |
30-10-2020 | 0 | 2000 | 2501926 | |
31-10-2020 | 23374.16 | 2525300 | 31 | |
27-11-2020 | 0 | 2000 | 2523300 | |
30-11-2020 | 22813.4 | 2546113 | 30 | |
31-12-2020 | 0 | 2000 | 2544113 | |
31-12-2020 | 23768.29 | 2567882 | 31 | |
30-01-2021 | 0 | 2000 | 2565882 | |
31-01-2021 | 23971.66 | 2589853 | 31 | |
26-02-2021 | 0 | 2000 | 2587853 | |
28-02-2021 | 21837.23 | 2609691 | 28 | |
31-03-2021 | 0 | 30400 | 2579291 | |
31-03-2021 | 0 | 2000 | 2577291 | |
31-03-2021 | 24078.25 | 2601369 | 31 | |
30-04-2021 | 0 | 3800 | 2597569 | |
30-04-2021 | 0 | 2000 | 2595569 | |
30-04-2021 | 23466.79 | 2619036 | 30 | |
29-05-2021 | 0 | 3800 | 2615236 | |
29-05-2021 | 0 | 2000 | 2613236 | |
31-05-2021 | 24414.07 | 2637650 | 31 | |
30-06-2021 | 0 | 3800 | 2633850 | |
30-06-2021 | 0 | 2000 | 2631850 | |
30-06-2021 | 25381.13 | 2657231 | 32 | |
31-07-2021 | 0 | 3800 | 2653431 | |
31-07-2021 | 0 | 2000 | 2651431 | |
31-07-2021 | 24770.9 | 2676202 | 31 | |
31-08-2021 | 0 | 3800 | 2672402 | |
31-08-2021 | 0 | 2000 | 2670402 | |
31-08-2021 | 24948.14 | 2695350 | 31 | |
30-09-2021 | 0 | 3800 | 2691550 | |
30-09-2021 | 0 | 2000 | 2689550 | |
30-09-2021 | 24316.48 | 2713866 | 30 |
