1 / 32

Welcome - PowerPoint PPT Presentation

  • Updated On :

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.

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
Download Presentation

PowerPoint Slideshow about 'Welcome' - axl

An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Welcome l.jpg

  • 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

Slide2 l.jpg

Let s try it out with a simple question l.jpg
Let’s try it out with a simple question firmly

  • Remember:

  • Look out for thegreen light on the handset which flashes top indicate that your vote has been received

  • No need to hurry….

How did you travel to university today l.jpg
How did you travel to university today? firmly

  • Tube/train

  • Bus

  • Bicycle

  • Walking

  • Other

This slide shows…?

Outline l.jpg
Outline firmly

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

Applications l.jpg
Applications firmly

Week 1

  • Portfolio mean and variance

    Week 2

  • Simple Monte Carlo analysis

    Week 3

  • Revision

Reading l.jpg
Reading firmly

  • Mary Jackson, Mike Staunton Advanced modelling in finance using Excel and VBA Wiley 2001.

    • Chapters 2-3 possibly 4 for more advanced students

Have you used excel before l.jpg
Have you used Excel before? firmly

  • No, not really

  • For elementary operations only

  • For moderately complex calculations

  • Have recorded macros

  • Can program in VBA

What can excel do in finance l.jpg
What can Excel do in Finance? firmly

Examples of problem

1. Demonstrating key concepts

2. Portfolio selection

3. Option pricing

4. Value at risk

5. Non-lognormal returns


1. Simulation of markets:Monte Carlo and bootstrapping

2. Numerical solution of problems with no analytic solution

Advantages and disadvantages of excel l.jpg
Advantages and disadvantages of Excel firmly


  • Wide range of powerful techniques

  • Input and output need little programming

  • Example 1

  • Disadvantages

  • Not a compiled language

  • Slower than specialised mathematical programming languages

Good practice in spreadsheet work l.jpg
Good practice in spreadsheet work firmly

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.

Good practice 2 l.jpg
Good practice (2) firmly

  • 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

How am i getting through am i going l.jpg
How am I getting through? Am I going… firmly

  • Too slow

  • About right

  • Too fast?

How much is new l.jpg
How much is new? firmly

  • All this is new to me

  • Most is new to me

  • Some things are new to me

  • I knew almost all this before

To test your understanding the rand function in excel l.jpg
To test your understanding, the firmlyRand() function in Excel:

  • Creates a normally distributed variable

  • Creates a number entirely at random

  • Replicates the throw of a die

  • Creates a uniform distribution in the range {0,1}

More advanced functions l.jpg
More advanced functions firmly

  • Arrays

  • Frequency

  • Lookup

  • Regression approaches

  • Random number generation

Arrays l.jpg
Arrays firmly

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

Frequency l.jpg
Frequency firmly

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

Lookup l.jpg
Lookup firmly

See Example 4

Regression l.jpg
Regression firmly

Three ways (at least) to do regression:

1. Program all the formulae yourself


2. Use Statistical functions Intercept, slope, RSQ, etc


3. Use Linest Array function


4. Use Data Analysis Add-In, Regression Analysis


Random number functions l.jpg
Random Number Functions firmly

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

Turning rand into a normal distribution l.jpg
Turning Rand() into a normal distribution firmly


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



The Excel command is NormSInv(Prob)

This is a clever trick which can be used with other probability functions whose cumulative function has an inverse

Static random numbers l.jpg
Static random numbers firmly

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

Random number generation l.jpg
Random number generation… firmly

  • Can be done different ways in Excel

  • Needs care in selection for proper research

  • Is helped by the use of an inverse function

  • All of the above

To create an array variable press l.jpg
To create an array variable press… firmly

  • CTRL+ Shift + Del

  • Alt+ Shift+ Enter

  • CTRL +Shift +Enter

Homework l.jpg
Homework firmly

First program the following in Excel:

The expected returns on a portfolio of two assets X1 and X2 with returns R1 andR2 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)


r = covariance /(sqrt of product of variances)

This questions looks l.jpg
This questions looks… firmly

  • Very difficult

  • Challenging but do-able

  • Do-able with some effort

  • Quite easy

  • Wrongly conceived

Homework continued l.jpg
Homework continued firmly

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