← Jane Street Interview Insights

Jane Street·Software Engineer·Technical Phone Screen·Intermediate

Intermediate
May 2026

Summary

Jane Street SWE interview that leaned harder into spreadsheet mechanics than I expected. The whole thing was basically one extended Excel problem about collateral valuation, which felt oddly niche but made sense in context.

Questions Asked (1)

Q1

You have an Excel sheet with columns for Asset, Market Value, and Haircut %. Using only cell references (no hardcoded numbers), write formulas to calculate each asset's haircut-adjusted value and the total adjusted collateral value. Walk through your formula choices, how you'd handle absolute vs. relative references, and how you'd make the output update automatically when inputs change.

Technical Trade-offsData Modeling
Author's notes

This was more involved than it looks.

Create a free account to read the full note

AI HintsAI Generated

Suggested Approach

Start by clarifying the data layout and assumptions, then demonstrate the formula for the first asset using relative references for row-wise calculations and absolute references for any fixed parameters. Finally, show how to sum the adjusted values and explain how Excel's automatic recalculation ensures updates when inputs change.

Pro tip: Mention that you would use structured references if the data is in an Excel Table, which makes formulas self-expanding and easier to audit. Also, note that you'd consider using the LET function to avoid repeating the same expression, improving readability and performance.

1. Clarify the Data Layout

Assume columns: A for Asset, B for Market Value, C for Haircut %. Data starts in row 2. Confirm if haircut is expressed as a percentage (e.g., 5% for 5%) or decimal (0.05).

2. Write the Adjusted Value Formula

In cell D2, enter =B2*(1-C2). This uses relative references so it can be copied down. If haircut is a fixed value in a separate cell, use absolute reference like $C$1.

3. Copy the Formula Down

Drag the fill handle or copy-paste the formula from D2 to D3, D4, etc. Relative references adjust automatically to B3*(1-C3), B4*(1-C4), etc.

4. Calculate Total Adjusted Collateral

In a separate cell, use =SUM(D2:Dn) where n is the last row of data. This sums all adjusted values. Alternatively, use =SUMPRODUCT(B2:Bn,1-C2:Cn) to compute directly without helper column.

5. Ensure Automatic Updates

Excel recalculates formulas automatically when inputs change (unless manual calculation is enabled). For dynamic ranges, convert the data to an Excel Table (Ctrl+T) and use structured references like =SUM(Table1[Adjusted Value]).

Key Points to Mention

  • Relative vs. absolute references: relative for row-wise calculations (B2, C2), absolute for fixed parameters ($C$1).
  • Use of fill handle or copy-paste to apply formula to all rows efficiently.
  • SUM function for total adjusted value; SUMPRODUCT as an alternative without helper column.
  • Excel's automatic recalculation feature ensures updates when inputs change.
  • Structured references (Excel Tables) for dynamic ranges and easier formula management.
  • Consideration of haircut as percentage vs. decimal; formula adjusts accordingly (1 - C2).

AI-generated suggestions, not part of the candidate's original notes. May be inaccurate — verify before relying on them.