COMP2022 Programming for FinTech Applications
Spring 2020
Professor: Dr. Grace Wang
Week III: EXCEL & VBA 2
1
1
Agenda
qFinance-related
§ Monte Carlo Simulation § Portfolio model
qExcel
§ More VBA
§ Data table
§ Charting (TA session)
2
2
1
Monte Carlo
qComputational method using random numbers
qExamples
§ What is the value of p
§ Random wealth and consumption over time
3
3
Wikipedia
4
4
2
Warning
qMonte Carlo is often time-consuming, computationally wasteful § Our first example (computing p) is a good illustration
qAnalytical methods (formulas … ) are better if available qBut, sometimes there’s nothing else
5
5
Computing p: Unit circle inscribed in unit square
1.0
0.5
-1.0 -0.5
-0.5
-1.0
0.5 1.0
qThe square has area 4
qUnit circle has area p
qCircle in upper right-hand quadrant has area p/4
6
6
3
Computing p: Monte Carlo
qStrategy: “Shoot” random point {x,y} at upper-right hand quadrant § Point is inside circle if x2+y2 <1
§ Use Excel function Rand() twice to simulate x and y coordinates
qCount number of points inside circle as ratio of total number of points shot
qThis ratio ≈ p/4
7
7
Examples
Use Excel function rand() to simulate x and y coordinates of random point.
8
8
4
In Excel
4
5
A
Inside circle 23
Pi? 3.066666667 <-- =B3/B2*4
B
C
D
E
1
COMPUTING PI USING MONTE CARLO METHODS INITIAL EXPERIMENT
2
3
Number of data points
30
<-- =COUNT(A:A)
<-- =COUNTIF(D:D,TRUE)
6
1
2
3
Each cell in these columns contains the Excel function =Rand()
7
Experiment
Random1
0.23437
0.95535
Random2
0.03080
0.61267
In unit circle?
8
TRUE
<-- =(B8^2+C8^2<=1)
9
0.92322
FALSE
10
0.25644
TRUE
qColumns B and C have function Rand().
qColumn D uses Boolean function to determine if point is inside unit circle qCell B4 approximates p by
ØComputing ratio of inside/total points ØMultiplying this ratio by 4
9
9
Different experiments give different approximations
1
A
B
COMPUTING PI USING MONTE CARLO METHODS INITIAL EXPERIMENT
C
D
E
2
Number of data points
30
<-- =COUNT(A:A)
3
Inside circle 21
Pi? 2.8 <-- =B3/B2*4
<-- =COUNTIF(D:D,TRUE)
4
5
6
7
8
Experiment
1
Each cell in these columns contains the Excel function =Rand()
Random1
0.88255
Random2
0.06206
In unit circle?
TRUE
<-- =(B8^2+C8^2<=1)
9
TRUE
10
2
3
0.37558
0.81044
0.41664
0.65635
FALSE
4
5
A
Inside circle 24
Pi? 3.2 <-- =B3/B2*4
B
C
D
E
1
COMPUTING PI USING MONTE CARLO METHODS INITIAL EXPERIMENT
2
3
Number of data points
30
<-- =COUNT(A:A)
<-- =COUNTIF(D:D,TRUE)
6
1
2
3
Each cell in these columns contains the Excel function =Rand()
7
Experiment
Random1
0.27544
0.91731
Random2
0.04368
0.52793
In unit circle?
8
TRUE
<-- =(B8^2+C8^2<=1)
9
0.81204
TRUE
10
0.90177
FALSE
10
10
5
Increasing number of experiments improves approximation
1
3
A
B
COMPUTING PI USING MONTE CARLO METHODS INITIAL EXPERIMENT
Inside circle 3240
Pi? 3.142580019 <-- =B3/B2*4
<-- =COUNT(A:A)
C
D
E
2
Number of data points
4124
<-- =COUNTIF(D:D,TRUE)
4
5
6
8
1
Each cell in these columns contains the Excel function =Rand()
7
Experiment
Random1
0.79001
Random2
In unit circle?
0.89301
FALSE
<-- =(B8^2+C8^2<=1)
9
10
2
3
4
0.24801
0.28735
0.63139
0.07722
0.06308
TRUE
TRUE
11
0.59986
TRUE
Here we’ve done 4,124 experiments. Accuracy much improved!
11
11
Better done with VBA
In 34 seconds VBA does 50 million simulations
1
A
B
C
COMPUTING PI USING VBA
2
Number of data points 50,000,000
Pi? 3.14162752 <-- This cell called "Estimate"
<-- This cell called "Number"
3
4
5
6
7
8
StartTime
StopTime
Elapsed
12:13:33
12:14:07
0:00:34
<-- This cell called "StartTime"
<-- This cell called "StopTime"
<-- =Stoptime-StartTime
9
10
11
12
Note
[Ctrl]+a runs the macro "MonteCarlo"
13
[Ctrl]+q runs the macro "MonteCarloTime" which also records the time
[Ctrl]+t runs the macro "MonteCarloTimeRecord" which records the
results as they are generated. For large number of points, this takes a 14 very long time!
12
12
6
This is NOT a good way to estimate p qRamanujan’s formula gets 15-digit accuracy in only two iterations (Indian
mathematician Srinivasan Ramanujan (1887-1920) ) 1 8 ¥ (4n)!(1103+26390n)
4
p = 9801å 3964n
n=0 (n!)
qRamanujan has an even faster formula
§ See wonderful article http://www.cecm.sfu.ca/organics/papers/borwein/index.html
§ Read a biography of Ramanujan:
The Man Who Knew Infinity: A Life of the Genius Ramanujan by Robert Kanigel
§ Expand your horizons
13
13
VBA routine
E
3
Sub MonteCarlo()
4
n = Range("Number")
5
Hits = 0
6
For Index = 1 To n
7
If Rnd ^ 2 + Rnd ^ 2 < 1 Then Hits = Hits + 1
8
Next Index
9
Range("Estimate") = 4 * Hits / n
10
End Sub
qVBA uses cell names Number and Estimate (cells B2 and B3)
qNote that the random number generator in VBA is Rnd; same as Rand() in Excel
14
14
7
One more Monte Carlo simulation
qYou are invested in a combination of risky and riskless asset
qYou want to draw down an annual sum and still have money left over qCan you do it?
qApplications:
§ Retirement strategies
§ Endowment management
15
15
Retirement simulation
qYou have $1,000,000 at age 65
qWant to withdraw $150,000 at end of each year for next 10
years
qMoney is invested in risky asset and risk-free asset § Risky has mean annual return μ = 8% and s = 20%
§ Risk-free rate = 6%
qIf you invest 70% in risky and 30% in risk free, will you have positive wealth at end of 10 years?
16
16
8
Simulation strategy
qWealth at end of each year is sum of:
§ Value of risky investment
Initial wealth * 70% * exp[μ+s*Z] , where Z is a draw from standard normal distribution
üZ is simulated with Excel function NormSInv(Rand()) § Value of riskless investment
Initial wealth * 30% * exp[rf]
§ From this sum subtract annual draw of $150,000
17
17
Retirement simulation
19
A
3
4
8
10
B
772,985
600,918
201,485
66,195
C
541,090
420,643
46,337
D
231,896
180,276
19,859
E
0.2468
-0.1490
-0.4724
-0.0254
F
1.1381
1.0515
1.0778
G
606,022
83,412
H
150,000
I
1
2
Current wealth
1,000,000
PLANNING YOUR RETIREMENT
3
Riskfree rate
6%
4
6
10
13
Parameters of risky investment
5
Expected annual return
Standard deviation of return
Year
1
1,000,000
8%
20%
7
8
Proportion invested in risky 70% Annual drawdown 150,000
9
Wealth at beginning of year
Invested in risky
700,000
Invested in bonds
300,000
Random number, normally distributed
-1.2459
1+return on risky investment
0.8444
Wealth at end of year
909,604
Drawdown
Left at end of 10 years
11
150,000
12
2
759,604
531,723
227,881
0.8373
1.2808
922,985
862,046
150,000
14
712,046
498,432
213,614
750,918
150,000
15
150,000
16
17
5
6
456,022
319,215
136,807
-1.1769
0.9856
0.8561
418,543
150,000
18
7
268,543
187,980
141,040
80,563
60,446
1.3347
1.4147
351,485
150,000
216,195
150,000
150,000
20
9
-66,588
-46,611
-19,976
1.0822
-0.9549
1.3451
0.8950
-62,927
150,000 -212,927
21
22
Normally-distributed random numbers generated by =NORMSINV(RAND())
=C20*F20+D20*EXP($B$3)
23
24
26
Wealth at beginning of year =G19-H19
1+return on risky investment =EXP($B$5+$B$6*E20)
25
27
Investment in risky asset =B20*$B$7
18
18
9
100 simulations
A
B
C
D
E
F
G
H
I
J
1
THIS IS 100 SIMULATIONS
2
Initial pension money
1,000,000
3
Percent invested in risky asset
60%
4
Percent in riskless
40%
5
Riskless interest rate
4%
Cell D:14 contains formula =D13*($B$3*EXP(mu+sigma*NORMSINV(RAN D()))+$B$4*EXP(riskfree))-draw
6
Risky asset mean return
12%
7
Risky asset sigma
30%
8
Annual draw
150,000
9
Prob of ending > 0
53%
<-- =COUNTIF(B23:CW23,">0″)/100
10
11
Experiment
1
2
3
4
5
6
7
8
9
12
Year
End-year cash
13
0
1,000,000
1,000,000
1,000,000
1,000,000
1,000,000
1,000,000
1,000,000
1,000,000
1,000,000
14
1
1,284,691
973,810
880,108
842,633
1,023,932
1,136,845
966,305
1,039,363
749,577
15
2
991,723
1,070,729
1,010,202
1,353,411
897,097
1,190,530
1,228,205
900,988
462,381
16
3
1,109,200
1,047,293
1,054,500
1,160,359
817,748
1,471,398
1,591,855
563,572
504,556
17
4
979,899
1,143,861
1,191,999
1,051,623
1,075,436
1,080,554
1,943,674
463,740
407,396
18
5
1,097,456
1,867,889
1,020,197
761,775
1,401,917
795,271
1,865,281
444,431
236,792
19
6
702,092
1,959,645
1,336,467
614,017
1,379,433
787,468
1,546,223
304,111
88,082
20
7
614,245
2,681,479
1,349,789
582,462
1,180,372
684,239
1,922,739
113,335
-47,447
21
8
500,352
1,980,347
991,949
672,588
1,273,185
491,172
2,452,886
-1,630
-209,745
22
9
466,171
2,415,202
726,830
430,377
978,177
334,516
2,274,070
-151,710
-381,150
23
10
550,586
1,701,000
894,870
306,099
726,845
304,778
2,293,821
-322,958
-543,943
19
19
Portfolio models
20
20
10
Investment Opportunity Set
a) Opportunity set with two risky assets
We construct a portfolio with the following two assets;
μ=wμ+wμ p1122
s2 =w2s2 +w2s2 +2wws
p 11 22 1212
E(R) σ(R)
Asset 1 15% 40%
Asset 2 5% 20%
ρ12 0.0
6-21
21
Several selected portfolios
Portfolio
A -0.2
B 0.0
C 1/6
D* 1/5
E 1/3
F 1/2
G 2/3
H 5/6
I 1.0
H 1.2
σ(Rp) (%)
25.3
20.0
17.9 17.89 18.8 22.4 27.6 33.4 40.0
48.2
w1 w2 E(Rp) (%)
1.2 3.0
1.0 5.0 5/6 6.7 4/5 7.0 2/3 8.3 1/2 10.0 1/3 11.7 1/6 13.3 0.0 15.0
-0.2 17.0
6-22
22
11
Basic example: WMT & TGT
A
B
C
D
E
F
G
1
PRICE AND RETURN DATA FOR WALMART (WMT)
AND TARGET (TGT)
Yahoo’s closing price adjusts for dividends
2
Prices
Returns
3
Date
WMT
TGT
WMT
TGT
4
5-Jul-01
26.07
37.40
5
1-Aug-01
22.00
33.53
-16.97%
-10.92%
<-- =LN(C5/C4)
6
4-Sep-01
20.07
30.73
-9.18%
-8.72%
<-- =LN(C6/C5)
59
1-Feb-06
58.46
54.29
4.96%
-0.46%
60
1-Mar-06
60.23
51.90
2.98%
-4.50%
61
3-Apr-06
65.46
52.99
8.33%
2.08%
62
1-May-06
60.84
48.92
-7.32%
-7.99%
63
1-Jun-06
67.51
48.87
10.40%
-0.10%
64
3-Jul-06
67.65
49.17
0.21%
0.61%
65
66
WMT
TGT
67
Monthly mean
1.59%
0.46%
<-- =AVERAGE(F5:F64)
68
Monthly variance
0.0093
0.0052
<-- =VARP(F5:F64)
69
Monthly standard deviation
9.63%
7.19%
<-- =STDEVP(F5:F64)
70
71
Annual mean
19.07%
5.47%
<-- =12*F67
72
Annual variance
0.1114
0.0620
<-- =12*F68
73
Annual standard deviation
33.37%
24.90%
<-- =SQRT(F72)
74
75
Covariance
0.0038
<-- =COVAR(E5:E64,F5:F64)
76
Correlation
0.5484
<-- =CORREL(E5:E64,F5:F64)
23
23
4
5
Variance
Standard deviation
A
B
0.93%
9.63%
C
0.52%
D
E
F
1
CALCULATING THE MEAN AND STANDARD DEVIATION OF A PORTFOLIO
2
3
Asset returns
Mean return
WMT
1.59%
TGT
0.46%
7.19%
6
7
Covariance
0.0038
8
9
Proportion of WMT 0.5 <-- In the data table below this is varied from -0.5 to 1.5
10
Portfolio mean return
1.02%
<-- =B8*B3+(1-B8)*C3
11
12
Portfolio return variance
Portfolio return standard deviation
0.0036
6.01%
<-- =B8^2*B4+(1-B8)^2*C4+2*B8*(1-B8)*B7
<-- =SQRT(B11)
Portfolioexpected E(r)=x E(r )+x E(r )
p WMT WMT TGT TGT
return
Portfolio variance s 2 p
NotethatxTGT =1-xWMT
= w2 s 2 + w2 s 2 WMT WMT TGT TGT
+2*w *w Cov(r ,r ) WMT TGT WMT TGT
24
24
12
A
B
C
D
E
F
G
14
Data table: Varying the proportion of WMT
15
Portfolio standard deviation
Porfolio mean return
16
Proportion of WMT
6.01%
1.02%
<-- =B10, Table header
17
-0.5
11.81%
-0.11%
2.5%
2.0%
1.5%
1.0%
0.5%
Portfolio Mean and Standard Deviation Varying the Proportions of WMT and TGT
0.0%
0% 2% 4% 6% 8% 10% 12% 14% 16%
-0.5%
Standard deviation
18
-0.4
10.77%
0.00%
19
-0.3
9.78%
0.12%
20
-0.2
8.84%
0.23%
21
-0.1
7.96%
0.34%
22
0
7.19%
0.46%
23
0.1
6.54%
0.57%
24
0.2
6.06%
0.68%
25
0.3
5.80%
0.80%
26
0.4
5.78%
0.91%
27
0.5
6.01%
1.02%
28
0.6
6.46%
1.14%
29
0.7
7.08%
1.25%
30
0.8
7.84%
1.36%
31
0.9
8.70%
1.48%
32
1
9.63%
1.59%
33
1.1
10.62%
1.70%
34
1.2
11.65%
1.82%
35
1.3
12.71%
1.93%
36
1.4
13.79%
2.04%
37
1.5
14.89%
2.16%
You need to know how to use data table
25
25
Investment opportunity line (with 2 risky assets)
μ
E D*
C
B
A
IJ G
H F
σ
26
26
13
Mean return
Special cases of investment opportunity line
æμ -μ ö μs -μs μp = ç 1 2 ÷sp + 2 1 1 2
i)whenρ =+1 12
Þ
ès1 -s2 ø s =ws +ws
s1 -s2
μ
Asset 2
p1122
Asset 1
σ
27
27
ii) when ρ12 = -1
the riskless portfolio
μ
Asset 1
Asset 2
σ
28
28
14
μ
ρ= -1
-1<ρ<1
ρ=1
Asset 2
Asset 1
σ
q The smaller the correlation, the greater the risk reduction potential.
q If ρ = +1, no risk reduction is possible q If ρ = -1, a riskless portfolio is possible.
29
29
Finding the Minimum Variance Portfolio
With two risky assets
¶s2 p=0Þ
¶w1 Example:
Asset 1
15%
40% ρ12 0.0
w1* =
What if ρ = -1, 0.5, -0.5?
Asset 2
5% 20%
E(R) σ(R)
6-30
30
15
N risky assets.
Portfolio means & variances Case of N assets
Vector of expected returns: éE(r)ù ê1ú
Variance-covariance matrix:
E ( r ) = ê E ( r2 ) ú ê!ú
êE(r )ú ëNû
és11 s12 ! s1N ù ês s ! s ú
S=ê 21 22 2Nú ê"ú
ês s ! s ú ë N1 N2 NNû
31
31
A portfolio of risky assets is a set of proportions xi which sum to 1. é x1 ù
êxú
x=ê 2ú,åx=1
ëNû
The portfolio expected return is:
ê!ú i=1
i êx ú
N
T2 E(r)=x ×R=[x x ! x ]êE(r)ú
x12Nê"ú
=
åN
i=1
x E (r ) ii
éE(r)ù ê1ú
êE(r )ú ëNû
32
32
16
The portfolio variance
s2 =xTSx x
és s !sùéxù ê 11 12 1N úê 1 ú
ês s !súêxú
=[xx!x]ês21 s22 !s2Núêx2ú 12Nê"úê"ú
NN
= ååx x s
i j ij i=1 j=1
åN NN
åå i=1 i=1 j=i+1
222 s=xs+2xxs
x
ii
i j ij
ë N1 N2
NNûë Nû
33
33
The covariance between two portfolios x and y s =Cov(r,r)=xTSy=yTSx
és11 s12 ! s1Nùéy1ù ês s !súêyú
xy xy
=[x x !x]ê21 22 N2úê2ú 12Nê"úê"ú
NN
= ååx y s
i j ij i=1 j=1
ês s !súêyú
ë N2 N2
NNûë Nû
34
34
17
A
B
C
D
E
F
G
1
A FOUR-ASSET PORTFOLIO PROBLEM
2
Variance-covariance, S
Mean returns E(r)
3
0.10
0.01
0.03
0.05
6%
4
0.01
0.30
0.06
-0.04
8%
5
0.03
0.06
0.40
0.02
10%
6
0.05
-0.04
0.02
0.50
15%
7
8
Portfolio X
0.2
0.3
0.4
0.1
9
Portfolio Y
0.2
0.1
0.1
0.6
10
11
Portfolio X and Y statistics: Mean, variance, covariance, correlation
12
Mean, E(rX)
9.10%
Mean, E(rY)
12.00%
<-- =MMULT(B9:E9,$F$3:$F$6)
13
Variance, sX2
0.1216
Variance, sY2
0.2034
<-- {=MMULT(B9:E9,MMULT(A3:D6,TRANSPOSE(B9:E9)))}
14
Covariance(X,Y)
0.0714
<-- {=MMULT(B8:E8,MMULT(A3:D6,TRANSPOSE(B9:E9)))}
15
Correlation, rXY
0.4540
<-- =B14/SQRT(B13*E13)
16
17
Calculating returns of combinations of Portfolio x and Portfolio y
18
Proportion of Portfolio x
0.3
19
Mean return, E(rp)
11.13%
<-- =B18*B12+(1-B18)*E12
20
Variance of return, sp2
14.06%
<-- =B18^2*B13+(1-B18)^2*E13+2*B18*(1-B18)*B14
21
Stand. dev. of return, sp
37.50%
<-- =SQRT(B20)
qTo do the multi-asset computations, use Excel array functions Mmult and Transpose .
35
35
Opportunity set with many risky assets
μ
Individual Assets
s
36
36
18
Efficient portfolios
11%
10%
9%
8%
7%
6%
5%
4%
10% 20%
Efficient and envelope
Envelope, not efficient
30%
Infeasible portfolio
40% 50%
Feasible, not efficient
60% 70%
80% 90%
Feasible Portfolios
Portfolio standard deviation
37
37
q Efficient portfolio μ
- a portfolio whose return is higher than any other portfolio’s return at a given risk and a portfolio whose risk is lower than any other portfolio’s risk at a given return
q Efficient Frontier (with risky assets only): the set of all efficient portfolios
q The efficient frontier is the set of all convex combinations of any two efficient portfolios.
• Global MVP
σ
38
38
19
Portfolio mean return
References
qhttps://www.excel-easy.com/ qhttps://www.excelfunctions.net qFinancial Modeling, by Simon Benninga
39
39
What is following-up
qWeek 4 (Mar. 6) § R
§ Review Excel if needed
qWeek 5 (Mar. 13) §R
§ Excel Quiz
qExcel
§ Excel sheet operation § Data table
§ Charting
§ VBA programming
40
40
20