Excel Formula for Calculating Overdue Charges

shibinrajmarar

New Member
Joined
Jun 12, 2019
Messages
1
Dear All,

Please find the Loan EMI and Collection table below:

As per EMI ScheduleAs per Collection Report
Account NumberMonth NumberSum of EMICumulative EMIDue Date
Sum of PrincipalSum of Interest Sum of EMICumulative EMI PayDate of Pay
Sum of PrincipalSum of Interest AmountDelay in DaysOverdue Charges
393020000004May-182303230310-May-18128910142330233018-May-1812891041
393020000004Jun-182303460611-Jun-1813149892330
393020000004Jul-182303690910-Jul-1813409632330
393020000004Aug-182303921210-Aug-1813669372330
393020000004Sep-1823031151511-Sep-1813939104600693024-Sep-1826541946
393020000004Oct-1823031381810-Oct-1814208832303923324-Oct-181366937
393020000004Nov-1823031612110-Nov-18144885550001423326-Nov-1828132187
393020000004Dec-1823031842410-Dec-18147682714233
393020000004Jan-1923032072710-Jan-19150579814233
393020000004Feb-1923032303011-Feb-19153476987972303019-Feb-1959632834
393020000004Mar-1923032533311-Mar-19156473923032533331-Mar-191564739
Grand Total2533315649968425333156499684

<colgroup><col><col><col><col span="2"><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

EMI Schedule with Due Dates and EMI Amount (Marked under Red heading) and Actual Payment with Date of Pay (Marked under Green heading)

I need a formula against each EMI line item to calculate (under purple heading)
1. Delay in days for Payment of each EMI
2. Overdue Charges @ INR 100 for the first day delay and INR 10 each for subsequent days of delay (for every EMI Pay)

Appeciate if anyone can help me to tackle this in excel.
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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