Interest Calculation

[rakupareek]

Hi everyone
Please suggest how to make fully automatic Excell sheet by uses of VBA
My Quation is
I have given 10000 rupees to someone on 16-06-2020
He return me 2000 Rs on 14-04-2021
He Return me 1900 on 15-07-2021
He return me 2500 on 13-03-2024
Now I want to calculate interest @5% per year for Money used days
calculate simple interest till 15-06-2021
But carry forward opening balance on 16-06-2021 principle +interest for previous one year
Now calculate interest from 16-06-2021 on principle +interest till 15-06-2022
This loop will run till final payment

 

[SanjayGMusafir]

Please suggest how to make fully automatic Excell sheet by uses of VBA

Why you need vba when it can be done with simple Excel formulae and table...

 

[rakupareek]

I want this to work automatic
If this can be done without VBA then please do it
Thanks in advance

 

[SanjayGMusafir]

Check this and revert -

Book1
	A	B	C	D	E	F
1	Date	Principal	Interest	Total	Paid	Bal Principal
2	6/16/20	10,000		10,000	-	10,000
3	4/14/21	10,000	414	10,414	2,000	8,000
4	6/15/21	8,000	68	8,068	-	8,068
5	7/15/21	8,068	33	8,101	1,900	6,201
6	6/15/22	6,201	285	6,486	-	6,486
7	6/15/23	6,486	324	6,810	-	6,810
8	3/13/24	6,810	254	7,064	2,500	4,564
9	6/15/24	4,564	59	4,622		4,622
Sheet1

Cell Formulas
Range	Formula
D2:D9	D2	=SUM(B2:C2)
B3:B9	B3	=F2
C3:C9	C3	=B3*5%*(A3-A2)/365
F2:F3	F2	=B2-E2
F4:F9	F4	=D4-E4