Transcript Slide 1

Welcome
• You have been given a handset as you
entered
• Please don’t press any buttons yet! You
may accidentally stop it working.
• At the end of the session leave the
handset with the attendant at each exit
- Please don’t steal any – they are of no use
without the rest of the system
1
• When you are prompted to
answer a question press the
button firmly
• As you vote check for the
green light
– If it flashes green your vote
counts
– If it doesn’t, try again
• One vote per handset
– only your most recent answer
will count
2
Let’s try it out with a simple
question
• Remember:
• Look out for the green light on the
handset which flashes top indicate that
your vote has been received
• No need to hurry….
3
How did you travel to university
today?
Tube/train
Bus
Bicycle
Walking
Other
62%
23%
15%
th
e
al
k
O
in
g
e
B
ic
y
cl
us
B
/tr
ai
n
be
Tu
0%
r
0%
This slide shows…?
W
1.
2.
3.
4.
5.
4
Excel for advanced
calculations
John Cubbin
5
Outline
Week 1
• Excel from basics to advanced functions
Week 2
• Excel add-ins and introductory
programming ideas, recording macros
Week 3
• Amending recorded macros using VBA
programming language
6
Applications
Week 1
• Portfolio mean and variance
Week 2
• Simple Monte Carlo analysis
Week 3
• Revision
7
Reading
• Mary Jackson, Mike Staunton Advanced
modelling in finance using Excel and VBA
Wiley 2001.
– Chapters 2-3 possibly 4 for more advanced
students
8
Have you used Excel before?
1. No, not really
2. For elementary
operations only
3. For moderately
complex calculations
4. Have recorded
macros
5. Can program in VBA
58%
33%
8%
0%
Fo
Fo
re
le
m
en
N
o,
no
t
re
a
lly
ta
ry
rm
op
od
er
er
a.
at
..
el
H
av
y
co
e
re
m
co
p.
..
rd
e
C
d
an
m
ac
pr
ro
og
s
ra
m
in
VB
A
0%
9
Have you worked in the finance or
economics area before?
1. Yes
2. No
3. Not sure
8%
31%
62%
10
Yes
No
Not sure
What can Excel do in Finance?
Examples of problem
1. Demonstrating key concepts
2. Portfolio selection
3. Option pricing
4. Value at risk
5. Non-lognormal returns
Methodologies
1. Simulation of markets:
Monte Carlo and bootstrapping
2. Numerical solution of problems with no analytic
solution
11
Advantages and disadvantages of
Excel
Advantages
• Wide range of powerful techniques
• Input and output need little programming
• Example 1
Disadvantages
• Not a compiled language
•Slower than specialised mathematical programming
languages
12
Good practice in spreadsheet work
Other people (and yourself later) may need to
work out what on earth is going on! It is not easy
to know. Example 2
• Transparency
– Make it clear what is going on
– Group obvious things together
– Keep background stuff out of the way
• Documentation
–Where it is not self evident, add labels, comments etc.
13
Good practice (2)
• Auditing –necessary to ensure accuracy
– Make use of check sums and conditional formatting to
trap errors
– Use the formula auditing tool box to make sure your
intentions have been fulfilled
– Do a series of calculations where you know the
correct answer to make sure you are returning sensible
results
– In important applications, get another person to audit
and comment on your work
14
How am I getting through? Am I
going…
1. Too slow
2. About right
3. Too fast?
92%
8%
t?
fa
s
o
To
bo
A
To
o
sl
ow
ut
rig
ht
0%
15
How much is new?
1. All this is new to
me
2. Most is new to me
3. Some things are
new to me
4. I knew almost all
this before
42%
33%
17%
ll
th
is
ta
al
m
os
in
gs
Ik
ne
w
th
m
e
...
..
w
ne
ar
e
ne
s
M
os
ti
So
A
ll
t
hi
s
is
ne
w
w
to
to
m
e
m
e
8%
16
To test your understanding, the
Rand() function in Excel:
1. Creates a normally
distributed variable
2. Creates a number
entirely at random
3. Replicates the throw of
a die
4. Creates a uniform
distribution in the range
{0,1}
62%
38%
th
e
di
s.
..
un
ifo
rm
th
ro
w
ti.
..
a
te
s
re
a
C
R
te
s
re
a
C
ep
a
lic
at
es
nu
m
be
r
en
di
...
no
rm
al
ly
a
te
s
re
a
C
0%
..
0%
17
Section 2: More advanced functions
18
More advanced functions
•
•
•
•
•
Arrays
Frequency
Lookup
Regression approaches
Random number generation
19
Arrays
1xN, NxN, Nx1, NxM
Each array can be given a name as follows:
Select a range
From the menu select Insert…, Name, Define
If there is a label at the top or side of the array this will be
the default name
20
Frequency
Counts the cells with a given frequency
Format is Frequency( data array, bins array)
The key to using formulae covering a whole
array is to press CTRL +Shift+ Enter instead of
Enter when you have entered the formula.
See Example 3
21
Lookup
Value
Result
10
Fail
20
Fail
50
Pass
60
Merit
70
Distinction
See Example 4
22
Regression
Three ways (at least) to do regression:
1. Program all the formulae yourself Dynamic
2. Use Statistical functions Intercept, slope,
RSQ, etc
Dynamic
3. Use Linest Array function
Dynamic
4. Use Data Analysis Add-In, Regression
Analysis
Static
23
Random Number Functions
Much literature on random numbers
- In real life applications, make sure you get a good one with
long cycle time
- For teaching purposes, Excel functions work fine
- You have already come across Rand(), which gives an
outcome in the range {0,1}. This is dynamic
- This can be used to create other random distributions
24
Turning Rand() into a normal
distribution
1
Cumulative
probability F
The inverse of F does the
opposite
F converts a Standard
Normal distributed
variable into a variable
in the range {0,1}
We can use this fact to
convert a uniform random
variable into a normally
distributed variable
0
The Excel command is
NormSInv(Prob)
Z
This is a clever trick which can be used
with other probability functions whose
cumulative function has an inverse
25
Static random numbers
In Tools…Data analysis…
You can generate number of different
distributions.
However these are generated just once and
not recalculated.
Rand() is recalculated every time.
To stop this happening you can do Copy...
Paste Special…Values
26
Random number generation…
1. Can be done different
ways in Excel
2. Needs care in
selection for proper
research
3. Is helped by the use
of an inverse function
4. All of the above
67%
22%
11%
ov
e
ab
ft
he
A
d
Is
he
lp
e
ll
o
th
e
by
in
re
ca
ds
ee
N
..
le
c
se
er
e
di
ff
ne
do
be
an
C
us
e
ti.
.
n.
..
0%
27
To create an array variable press…
1. CTRL+ Shift + Del
2. Alt+ Shift+ Enter
3. CTRL +Shift
+Enter
100%
te
r
+S
hi
ft
TR
L
C
A
lt+
Sh
ift
+
+E
n
En
t
De
l
+
Sh
ift
TR
L+
C
0%
er
0%
28
Homework
First program the following in Excel:
The expected returns on a portfolio of two assets X1 and X2 with
returns R1 and R2 is
RE = w1R1 + w2R2 ; In a specific case R1 =10% R2=3%
SD1 = 0.15
SD2 = 0.02
Correlation coefficient r (X1X2) = 0.1
Work out the portfolio that would be required to earn an expected return of
7.2%.
What is the standard deviation of this portfolio’s returns?
Recall that the variance of a weighted average is
w12Var(X1) + w22Var(X2) -w1w2Cov(X1X2)
and
r = covariance /(sqrt of
product of variances)
29
This questions looks…
1. Very difficult
2. Challenging but
do-able
3. Do-able with
some effort
4. Quite easy
5. Wrongly
conceived
64%
18%
9% 9%
d
ei
ve
ro
ng
l
y
co
nc
ui
te
Q
W
w
le
ea
sy
ef
fo
rt
e
le
ith
so
m
td
oab
bu
in
g
oab
D
C
ha
lle
ng
Ve
ry
di
ffi
c
ul
t
0%
30
Homework continued
Next be prepared to discuss the
following questions:
• How can you be sure this is the correct
answer?
• How would you generalise this spreadsheet for
multiple assets?
You may work in groups of 2 or 3
31
Please remember to return Handsets
32