2c:null 2d:["$","title",null,{"children":"Fleming Deal Model Notebook | Ledger | Observable"}] 32:Tc9f,html`

Loss Portfolio Transfer (LPT) - Participating Note

${terms["Note Term (years)"]} Year Note

Gross Reserves

$${(
        terms["Gross Reserves ($)"] / 1000000
      ).toFixed(0)}M

Coverage Layer

${(
        terms["Attachment"] * 100
      ).toFixed(0)}% - ${(terms["Exhaustion"] * 100).toFixed(0)}%

Placement

${( terms["Percent Placed"] * 100 ).toFixed(0)}%

Profit Sharing

${( terms["Profit Sharing %"] * 100 ).toFixed(0)}% @ ${(terms["Profit Attach"] * 100).toFixed(0)}-${( terms["Profit Exhaust"] * 100 ).toFixed(0)}%

Premium Rate

${(terms["Premium %"] * 100).toFixed( 0 )}%

Annual Expenses

${( terms["Annual Expense %"] * 100 ).toFixed(2)}%

`33:T3c9b,viewof scenarioSelection = { if (!scenariosData) { return html`

⚠️ Loading scenarios...

`; } const container = html`

Scenario Selection

${scenariosData.length.toLocaleString()} scenarios sorted by loss development

Loss Development Percentile

${["P1", "P5", "P10", "P25", "P50", "P75", "P90", "P95", "P99"] .map( (p) => ` ` ) .join("")}

Select Scenario

Scenario ID

Rank

5000

Loss Dev

1.0000

Lowest Highest

`; // Calculate percentiles const total = scenariosData.length; const percentiles = { P1: Math.floor(total * 0.01), P5: Math.floor(total * 0.05), P10: Math.floor(total * 0.1), P25: Math.floor(total * 0.25), P50: Math.floor(total * 0.5), P75: Math.floor(total * 0.75), P90: Math.floor(total * 0.9), P95: Math.floor(total * 0.95), P99: Math.floor(total * 0.99) }; let currentMode = "percentile"; let currentPercentile = "P50"; let currentIndex = percentiles.P50; // Get elements const percentileContent = container.querySelector(".percentile-content"); const manualContent = container.querySelector(".manual-content"); const modeButtons = container.querySelectorAll(".mode-btn"); const slider = container.querySelector(".scenario-slider"); const displayId = container.querySelector(".display-scenario-id"); const displayRank = container.querySelector(".display-rank"); const displayLossDev = container.querySelector(".display-loss-dev"); const percentileButtons = container.querySelectorAll(".percentile-btn"); const canvas = container.querySelector(".mini-chart"); const ctx = canvas.getContext("2d"); // Manual mode inputs const manualIdInput = container.querySelector(".manual-scenario-id"); const manualRankInput = container.querySelector(".manual-rank"); const manualLossDevInput = container.querySelector(".manual-loss-dev"); const manualDisplayId = container.querySelector(".manual-display-id"); const manualDisplayRank = container.querySelector(".manual-display-rank"); const manualDisplayLossDev = container.querySelector( ".manual-display-loss-dev" ); // Add hover styles for inputs const inputs = [manualIdInput, manualRankInput, manualLossDevInput]; inputs.forEach((input) => { input.addEventListener("focus", (e) => { e.target.style.borderColor = "#8b5cf6"; e.target.style.boxShadow = "0 0 0 3px rgba(139, 92, 246, 0.1)"; }); input.addEventListener("blur", (e) => { e.target.style.borderColor = "#e5e7eb"; e.target.style.boxShadow = "none"; }); }); // Draw mini distribution chart function drawMiniChart() { const width = 900; const height = 80; ctx.clearRect(0, 0, width, height); // Create histogram bins const bins = 50; const values = scenariosData.map((s) => s.finalLossDev); const min = Math.min(...values); const max = Math.max(...values); const binWidth = (max - min) / bins; const histogram = new Array(bins).fill(0); values.forEach((v) => { const binIdx = Math.min(Math.floor((v - min) / binWidth), bins - 1); histogram[binIdx]++; }); const maxCount = Math.max(...histogram); const barWidth = width / bins; // Draw bars ctx.fillStyle = "#e9d5ff"; histogram.forEach((count, i) => { const barHeight = (count / maxCount) * (height - 10); ctx.fillRect(i * barWidth, height - barHeight, barWidth - 1, barHeight); }); // Draw selected position const selectedPos = (currentIndex / total) * width; ctx.strokeStyle = "#ef4444"; ctx.lineWidth = 2; ctx.beginPath(); ctx.moveTo(selectedPos, 0); ctx.lineTo(selectedPos, height); ctx.stroke(); } function updateDisplay() { const scenario = scenariosData[currentIndex]; if (currentMode === "percentile") { displayId.textContent = scenario.id; displayRank.textContent = `${currentIndex + 1} / ${total}`; displayLossDev.textContent = scenario.finalLossDev.toFixed(4); slider.value = currentIndex; } else { manualDisplayId.textContent = scenario.id; manualDisplayRank.textContent = `${currentIndex + 1} / ${total}`; manualDisplayLossDev.textContent = scenario.finalLossDev.toFixed(4); } drawMiniChart(); container.value = currentIndex; container.dispatchEvent(new Event("input", { bubbles: true })); } function setActiveButton(percentile) { percentileButtons.forEach((btn) => { if (btn.dataset.percentile === percentile) { btn.style.background = "linear-gradient(135deg, #8b5cf6 0%, #7c3aed 100%)"; btn.style.color = "white"; btn.style.borderColor = "#8b5cf6"; } else { btn.style.background = "white"; btn.style.color = "#6b7280"; btn.style.borderColor = "#e5e7eb"; } }); } function setActiveMode(mode) { modeButtons.forEach((btn) => { if (btn.dataset.mode === mode) { btn.style.background = "#8b5cf6"; btn.style.color = "white"; } else { btn.style.background = "transparent"; btn.style.color = "#6b7280"; } }); if (mode === "percentile") { percentileContent.style.display = "grid"; manualContent.style.display = "none"; } else { percentileContent.style.display = "none"; manualContent.style.display = "block"; } } // Mode toggle handlers modeButtons.forEach((btn) => { btn.addEventListener("click", () => { currentMode = btn.dataset.mode; setActiveMode(currentMode); updateDisplay(); }); }); // Percentile button handlers percentileButtons.forEach((btn) => { btn.addEventListener("click", () => { currentPercentile = btn.dataset.percentile; currentIndex = percentiles[currentPercentile]; setActiveButton(currentPercentile); updateDisplay(); }); }); // Slider handler slider.addEventListener("input", (e) => { currentIndex = parseInt(e.target.value); updateDisplay(); }); // Manual input handlers manualIdInput.addEventListener("input", (e) => { const id = parseInt(e.target.value); const foundIndex = scenariosData.findIndex((s) => s.id === id); if (foundIndex !== -1) { currentIndex = foundIndex; updateDisplay(); manualRankInput.value = currentIndex + 1; manualLossDevInput.value = scenariosData[currentIndex].finalLossDev.toFixed(4); } }); manualRankInput.addEventListener("input", (e) => { const rank = parseInt(e.target.value); if (rank >= 1 && rank <= total) { currentIndex = rank - 1; updateDisplay(); const scenario = scenariosData[currentIndex]; manualIdInput.value = scenario.id; manualLossDevInput.value = scenario.finalLossDev.toFixed(4); } }); manualLossDevInput.addEventListener("input", (e) => { const targetLossDev = parseFloat(e.target.value); if (!isNaN(targetLossDev)) { // Find closest scenario by loss dev let closestIndex = 0; let minDiff = Math.abs(scenariosData[0].finalLossDev - targetLossDev); for (let i = 1; i < scenariosData.length; i++) { const diff = Math.abs(scenariosData[i].finalLossDev - targetLossDev); if (diff < minDiff) { minDiff = diff; closestIndex = i; } } currentIndex = closestIndex; updateDisplay(); const scenario = scenariosData[currentIndex]; manualIdInput.value = scenario.id; manualRankInput.value = currentIndex + 1; } }); // Initialize setActiveMode("percentile"); setActiveButton("P50"); updateDisplay(); container.value = currentIndex; return container; }34:T2305,// Development vs Payment Pattern Chart { // Create container const container = html`

Development vs Payment Pattern

Scenario ${ scenariosData[selectedScenarioIndex].id } • Loss Dev: ${scenariosData[ selectedScenarioIndex ].finalLossDev.toFixed(4)}

`; const canvas = container.querySelector(".development-chart"); const ctx = canvas.getContext("2d"); // Set canvas resolution const dpr = window.devicePixelRatio || 1; const rect = canvas.getBoundingClientRect(); canvas.width = rect.width * dpr; canvas.height = rect.height * dpr; ctx.scale(dpr, dpr); const width = rect.width; const height = rect.height; const padding = { top: 50, right: 80, bottom: 70, left: 80 }; const chartWidth = width - padding.left - padding.right; const chartHeight = height - padding.top - padding.bottom; // Get the selected scenario const selectedScenario = scenariosData[selectedScenarioIndex]; const scenarioId = selectedScenario.id; // Try different ways to access the data let ultimateLoss, claimPayments, ultimateDevelopment; // Option 1: modelResults.scenarios is an object with scenario IDs as keys if (modelResults.scenarios && modelResults.scenarios[scenarioId]) { const scenarioData = modelResults.scenarios[scenarioId]; ultimateLoss = scenarioData.ultimateLoss; claimPayments = scenarioData.claimsPaid.map((x) => Math.abs(x)); ultimateDevelopment = scenarioData.lossDevelopment.map((dev) => dev * 100); } // Option 2: modelResults has arrays indexed by scenarioId else if (modelResults.ultimateLoss && modelResults.ultimateLoss[scenarioId]) { ultimateLoss = modelResults.ultimateLoss[scenarioId]; claimPayments = modelResults.claimsPaid[scenarioId].map((x) => Math.abs(x)); ultimateDevelopment = modelResults.lossDevelopment[scenarioId].map( (dev) => dev * 100 ); } // Option 3: modelResults has arrays indexed by selectedScenarioIndex else if ( modelResults.ultimateLoss && modelResults.ultimateLoss[selectedScenarioIndex] ) { ultimateLoss = modelResults.ultimateLoss[selectedScenarioIndex]; claimPayments = modelResults.claimsPaid[selectedScenarioIndex].map((x) => Math.abs(x) ); ultimateDevelopment = modelResults.lossDevelopment[ selectedScenarioIndex ].map((dev) => dev * 100); } else { return html`

⚠️ Error: Cannot find scenario data. Please check data structure.
Looking for scenarioId: ${scenarioId}, index: ${selectedScenarioIndex}

`; } const quarters = claimPayments.map((_, i) => i + 1); // Calculate cumulative payments as percentage const cumulativePayments = []; let cumSum = 0; claimPayments.forEach((payment) => { cumSum += payment; cumulativePayments.push((cumSum / ultimateLoss) * 100); }); // Draw background ctx.fillStyle = "#ffffff"; ctx.fillRect(0, 0, width, height); // Draw grid lines ctx.strokeStyle = "#f3f4f6"; ctx.lineWidth = 1; const ySteps = 10; for (let i = 0; i <= ySteps; i++) { const y = padding.top + (chartHeight / ySteps) * i; ctx.beginPath(); ctx.moveTo(padding.left, y); ctx.lineTo(padding.left + chartWidth, y); ctx.stroke(); // Draw percentage labels ctx.fillStyle = "#9ca3af"; ctx.font = "600 11px system-ui"; ctx.textAlign = "right"; const percentage = 100 - (100 / ySteps) * i; ctx.fillText(`${percentage.toFixed(0)}%`, padding.left - 15, y + 4); } // Draw vertical grid lines quarters.forEach((q, i) => { const x = padding.left + (chartWidth / (quarters.length - 1)) * i; ctx.strokeStyle = "#f3f4f6"; ctx.lineWidth = 1; ctx.beginPath(); ctx.moveTo(x, padding.top); ctx.lineTo(x, padding.top + chartHeight); ctx.stroke(); }); // Draw axes ctx.strokeStyle = "#d1d5db"; ctx.lineWidth = 2; ctx.beginPath(); ctx.moveTo(padding.left, padding.top); ctx.lineTo(padding.left, padding.top + chartHeight); ctx.lineTo(padding.left + chartWidth, padding.top + chartHeight); ctx.stroke(); // Function to convert percentage to y coordinate const percentToY = (percent) => { return padding.top + chartHeight - (percent / 100) * chartHeight; }; // Draw ultimate development line (blue/teal) ctx.strokeStyle = "#06b6d4"; ctx.lineWidth = 3; ctx.beginPath(); ultimateDevelopment.forEach((percent, i) => { const x = padding.left + (chartWidth / (quarters.length - 1)) * i; const y = percentToY(percent); if (i === 0) { ctx.moveTo(x, y); } else { ctx.lineTo(x, y); } }); ctx.stroke(); // Draw points on ultimate development line ultimateDevelopment.forEach((percent, i) => { const x = padding.left + (chartWidth / (quarters.length - 1)) * i; const y = percentToY(percent); ctx.fillStyle = "#06b6d4"; ctx.beginPath(); ctx.arc(x, y, 5, 0, Math.PI * 2); ctx.fill(); ctx.strokeStyle = "#ffffff"; ctx.lineWidth = 2; ctx.stroke(); }); // Draw cumulative payment line (purple) ctx.strokeStyle = "#8b5cf6"; ctx.lineWidth = 3; ctx.beginPath(); cumulativePayments.forEach((percent, i) => { const x = padding.left + (chartWidth / (quarters.length - 1)) * i; const y = percentToY(percent); if (i === 0) { ctx.moveTo(x, y); } else { ctx.lineTo(x, y); } }); ctx.stroke(); // Draw points on cumulative payment line cumulativePayments.forEach((percent, i) => { const x = padding.left + (chartWidth / (quarters.length - 1)) * i; const y = percentToY(percent); ctx.fillStyle = "#8b5cf6"; ctx.beginPath(); ctx.arc(x, y, 5, 0, Math.PI * 2); ctx.fill(); ctx.strokeStyle = "#ffffff"; ctx.lineWidth = 2; ctx.stroke(); }); // Draw x-axis labels (quarters) ctx.fillStyle = "#6b7280"; ctx.font = "600 12px system-ui"; ctx.textAlign = "center"; quarters.forEach((quarter, i) => { const x = padding.left + (chartWidth / (quarters.length - 1)) * i; ctx.fillText(`Q${quarter}`, x, padding.top + chartHeight + 25); }); // Draw y-axis title ctx.save(); ctx.translate(padding.left - 60, height / 2); ctx.rotate(-Math.PI / 2); ctx.textAlign = "center"; ctx.fillStyle = "#1f2937"; ctx.font = "700 13px system-ui"; ctx.fillText("Percentage (%)", 0, 0); ctx.restore(); // Draw x-axis title ctx.fillStyle = "#1f2937"; ctx.font = "700 13px system-ui"; ctx.textAlign = "center"; ctx.fillText("Quarter", width / 2, height - 20); // Draw legend const legendY = padding.top - 20; const legendX = width / 2 - 120; // Ultimate Development line ctx.strokeStyle = "#06b6d4"; ctx.lineWidth = 3; ctx.beginPath(); ctx.moveTo(legendX, legendY); ctx.lineTo(legendX + 30, legendY); ctx.stroke(); ctx.fillStyle = "#06b6d4"; ctx.beginPath(); ctx.arc(legendX + 15, legendY, 5, 0, Math.PI * 2); ctx.fill(); ctx.fillStyle = "#1f2937"; ctx.font = "600 12px system-ui"; ctx.textAlign = "left"; ctx.fillText("Ultimate Development", legendX + 35, legendY + 4); // Cumulative Payment line const legend2X = legendX + 200; ctx.strokeStyle = "#8b5cf6"; ctx.lineWidth = 3; ctx.beginPath(); ctx.moveTo(legend2X, legendY); ctx.lineTo(legend2X + 30, legendY); ctx.stroke(); ctx.fillStyle = "#8b5cf6"; ctx.beginPath(); ctx.arc(legend2X + 15, legendY, 5, 0, Math.PI * 2); ctx.fill(); ctx.fillStyle = "#1f2937"; ctx.fillText("Cumulative Claims Paid", legend2X + 35, legendY + 4); // Add summary statistics const finalDevelopment = ultimateDevelopment[ultimateDevelopment.length - 1]; const finalPayment = cumulativePayments[cumulativePayments.length - 1]; const gap = finalDevelopment - finalPayment; ctx.font = "600 11px system-ui"; ctx.fillStyle = "#6b7280"; ctx.textAlign = "center"; ctx.fillText( `Final Development: ${finalDevelopment.toFixed( 1 )}% • Final Paid: ${finalPayment.toFixed(1)}% • Gap: ${gap.toFixed( 1 )}% • Ultimate Loss: $${(ultimateLoss / 1000000).toFixed(2)}M`, width / 2, height - padding.bottom + 55 ); return container; }35:T1eb1,viewof investmentReturnController = { const container = html`

Investment Return

Annual Return Rate

0% 5.5% 15%

✓ Investment Return Loaded: Fixed at 5.5% across all 21 quarters

`; let mode = "fixed"; let fixedRate = 0.055; let customRates = Array(21).fill(0.055); const fixedPanel = container.querySelector(".fixed-panel"); const customPanel = container.querySelector(".custom-panel"); const slider = container.querySelector(".rate-slider"); const rateDisplay = container.querySelector(".rate-display"); const statusText = container.querySelector(".status-text"); const modeButtons = container.querySelectorAll(".mode-btn"); const canvas = container.querySelector("canvas"); const ctx = canvas.getContext("2d"); const width = 900; const height = 280; const padding = 40; const chartWidth = width - 2 * padding; const chartHeight = height - 2 * padding; function updateStatus() { if (mode === "fixed") { statusText.innerHTML = `Fixed at ${(fixedRate * 100).toFixed( 1 )}% across all 21 quarters`; } else { const min = Math.min(...customRates); const max = Math.max(...customRates); statusText.innerHTML = `Custom curve from ${(min * 100).toFixed( 1 )}% to ${(max * 100).toFixed(1)}%`; } } function drawCanvas() { ctx.clearRect(0, 0, width, height); ctx.strokeStyle = "#f3f4f6"; ctx.lineWidth = 1; for (let i = 0; i <= 10; i++) { const y = padding + (i * chartHeight) / 10; ctx.beginPath(); ctx.moveTo(padding, y); ctx.lineTo(width - padding, y); ctx.stroke(); } for (let i = 0; i <= 20; i++) { const x = padding + (i * chartWidth) / 20; ctx.beginPath(); ctx.moveTo(x, padding); ctx.lineTo(x, height - padding); ctx.stroke(); } ctx.strokeStyle = "#9ca3af"; ctx.lineWidth = 2; ctx.beginPath(); ctx.moveTo(padding, padding); ctx.lineTo(padding, height - padding); ctx.lineTo(width - padding, height - padding); ctx.stroke(); ctx.fillStyle = "#6b7280"; ctx.font = "11px sans-serif"; ctx.textAlign = "right"; for (let i = 0; i <= 10; i++) { const y = padding + (i * chartHeight) / 10; const rate = (1 - i / 10) * 0.15; ctx.fillText(`${(rate * 100).toFixed(0)}%`, padding - 10, y + 4); } ctx.strokeStyle = "#8b5cf6"; ctx.lineWidth = 3; ctx.beginPath(); customRates.forEach((rate, i) => { const x = padding + (i * chartWidth) / 20; const y = padding + (1 - rate / 0.15) * chartHeight; if (i === 0) ctx.moveTo(x, y); else ctx.lineTo(x, y); }); ctx.stroke(); ctx.fillStyle = "#8b5cf6"; customRates.forEach((rate, i) => { const x = padding + (i * chartWidth) / 20; const y = padding + (1 - rate / 0.15) * chartHeight; ctx.beginPath(); ctx.arc(x, y, 5, 0, Math.PI * 2); ctx.fill(); }); updateStatus(); } function updateRate(e) { const rect = canvas.getBoundingClientRect(); const x = e.clientX - rect.left; const y = e.clientY - rect.top; const scaleX = width / rect.width; const scaleY = height / rect.height; const canvasX = x * scaleX; const canvasY = y * scaleY; if ( canvasX < padding || canvasX > width - padding || canvasY < padding || canvasY > height - padding ) return; const quarterIndex = Math.round(((canvasX - padding) / chartWidth) * 20); if (quarterIndex < 0 || quarterIndex > 20) return; const rate = (1 - (canvasY - padding) / chartHeight) * 0.15; customRates[quarterIndex] = Math.max(0, Math.min(0.15, rate)); drawCanvas(); emitChange(); } function emitChange() { const rates = mode === "fixed" ? Array(21).fill(fixedRate) : [...customRates]; container.value = rates; container.dispatchEvent(new Event("input", { bubbles: true })); } modeButtons.forEach((btn) => { btn.addEventListener("click", () => { mode = btn.dataset.mode; modeButtons.forEach((b) => { if (b.dataset.mode === mode) { b.style.background = "#8b5cf6"; b.style.color = "white"; } else { b.style.background = "transparent"; b.style.color = "#6b7280"; } }); if (mode === "fixed") { fixedPanel.style.display = "block"; customPanel.style.display = "none"; } else { fixedPanel.style.display = "none"; customPanel.style.display = "block"; drawCanvas(); } updateStatus(); emitChange(); }); }); slider.addEventListener("input", (e) => { fixedRate = parseFloat(e.target.value); rateDisplay.textContent = `${(fixedRate * 100).toFixed(1)}%`; updateStatus(); emitChange(); }); let isDrawing = false; canvas.addEventListener("mousedown", (e) => { isDrawing = true; updateRate(e); }); canvas.addEventListener("mousemove", (e) => { if (isDrawing) updateRate(e); }); canvas.addEventListener("mouseup", () => (isDrawing = false)); canvas.addEventListener("mouseleave", () => (isDrawing = false)); updateStatus(); container.value = Array(21).fill(0.055); return container; } 36:Tf98,// Model Results Display { const container = html`

$${( modelResults.noteAmount / 1000000 ) .toFixed(1) .replace(/\.0$/, "")}M

Investor capital needed

${( modelResults.returns.IRR * 100 ).toFixed(2)}%

Annualized return rate

${modelResults.returns.MOIC.toFixed( 2 )}x

Multiple on invested capital

`; return container; }37:T96f,{ const data = modelResults.reserves.endingBalance.map((balance, i) => ({ quarter: i, reserves: balance / 1000000, claims: Math.abs(modelResults.reserves.claimPayments[i]) / 1000000 })); return Plot.plot({ width: 1000, height: 350, marginLeft: 70, marginBottom: 60, marginTop: 40, style: { background: "white" }, title: "Gross Loss Reserve Runoff", subtitle: "", x: { label: "Quarter", grid: true, tickFormat: (d) => `Q${d}` }, y: { label: "Amount ($M)", grid: true }, color: { domain: ["Reserves", "Claims Paid"], range: ["#f59e0b", "#dc2626"], legend: true }, marks: [ // Reserves area Plot.areaY(data, { x: "quarter", y: "reserves", fill: "#f59e0b", fillOpacity: 0.25, curve: "catmull-rom" }), Plot.line(data, { x: "quarter", y: "reserves", stroke: "#f59e0b", strokeWidth: 3.5, curve: "catmull-rom", title: (d) => `Q${d.quarter} Reserves: ${d.reserves.toFixed(1)}M` }), Plot.dot(data, { x: "quarter", y: "reserves", fill: "#f59e0b", r: 4 }), // Claims bars Plot.barY(data, { x: "quarter", y: "claims", fill: "#dc2626", fillOpacity: 0.6, title: (d) => `Q${d.quarter} Claims: ${d.claims.toFixed(1)}M paid` }), // Initial reserves label Plot.text([data[0]], { x: "quarter", y: "reserves", text: (d) => `Initial: ${d.reserves.toFixed(0)}M`, dy: -15, dx: 30, fontSize: 11, fontWeight: "bold", fill: "#f59e0b" }), // Final reserves label Plot.text([data[data.length - 2]], { x: "quarter", y: "reserves", text: (d) => `Final: ${d.reserves.toFixed(0)}M`, dy: -15, dx: -30, fontSize: 11, fontWeight: "bold", fill: "#f59e0b" }), // Runoff rate annotation Plot.text([{ quarter: 10, reserves: data[0].reserves * 0.7 }], { x: "quarter", y: "reserves", text: `${( (1 - data[data.length - 2].reserves / data[0].reserves) * 100 ).toFixed(0)}% runoff over ${data.length - 1} quarters`, fontSize: 10, fill: "#6b7280" }) ] }); }38:T1cc9,{ // Import d3-sankey const d3Sankey = await import("https://cdn.skypack.dev/d3-sankey@0.12.3"); // Create container const container = html`

Trust Account Cash Flow - Overall View

Aggregate cash inflows and outflows across all quarters

`; const width = 1000; const height = 600; const margin = { top: 40, right: 200, bottom: 40, left: 200 }; // Calculate aggregate totals const totals = { totalPremium: d3.sum(modelResults.trustAccount.premiumContributed) / 1000000, totalCollateralIn: d3.sum(modelResults.trustAccount.collateralContributed) / 1000000, totalIncome: d3.sum(modelResults.trustAccount.investmentIncome) / 1000000, totalClaims: d3.sum(modelResults.trustAccount.claimsPaid.map((x) => Math.abs(x))) / 1000000, totalCollateralOut: d3.sum( modelResults.trustAccount.collateralReleased.map((x) => Math.abs(x)) ) / 1000000 }; // Create nodes const nodes = [ // SOURCE NODES (left side) { id: 0, name: "Premium", color: "#8b5cf6", value: totals.totalPremium }, { id: 1, name: "Collateral In", color: "#06b6d4", value: totals.totalCollateralIn }, { id: 2, name: "Investment\nIncome", color: "#10b981", value: totals.totalIncome }, // CENTER NODE { id: 3, name: "Trust\nAccount", color: "#6366f1", value: 0 }, // SINK NODES (right side) { id: 4, name: "Claims\nPaid", color: "#ef4444", value: totals.totalClaims }, { id: 5, name: "Collateral Out", color: "#f97316", value: totals.totalCollateralOut } ]; // Create links const links = [ // INFLOWS to Trust Account { source: 0, target: 3, value: totals.totalPremium, color: "#c4b5fd" }, { source: 1, target: 3, value: totals.totalCollateralIn, color: "#67e8f9" }, { source: 2, target: 3, value: totals.totalIncome, color: "#6ee7b7" }, // OUTFLOWS from Trust Account { source: 3, target: 4, value: totals.totalClaims, color: "#fca5a5" }, { source: 3, target: 5, value: totals.totalCollateralOut, color: "#fdba74" } ]; const svg = d3 .create("svg") .attr("width", width) .attr("height", height) .attr("viewBox", [0, 0, width, height]) .style("max-width", "100%") .style("height", "auto") .style("background", "transparent"); const sankey = d3Sankey .sankey() .nodeId((d) => d.id) .nodeWidth(40) .nodePadding(30) .nodeAlign(d3Sankey.sankeyCenter) .extent([ [margin.left, margin.top], [width - margin.right, height - margin.bottom] ]); const graph = sankey({ nodes: nodes.map((d) => Object.assign({}, d)), links: links.map((d) => Object.assign({}, d)) }); // Add gradient definitions const defs = svg.append("defs"); links.forEach((link, i) => { const gradient = defs .append("linearGradient") .attr("id", `gradient-${i}`) .attr("gradientUnits", "userSpaceOnUse") .attr("x1", (d) => graph.links[i].source.x1) .attr("x2", (d) => graph.links[i].target.x0); gradient.append("stop").attr("offset", "0%").attr("stop-color", link.color); gradient .append("stop") .attr("offset", "100%") .attr("stop-color", link.color); }); // Draw links with gradients svg .append("g") .selectAll("path") .data(graph.links) .join("path") .attr("d", d3Sankey.sankeyLinkHorizontal()) .attr("fill", "none") .attr("stroke", (d, i) => `url(#gradient-${i})`) .attr("stroke-width", (d) => Math.max(3, d.width)) .attr("opacity", 0.6) .on("mouseover", function (event, d) { d3.select(this) .attr("opacity", 0.9) .attr("stroke-width", (d) => Math.max(4, d.width * 1.2)); }) .on("mouseout", function (event, d) { d3.select(this) .attr("opacity", 0.6) .attr("stroke-width", (d) => Math.max(3, d.width)); }) .append("title") .text( (d) => `${nodes[d.source.index].name.replace("\n", " ")} → ${nodes[ d.target.index ].name.replace("\n", " ")}\n$${d.value.toFixed(2)}M` ); // Draw nodes const node = svg.append("g").selectAll("g").data(graph.nodes).join("g"); node .append("rect") .attr("x", (d) => d.x0) .attr("y", (d) => d.y0) .attr("height", (d) => d.y1 - d.y0) .attr("width", (d) => d.x1 - d.x0) .attr("fill", (d) => d.color) .attr("rx", 6) .style("stroke", "white") .style("stroke-width", 3) .on("mouseover", function (event, d) { d3.select(this).style("opacity", 0.8).style("stroke-width", 4); }) .on("mouseout", function (event, d) { d3.select(this).style("opacity", 1).style("stroke-width", 3); }) .append("title") .text((d) => `${d.name.replace("\n", " ")}\n$${d.value.toFixed(2)}M`); // Add node labels - positioned outside the nodes for better visibility node.each(function (d) { const nodeGroup = d3.select(this); const isLeftSide = d.x0 < width / 2; // Main label const labelGroup = nodeGroup .append("text") .attr("x", isLeftSide ? d.x0 - 10 : d.x1 + 10) .attr("y", (d.y0 + d.y1) / 2) .attr("text-anchor", isLeftSide ? "end" : "start") .style("font-size", "14px") .style("font-weight", "700") .style("fill", "#1f2937") .style("pointer-events", "none"); // Split multi-line labels const lines = d.name.split("\n"); lines.forEach((line, i) => { labelGroup .append("tspan") .attr("x", isLeftSide ? d.x0 - 10 : d.x1 + 10) .attr("dy", i === 0 ? 0 : "1.2em") .text(line); }); // Value label nodeGroup .append("text") .attr("x", isLeftSide ? d.x0 - 10 : d.x1 + 10) .attr("y", (d.y0 + d.y1) / 2 + lines.length * 14) .attr("text-anchor", isLeftSide ? "end" : "start") .style("font-size", "12px") .style("font-weight", "600") .style("fill", d.color) .style("pointer-events", "none") .text(`$${d.value.toFixed(1)}M`); }); // Add summary statistics const totalInflow = totals.totalPremium + totals.totalCollateralIn + totals.totalIncome; const totalOutflow = totals.totalClaims + totals.totalCollateralOut; const summary = svg .append("g") .attr("transform", `translate(${width / 2}, ${height - 15})`); summary .append("text") .attr("text-anchor", "middle") .attr("fill", "#6b7280") .attr("font-size", "11px") .attr("font-weight", "600") .html( `Total Inflow: $${totalInflow.toFixed( 2 )}M • Total Outflow: $${totalOutflow.toFixed( 2 )}M` ); container.querySelector("#sankey-container").appendChild(svg.node()); return container; }39:Ta54,{ const totalData = modelResults.collateral.totalRequired.map((req, i) => ({ quarter: i, amount: req / 1000000, type: "Total Required (MAX)" })); const annualData = modelResults.collateral.annualValuation .map((val, i) => ({ quarter: i, amount: val / 1000000, type: "Annual Valuation" })) .filter((d) => d.amount > 0); const interimData = modelResults.collateral.interimQuarter .map((val, i) => ({ quarter: i, amount: val / 1000000, type: "Interim Quarter" })) .filter((d) => d.amount > 0); const coverageData = modelResults.collateral.coverageBased.map((val, i) => ({ quarter: i, amount: val / 1000000, type: "Coverage Based" })); const allData = [...annualData, ...interimData, ...coverageData]; return Plot.plot({ width: 1000, height: 400, marginLeft: 70, marginBottom: 60, marginTop: 40, style: { background: "white" }, title: "Collateral Requirements by Calculation Method", subtitle: "Multiple methods with total requirement shown as dashed line", x: { label: "Quarter", grid: true, tickFormat: (d) => `Q${d}` }, y: { label: "Collateral Required ($M)", grid: true }, color: { domain: [ "Annual Valuation", "Interim Quarter", "Coverage Based", "Total Required (MAX)" ], range: ["#8b5cf6", "#ec4899", "#06b6d4", "#1f2937"], legend: true }, marks: [ // Component lines Plot.line(allData, { x: "quarter", y: "amount", stroke: "type", strokeWidth: 2.5, curve: "step", title: (d) => `${d.type} Q${d.quarter}: ${d.amount.toFixed(1)}M` }), Plot.dot( allData.filter((d, i) => i % 2 === 0), { x: "quarter", y: "amount", fill: "type", r: 4 } ), // Total required line (bold dashed) Plot.line(totalData, { x: "quarter", y: "amount", stroke: "#1f2937", strokeWidth: 4, strokeDasharray: "8,4", curve: "step", title: (d) => `Total Required Q${d.quarter}: ${d.amount.toFixed(1)}M` }), // Annotation for methodology Plot.text( [ { quarter: 1, amount: Math.max(...totalData.map((d) => d.amount)) * 0.95 } ], { x: "quarter", y: "amount", text: "Dashed line = MAX of all methods", fontSize: 10, fill: "#6b7280", dx: 100 } ) ] }); }3a:T24a2,{ // Prepare the data const cashFlows = modelResults.operatingAccount.investorCashFlow.map( (cf, i) => ({ quarter: i, amount: cf / 1000000, // Convert to millions type: i === 0 ? "initial" : cf < 0 ? "dividend" : "return", label: i === 0 ? "Initial Investment" : cf < 0 ? "Dividend" : "Return" }) ); // Calculate cumulative positions for waterfall let cumulative = 0; const waterfallData = cashFlows.map((d, i) => { const start = cumulative; cumulative += d.amount; return { ...d, start: start, end: cumulative, height: Math.abs(d.amount) }; }); const width = 1200; const height = 500; const margin = { top: 60, right: 40, bottom: 80, left: 80 }; const innerWidth = width - margin.left - margin.right; const innerHeight = height - margin.top - margin.bottom; // Create container const container = d3 .create("div") .style("position", "relative") .style("width", "100%"); // Create SVG - NO BORDER OR SHADOW const svg = container .append("svg") .attr("width", width) .attr("height", height) .attr("viewBox", [0, 0, width, height]) .attr("style", "max-width: 100%; height: auto; background: transparent;"); const g = svg .append("g") .attr("transform", `translate(${margin.left},${margin.top})`); // Scales const xScale = d3 .scaleBand() .domain(waterfallData.map((d) => d.quarter)) .range([0, innerWidth]) .padding(0.3); const yScale = d3 .scaleLinear() .domain([ d3.min(waterfallData, (d) => Math.min(d.start, d.end)) * 1.1, d3.max(waterfallData, (d) => Math.max(d.start, d.end)) * 1.1 ]) .range([innerHeight, 0]); // Color scale const colorScale = (d) => { if (d.type === "initial") return "#6366f1"; // Indigo for initial if (d.type === "dividend") return "#10b981"; // Green for dividends (money coming back) return "#ef4444"; // Red for returns (money going out) }; // Add gradient definitions const defs = svg.append("defs"); ["#6366f1", "#10b981", "#ef4444"].forEach((color, idx) => { const gradient = defs .append("linearGradient") .attr("id", `gradient-${idx}`) .attr("x1", "0%") .attr("y1", "0%") .attr("x2", "0%") .attr("y2", "100%"); gradient .append("stop") .attr("offset", "0%") .attr("stop-color", color) .attr("stop-opacity", 0.9); gradient .append("stop") .attr("offset", "100%") .attr("stop-color", color) .attr("stop-opacity", 0.7); }); // Grid lines g.append("g") .attr("class", "grid") .selectAll("line") .data(yScale.ticks(8)) .join("line") .attr("x1", 0) .attr("x2", innerWidth) .attr("y1", (d) => yScale(d)) .attr("y2", (d) => yScale(d)) .attr("stroke", "#e5e7eb") .attr("stroke-dasharray", "2,2"); // Connector lines between bars g.append("g") .selectAll("line.connector") .data(waterfallData.slice(0, -1)) .join("line") .attr("class", "connector") .attr("x1", (d, i) => xScale(d.quarter) + xScale.bandwidth()) .attr("x2", (d, i) => xScale(waterfallData[i + 1].quarter)) .attr("y1", (d) => yScale(d.end)) .attr("y2", (d, i) => yScale(waterfallData[i + 1].start)) .attr("stroke", "#9ca3af") .attr("stroke-width", 2) .attr("stroke-dasharray", "4,4") .attr("opacity", 0.5); // Waterfall bars with gradients const bars = g .append("g") .selectAll("rect") .data(waterfallData) .join("rect") .attr("x", (d) => xScale(d.quarter)) .attr("y", (d) => yScale(Math.max(d.start, d.end))) .attr("width", xScale.bandwidth()) .attr("height", (d) => Math.abs(yScale(d.start) - yScale(d.end))) .attr("fill", (d) => { if (d.type === "initial") return "url(#gradient-0)"; if (d.type === "dividend") return "url(#gradient-1)"; return "url(#gradient-2)"; }) .attr("rx", 6) .attr("stroke", (d) => colorScale(d)) .attr("stroke-width", 2); // Tooltip const tooltip = container .append("div") .style("position", "absolute") .style("background", "white") .style("border", "1px solid #e5e7eb") .style("border-radius", "8px") .style("padding", "12px") .style("pointer-events", "none") .style("opacity", 0) .style("box-shadow", "0 4px 6px rgba(0,0,0,0.1)") .style("font-size", "13px") .style("z-index", "1000") .style("transition", "opacity 0.2s"); // Add hover effects bars .on("mouseenter", function (event, d) { d3.select(this) .transition() .duration(200) .attr("stroke-width", 3) .attr("opacity", 1); tooltip .style("opacity", 1) .html( `

Q${ d.quarter }

${d.label}

${d.amount >= 0 ? "+" : ""}$${d.amount.toFixed(2)}M

Cumulative: $${d.end.toFixed(2)}M

` ) .style("left", event.pageX + 10 + "px") .style("top", event.pageY - 10 + "px"); }) .on("mouseleave", function () { d3.select(this) .transition() .duration(200) .attr("stroke-width", 2) .attr("opacity", 0.85); tooltip.style("opacity", 0); }); // Value labels on bars (for significant amounts) g.append("g") .selectAll("text.value") .data(waterfallData.filter((d) => Math.abs(d.amount) > 0.5)) .join("text") .attr("class", "value") .attr("x", (d) => xScale(d.quarter) + xScale.bandwidth() / 2) .attr("y", (d) => { const barTop = yScale(Math.max(d.start, d.end)); return barTop - 5; }) .attr("text-anchor", "middle") .attr("fill", (d) => colorScale(d)) .attr("font-size", "11px") .attr("font-weight", "600") .text((d) => `$${Math.abs(d.amount).toFixed(1)}M`); // X axis g.append("g") .attr("transform", `translate(0,${innerHeight})`) .call(d3.axisBottom(xScale).tickFormat((d) => `Q${d}`)) .call((g) => g.select(".domain").attr("stroke", "#d1d5db")) .call((g) => g.selectAll(".tick line").attr("stroke", "#d1d5db")) .call((g) => g .selectAll(".tick text") .attr("fill", "#6b7280") .attr("font-size", "12px") ); // Y axis g.append("g") .call( d3 .axisLeft(yScale) .ticks(8) .tickFormat((d) => `$${d.toFixed(0)}M`) ) .call((g) => g.select(".domain").attr("stroke", "#d1d5db")) .call((g) => g.selectAll(".tick line").attr("stroke", "#d1d5db")) .call((g) => g .selectAll(".tick text") .attr("fill", "#6b7280") .attr("font-size", "12px") ); // Y axis label g.append("text") .attr("transform", "rotate(-90)") .attr("y", -60) .attr("x", -innerHeight / 2) .attr("text-anchor", "middle") .attr("fill", "#374151") .attr("font-size", "13px") .attr("font-weight", "600") .text("Cumulative Position ($M)"); // Title svg .append("text") .attr("x", width / 2) .attr("y", 30) .attr("text-anchor", "middle") .attr("fill", "#1f2937") .attr("font-size", "20px") .attr("font-weight", "700") .text("Investor Cash Flow Waterfall"); // Legend const legendData = [ { label: "Initial Investment", color: "#6366f1" }, { label: "Dividends (Inflow)", color: "#10b981" }, { label: "Returns (Outflow)", color: "#ef4444" } ]; const legend = svg .append("g") .attr("transform", `translate(${width - 350}, 20)`); legendData.forEach((item, i) => { const legendItem = legend .append("g") .attr("transform", `translate(${i * 115}, 0)`); legendItem .append("rect") .attr("width", 14) .attr("height", 14) .attr("rx", 3) .attr("fill", item.color); legendItem .append("text") .attr("x", 20) .attr("y", 11) .attr("fill", "#4b5563") .attr("font-size", "11px") .attr("font-weight", "500") .text(item.label); }); // Summary metrics box const initialInvestment = Math.abs(waterfallData[0].amount); const totalDividends = waterfallData .filter((d) => d.type === "dividend") .reduce((sum, d) => sum + Math.abs(d.amount), 0); const summary = svg .append("g") .attr("transform", `translate(${margin.left}, ${height - 35})`); const summaryBg = summary .append("rect") .attr("x", -10) .attr("y", -18) .attr("width", 360) .attr("height", 28) .attr("fill", "#f9fafb") .attr("rx", 6); summary .append("text") .attr("x", 0) .attr("y", 0) .attr("fill", "#6b7280") .attr("font-size", "11px") .attr("font-weight", "600") .html( `Initial Investment: $${initialInvestment.toFixed( 2 )}M • Total Dividends: $${totalDividends.toFixed( 2 )}M` ); return container.node(); }3b:T48a,html`

Operating Account & Investor Cash Flows

${Inputs.table( modelResults.operatingAccount.beginningBalance.map((_, i) => ({ Quarter: i, Beginning: (modelResults.operatingAccount.beginningBalance[i] / 1000000).toFixed( 2 ) + "M", "Note Proceeds": (modelResults.operatingAccount.noteProceeds[i] / 1000000).toFixed(2) + "M", "Collateral Mvmt": (modelResults.operatingAccount.collateralMovement[i] / 1000000).toFixed( 2 ) + "M", Dividends: (modelResults.operatingAccount.noteDividends[i] / 1000000).toFixed(2) + "M", "Admin Expense": (modelResults.operatingAccount.adminExpense[i] / 1000000).toFixed(2) + "M", Ending: (modelResults.operatingAccount.endingBalance[i] / 1000000).toFixed(2) + "M", "Investor CF": (modelResults.operatingAccount.investorCashFlow[i] / 1000000).toFixed( 2 ) + "M" })), { rows: 10 } )}

`3c:T43f,html`

Trust Account Movements

${Inputs.table( modelResults.trustAccount.beginningBalance.map((_, i) => ({ Quarter: i, Beginning: (modelResults.trustAccount.beginningBalance[i] / 1000000).toFixed(2) + "M", Premium: (modelResults.trustAccount.premiumContributed[i] / 1000000).toFixed(2) + "M", "Collateral In": (modelResults.trustAccount.collateralContributed[i] / 1000000).toFixed( 2 ) + "M", "Collateral Out": (modelResults.trustAccount.collateralReleased[i] / 1000000).toFixed(2) + "M", "Claims Paid": (modelResults.trustAccount.claimsPaid[i] / 1000000).toFixed(2) + "M", "Investment Income": (modelResults.trustAccount.investmentIncome[i] / 1000000).toFixed(2) + "M", Ending: (modelResults.trustAccount.endingBalance[i] / 1000000).toFixed(2) + "M" })), { rows: 10 } )}

`3d:T58d,scenariosData = { // Load from attached file const text = await FileAttachment("scenarios_renamed.csv").text(); const parsed = d3.csvParse(text, d3.autoType); // Transform to our format const scenarios = parsed.map((row) => ({ id: row.scenario_id, claimPattern: [ row.q1_claim, row.q2_claim, row.q3_claim, row.q4_claim, row.q5_claim, row.q6_claim, row.q7_claim, row.q8_claim, row.q9_claim, row.q10_claim, row.q11_claim, row.q12_claim, row.q13_claim, row.q14_claim, row.q15_claim, row.q16_claim, row.q17_claim, row.q18_claim, row.q19_claim, row.q20_claim, row.q21_claim ], lossDevPattern: [ row.q1_loss_dev, row.q2_loss_dev, row.q3_loss_dev, row.q4_loss_dev, row.q5_loss_dev, row.q6_loss_dev, row.q7_loss_dev, row.q8_loss_dev, row.q9_loss_dev, row.q10_loss_dev, row.q11_loss_dev, row.q12_loss_dev, row.q13_loss_dev, row.q14_loss_dev, row.q15_loss_dev, row.q16_loss_dev, row.q17_loss_dev, row.q18_loss_dev, row.q19_loss_dev, row.q20_loss_dev, row.q21_loss_dev ], finalLossDev: row.q21_loss_dev })); // Sort by q21_loss_dev (final loss development) scenarios.sort((a, b) => a.finalLossDev - b.finalLossDev); return scenarios; }3e:T1ebd,function runCashFlowWaterfall(reserves, noteAmount, terms, investmentReturn) { const quarters = reserves.beginningBalance.length; const expectedLoss = terms["Gross Reserves ($)"]; const noteTerm = terms["Note Term (years)"]; // Initialize arrays const cumulativeGrossClaims = new Array(quarters).fill(0); const cumulativeReinsuredPayments = new Array(quarters).fill(0); const theoreticalReinsuredPayments = new Array(quarters).fill(0); const collateral = { annualValuation: new Array(quarters).fill(0), interimQuarter: new Array(quarters).fill(0), coverageBased: new Array(quarters).fill(0), profitCommission: new Array(quarters).fill(0), totalRequired: new Array(quarters).fill(0) }; const trust = { beginningBalance: new Array(quarters).fill(0), premiumContributed: new Array(quarters).fill(0), collateralContributed: new Array(quarters).fill(0), collateralReleased: new Array(quarters).fill(0), residualReturned: new Array(quarters).fill(0), claimsPaid: new Array(quarters).fill(0), investmentIncome: new Array(quarters).fill(0), endingBalance: new Array(quarters).fill(0) }; const operating = { beginningBalance: new Array(quarters).fill(0), noteProceeds: new Array(quarters).fill(0), collateralMovement: new Array(quarters).fill(0), noteDividends: new Array(quarters).fill(0), adminExpense: new Array(quarters).fill(0), endingBalance: new Array(quarters).fill(0), investorCashFlow: new Array(quarters).fill(0) }; // Sequential calculation through each period for (let q = 0; q < quarters; q++) { // STEP 1: Calculate cumulative gross claims cumulativeGrossClaims[q] = -reserves.claimPayments .slice(0, q + 1) .reduce((sum, val) => sum + val, 0); // STEP 2: Calculate theoretical reinsured payment if (q <= 4 * noteTerm) { const cumulativeClaimPct = cumulativeGrossClaims[q] / expectedLoss; const coverageTodatePct = Math.min(cumulativeClaimPct, terms["Exhaustion"]) - Math.min(cumulativeClaimPct, terms["Attachment"]); const totalCoverageAmount = coverageTodatePct * expectedLoss * terms["Percent Placed"]; const cumulativePriorTheoretical = q > 0 ? theoreticalReinsuredPayments .slice(0, q) .reduce((sum, val) => sum + val, 0) : 0; theoreticalReinsuredPayments[q] = -totalCoverageAmount - cumulativePriorTheoretical; } else { theoreticalReinsuredPayments[q] = 0; } // STEP 3: Calculate Required Collateral if (q % 4 === 0 && q < 4 * noteTerm) { const currentReserves = reserves.beginningBalance[q] + reserves.changeInReserves[q]; const coverageWidth = terms["Exhaustion"] - terms["Attachment"]; collateral.annualValuation[q] = currentReserves * coverageWidth * terms["Percent Placed"]; } if (q % 4 === 0) { if (q <= 4 * noteTerm) { collateral.interimQuarter[q] = collateral.annualValuation[q]; } } else { if (q > 0) { collateral.interimQuarter[q] = collateral.interimQuarter[q - 1] + theoreticalReinsuredPayments[q - 1]; } } const lossDev = reserves.changeInReserves[q] / expectedLoss + 1; // Approximate loss development const currentExposurePct = Math.min(lossDev, terms["Exhaustion"]) - Math.min(lossDev, terms["Attachment"]); const currentExposureDollars = currentExposurePct * terms["Gross Reserves ($)"] * terms["Percent Placed"]; const cumulativeActualPayments = q > 0 ? trust.claimsPaid.slice(0, q).reduce((sum, val) => sum + val, 0) : 0; collateral.coverageBased[q] = currentExposureDollars + cumulativeActualPayments; if (q % 4 === 0) { const profitLayer = Math.max(lossDev, terms["Profit Exhaust"]) - Math.max(lossDev, terms["Profit Attach"]); collateral.profitCommission[q] = terms["Gross Reserves ($)"] * terms["Percent Placed"] * terms["Profit Sharing %"] * profitLayer; } collateral.totalRequired[q] = Math.max( collateral.annualValuation[q], collateral.interimQuarter[q], collateral.coverageBased[q] + collateral.profitCommission[q] ); // STEP 4: Update Trust Account if (q === 0) { trust.premiumContributed[0] = terms["Premium %"] * terms["Gross Reserves ($)"] * terms["Percent Placed"]; trust.collateralContributed[0] = collateral.totalRequired[0] - trust.premiumContributed[0]; } else { trust.beginningBalance[q] = trust.endingBalance[q - 1]; const required = collateral.totalRequired[q]; const availableBeforeAdjustment = trust.beginningBalance[q]; if (q <= 4 * noteTerm) { const collateralChange = required - availableBeforeAdjustment; if (collateralChange < 0) { trust.collateralReleased[q] = collateralChange; } else { trust.collateralContributed[q] = 0; } } if (q === 4 * noteTerm) { trust.residualReturned[q] = -required; } } const availableForClaims = trust.beginningBalance[q] + trust.premiumContributed[q] + trust.collateralContributed[q] + trust.collateralReleased[q]; if (theoreticalReinsuredPayments[q] < 0) { const amountToPay = Math.min( -theoreticalReinsuredPayments[q], availableForClaims ); trust.claimsPaid[q] = -amountToPay; } else { trust.claimsPaid[q] = 0; } cumulativeReinsuredPayments[q] = (q > 0 ? cumulativeReinsuredPayments[q - 1] : 0) + -trust.claimsPaid[q]; const balanceBeforeIncome = trust.beginningBalance[q] + trust.premiumContributed[q] + trust.collateralContributed[q] + trust.collateralReleased[q] + trust.residualReturned[q] + trust.claimsPaid[q] / 2; trust.investmentIncome[q] = (investmentReturn[q] / 4) * balanceBeforeIncome; trust.endingBalance[q] = trust.beginningBalance[q] + trust.premiumContributed[q] + trust.collateralContributed[q] + trust.collateralReleased[q] + trust.residualReturned[q] + trust.claimsPaid[q] + trust.investmentIncome[q]; // STEP 5: Update Operating Account if (q === 0) { operating.noteProceeds[0] = noteAmount; operating.collateralMovement[0] = -( trust.collateralContributed[0] + trust.collateralReleased[0] ); operating.adminExpense[0] = (-noteAmount * terms["Annual Expense %"]) / 4; operating.endingBalance[0] = operating.noteProceeds[0] + operating.collateralMovement[0] + operating.adminExpense[0]; operating.investorCashFlow[0] = noteAmount; } else { operating.beginningBalance[q] = operating.endingBalance[q - 1]; operating.collateralMovement[q] = -( trust.collateralContributed[q] + trust.collateralReleased[q] ); if (q < 4 * noteTerm) { operating.noteDividends[q] = -operating.collateralMovement[q]; operating.adminExpense[q] = (-noteAmount * terms["Annual Expense %"]) / 4; } else { const available = operating.beginningBalance[q] + operating.collateralMovement[q]; operating.noteDividends[q] = -available; } operating.endingBalance[q] = operating.beginningBalance[q] + operating.collateralMovement[q] + operating.noteDividends[q] + operating.adminExpense[q]; operating.investorCashFlow[q] = operating.noteProceeds[q] + operating.noteDividends[q]; } } return { collateral, trust, operating, cumulativeGrossClaims, theoreticalReinsuredPayments, cumulativeReinsuredPayments }; }3f:T475,function calculateReturns(investorCashFlows) { // IRR calculation using Newton-Raphson function irr(cashflows) { let rate = 0.05; const maxIter = 100; const tol = 1e-6; for (let i = 0; i < maxIter; i++) { let npv = 0; let dnpv = 0; for (let t = 0; t < cashflows.length; t++) { npv += cashflows[t] / Math.pow(1 + rate, t); dnpv += (-t * cashflows[t]) / Math.pow(1 + rate, t + 1); } const newRate = rate - npv / dnpv; if (Math.abs(newRate - rate) < tol) return newRate; rate = newRate; } return rate; } // Calculate quarterly IRR const quarterlyIRR = irr(investorCashFlows); // Annualize: (1 + quarterly)^4 - 1 const annualIRR = Math.pow(1 + quarterlyIRR, 4) - 1; // MOIC: Total cash returned / Initial investment // Python: initial_investment = -investor_cf[0] // Python: total_returned = investor_cf[1:].sum() const initialInvestment = -investorCashFlows[0]; const totalReturned = investorCashFlows.slice(1).reduce((a, b) => a + b, 0); const moic = totalReturned / initialInvestment; return { IRR: annualIRR, MOIC: moic }; }40:T42e,// Debug cell - add this temporarily html`

  Debug Info:

  First 5 Cash Flows:

  ${modelResults.operatingAccount.investorCashFlow
    .slice(0, 5)
    .map((cf, i) => `Q${i}: ${cf.toFixed(2)}`)
    .join("
")}

  Initial Investment (should be negative):

  ${(-modelResults.operatingAccount.investorCashFlow[0]).toFixed(2)}

  Total Returned (sum of rest):

  ${modelResults.operatingAccount.investorCashFlow
    .slice(1)
    .reduce((a, b) => a + b, 0)
    .toFixed(2)}

  MOIC Calculation:

  ${modelResults.operatingAccount.investorCashFlow
    .slice(1)
    .reduce((a, b) => a + b, 0)} / 
  ${-modelResults.operatingAccount.investorCashFlow[0]} = 
  ${modelResults.returns.MOIC}

  Current IRR: ${(modelResults.returns.IRR * 100).toFixed(
    4
  )}%

  Expected IRR: 6.93%

` 41:[{"id":"94842fdb02c548195b5df338a7059c57fa4ff42c2346d73a93383fe0ba0cd70173f8b1b64304ef4e49481e8b964226860feb55b9ed4ce9965bd22c1716dd9c12","href":"https://static.observableusercontent.com/files/94842fdb02c548195b5df338a7059c57fa4ff42c2346d73a93383fe0ba0cd70173f8b1b64304ef4e49481e8b964226860feb55b9ed4ce9965bd22c1716dd9c12","name":"scenarios_renamed.csv","type":"text/csv","lastModified":1760987019611.382,"size":7517078}] 30:{"id":"4c3c6579cad54e81","slug":null,"title":"Fleming Deal Model Notebook","thumbnail":"056aced0ff0820fb9fb6d721b694718b67c5eeadfc97f33733fa5e670f1b121b","access_level":"public","create_time":"$D2025-10-17T15:43:48.711Z","update_time":"$D2025-10-24T21:12:15.149Z","view_time":null,"first_public_time":"$D2025-10-17T15:43:48.711Z","has_unpublished_edits":null,"fork":false,"stars":1,"starred":false,"owner":{"type":"team","id":"5b9bd0b917e240d6","login":"ledger","name":"Ledger","avatar_url":"https://avatars-next.observableusercontent.com/c0873f971b4f7860a38fc812d8f161d44fc1110a7470efba6ea5413fef288f74","tier":"pro"},"version":548,"publish_version":null,"role":null,"authored":false,"authors":[{"type":"individual","id":"ea875417b7bd3d18","login":"jeff-zhao","name":"Jeff Zhao","avatar_url":"https://avatars-next.observableusercontent.com/6eed357a80f9dfd8b2aa354f7e031493757f13e52a80cbdb9ca8b36e7e99a3fb","tier":null}],"members":[{"type":"individual","id":"ea875417b7bd3d18","login":"jeff-zhao","name":"Jeff Zhao","avatar_url":"https://avatars-next.observableusercontent.com/6eed357a80f9dfd8b2aa354f7e031493757f13e52a80cbdb9ca8b36e7e99a3fb","tier":null,"role":"editor"},{"type":"team","id":"5b9bd0b917e240d6","login":"ledger","name":"Ledger","avatar_url":"https://avatars-next.observableusercontent.com/c0873f971b4f7860a38fc812d8f161d44fc1110a7470efba6ea5413fef288f74","tier":"pro","role":"viewer"}],"comments":[],"custom_thumbnail":true,"trashed":false,"lapsed":null,"subscribed":null,"forks":1,"collection":null,"type":"notebook","body":{"title":"Fleming Deal Model Notebook","cells":[{"id":0,"value":"# **Fleming Deal Model Notebook**\n\n> ⚠️ **Preliminary – Subject to Change** \n> For *discussion purposes only*. This information is confidential and not to be quoted, circulated, or relied upon. \n> Reproduction, in whole or in part, is prohibited.\n\n---\n\n## **Overview**\n\nThis Stochastic cash flow model evaluates key assumptions around:\n\n- Underlying portfolio profitability and claim development \n- Capital and collateral requirements \n- Investment and expense structure \n- Reinsurance layers and profit participation terms\n","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":5,"value":"## Reinsurance Terms","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":399,"value":"$32","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":80,"value":"## Interactive Assumptions","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":409,"value":"$33","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":541,"value":"$34","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":385,"value":"$35","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":272,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":136,"value":"html`${sectionHeader(\"Model Results\", \"\")}`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":202,"value":"$36","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":269,"value":"html`${sectionHeader(\n \"Loss Reserve Runoff\",\n \"Gross loss reserves declining as claims are paid over time\"\n)}`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":230,"value":"$37","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":223,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":260,"value":"html`${sectionHeader(\n \"Trust Account Dynamics\",\n \"Trust balance evolution with claims payments and investment income\"\n)}`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":526,"value":"$38","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":225,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":274,"value":"html`${sectionHeader(\n \"Collateral Requirements\",\n \"Quarterly collateral needs based on different calculation methods\"\n)}`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":227,"value":"$39","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":263,"value":"html`${sectionHeader(\n \"Investor Cash Flow Analysis\",\n \"Quarterly cash flows and cumulative position over the life of the investment\"\n)}`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":522,"value":"$3a","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":107,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":305,"value":"## Tables Output","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":141,"value":"html`

\n Gross Reserve Waterfall\n

\n ${Inputs.table(\n modelResults.reserves.beginningBalance.map((_, i) => ({\n Quarter: i,\n \"Beginning Balance\":\n (modelResults.reserves.beginningBalance[i] / 1000000).toFixed(2) + \"M\",\n \"Change in Reserves\":\n (modelResults.reserves.changeInReserves[i] / 1000000).toFixed(2) + \"M\",\n \"Claim Payments\":\n (modelResults.reserves.claimPayments[i] / 1000000).toFixed(2) + \"M\",\n \"Ending Balance\":\n (modelResults.reserves.endingBalance[i] / 1000000).toFixed(2) + \"M\"\n })),\n {\n rows: 10\n }\n )}\n

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":147,"value":"$3b","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":144,"value":"$3c","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":155,"value":"html`

\n Investor Cash Flow Detail\n

\n ${Inputs.table(\n modelResults.operatingAccount.investorCashFlow.map((cf, i) => ({\n Quarter: i,\n \"Cash Flow ($)\": cf.toLocaleString(\"en-US\", {\n style: \"currency\",\n currency: \"USD\",\n minimumFractionDigits: 2,\n maximumFractionDigits: 2\n }),\n \"Cash Flow (M)\": (cf / 1000000).toFixed(4),\n Type:\n i === 0\n ? \"Initial Investment (Outflow)\"\n : cf < 0\n ? \"Dividend (Inflow)\"\n : \"Return\"\n })),\n {\n rows: 21,\n columns: [\"Quarter\", \"Cash Flow ($)\", \"Cash Flow (M)\", \"Type\"]\n }\n )}\n

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":337,"value":"---","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":8,"value":"### Static inputs & Calculation Functions ","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":32,"value":"// Cell 1: Define your terms\nterms = ({\n \"Gross Reserves ($)\": 417000000,\n \"Percent Placed\": 0.8,\n Attachment: 0.0,\n Exhaustion: 1.35,\n \"Premium %\": 0.98,\n \"Profit Attach\": 0.95,\n \"Profit Exhaust\": 1.0,\n \"Profit Sharing %\": 0.5,\n \"Annual Expense %\": 0.002,\n \"Note Term (years)\": 5,\n \"Num Quarters\": 20\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":17,"value":"import { table } from \"@observablehq/stdlib\"","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":403,"value":"$3d","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":412,"value":"selectedScenarioIndex = scenarioSelection","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":78,"value":"viewof claimPaymentInput = Inputs.textarea({\n label: \"Claim Payment Pattern\",\n value:\n \"0.063, 0.063, 0.05, 0.05, 0.046, 0.046, 0.04, 0.04, 0.036, 0.036, 0.028, 0.028, 0.025, 0.025, 0.018, 0.018, 0.017, 0.017, 0.014, 0.014, 0\",\n rows: 2,\n width: 900\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":89,"value":"claimPaymentPattern = {\n if (scenariosData && selectedScenarioIndex !== undefined) {\n return scenariosData[selectedScenarioIndex].claimPattern;\n } else {\n return claimPaymentInput\n .split(\",\")\n .map((x) => parseFloat(x.trim()))\n .filter((x) => !isNaN(x));\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":94,"value":"viewof lossDevInput = Inputs.textarea({\n label: \"Loss Development Pattern\",\n value:\n \"1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0, 1.0\",\n rows: 2,\n width: 900\n})","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":99,"value":"lossDevelopmentPattern = {\n if (scenariosData && selectedScenarioIndex !== undefined) {\n return scenariosData[selectedScenarioIndex].lossDevPattern;\n } else {\n return lossDevInput\n .split(\",\")\n .map((x) => parseFloat(x.trim()))\n .filter((x) => !isNaN(x));\n }\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":112,"value":"investmentReturn = investmentReturnController","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":116,"value":"function calculateGrossReserves(grossReserves, claimPattern, lossDevPattern) {\n const quarters = claimPattern.length;\n const reserves = {\n beginningBalance: new Array(quarters).fill(0),\n changeInReserves: new Array(quarters).fill(0),\n claimPayments: new Array(quarters).fill(0),\n endingBalance: new Array(quarters).fill(0)\n };\n\n for (let q = 0; q < quarters - 1; q++) {\n if (q === 0) {\n reserves.beginningBalance[0] = grossReserves;\n reserves.changeInReserves[0] = grossReserves * (lossDevPattern[0] - 1);\n } else {\n reserves.beginningBalance[q] = reserves.endingBalance[q - 1];\n reserves.changeInReserves[q] =\n grossReserves * (lossDevPattern[q] - lossDevPattern[q - 1]);\n }\n\n reserves.claimPayments[q] = -grossReserves * claimPattern[q];\n reserves.endingBalance[q] =\n reserves.beginningBalance[q] +\n reserves.changeInReserves[q] +\n reserves.claimPayments[q];\n }\n\n return reserves;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":119,"value":"function calculateInitialCollateral(reserves, terms) {\n const currentReserves =\n reserves.beginningBalance[0] + reserves.changeInReserves[0];\n const coverageWidth = terms[\"Exhaustion\"] - terms[\"Attachment\"];\n const initialCollateral =\n currentReserves * coverageWidth * terms[\"Percent Placed\"];\n return initialCollateral;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":122,"value":"function calculateNoteAmount(initialCollateral, terms) {\n const grossReserves = terms[\"Gross Reserves ($)\"];\n const premium = terms[\"Premium %\"] * grossReserves * terms[\"Percent Placed\"];\n const collateralGap = initialCollateral - premium;\n const expenseFactor =\n 1 - terms[\"Annual Expense %\"] * terms[\"Note Term (years)\"];\n const noteAmount = collateralGap / expenseFactor;\n return noteAmount;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":127,"value":"$3e","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":130,"value":"$3f","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":133,"value":"modelResults = {\n // Step 1: Calculate reserves\n const reserves = calculateGrossReserves(\n terms[\"Gross Reserves ($)\"],\n claimPaymentPattern,\n lossDevelopmentPattern\n );\n\n // Step 2: Calculate initial collateral\n const initialCollateral = calculateInitialCollateral(reserves, terms);\n\n // Step 3: Calculate note amount\n const noteAmount = calculateNoteAmount(initialCollateral, terms);\n\n // Step 4: Run waterfall\n const waterfall = runCashFlowWaterfall(\n reserves,\n noteAmount,\n terms,\n investmentReturn\n );\n\n // Step 5: Calculate returns\n const returns = calculateReturns(waterfall.operating.investorCashFlow);\n\n return {\n noteAmount,\n initialCollateral,\n reserves,\n collateral: waterfall.collateral,\n trustAccount: waterfall.trust,\n operatingAccount: waterfall.operating,\n returns\n };\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":257,"value":"function sectionHeader(title, subtitle) {\n return html`

\n ${title}\n

\n ${subtitle}\n

`;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":281,"value":"function styledTable(title, data, options = {}) {\n return html`

\n ${title}\n

\n ${Inputs.table(data, { rows: 10, ...options })}\n

`;\n}","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":164,"value":"---\n### Debugging Cells","pinned":false,"hidden":false,"mode":"md","author":"$undefined","summary":"$undefined","output":"","database":"$undefined"},{"id":110,"value":"html`

\n Input Validation:
\n Claim Payments: ${claimPaymentPattern.length} elements ✓
\n Loss Development: ${lossDevelopmentPattern.length} elements ✓
\n Investment Return: ${investmentReturn.length} elements ✓
\n ${\n claimPaymentPattern.length === 21 &&\n lossDevelopmentPattern.length === 21 &&\n investmentReturn.length === 21\n ? '✓ All inputs valid (21 elements each)'\n : '⚠ Warning: Should have 21 elements each'\n }\n

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":174,"value":"$40","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"},{"id":167,"value":"html`

\n Investor Cash Flows (Raw Array):
\n

\n [${modelResults.operatingAccount.investorCashFlow\n .map((cf) => cf.toExponential(8))\n .join(\",\\n \")}]\n

\n Note: Compare this with your Python output. First value should be positive (investment), rest should be negative (returns to investor).\n

`","pinned":false,"hidden":false,"mode":"ojs","author":"$undefined","summary":"$undefined","output":"$undefined","database":"$undefined"}],"files":"$W41","stdlib":"1","titling":"auto"}} 31:"$30:body"