User:Benjaminkwok9/sandbox

- price level =$B$11*E2.

=$B$11*K2

- first breached =IFERROR(INDEX(DataTable[date],MIN(IF(DataTable[close]<=G2, IF(DataTable[barrier observation]=TRUE, ROW(DataTable[date])-1, 999999),999999)),1), "N/A")

=IFERROR(INDEX(DataTable[date],MIN(IF(DataTable[close]>=M2, IF(DataTable[barrier observation]=TRUE, ROW(DataTable[date])-1, 999999),999999)),1), "N/A")

- barrier observation =IF(MOD(P2,$B$5) = 0, TRUE, "")

- equity allocation =IF(S2= TRUE, IF([@close]<$B$11,LoopBarrierTable(B4,$H$2,[@date]), LoopBarrierTable(B4,$N$2,[@date])),B4)

=IF(S3= TRUE, IF([@close]<$B$11,LoopBarrierTable(T2,$H$2,[@date]), LoopBarrierTable(T2,$N$2,[@date])),T2)

LoopBarrierTable: =LAMBDA(allocation,first_breach,date, IF(first_breach = "", allocation, IF(first_breach= date, LoopBarrierTable(allocation+OFFSET(first_breach, 0, -2), OFFSET(first_breach, 1, 0), date),LoopBarrierTable(allocation, OFFSET(first_breach, 1, 0), date))))

- cash notional =(1-T2) * $B$2

- coupon accrued =[@[cash notional]]*$B$3/252

- cumulative coupon =SUM($V$2:[@[coupon accrued ]])

- coupon payment =IF(MOD(P2,$B$6) = 0, SUM(OFFSET([@[coupon accrued ]],-$B$6+1,0):[@[coupon accrued ]]), "")