BCDEFGHIJKLYZ
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
ParamsCollateral200.00
7
LTV80%
8
Strike50,000.00
9
Days90
10
BS Vol50%3.13%
11
Slippage Rebate80%
12
Fee0.15%
13
14
BTCUSDReal Growth0%
15
Real Vol49%Main Risk is BTCUSD going down => Hedge BTC Downside (= USDBTC Call)
16
T-ttBTCUSDBorrowed USDLTVBTC Balance in USD (Locked)USD Balance (Locked)Collateral Value (in USD)Weight
17
Initialisation 50,000.00
18
0.24660 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.24381 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.24112 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.23843 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.23564 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.23295 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.23016 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.22747 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.22478 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.22199 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.219210 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.216411 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.213712 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.211013 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.208214 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.205515 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.202716 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.200017 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.197318 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.194519 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.191820 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.189021 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.186322 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.183623 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.180824 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.178125 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.175326 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.172627 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.169928 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.167129 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.164430 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.161631 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.158932 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.156233 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.153434 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.150735 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.147936 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.145237 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.142538 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.139739 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.137040 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.134241 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.131542 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.128843 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.126044 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.123345 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.120546 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.117847 40,070.65 8,000,000.00 93.45% 7,582,664.99 978,200.68 8,560,865.67 11.43%
66
0.115148 38,568.72 8,000,000.00 96.83% 7,649,409.59 612,497.45 8,261,907.04 7.41%
67
0.112349 37,480.64 8,000,000.00 99.51% 7,629,828.38 409,349.00 8,039,177.38 5.09%
68
0.109650 36,576.97 8,000,000.00 101.90% 7,572,507.14 278,484.37 7,850,991.51 3.55%
69
0.106851 37,621.07 8,000,000.00 99.21% 7,671,989.38 391,772.82 8,063,762.19 4.86%
70
0.104152 35,648.32 8,000,000.00 104.63% 7,479,050.21 167,292.06 7,646,342.28 2.19%
71
0.101453 34,776.46 8,000,000.00 107.23% 7,357,460.14 103,423.06 7,460,883.21 1.39%
72
0.098654 33,469.78 8,000,000.00 111.41% 7,133,478.07 47,490.43 7,180,968.50 0.66%
73
0.095955 31,651.02 8,000,000.00 117.82% 6,776,523.81 13,681.39 6,790,205.20 0.20%
74
0.093256 31,789.47 8,000,000.00 117.30% 6,806,768.95 13,075.90 6,819,844.85 0.19%
75
0.090457 31,371.11 8,000,000.00 118.87% 6,721,730.61 8,357.54 6,730,088.15 0.12%
76
0.087758 31,145.34 8,000,000.00 119.73% 6,675,668.91 5,973.82 6,681,642.73 0.09%
77
0.084959 30,996.85 8,000,000.00 120.30% 6,645,339.02 4,436.98 6,649,776.00 0.07%
78
0.082260 30,993.40 8,000,000.00 120.32% 6,645,378.79 3,643.48 6,649,022.27 0.05%
79
0.079561 30,646.91 8,000,000.00 121.68% 6,572,473.40 2,193.64 6,574,667.04 0.03%
80
0.076762 30,915.01 8,000,000.00 120.62% 6,629,949.11 2,214.39 6,632,163.50 0.03%
81
0.074063 30,634.79 8,000,000.00 121.73% 6,570,689.13 1,343.05 6,572,032.18 0.02%
82
0.071264 30,392.75 8,000,000.00 122.70% 6,519,283.80 810.36 6,520,094.15 0.01%
83
0.068565 30,352.47 8,000,000.00 122.86% 6,510,868.41 577.94 6,511,446.35 0.01%
84
0.065866 30,479.72 8,000,000.00 122.35% 6,538,261.23 480.02 6,538,741.25 0.01%
85
0.063067 30,931.81 8,000,000.00 120.56% 6,635,163.45 556.01 6,635,719.46 0.01%
86
0.060368 31,611.29 8,000,000.00 117.97% 6,780,655.44 810.24 6,781,465.68 0.01%
87
0.057569 31,392.37 8,000,000.00 118.79% 6,734,040.76 447.42 6,734,488.18 0.01%
88
0.054870 31,339.21 8,000,000.00 118.99% 6,722,797.37 282.20 6,723,079.57 0.00%
89
0.052171 31,259.36 8,000,000.00 119.30% 6,705,779.94 164.28 6,705,944.22 0.00%
90
0.049372 30,189.76 8,000,000.00 123.52% 6,476,450.86 23.25 6,476,474.12 0.00%
91
0.046673 29,905.91 8,000,000.00 124.70% 6,415,571.16 7.98 6,415,579.14 0.00%
92
0.043874 29,419.71 8,000,000.00 126.76% 6,311,275.19 1.68 6,311,276.87 0.00%
93
0.041175 29,765.37 8,000,000.00 125.29% 6,385,427.82 1.29 6,385,429.12 0.00%
94
0.038476 31,210.68 8,000,000.00 119.48% 6,695,477.45 6.37 6,695,483.82 0.00%
95
0.035677 30,372.62 8,000,000.00 122.78% 6,515,697.36 0.54 6,515,697.89 0.00%
96
0.032978 30,749.47 8,000,000.00 121.28% 6,596,542.30 0.35 6,596,542.65 0.00%
97
0.030179 30,571.62 8,000,000.00 121.98% 6,558,389.80 0.07 6,558,389.86 0.00%
98
0.027480 31,148.43 8,000,000.00 119.72% 6,682,128.85 0.07 6,682,128.92 0.00%
99
0.024781 32,582.49 8,000,000.00 114.45% 6,989,771.57 0.21 6,989,771.79 0.00%
100
0.021982 32,695.91 8,000,000.00 114.06% 7,014,102.97 0.07 7,014,103.04 0.00%