Finding difference of two dates for the same customer name and the same receipt type and c

[MyTaxcel]

Dear Experts,

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

[First WorkSheet ] [On Another Work sheet]

Sr. No Party Name Receipt Type Date Amount PartyName Land Premium Lease Rent
1 Laopala RG Ltd. Land Premium 10-11-18 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-18 20000 Devendra Rawat =(Interest on land Premium for the year here ) =(Interest on Lease Rent for the year here )
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.

[menem]

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) ]

Where is 31/03/2019 or 17/02/2019 ?

Regards.

[MyTaxcel]

Thanks for reply..

31/03/2019 is the financial year end (01/04/2018 To 31/03/2019) , both dates can be taken as constant or referenced from the fixed cells.


Example is reproduced due to mistake in one of the dates
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/12/2018) Days on 70000, + (31/03/2019 - 17/02/2019) Days on [70000+ ( Interest on Lease Rent (Calculated in Formula) ]

There are two types of receipts (a) Land Premium and (b) Lease Rent.

Consider the case of Lease Rent only..

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)

Interest on Lease Rent only will be total = 1+2 in a single cell

[Richard Buttrey]

With a couple of helper columns on sheet 1 perhaps in C2 on Sheet2 copied across and down

=SUMIFS(Sheet1!$G$2:$G$6,Sheet1!$C$2:$C$6,C$1,Sheet1!$B$2:$B$6,$B2)

See attached

[MyTaxcel]

Thank you Richard for replying..

There are couple of more conditions as mentioned in the thread.. Interest has to calculated for each Party and within party "Receipt Type" separately.. that means difference in days has be calculated for each Receipt Type within each party.. I hope you have understood what I want to convey...

sorry for the poor drafting in English.


Thanks and Regards

[Richard Buttrey]

Where in the workbook do you hold what you appear to be saying is a requirement for a different interest rate for each Type/party?

If that's not what you are saying please manually add the results you expect to see along and unless it's obvious a note that explains how you have calculated them.

[MyTaxcel]

Thanks for your suggestions,

I have incorporated the expected results in the excel file.. and also the my workings to calculate the interest receipt type wise and party wise.

Note : Receipt Type wise interest has to calculated for each party separately.