B | C | D | E | F | G | H | I | J | K | L | Y | Z | |
---|---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | 1. Cells in red are hard-coded (i.e. you can change). | ||||||||||||
2 | 2. Cells in yellow indicate that their formulae are different from the rest of the column cells (hence don't copy/paste). | ||||||||||||
3 | 5. Ext. Oracle diffuse as geometric brownian motion of Growth (drift) and Vol (for simple illustration, suggest vol = 0). | ||||||||||||
4 | |||||||||||||
5 | Borrow USD with BTC Collateral | ||||||||||||
6 | Params | Collateral | 200.00 | ||||||||||
7 | LTV | 80% | |||||||||||
8 | Strike | 50,000.00 | |||||||||||
9 | Days | 90 | |||||||||||
10 | BS Vol | 50% | 3.13% | ||||||||||
11 | Slippage Rebate | 80% | |||||||||||
12 | Fee | 0.15% | |||||||||||
13 | |||||||||||||
14 | BTCUSD | Real Growth | 0% | ||||||||||
15 | Real Vol | 49% | Main Risk is BTCUSD going down => Hedge BTC Downside (= USDBTC Call) | ||||||||||
16 | T-t | t | BTCUSD | Borrowed USD | LTV | BTC Balance in USD (Locked) | USD Balance (Locked) | Collateral Value (in USD) | Weight | ||||
17 | Initialisation | 50,000.00 | |||||||||||
18 | 0.2466 | 0 | 50,000.00 | 8,000,000.00 | 80.00% | 4,506,018.64 | 5,493,981.36 | 10,000,000.00 | 54.94% | ||||
19 | 0.2438 | 1 | 50,046.19 | 8,000,000.00 | 79.97% | 4,495,829.08 | 5,508,345.95 | 10,004,175.02 | 55.06% | ||||
20 | 0.2411 | 2 | 50,336.82 | 8,000,000.00 | 79.76% | 4,416,674.16 | 5,613,413.61 | 10,030,087.77 | 55.97% | ||||
21 | 0.2384 | 3 | 52,282.27 | 8,000,000.00 | 78.53% | 3,873,253.80 | 6,313,598.39 | 10,186,852.19 | 61.98% | ||||
22 | 0.2356 | 4 | 51,485.29 | 8,000,000.00 | 79.01% | 4,094,860.52 | 6,030,810.67 | 10,125,671.19 | 59.56% | ||||
23 | 0.2329 | 5 | 52,133.28 | 8,000,000.00 | 78.62% | 3,912,150.03 | 6,263,680.29 | 10,175,830.32 | 61.55% | ||||
24 | 0.2301 | 6 | 49,817.10 | 8,000,000.00 | 80.16% | 4,574,214.44 | 5,406,060.58 | 9,980,275.01 | 54.17% | ||||
25 | 0.2274 | 7 | 48,703.12 | 8,000,000.00 | 81.03% | 4,900,948.95 | 4,971,806.67 | 9,872,755.62 | 50.36% | ||||
26 | 0.2247 | 8 | 47,373.22 | 8,000,000.00 | 82.21% | 5,288,595.13 | 4,442,289.46 | 9,730,884.59 | 45.65% | ||||
27 | 0.2219 | 9 | 44,378.68 | 8,000,000.00 | 85.54% | 6,090,202.78 | 3,262,034.72 | 9,352,237.50 | 34.88% | ||||
28 | 0.2192 | 10 | 45,162.27 | 8,000,000.00 | 84.59% | 5,907,362.29 | 3,549,909.52 | 9,457,271.82 | 37.54% | ||||
29 | 0.2164 | 11 | 44,434.99 | 8,000,000.00 | 85.47% | 6,103,150.40 | 3,256,312.99 | 9,359,463.39 | 34.79% | ||||
30 | 0.2137 | 12 | 44,730.65 | 8,000,000.00 | 85.11% | 6,043,230.23 | 3,356,625.06 | 9,399,855.29 | 35.71% | ||||
31 | 0.2110 | 13 | 45,154.32 | 8,000,000.00 | 84.60% | 5,947,586.78 | 3,508,910.02 | 9,456,496.80 | 37.11% | ||||
32 | 0.2082 | 14 | 43,495.11 | 8,000,000.00 | 86.74% | 6,367,100.36 | 2,856,111.03 | 9,223,211.38 | 30.97% | ||||
33 | 0.2055 | 15 | 42,228.83 | 8,000,000.00 | 88.60% | 6,648,099.66 | 2,381,015.91 | 9,029,115.58 | 26.37% | ||||
34 | 0.2027 | 16 | 42,059.23 | 8,000,000.00 | 88.87% | 6,697,358.98 | 2,304,884.07 | 9,002,243.06 | 25.60% | ||||
35 | 0.2000 | 17 | 41,062.46 | 8,000,000.00 | 90.52% | 6,884,987.54 | 1,953,020.03 | 8,838,007.57 | 22.10% | ||||
36 | 0.1973 | 18 | 42,179.13 | 8,000,000.00 | 88.70% | 6,708,230.85 | 2,311,323.66 | 9,019,554.51 | 25.63% | ||||
37 | 0.1945 | 19 | 40,591.57 | 8,000,000.00 | 91.40% | 6,986,341.97 | 1,766,766.67 | 8,753,108.63 | 20.18% | ||||
38 | 0.1918 | 20 | 41,626.65 | 8,000,000.00 | 89.62% | 6,842,653.82 | 2,083,798.39 | 8,926,452.21 | 23.34% | ||||
39 | 0.1890 | 21 | 41,937.03 | 8,000,000.00 | 89.11% | 6,805,682.84 | 2,171,550.67 | 8,977,233.51 | 24.19% | ||||
40 | 0.1863 | 22 | 42,153.30 | 8,000,000.00 | 88.77% | 6,783,788.54 | 2,228,472.57 | 9,012,261.10 | 24.73% | ||||
41 | 0.1836 | 23 | 42,788.29 | 8,000,000.00 | 87.79% | 6,673,872.07 | 2,438,883.08 | 9,112,755.15 | 26.76% | ||||
42 | 0.1808 | 24 | 44,556.45 | 8,000,000.00 | 85.37% | 6,264,943.14 | 3,106,443.75 | 9,371,386.89 | 33.15% | ||||
43 | 0.1781 | 25 | 44,530.64 | 8,000,000.00 | 85.40% | 6,288,677.77 | 3,079,085.64 | 9,367,763.41 | 32.87% | ||||
44 | 0.1753 | 26 | 44,883.89 | 8,000,000.00 | 84.95% | 6,209,910.80 | 3,207,309.71 | 9,417,220.51 | 34.06% | ||||
45 | 0.1726 | 27 | 45,321.68 | 8,000,000.00 | 84.41% | 6,102,774.83 | 3,374,239.51 | 9,477,014.34 | 35.60% | ||||
46 | 0.1699 | 28 | 46,231.84 | 8,000,000.00 | 83.38% | 5,845,199.08 | 3,749,943.15 | 9,595,142.23 | 39.08% | ||||
47 | 0.1671 | 29 | 46,224.27 | 8,000,000.00 | 83.38% | 5,862,070.11 | 3,732,127.33 | 9,594,197.44 | 38.90% | ||||
48 | 0.1644 | 30 | 45,124.67 | 8,000,000.00 | 84.69% | 6,207,983.35 | 3,238,708.99 | 9,446,692.34 | 34.28% | ||||
49 | 0.1616 | 31 | 43,499.73 | 8,000,000.00 | 86.91% | 6,656,942.92 | 2,548,305.74 | 9,205,248.66 | 27.68% | ||||
50 | 0.1589 | 32 | 44,971.33 | 8,000,000.00 | 84.95% | 6,283,158.90 | 3,134,241.73 | 9,417,400.63 | 33.28% | ||||
51 | 0.1562 | 33 | 45,581.76 | 8,000,000.00 | 84.20% | 6,119,411.60 | 3,381,366.66 | 9,500,778.26 | 35.59% | ||||
52 | 0.1534 | 34 | 43,807.57 | 8,000,000.00 | 86.58% | 6,635,332.66 | 2,604,924.81 | 9,240,257.47 | 28.19% | ||||
53 | 0.1507 | 35 | 42,678.91 | 8,000,000.00 | 88.30% | 6,920,266.71 | 2,139,803.24 | 9,060,069.96 | 23.62% | ||||
54 | 0.1479 | 36 | 42,195.56 | 8,000,000.00 | 89.09% | 7,041,522.42 | 1,938,333.79 | 8,979,856.21 | 21.59% | ||||
55 | 0.1452 | 37 | 43,354.91 | 8,000,000.00 | 87.29% | 6,811,553.85 | 2,353,812.11 | 9,165,365.95 | 25.68% | ||||
56 | 0.1425 | 38 | 44,212.80 | 8,000,000.00 | 86.06% | 6,612,926.81 | 2,682,932.25 | 9,295,859.06 | 28.86% | ||||
57 | 0.1397 | 39 | 45,450.27 | 8,000,000.00 | 84.47% | 6,266,498.51 | 3,204,324.80 | 9,470,823.32 | 33.83% | ||||
58 | 0.1370 | 40 | 42,828.13 | 8,000,000.00 | 88.33% | 6,988,337.16 | 2,068,649.82 | 9,056,986.98 | 22.84% | ||||
59 | 0.1342 | 41 | 42,645.66 | 8,000,000.00 | 88.62% | 7,053,351.34 | 1,973,497.84 | 9,026,849.18 | 21.86% | ||||
60 | 0.1315 | 42 | 41,625.10 | 8,000,000.00 | 90.40% | 7,268,417.33 | 1,581,360.17 | 8,849,777.50 | 17.87% | ||||
61 | 0.1288 | 43 | 40,807.34 | 8,000,000.00 | 91.94% | 7,408,890.82 | 1,292,765.28 | 8,701,656.09 | 14.86% | ||||
62 | 0.1260 | 44 | 40,623.36 | 8,000,000.00 | 92.30% | 7,456,155.82 | 1,211,671.75 | 8,667,827.57 | 13.98% | ||||
63 | 0.1233 | 45 | 40,561.54 | 8,000,000.00 | 92.42% | 7,489,215.02 | 1,167,151.56 | 8,656,366.58 | 13.48% | ||||
64 | 0.1205 | 46 | 40,963.69 | 8,000,000.00 | 91.64% | 7,470,851.77 | 1,259,245.13 | 8,730,096.90 | 14.42% | ||||
65 | 0.1178 | 47 | 40,070.65 | 8,000,000.00 | 93.45% | 7,582,664.99 | 978,200.68 | 8,560,865.67 | 11.43% | ||||
66 | 0.1151 | 48 | 38,568.72 | 8,000,000.00 | 96.83% | 7,649,409.59 | 612,497.45 | 8,261,907.04 | 7.41% | ||||
67 | 0.1123 | 49 | 37,480.64 | 8,000,000.00 | 99.51% | 7,629,828.38 | 409,349.00 | 8,039,177.38 | 5.09% | ||||
68 | 0.1096 | 50 | 36,576.97 | 8,000,000.00 | 101.90% | 7,572,507.14 | 278,484.37 | 7,850,991.51 | 3.55% | ||||
69 | 0.1068 | 51 | 37,621.07 | 8,000,000.00 | 99.21% | 7,671,989.38 | 391,772.82 | 8,063,762.19 | 4.86% | ||||
70 | 0.1041 | 52 | 35,648.32 | 8,000,000.00 | 104.63% | 7,479,050.21 | 167,292.06 | 7,646,342.28 | 2.19% | ||||
71 | 0.1014 | 53 | 34,776.46 | 8,000,000.00 | 107.23% | 7,357,460.14 | 103,423.06 | 7,460,883.21 | 1.39% | ||||
72 | 0.0986 | 54 | 33,469.78 | 8,000,000.00 | 111.41% | 7,133,478.07 | 47,490.43 | 7,180,968.50 | 0.66% | ||||
73 | 0.0959 | 55 | 31,651.02 | 8,000,000.00 | 117.82% | 6,776,523.81 | 13,681.39 | 6,790,205.20 | 0.20% | ||||
74 | 0.0932 | 56 | 31,789.47 | 8,000,000.00 | 117.30% | 6,806,768.95 | 13,075.90 | 6,819,844.85 | 0.19% | ||||
75 | 0.0904 | 57 | 31,371.11 | 8,000,000.00 | 118.87% | 6,721,730.61 | 8,357.54 | 6,730,088.15 | 0.12% | ||||
76 | 0.0877 | 58 | 31,145.34 | 8,000,000.00 | 119.73% | 6,675,668.91 | 5,973.82 | 6,681,642.73 | 0.09% | ||||
77 | 0.0849 | 59 | 30,996.85 | 8,000,000.00 | 120.30% | 6,645,339.02 | 4,436.98 | 6,649,776.00 | 0.07% | ||||
78 | 0.0822 | 60 | 30,993.40 | 8,000,000.00 | 120.32% | 6,645,378.79 | 3,643.48 | 6,649,022.27 | 0.05% | ||||
79 | 0.0795 | 61 | 30,646.91 | 8,000,000.00 | 121.68% | 6,572,473.40 | 2,193.64 | 6,574,667.04 | 0.03% | ||||
80 | 0.0767 | 62 | 30,915.01 | 8,000,000.00 | 120.62% | 6,629,949.11 | 2,214.39 | 6,632,163.50 | 0.03% | ||||
81 | 0.0740 | 63 | 30,634.79 | 8,000,000.00 | 121.73% | 6,570,689.13 | 1,343.05 | 6,572,032.18 | 0.02% | ||||
82 | 0.0712 | 64 | 30,392.75 | 8,000,000.00 | 122.70% | 6,519,283.80 | 810.36 | 6,520,094.15 | 0.01% | ||||
83 | 0.0685 | 65 | 30,352.47 | 8,000,000.00 | 122.86% | 6,510,868.41 | 577.94 | 6,511,446.35 | 0.01% | ||||
84 | 0.0658 | 66 | 30,479.72 | 8,000,000.00 | 122.35% | 6,538,261.23 | 480.02 | 6,538,741.25 | 0.01% | ||||
85 | 0.0630 | 67 | 30,931.81 | 8,000,000.00 | 120.56% | 6,635,163.45 | 556.01 | 6,635,719.46 | 0.01% | ||||
86 | 0.0603 | 68 | 31,611.29 | 8,000,000.00 | 117.97% | 6,780,655.44 | 810.24 | 6,781,465.68 | 0.01% | ||||
87 | 0.0575 | 69 | 31,392.37 | 8,000,000.00 | 118.79% | 6,734,040.76 | 447.42 | 6,734,488.18 | 0.01% | ||||
88 | 0.0548 | 70 | 31,339.21 | 8,000,000.00 | 118.99% | 6,722,797.37 | 282.20 | 6,723,079.57 | 0.00% | ||||
89 | 0.0521 | 71 | 31,259.36 | 8,000,000.00 | 119.30% | 6,705,779.94 | 164.28 | 6,705,944.22 | 0.00% | ||||
90 | 0.0493 | 72 | 30,189.76 | 8,000,000.00 | 123.52% | 6,476,450.86 | 23.25 | 6,476,474.12 | 0.00% | ||||
91 | 0.0466 | 73 | 29,905.91 | 8,000,000.00 | 124.70% | 6,415,571.16 | 7.98 | 6,415,579.14 | 0.00% | ||||
92 | 0.0438 | 74 | 29,419.71 | 8,000,000.00 | 126.76% | 6,311,275.19 | 1.68 | 6,311,276.87 | 0.00% | ||||
93 | 0.0411 | 75 | 29,765.37 | 8,000,000.00 | 125.29% | 6,385,427.82 | 1.29 | 6,385,429.12 | 0.00% | ||||
94 | 0.0384 | 76 | 31,210.68 | 8,000,000.00 | 119.48% | 6,695,477.45 | 6.37 | 6,695,483.82 | 0.00% | ||||
95 | 0.0356 | 77 | 30,372.62 | 8,000,000.00 | 122.78% | 6,515,697.36 | 0.54 | 6,515,697.89 | 0.00% | ||||
96 | 0.0329 | 78 | 30,749.47 | 8,000,000.00 | 121.28% | 6,596,542.30 | 0.35 | 6,596,542.65 | 0.00% | ||||
97 | 0.0301 | 79 | 30,571.62 | 8,000,000.00 | 121.98% | 6,558,389.80 | 0.07 | 6,558,389.86 | 0.00% | ||||
98 | 0.0274 | 80 | 31,148.43 | 8,000,000.00 | 119.72% | 6,682,128.85 | 0.07 | 6,682,128.92 | 0.00% | ||||
99 | 0.0247 | 81 | 32,582.49 | 8,000,000.00 | 114.45% | 6,989,771.57 | 0.21 | 6,989,771.79 | 0.00% | ||||
100 | 0.0219 | 82 | 32,695.91 | 8,000,000.00 | 114.06% | 7,014,102.97 | 0.07 | 7,014,103.04 | 0.00% |