Use Excel formulas to compute haircuts
Company: Jane Street
Role: Software Engineer
Category: Data Manipulation (SQL/Python)
Difficulty: medium
Interview Round: HR Screen
You are given an Excel worksheet with columns such as Asset, Market Value, and Haircut %. Using only cell references (no copy-paste of numeric values), write formulas to compute each asset’s haircut-adjusted value and the total adjusted collateral value. Explain which formulas you would use, how you would apply absolute vs. relative references, and how you would ensure changes in the input table automatically propagate to the results.
Overview: This question evaluates spreadsheet proficiency in constructing dynamic Excel formulas, the correct application of absolute versus relative cell references, and aggregation techniques to compute haircut-adjusted asset values and totals.
Read the full Jane Street Software Engineer interview experience this question came from
You are given a table of collateral assets with their market values and applicable haircut percentages. Write a SQL query that returns, for each asset, its haircut-adjusted value and the total adjusted collateral value across all assets.
The haircut-adjusted value for each asset is defined as:
adjusted_value = market_value * (1 - haircut_pct / 100)
Your result should include one row per asset with columns: asset_id, asset_name, market_value, haircut_pct, adjusted_value, and total_adjusted_collateral (the same total repeated on every row).
Tables
collateral_assets(asset_id INT, asset_name VARCHAR(100), market_value DECIMAL(15,2), haircut_pct DECIMAL(5,2))
Hints
- Compute the per-asset adjusted value with a simple arithmetic expression using the haircut percentage divided by 100.
- Use a window function like SUM(...) OVER () or a subquery to compute the total of all adjusted values.