Calculation for default & delinquent payment

I am looking for a formula that calculates the monthly default & delinquent amount for each customer.

A customer would be a delinquent customer if no payment received from the last two months. If three months elapsed without payment customer becomes the default.

In the attached spreadsheet, column O to column T shows the monthly repayment amount & outstanding balance by month. Column AE to AJ represents the payments received by the month, and column AL to AS shows the default & delinquent payment calculation.

E.g., the customer in line 8 is missing July, Aug & Sept payment; therefore, as per the criteria customer is in delinquent status in September i.e., in cell AN8, the formula should return the outstanding loan balance from the last payment month (in this case June).

Similarly, if the same customer hasn't paid in the 3rd month, it becomes the default in October. The formula should return the value from the outstanding loan balance. Also, once the customer is reported as a default/ delinquent customer, nothing should be populated in the coming months until the customer starts paying back.

Hope this make sense.....

Administrative note

Still using XL2003 as indicated in your profile?
If not,perhaps update your profile as necessary to properly reflect the exact version(s) of Excel your question relates to? Members tailor answers based on your Excel version.
Thanks

If I understand correctly then the following may help.
1. The month names have been changed to dates and then custom was applied.
2. The order of default and delinquent is reversed in cells AL6:AS6
3. The following formula is applied to AL8:AS888 :

Formula:

Please Login or Register  to view this content.

Note that some rows display #N/A because there is no balance given for May and payments were neither made in June nor July.
Let us know if you have any questions.

I believe that the following formula will do what you want:

Formula:

Please Login or Register  to view this content.

Select cell AZ8, paste the formula into the formula bar, drag the fill handle over to cell BN8 and then down to cell BN23
Let us know if you have any questions.