126 lines
4.1 KiB
Markdown
126 lines
4.1 KiB
Markdown
# 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:
|
|
|
|
```bash
|
|
pip install -r requirements.txt
|
|
```
|
|
|
|
Or install directly:
|
|
|
|
```bash
|
|
pip install xlsxwriter
|
|
```
|
|
|
|
## Usage
|
|
|
|
Run the generator script:
|
|
|
|
```bash
|
|
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
|
|
|