27-combi/EXCEL_GENERATOR_README.md

DeFi Collateral Simulation Excel Generator

This Python script generates an Excel workbook (DeFi_Collateral_Simulation.xlsx) for simulating DeFi collateral positions with multi-round debt repayment and collateral rebalancing.

Installation

1. Install Python 3.7 or higher
2. Install required dependencies:

pip install -r requirements.txt

Or install directly:

pip install xlsxwriter

Usage

Run the generator script:

python generate_defi_simulation.py

This will create DeFi_Collateral_Simulation.xlsx in the current directory.

Workbook Structure

Assets Sheet

• Asset: Asset name (ETH, wBTC, stETH, USDC)
• Amount: User input for asset quantity
• Price (USD): User input for asset price
• Value (USD): Calculated as Amount * Price
• Collateral ON/OFF: Dropdown (✅/❌) to enable/disable as collateral
• LTV: Loan-to-Value ratio (defaults: ETH 0.80, wBTC 0.70, stETH 0.75, USDC 0.90)
• Liquidation Threshold: Display only
• Collateral Value: IF(CollateralOn="✅", Value, 0)
• Max Borrowable: IF(CollateralOn="✅", Value * LTV, 0)

Summary Sheet

• Total Collateral Value: Sum of all collateral values
• Total Max Borrowable: Sum of all max borrowable amounts
• Borrowed (input): User-entered borrowed amount
• Portfolio LTV: Borrowed / TotalCollateral
• Health Factor (HF): TotalMaxBorrow / Borrowed
• Status: ✅ Safe if HF ≥ 2, ⚠ Risky otherwise

Simulation Sheet

Multi-round simulation (Rounds 0-10) with:

• Round: Round number (0 = initial state)
• Borrowed: MAX(Borrowed_{t-1} - Repay_t, 0)
• Repay Amount: User input per round
• Swap Volatile → Stable: User input (USD value to swap from volatile to stable)
• New Collateral Value: Recomputed from asset mix after swaps
• Max Borrow: Recomputed from asset mix after swaps (not static ratio)
• HF: MaxBorrow / Borrowed
• LTV: Borrowed / NewCollateralValue
• Status: ✅ if HF ≥ 2, ⚠ otherwise
• Suggested Repay: Heuristic optimizer suggestion
• Suggested Swap: Heuristic optimizer suggestion

Optimization Controls

• Max Repay per Round: Cap for repay suggestions
• Max Swap per Round: Cap for swap suggestions
• Optimization On/Off: Enable/disable optimizer (✅/❌)

Swap Mechanics

• Swaps reduce volatile collateral values pro-rata (based on each asset's collateral value)
• Swaps increase USDC collateral value by the same amount
• All calculations respect the ✅/❌ toggles from the Assets sheet
• Max Borrow is recomputed from the adjusted asset mix each round

Redeploy (optional) Sheet

Advanced asset-level redeploy grid for fine-grained control over swaps per asset per round.

Help Sheet

Test cases and documentation.

Key Features

1. Per-Round Recomputation: Max Borrow is recalculated from the current asset mix after each swap, not approximated by static ratios.

2. Swap Mechanics:

   • Pro-rata reduction across volatile assets
   • Direct increase to USDC
   • Maintains internal consistency (amounts and prices)

3. Heuristic Optimizer:

   • Suggests repay/swap amounts to bring HF to 2.0
   • Respects user-defined caps
   • Only suggests when HF < 2.0

4. Conditional Formatting:

   • HF column: Green if ≥ 2, Red otherwise

5. Named Ranges:

   • All key ranges are named for extensibility
   • Easy to reference in formulas

Test Cases

See the Help sheet for detailed test cases including:

• Baseline scenario
• Swap only
• Repay only
• Combined actions
• Optimizer validation

Extensibility

To add more assets:

1. Extend the DEFAULT_ASSETS list in generate_defi_simulation.py
2. Re-run the generator
3. All formulas will automatically adjust to include the new assets

Notes

• The helper block (hidden columns L onwards) performs per-asset, per-round calculations
• Formulas are Excel-native and recalculate automatically
• The workbook is idempotent: re-running overwrites the same file deterministically