Finding difference of two dates for the same customer name and the same receipt type and calculating interest @ 12% p.a.

[MyTaxcel]

Dear Experts,

I have a problem in calculating the interest on balance amount.

Sr. No	Party Name	Receipt Type	Date	Amount				Land Premium	Lease Rent
1	Laopala RG Ltd.	Land Premium	10-11-2018	50000			Laopala RG Ltd.	=(Interest on land Premium for the year here )	=(Interest on Lease Rent for the year here )
2	Devendra Rawat	Lease Rent	15-11-2018	20000			Devendra Rawat	=(Interest on land Premium for the yearhere )	=(Interest on Lease Rent for the year here )
3	Laopala RG Ltd.	Lease Rent	04-12-2018	70000
4	Devendra Rawat	Lease Rent	20-01-2019	40000
5	Laopala RG Ltd.	Lease Rent	17-02-2019	20000​

There is another worksheet, Which has only unique party names (refer to top right side of table)

from this table, I want to calculate total calculate Interest for the year (01/04/2018 to 31/03/2019) for each Receipt Type for each party.. Interest will be calculated as = [Amount]*[No of Days/365]*[12%]

For Example, in case of Laopala RG Ltd,
a. Interest on land Premium will be calculated for (31/03/2019- 10/11/2018) Days on 50000,
b. Interest on Lease Rent will be calculated for (17/02/2019 - 04/11/2018) Days on 70000, + (31/03/2019 - 17/02/2019) Days on [70000+ ( Interest on Lease Rent (Calculated in Formula) ]

Can this be done using Excel Array Formulas (Excel 2007 or Excel 2013 only) ? No VBA due to frequent transfer of files on gmail

Though this is similar to interest on ledger balances of a Party with two major difference, table consists more than one party and interest is to be calculated for each receipt type separately.

 

[MyTaxcel]

For more clarification

Data Sheet

Sr. No	Party Name	Receipt Type	Date	Amount
1	Laopala RG Ltd.	Land Premium	10-11-18	50000
2	Devendra Rawat	Lease Rent	15-11-18	20000
3	Laopala RG Ltd.	Lease Rent	04-12-18	70000
4	Devendra Rawat	Lease Rent	20-01-19	40000
5	Laopala RG Ltd.	Lease Rent	17-02-19	20000

		…. And so on, there are n number of receipt types and Parties
1	Rate of Interest is 12% and Financial Year is (01/04/2018 to 31/03/2019 containg 365 days)
2	There is another worksheet name Output, Which has only unique party names, where interest has to calculated, It also contains expected results and calculation method
3	From this table, I want to calculate total calculate Interest for the year (01/04/2018 to 31/03/2019) for each Receipt Type for each party
4	Interest will be calculated as = [Amount]*[No of Days/365]*[12%]
5	For Example, in case of Laopala RG Ltd, there are two types of receipts (a) Land Premium and (b) Lease Rent and interest has to calcuated for both type of receipts separately
	a. In case of land Premium :
	Interest will calculated for (31/03/2019- 10/11/2018) = 141 Days on 50000,
	b. In case of Lease Rent :
	Laopala RG Ltd made first payment for Lease Rent on 04/12/2018 of 70000 and next payment on 17/02/2019.. therefore Interest has to be calculated for the year (01/04/2018 to 31/03/2019) as follows
	1. (17/02/2019 -04/12/2018)= 75 Days on 70000
	2. (31/03/2019 - 17/02/2019) =42 Days on ( 70000 + Amount in 1. Above)
6	Interest on Lease Rent only will be total = 1+2 in a single cell
7	Can this be done using Excel Array Formulas (Excel 2007 or Excel 2013 only) ? No VBA due to frequent transfer of files on gmail
8	Though this is similar to interest on ledger balances of a Party with two major difference, table consists more than one party and interest is to be calculated for each receipt type separately.

Output/Expected Result and Calculations

Interest Calculation.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S
1	Final Report and Results						Workings and Calcuation
2
3
4	Sr No	Party Name	Interest on Land Premium	Interest on Lease Rent			Land Premium							Lease Rent
5	1	Laopala RG Ltd.	2,317.81	2,992.60			Interest calculation for the Year (01/04/2018 to 31/03/2019)			Rate	12%			Interest calculation for the Year (01/04/2018 to 31/03/2019)			Rate	12%
6	2	Devendra Rawat	-	2,013.13
7							Laopala RG Ltd.							Laopala RG Ltd.
8							Date	Days	Trasaction Nature	Amount	Balance	Remark		Date	Days	Trasaction Nature	Amount	Balance	Remark
9							10-11-18	0	Receipt	50,000.00	50,000.00	First Transaction, therfore no interest till this transaction		04-12-18	0	Receipt	70,000.00	70,000.00	First Transaction, therfore no interest till this transaction
10							31-03-19	141	Interest on Land Premium	2,317.81	52,317.81	Since there are no further transactions till year end Interest has to be calculated from the date of last receipt		17-02-19	75	Interest on Lease Rent	1,726.03	71,726.03	Interest cacluated for 66 Days from date of first transaction to date of second receipt
11														17-02-19		Receipt	20,000.00	91,726.03	Second Receipt of Lease Rent
12														31-03-19	42	Interest on Lease Rent	1,266.57	92,992.60	Since there are no further transactions till year end Interest has to be calculated from the date of last receipt till year end
13
14							Total Interest for Laopala RG Ltd. on Land Premium			2,317.81				Total Interest for Laopala RG Ltd. on Lease Rent			2,992.60
15
16
17
18							Devendra Rawat							Devendra Rawat
19							Date	Days	Trasaction Nature	Amount	Balance	Remark		Date	Days	Trasaction Nature	Amount	Balance	Remark
20														15-11-18	0	Receipt	20,000.00	20,000.00	First Transaction, therfore no interest till this transaction
21														17-02-19	94	Interest on Lease Rent	618.08	20,618.08	Interest cacluated for 47 Days from date of first transaction to date of second trasaction
22														20-01-19		Receipt	40,000.00	60,618.08	Second Receipt of Lease Rent
23														31-03-19	70	Interest on Lease Rent	1,395.05	62,013.13	Since there are no further transactions till year end Interest has to be calculated from the date of last receipt till year end
24
25							Total Interest for Devendra Rawat on Land Premium			-		No Transactions		Total Interest for Devendra Rawat on Lease Rent			2,013.13
26
27
28
29
30
Output

Cell Formulas
Range	Formula
C5	C5	=J10
D5	D5	=Q10+Q12
C6	C6	=J21+J23
D6	D6	=+Q21+Q23
K9,R20,R9	K9	=+J9
H10,O23,O21,O12,O10	H10	=+G10-G9
J10	J10	=+K9*K5*H10/365
K10,R21:R23,R10:R12	K10	=+K9+J10
Q10,Q23,Q21,Q12	Q10	=+R9*$R$5*O10/365
J14	J14	=+J10
Q14	Q14	=+Q10+Q12
J25,Q25	J25	=+J21++J23