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 46,206.17 8,000,000.00 83.37% 5,544,213.75 4,051,915.95 9,596,129.70 42.22%
20
0.24112 49,525.83 8,000,000.00 80.41% 4,641,590.71 5,307,135.04 9,948,725.75 53.34%
21
0.23843 49,174.79 8,000,000.00 80.68% 4,744,686.37 5,170,746.50 9,915,432.86 52.15%
22
0.23564 49,711.80 8,000,000.00 80.27% 4,595,979.26 5,370,326.43 9,966,305.69 53.88%
23
0.23295 50,303.68 8,000,000.00 79.84% 4,429,413.23 5,590,434.42 10,019,847.65 55.79%
24
0.23016 51,735.76 8,000,000.00 78.91% 4,020,613.68 6,117,558.78 10,138,172.46 60.34%
25
0.22747 51,638.04 8,000,000.00 78.97% 4,048,058.71 6,082,526.99 10,130,585.70 60.04%
26
0.22478 53,528.16 8,000,000.00 77.93% 3,513,852.84 6,751,703.86 10,265,556.71 65.77%
27
0.22199 51,815.86 8,000,000.00 78.88% 3,994,764.07 6,147,344.39 10,142,108.46 60.61%
28
0.219210 50,599.23 8,000,000.00 79.66% 4,351,537.54 5,690,846.43 10,042,383.98 56.67%
29
0.216411 51,562.51 8,000,000.00 79.04% 4,067,246.87 6,054,378.79 10,121,625.67 59.82%
30
0.213712 49,856.90 8,000,000.00 80.20% 4,577,424.57 5,397,034.44 9,974,459.01 54.11%
31
0.211013 52,081.00 8,000,000.00 78.76% 3,910,657.41 6,247,014.95 10,157,672.36 61.50%
32
0.208214 51,072.54 8,000,000.00 79.38% 4,212,185.75 5,865,653.31 10,077,839.07 58.20%
33
0.205515 51,310.85 8,000,000.00 79.23% 4,140,064.91 5,957,286.72 10,097,351.63 59.00%
34
0.202716 50,408.38 8,000,000.00 79.83% 4,418,241.53 5,602,811.84 10,021,053.38 55.91%
35
0.200017 50,058.40 8,000,000.00 80.08% 4,529,640.78 5,460,288.91 9,989,929.69 54.66%
36
0.197318 48,044.11 8,000,000.00 81.74% 5,161,330.22 4,625,640.49 9,786,970.71 47.26%
37
0.194519 48,395.15 8,000,000.00 81.43% 5,059,760.51 4,764,506.49 9,824,267.01 48.50%
38
0.191820 48,260.39 8,000,000.00 81.55% 5,109,432.94 4,700,697.51 9,810,130.45 47.92%
39
0.189021 46,056.66 8,000,000.00 83.77% 5,787,982.70 3,761,435.36 9,549,418.06 39.39%
40
0.186322 46,695.15 8,000,000.00 83.09% 5,610,693.12 4,017,132.84 9,627,825.95 41.72%
41
0.183623 47,171.04 8,000,000.00 82.61% 5,475,747.35 4,208,327.54 9,684,074.89 43.46%
42
0.180824 46,103.99 8,000,000.00 83.74% 5,812,679.56 3,740,815.26 9,553,494.82 39.16%
43
0.178125 44,267.64 8,000,000.00 86.01% 6,337,822.04 2,963,397.80 9,301,219.84 31.86%
44
0.175326 40,886.02 8,000,000.00 91.43% 7,035,824.57 1,713,801.20 8,749,625.77 19.59%
45
0.172627 41,157.74 8,000,000.00 90.95% 7,015,663.53 1,780,569.61 8,796,233.14 20.24%
46
0.169928 38,832.24 8,000,000.00 95.58% 7,276,794.47 1,093,060.14 8,369,854.61 13.06%
47
0.167129 38,612.65 8,000,000.00 96.06% 7,307,419.83 1,020,904.38 8,328,324.21 12.26%
48
0.164430 38,318.59 8,000,000.00 96.71% 7,338,091.40 933,962.13 8,272,053.53 11.29%
49
0.161631 39,525.47 8,000,000.00 94.15% 7,285,473.24 1,211,309.59 8,496,782.83 14.26%
50
0.158932 38,583.60 8,000,000.00 96.18% 7,362,030.01 955,873.75 8,317,903.76 11.49%
51
0.156233 38,643.85 8,000,000.00 96.05% 7,379,177.88 950,227.14 8,329,405.02 11.41%
52
0.153434 40,272.42 8,000,000.00 92.73% 7,272,574.72 1,354,611.74 8,627,186.46 15.70%
53
0.150735 40,830.17 8,000,000.00 91.67% 7,226,848.27 1,499,849.70 8,726,697.97 17.19%
54
0.147936 39,694.82 8,000,000.00 93.93% 7,369,088.63 1,148,199.67 8,517,288.30 13.48%
55
0.145237 38,915.39 8,000,000.00 95.60% 7,438,053.04 930,553.38 8,368,606.42 11.12%
56
0.142538 41,082.22 8,000,000.00 91.36% 7,250,947.53 1,505,489.79 8,756,437.32 17.19%
57
0.139739 41,616.19 8,000,000.00 90.40% 7,193,530.98 1,655,963.26 8,849,494.24 18.71%
58
0.137040 41,836.68 8,000,000.00 90.01% 7,181,439.29 1,706,096.46 8,887,535.75 19.20%
59
0.134241 42,012.09 8,000,000.00 89.71% 7,176,061.39 1,741,563.75 8,917,625.14 19.53%
60
0.131542 40,466.99 8,000,000.00 92.63% 7,418,956.29 1,217,433.56 8,636,389.85 14.10%
61
0.128843 40,736.97 8,000,000.00 92.10% 7,414,609.84 1,271,141.03 8,685,750.88 14.63%
62
0.126044 39,546.28 8,000,000.00 94.57% 7,538,195.59 920,781.40 8,458,976.99 10.89%
63
0.123345 40,279.05 8,000,000.00 93.06% 7,509,870.80 1,086,445.39 8,596,316.19 12.64%
64
0.120546 40,739.37 8,000,000.00 92.15% 7,490,496.25 1,190,898.77 8,681,395.01 13.72%
65
0.117847 42,392.03 8,000,000.00 89.22% 7,258,743.53 1,708,352.15 8,967,095.68 19.05%
66
0.115148 43,588.86 8,000,000.00 87.32% 7,013,898.14 2,148,111.95 9,162,010.09 23.45%
67
0.112349 43,567.60 8,000,000.00 87.35% 7,049,665.35 2,108,891.46 9,158,556.81 23.03%
68
0.109650 43,626.12 8,000,000.00 87.26% 7,065,662.19 2,102,371.90 9,168,034.09 22.93%
69
0.106851 44,741.22 8,000,000.00 85.66% 6,772,179.22 2,566,805.17 9,338,984.39 27.48%
70
0.104152 45,115.42 8,000,000.00 85.15% 6,680,491.46 2,714,411.40 9,394,902.86 28.89%
71
0.101453 42,918.04 8,000,000.00 88.67% 7,312,836.35 1,709,580.96 9,022,417.32 18.95%
72
0.098654 41,867.43 8,000,000.00 90.58% 7,539,256.25 1,293,146.11 8,832,402.36 14.64%
73
0.095955 40,668.85 8,000,000.00 92.98% 7,705,063.79 898,498.13 8,603,561.91 10.44%
74
0.093256 40,631.25 8,000,000.00 93.06% 7,739,356.43 856,932.32 8,596,288.75 9.97%
75
0.090457 41,688.24 8,000,000.00 91.01% 7,662,188.43 1,128,522.72 8,790,711.15 12.84%
76
0.087758 43,154.95 8,000,000.00 88.47% 7,427,275.72 1,615,745.70 9,043,021.42 17.87%
77
0.084959 41,995.41 8,000,000.00 90.61% 7,677,251.25 1,151,391.71 8,828,642.96 13.04%
78
0.082260 41,812.97 8,000,000.00 90.97% 7,740,585.59 1,053,954.38 8,794,539.97 11.98%
79
0.079561 42,912.25 8,000,000.00 89.00% 7,593,344.38 1,395,515.04 8,988,859.41 15.52%
80
0.076762 42,526.97 8,000,000.00 89.70% 7,707,805.57 1,210,434.83 8,918,240.40 13.57%
81
0.074063 43,653.62 8,000,000.00 87.80% 7,506,531.71 1,604,968.09 9,111,499.80 17.61%
82
0.071264 45,864.69 8,000,000.00 84.80% 6,783,629.23 2,649,833.53 9,433,462.75 28.09%
83
0.068565 47,227.51 8,000,000.00 83.23% 6,194,608.39 3,416,847.77 9,611,456.17 35.55%
84
0.065866 47,395.89 8,000,000.00 83.04% 6,145,987.81 3,487,469.60 9,633,457.41 36.20%
85
0.063067 48,415.91 8,000,000.00 82.04% 5,624,731.53 4,126,661.48 9,751,393.01 42.32%
86
0.060368 47,678.55 8,000,000.00 82.85% 6,061,606.87 3,594,397.27 9,656,004.14 37.22%
87
0.057569 49,735.24 8,000,000.00 81.23% 4,862,861.12 4,986,211.94 9,849,073.06 50.63%
88
0.054870 48,898.31 8,000,000.00 82.01% 5,388,856.15 4,365,701.99 9,754,558.14 44.76%
89
0.052171 49,739.41 8,000,000.00 81.34% 4,873,634.51 4,961,780.80 9,835,415.32 50.45%
90
0.049372 48,980.93 8,000,000.00 82.06% 5,378,736.20 4,370,796.52 9,749,532.72 44.83%
91
0.046673 49,542.70 8,000,000.00 81.58% 5,024,890.72 4,780,869.17 9,805,759.89 48.76%
92
0.043874 50,421.89 8,000,000.00 80.98% 4,418,529.89 5,460,740.82 9,879,270.70 55.27%
93
0.041175 52,577.99 8,000,000.00 80.22% 2,915,137.37 7,057,958.59 9,973,095.95 70.77%
94
0.038476 53,728.27 8,000,000.00 79.93% 2,168,986.14 7,840,011.09 10,008,997.23 78.33%
95
0.035677 51,935.43 8,000,000.00 81.01% 3,223,990.33 6,651,028.71 9,875,019.04 67.35%
96
0.032978 53,673.09 8,000,000.00 80.70% 2,022,698.07 7,890,195.55 9,912,893.63 79.60%
97
0.030179 54,889.80 8,000,000.00 80.61% 1,307,198.58 8,617,504.59 9,924,703.17 86.83%
98
0.027480 54,464.50 8,000,000.00 80.69% 1,400,177.07 8,513,768.19 9,913,945.26 85.88%
99
0.024781 55,487.86 8,000,000.00 80.70% 852,726.84 9,060,198.46 9,912,925.30 91.40%
100
0.021982 55,462.85 8,000,000.00 80.72% 745,780.90 9,165,397.83 9,911,178.73 92.48%