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 51,332.15 8,000,000.00 79.10% 4,137,161.58 5,976,511.99 10,113,673.57 59.09%
20
0.24112 49,451.97 8,000,000.00 80.42% 4,665,427.29 5,282,846.68 9,948,273.97 53.10%
21
0.23843 51,741.83 8,000,000.00 78.86% 4,022,553.42 6,121,780.62 10,144,334.04 60.35%
22
0.23564 52,061.54 8,000,000.00 78.67% 3,932,736.61 6,236,194.55 10,168,931.16 61.33%
23
0.23295 52,500.44 8,000,000.00 78.42% 3,809,151.46 6,392,370.70 10,201,522.17 62.66%
24
0.23016 50,235.81 8,000,000.00 79.87% 4,452,570.02 5,564,263.37 10,016,833.40 55.55%
25
0.22747 51,259.33 8,000,000.00 79.18% 4,158,214.46 5,945,406.81 10,103,621.27 58.84%
26
0.22478 50,632.89 8,000,000.00 79.59% 4,341,073.74 5,710,322.08 10,051,395.82 56.81%
27
0.22199 49,363.02 8,000,000.00 80.52% 4,716,969.29 5,218,737.13 9,935,706.42 52.53%
28
0.219210 48,318.87 8,000,000.00 81.37% 5,029,520.97 4,801,565.65 9,831,086.63 48.84%
29
0.216411 46,493.76 8,000,000.00 83.12% 5,561,150.48 4,063,684.10 9,624,834.57 42.22%
30
0.213712 44,882.40 8,000,000.00 84.94% 6,003,764.71 3,414,998.65 9,418,763.36 36.26%
31
0.211013 46,787.97 8,000,000.00 82.85% 5,496,131.96 4,160,044.57 9,656,176.53 43.08%
32
0.208214 47,734.43 8,000,000.00 81.94% 5,228,535.08 4,534,876.16 9,763,411.24 46.45%
33
0.205515 49,772.27 8,000,000.00 80.26% 4,613,584.37 5,353,568.39 9,967,152.76 53.71%
34
0.202716 50,049.37 8,000,000.00 80.06% 4,531,153.24 5,461,468.58 9,992,621.82 54.66%
35
0.200017 51,172.45 8,000,000.00 79.30% 4,183,870.46 5,904,929.43 10,088,799.89 58.53%
36
0.197318 50,885.37 8,000,000.00 79.48% 4,273,880.81 5,791,189.81 10,065,070.62 57.54%
37
0.194519 49,105.75 8,000,000.00 80.81% 4,837,709.81 5,062,205.33 9,899,915.14 51.13%
38
0.191820 49,602.40 8,000,000.00 80.42% 4,684,395.99 5,263,459.29 9,947,855.27 52.91%
39
0.189021 48,968.89 8,000,000.00 80.92% 4,892,388.56 4,993,705.79 9,886,094.35 50.51%
40
0.186322 49,016.20 8,000,000.00 80.88% 4,882,971.18 5,007,861.27 9,890,832.45 50.63%
41
0.183623 51,600.88 8,000,000.00 79.09% 4,042,466.19 6,072,308.62 10,114,774.81 60.03%
42
0.180824 51,359.81 8,000,000.00 79.24% 4,119,761.17 5,975,958.35 10,095,719.52 59.19%
43
0.178125 51,514.72 8,000,000.00 79.14% 4,068,264.68 6,039,834.47 10,108,099.15 59.75%
44
0.175326 50,859.93 8,000,000.00 79.57% 4,284,896.11 5,769,506.51 10,054,402.63 57.38%
45
0.172627 51,326.86 8,000,000.00 79.26% 4,128,617.31 5,964,175.30 10,092,792.61 59.09%
46
0.169928 53,315.60 8,000,000.00 78.18% 3,471,226.95 6,761,887.74 10,233,114.69 66.08%
47
0.167129 50,228.98 8,000,000.00 80.16% 4,495,567.19 5,484,870.33 9,980,437.52 54.96%
48
0.164430 50,202.44 8,000,000.00 80.18% 4,507,392.03 5,470,683.06 9,978,075.09 54.83%
49
0.161631 50,005.13 8,000,000.00 80.32% 4,579,371.56 5,380,848.68 9,960,220.24 54.02%
50
0.158932 49,380.64 8,000,000.00 80.80% 4,803,830.35 5,096,994.28 9,900,824.64 51.48%
51
0.156233 48,172.42 8,000,000.00 81.85% 5,236,112.33 4,537,834.95 9,773,947.28 46.43%
52
0.153434 48,947.99 8,000,000.00 81.18% 4,969,301.89 4,885,528.45 9,854,830.34 49.57%
53
0.150735 48,491.76 8,000,000.00 81.57% 5,141,208.10 4,666,026.61 9,807,234.72 47.58%
54
0.147936 48,194.36 8,000,000.00 81.84% 5,257,860.65 4,517,310.16 9,775,170.80 46.21%
55
0.145237 48,399.00 8,000,000.00 81.65% 5,193,673.67 4,603,699.69 9,797,373.36 46.99%
56
0.142538 48,577.73 8,000,000.00 81.50% 5,137,395.52 4,679,077.16 9,816,472.68 47.67%
57
0.139739 49,915.94 8,000,000.00 80.43% 4,638,374.17 5,307,738.06 9,946,112.23 53.36%
58
0.137040 51,909.00 8,000,000.00 79.17% 3,880,396.49 6,224,898.54 10,105,295.03 61.60%
59
0.134241 49,688.93 8,000,000.00 80.77% 4,725,252.98 5,179,686.01 9,904,938.99 52.29%
60
0.131542 51,648.78 8,000,000.00 79.48% 3,962,933.10 6,101,975.98 10,064,909.08 60.63%
61
0.128843 53,189.64 8,000,000.00 78.68% 3,375,752.84 6,791,966.02 10,167,718.86 66.80%
62
0.126044 53,705.68 8,000,000.00 78.44% 3,177,165.28 7,021,673.79 10,198,839.07 68.85%
63
0.123345 55,930.98 8,000,000.00 77.66% 2,408,876.81 7,892,755.13 10,301,631.94 76.62%
64
0.120546 57,122.71 8,000,000.00 77.33% 2,033,503.04 8,311,735.94 10,345,238.99 80.34%
65
0.117847 57,304.87 8,000,000.00 77.28% 1,959,792.22 8,391,697.40 10,351,489.63 81.07%
66
0.115148 54,587.05 8,000,000.00 78.30% 2,794,160.08 7,422,766.47 10,216,926.55 72.65%
67
0.112349 53,622.16 8,000,000.00 78.72% 3,131,280.01 7,030,773.03 10,162,053.04 69.19%
68
0.109650 51,855.89 8,000,000.00 79.71% 3,823,299.82 6,212,529.29 10,035,829.11 61.90%
69
0.106851 52,366.57 8,000,000.00 79.43% 3,602,360.53 6,469,145.37 10,071,505.90 64.23%
70
0.104152 52,436.15 8,000,000.00 79.39% 3,563,246.49 6,513,035.16 10,076,281.65 64.64%
71
0.101453 52,774.89 8,000,000.00 79.22% 3,409,727.69 6,688,681.08 10,098,408.76 66.24%
72
0.098654 52,463.04 8,000,000.00 79.38% 3,529,374.46 6,548,368.22 10,077,742.69 64.98%
73
0.095955 53,499.17 8,000,000.00 78.91% 3,077,188.56 7,061,112.02 10,138,300.58 69.65%
74
0.093256 53,052.77 8,000,000.00 79.12% 3,246,666.59 6,864,760.60 10,111,427.19 67.89%
75
0.090457 53,093.29 8,000,000.00 79.10% 3,212,091.61 6,901,811.34 10,113,902.94 68.24%
76
0.087758 51,120.26 8,000,000.00 80.35% 4,097,275.43 5,859,689.12 9,956,964.55 58.85%
77
0.084959 47,785.78 8,000,000.00 83.66% 5,680,769.16 3,881,933.57 9,562,702.73 40.59%
78
0.082260 49,129.54 8,000,000.00 82.45% 5,047,904.44 4,654,461.62 9,702,366.06 47.97%
79
0.079561 47,185.74 8,000,000.00 84.62% 5,987,461.62 3,467,079.43 9,454,541.05 36.67%
80
0.076762 48,170.86 8,000,000.00 83.61% 5,542,269.72 4,026,387.59 9,568,657.31 42.08%
81
0.074063 48,358.46 8,000,000.00 83.42% 5,470,497.53 4,119,568.03 9,590,065.56 42.96%
82
0.071264 48,358.12 8,000,000.00 83.42% 5,493,067.02 4,096,972.09 9,590,039.10 42.72%
83
0.068565 49,231.34 8,000,000.00 82.65% 5,044,250.96 4,635,374.82 9,679,625.78 47.89%
84
0.065866 48,505.59 8,000,000.00 83.36% 5,456,051.13 4,141,176.92 9,597,228.05 43.15%
85
0.063067 45,352.86 8,000,000.00 87.97% 6,934,522.78 2,159,611.41 9,094,134.20 23.75%
86
0.060368 43,941.11 8,000,000.00 90.42% 7,424,214.09 1,423,234.18 8,847,448.27 16.09%
87
0.057569 44,080.83 8,000,000.00 90.18% 7,443,382.39 1,427,673.57 8,871,055.97 16.09%
88
0.054870 45,331.49 8,000,000.00 88.27% 7,087,631.63 1,975,814.52 9,063,446.15 21.80%
89
0.052171 44,222.16 8,000,000.00 90.22% 7,501,763.32 1,365,651.51 8,867,414.83 15.40%
90
0.049372 45,050.72 8,000,000.00 88.88% 7,303,623.29 1,697,145.49 9,000,768.78 18.86%
91
0.046673 46,948.61 8,000,000.00 86.50% 6,490,554.85 2,757,803.78 9,248,358.63 29.82%
92
0.043874 45,771.29 8,000,000.00 88.36% 7,114,103.21 1,939,761.21 9,053,864.42 21.42%
93
0.041175 44,980.29 8,000,000.00 89.74% 7,485,649.42 1,429,337.37 8,914,986.80 16.03%
94
0.038476 46,156.01 8,000,000.00 88.02% 7,078,111.83 2,011,022.09 9,089,133.93 22.13%
95
0.035677 48,022.41 8,000,000.00 86.04% 6,027,365.65 3,271,155.65 9,298,521.30 35.18%
96
0.032978 46,958.58 8,000,000.00 87.63% 6,766,452.99 2,363,207.25 9,129,660.25 25.88%
97
0.030179 45,965.84 8,000,000.00 89.35% 7,366,397.83 1,587,328.10 8,953,725.93 17.73%
98
0.027480 46,998.00 8,000,000.00 87.93% 6,915,789.79 2,182,164.02 9,097,953.81 23.99%
99
0.024781 47,043.88 8,000,000.00 87.87% 7,005,122.39 2,099,276.27 9,104,398.65 23.06%
100
0.021982 47,238.64 8,000,000.00 87.59% 7,009,392.58 2,124,014.44 9,133,407.02 23.26%