HELP - Formula to compute for penalties and adjusted interests (Accounting Collection)

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

Bumping this thread up.

Bumping this thread up.

Anyone, please. :/

Deleted post - wrong thread.

The highlighted cells in YELLOW are the column I need to automatically produce results.

Again, I don't mind adding helper columns if it would make the job easier.

Thanks in advance! This is really big help.

BUMPING THIS THREAD. Still awaiting answers.

Hi sphereshot, I'm looking at your initial comment and trying to tie it to the sample file you uploaded. What is the Date of SOA? How did you currently derive the Penalty and Total amount to update that's currently in the attached file?

Originally Posted by quekbc

Hi sphereshot, I'm looking at your initial comment and trying to tie it to the sample file you uploaded. What is the Date of SOA? How did you currently derive the Penalty and Total amount to update that's currently in the attached file?

My bad, i didn't elaborate. I was actually beginning to think no one would reply anymore.

Date of SOA = date the statement-of-account is generated
I derive my data from separate ledgers. The ledgers are comprised of PMT, IPMT and PPMT functions. We are, after all, monitoring payments for monthly amortizations of housing loans.

Okay, I understand the concept. But how am I suppose to calculate that from the given information here?

Can you also elaborate more on the "Penalty" and the "Total Amount To Update"?

I have attached a sample (dummy ledger) for your reference. All computations and/or functions you need to know will be found in this ledger.

Thank you for staying with me on this topic.

Hope we can resolve this as soon as possible.

Thanks again!

Bumping this thread.

Still looking for assistance. Thanks!

Bumping this up.

You were referring to the wrong file.

The first file I uploaded is the one I am referring to in my first post.

The ledger you referred to is on for reference on how we compute for the penalty and the interest. Just to show what function and equation I used.