How to calculate the payment due past due date

Hi

This is my first post and I really need some help. I need to find a way to calculate the monthly charges due once it has passed the due date. These amounts are monthly charges due for service charge per resident in a housing estate. I have put in a column with conditional formatting that shows how many days the unit is past due. Since clients have the option to pay for more than a month, I have also put in columns that monitor the numbers covered under their last payment. Since the monthly charges are 17000 and we can not compute the charges based on the days past due, how do I show the exact amount due to the J column.

I have attached a copy of the spreadsheet.

Any suggestions on what formulas I can use?

Try

=IF(H4="","",DATEDIF(H4,TODAY(),"M")*17000)

Bob

You are a genius. Thanks for the assist.

Bob
I meant to ask you earlier but if it's not too much trouble could please explain how you came up with this? I would really like to understand what the formula means.

It is quite simple, DATEDIF is a function that calculates the number of periods between two dates. In this case the "M" means calculate the number of months. This number is multiplied by 17000. I test H4 for blank, just to be sure we don't get any errors.

=IF(H4="","",DATEDIF(H4,TODAY(),"M")*17000)

Datedif(Date1,Date2,Date type) Datedif finds the difference between 2 dates and the third arguement defines what it is "M" means months. So if H4 is not blank it finds the difference between your dates in terms of months then multiplies by 17,000.

sir,
could you please tell me solution for below given data
person X was taken a loan from y. X was agreed to pay in 55 installments(principle+interest)and also agreed to pay the late penalty of 36%. Below i giving data for calculation
S.NO Due Date Intallment Paid Dt. Amount
1 20-Mar-08 7243
2 20-Apr-08 7243 16-Apr-08 14468
3 20-May-08 7243
4 20-Jun-08 7243
5 20-Jul-08 7243
6 20-Aug-08 7243
7 20-Sep-08 7243 26-Sep-08 8250
8 20-Oct-08 7243
9 20-Nov-08 7243
10 20-Dec-08 7243
11 20-Jan-09 7243
12 20-Feb-09 7243
13 20-Mar-09 7243 18-Mar-09 16000
14 20-Apr-09 7243
15 20-May-09 4207 30-May-09 5036
16 20-Jun-09 4207
17 20-Jul-09 4207
18 20-Aug-09 4207
19 20-Sep-09 4207
20 20-Oct-09 4207 12-Oct-09 30000
21 20-Nov-09 4207
22 20-Dec-09 4207 26-Dec-09 8000
23 20-Jan-10 4207
24 20-Feb-10 4207
25 20-Mar-10 4207 9-Mar-10 15000
26 20-Apr-10 4207 26-Apr-10 10000
27 20-May-10 4207 25-May-10 10000
28 20-Jun-10 4207 29-Jun-10 7500
29 20-Jul-10 4207 22-Jul-10 7000
30 20-Aug-10 4207
31 20-Sep-10 4207
32 20-Oct-10 4207
33 20-Nov-10 4207
34 20-Dec-10 4207
35 20-Jan-11 4207
36 20-Feb-11 4207 28-Feb-11 38801
37 20-Mar-11 4207
38 20-Apr-11 4207
39 20-May-11 4207
40 20-Jun-11 4207
41 20-Jul-11 4207
42 20-Aug-11 4207 22-Aug-11 5000
43 20-Sep-11 4207
44 20-Oct-11 4207
45 20-Nov-11 4207
46 20-Dec-11 4207
47 20-Jan-12 4207
48 20-Feb-12 4207
49 20-Mar-12 4207
50 20-Apr-12 4207
51 20-May-12 4207
52 20-Jun-12 4207
53 20-Jul-12 4207
54 20-Aug-12 4207
55 20-Sep-12 2019