So I’ve built a trade simulator that uses a paralell spread change assumption, and changes in the yield curve. The goal is to find breakeven points over a 6 month time horizon for a butterfly trade. I’ve got 4 inputs (2s, 5s, 10s, 30s) and want to sensitive the relative outperformance of my strategy compared to doing nothing. A 4 way data table would be perfect for this, but the only way I’ve built it (using all the permutations of the 4 variables to create unique scenarios, and writing a lookup to the scenarios and senstizing that). Is there a way to do this without going overboard on the scenario count?
Excel is unfortunately limited in this aspect. One way I’ve somewhat worked around this is by building in multiple scenarios to the rows/columns.
Here’s an example… say a couple inputs are time horizon and interest rates. You can put in a single cell something like 1 / 1%, 1 / 2%, 1 / 3%, 2 / 1%, 2 / 2%, etc…
You would then just have to use some left or right functions to specify the portion of the cell to use as the variable, but to be honest, I’ve generally just built multiple tables since it’s easier/quicker.