Need help in Interest Calculation

Santosh080

Board Regular
Joined
Jul 15, 2016
Messages
77
Office Version
  1. 2021
Platform
  1. 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

DateInfoDebit AmountCredit AmountOutstanding
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
CreditBalance
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​
 

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

Forum statistics

Threads
1,223,910
Messages
6,175,318
Members
452,634
Latest member
cpostell

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top