# 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