# LOAN-000131 Root Cause Analysis

## Executive Summary

LOAN-000131 was created with **incorrect interest and processing fee calculations**. The loan used wrong rates at the time of creation, resulting in the borrower being charged KES 4,600 less than they should have been.

## The Facts

### Actual Database Values:
```
Principal Amount:    KES 20,000.00
Interest Amount:     KES  2,000.00  ← WRONG
Processing Fee:      KES    400.00  ← WRONG
Total Amount:        KES 22,400.00  ← WRONG
Duration:            90 days (3 months)
Amount Paid:         KES  3,200.00
Outstanding:         KES 19,200.00  ← WRONG
```

### What It Should Be (Mwamba Product - 10% monthly interest, 5% processing fee):
```
Principal Amount:    KES 20,000.00
Interest Amount:     KES  6,000.00  (20,000 × 10% × 3 months)
Processing Fee:      KES  1,000.00  (20,000 × 5%)
Total Amount:        KES 27,000.00
Duration:            90 days (3 months)
Amount Paid:         KES  3,200.00
Outstanding:         KES 23,800.00
```

### Discrepancy:
- **Interest**: Short by KES 4,000 (2,000 vs 6,000)
- **Processing Fee**: Short by KES 600 (400 vs 1,000)
- **Total**: Short by KES 4,600 (22,400 vs 27,000)

## Root Cause Analysis

### What Rates Were Actually Used?

By reverse engineering the stored values:

**Interest Calculation:**
```
Stored Interest: 2,000
Formula: Principal × Rate × Months
2,000 = 20,000 × Rate × 3
Rate = 2,000 / (20,000 × 3) = 0.0333... = 3.33% per month
```

**Processing Fee Calculation:**
```
Stored Processing Fee: 400
Formula: Principal × Rate
400 = 20,000 × Rate
Rate = 400 / 20,000 = 0.02 = 2%
```

### Conclusion:
The loan was created using:
- **Interest Rate: 3.33% per month** (should be 10%)
- **Processing Fee: 2%** (should be 5%)

## Possible Causes

### 1. Wrong Product Settings at Time of Creation
The Mwamba product may have had incorrect rates when this loan was created:
- `interest_rate` field was set to 3.33% instead of 10%
- `processing_fee` field was set to 2% instead of 5%

### 2. System Settings Override
There may have been system settings that overrode the product rates:
- `mwamba_interest_rate` = 3.33
- `mwamba_processing_fee` = 2.0

### 3. Manual Entry Error
Someone may have manually entered the loan with custom rates.

### 4. Code Bug (Now Fixed)
There may have been a bug in the calculation logic that has since been fixed.

## Impact Assessment

### Financial Impact:
- **Borrower owes**: KES 23,800 (actual)
- **System shows**: KES 19,200 (incorrect)
- **Underreported**: KES 4,600

### Business Impact:
1. **Revenue Loss**: KES 4,600 in interest and fees not being collected
2. **Reporting Accuracy**: All financial reports are inaccurate
3. **Compliance Risk**: Loan terms may not match what was agreed
4. **Customer Relations**: Borrower may dispute when corrected

## Recommended Actions

### Immediate (Critical):

1. **Verify Product Settings**
   ```sql
   SELECT product_type, interest_rate, processing_fee 
   FROM loan_products 
   WHERE product_type = 'mwamba';
   ```

2. **Check System Settings**
   ```sql
   SELECT * FROM system_settings 
   WHERE key LIKE 'mwamba%';
   ```

3. **Identify All Affected Loans**
   ```sql
   SELECT 
       l.loan_number,
       l.principal_amount,
       l.duration_days,
       l.interest_amount as stored_interest,
       (l.principal_amount * 0.10 * (l.duration_days / 30)) as correct_interest,
       l.processing_fee as stored_fee,
       (l.principal_amount * 0.05) as correct_fee
   FROM loans l
   INNER JOIN loan_applications la ON l.application_id = la.id
   INNER JOIN loan_products lp ON la.loan_product_id = lp.id
   WHERE lp.product_type = 'mwamba'
   AND (
       ABS(l.interest_amount - (l.principal_amount * 0.10 * (l.duration_days / 30))) > 0.01
       OR ABS(l.processing_fee - (l.principal_amount * 0.05)) > 0.01
   );
   ```

### Short-term (Important):

4. **Fix LOAN-000131**
   ```sql
   UPDATE loans
   SET 
       interest_amount = principal_amount * 0.10 * (duration_days / 30),
       processing_fee = principal_amount * 0.05,
       total_amount = principal_amount + (principal_amount * 0.10 * (duration_days / 30)) + (principal_amount * 0.05)
   WHERE loan_number = 'LOAN-000131';
   ```

5. **Communicate with Borrower**
   - Explain the error
   - Show correct calculation
   - Provide updated payment schedule
   - Offer payment plan if needed

6. **Fix All Affected Loans**
   - Run diagnostic query
   - Review each case
   - Apply corrections
   - Notify borrowers

### Long-term (Preventive):

7. **Add Validation**
   ```python
   def validate_loan_calculations(loan):
       """Validate loan calculations match product rates"""
       product = loan.application.loan_product
       months = loan.duration_days / 30
       
       expected_interest = loan.principal_amount * (product.get_interest_rate() / 100) * months
       expected_processing_fee = loan.principal_amount * (product.get_processing_fee() / 100)
       
       if abs(loan.interest_amount - expected_interest) > 0.01:
           raise ValueError(f"Interest amount mismatch: {loan.interest_amount} vs {expected_interest}")
       
       if abs(loan.processing_fee - expected_processing_fee) > 0.01:
           raise ValueError(f"Processing fee mismatch: {loan.processing_fee} vs {expected_processing_fee}")
   ```

8. **Add Database Constraints**
   ```sql
   -- Add check to ensure interest is reasonable
   ALTER TABLE loans ADD CONSTRAINT chk_interest_reasonable
   CHECK (interest_amount >= principal_amount * 0.01 AND interest_amount <= principal_amount * 2);
   
   -- Add check to ensure processing fee is reasonable
   ALTER TABLE loans ADD CONSTRAINT chk_processing_fee_reasonable
   CHECK (processing_fee >= 0 AND processing_fee <= principal_amount * 0.20);
   ```

9. **Add Audit Trail**
   - Log all rate changes
   - Track who created loans
   - Record rates used at time of creation

10. **Add Admin Warnings**
    - Show warning if calculated amounts don't match product rates
    - Highlight loans with unusual rates
    - Add verification step before loan approval

## Testing Recommendations

### Before Fix:
1. Backup database
2. Test fix on staging environment
3. Verify calculations manually
4. Check impact on reports

### After Fix:
1. Verify loan details page shows correct amounts
2. Check outstanding balance is correct
3. Verify reports are updated
4. Test payment processing
5. Generate new amortization schedule

## Communication Plan

### Internal:
1. Notify finance team of correction
2. Update accounting records
3. Adjust revenue projections
4. Review other Mwamba loans

### External (Borrower):
1. Send notification of correction
2. Explain the error clearly
3. Provide updated loan statement
4. Offer payment plan options
5. Document all communications

## Files Provided

1. `fix_loan_131_correct_rates.sql` - SQL fix script
2. `check_mwamba_product_settings.py` - Diagnostic tool
3. `MWAMBA_LOAN_131_ROOT_CAUSE_ANALYSIS.md` - This document
4. `QUICK_FIX_MWAMBA.txt` - Quick reference

## Approval Required

- [ ] Finance Manager approval
- [ ] Legal review (if needed)
- [ ] Customer service notification
- [ ] Database backup completed
- [ ] Fix tested on staging
- [ ] Communication to borrower prepared

---

**Prepared by**: AI Assistant  
**Date**: January 20, 2026  
**Status**: Awaiting Approval  
**Priority**: High