Good day!

I would like to seek guidance and assistance regarding an excel monitoring system I have been trying to improve.

Initially, it is a general monitoring file which enables my team to detect arrears incurred from monthly amortizations of clients. However, recently, it has been very challenging to manually compute for adjusted interests and penalties everytime a client fails to comply with his / her amortizations.

I would like to include the columns "TOTAL AMOUNT TO UPDATE" and "PENALTY CHARGES" on my monitoring.

Say, having column A = Client Name ; B = Monthly Amortization Amount ; C = Start Date of MA ; D = Months Due ; E = Months Paid ; F = # of Monthly Arrears ; G = Date of latest payment ; H = Total Amount Paid (overall) ; I = Total Amount to Update Principal and Interest ; J = Total Penalties Charged ;

columnA is hard-coded;
columnB is hard-coded;
columnC is hard-coded;
columnD comes from DATEDIF() of current date vs columnC;
columnE comes from TRUNC(columnH/columnB);
columnF comes from columnD - columnE;
columnI ??? (adjusted interests due to late payments should be based on a 10% amortization interest)
columnJ ??? (penalties should be computed 30 days from latest payment with a rate of 5% of MA amount per month)

I have tried several combinations but always ends up staring on a blank wall. I am open to add helper columns to facilitate with the computation.

Thanks in advance for your inputs.

'Bill