English   Danish

2011/2012  KAN-CM_F65  Financial Models in Excel (Quarter version)

English Title
Financial Models in Excel (Quarter version)

Course Information

Language English
Point 7,5 ECTS (225 SAT)
Type Elective
Level Full Degree Master
Duration One Quarter
Course Period Autumn . First Quarter
Please see e-campus
Time Table Please see course schedule at e-Campus
Study Board
Study Board for MSc in Economics and Business Administration
Course Coordinator
  • Peter Raahauge - Department of Finance
Administration: Louise Bruun Christensen - lbc.fi@cbs.dk
Main Category of the Course
  • Finance
Last updated on 29 maj 2012
Learning Objectives
At the exam, the student should be able to recognize and to implement in Excel the right method for solving problems similar to the ones dealt with during the course. These problems include:
• Using historical price series to draw inference and form portfolios with or without restrictions on volatility, expected return, and/or portfolio weights
• Evaluating investment performance
• Testing the CAPM using simple linear regressions
• Pricing options with closed form solutions (European Options)
• Pricing options with binomial grids
• Pricing options with Monte Carlo methods
• Determine implied volatility from option prices
• Predicting volatility with moving average, exponentially weighted moving average, and GARCH models
• Estimate exponentially weighted moving average models and GARCH models using the root mean squared error criteria and testing parameter values using maximum likelihood
• Simulating and evaluate portfolio strategies with Monte Carlo methods
• Form immunization strategies for bond portfolios with respect to parallel shifts
• Evaluate the effectiveness of immunization strategies with respect to parallel and other simple shifts in the term structure
• Estimate the parameters of a parametric term structure approximation, including cubic splines.
• Price callable bonds in binomial interest rate grids calibrated to the term structure
Also, for a good performance at the exam, the student must display a high level of command of methods used during the course by modifying the relevant methods to deal with problems slightly different from the problems dealt with during the course.
Prerequisite
The course is oriented towards a second year master student (at CBS) with the following background: Master course in portfolio theory, master course in bond and option analysis, undergraduate course in math (matrix algebra and optimization), and an undergraduate course in statistics. Basic Excel-skills are required.
Examination
4 hour individual open book computer exam
Financial Models in Excel, (Quarter version):
Assessment Written Exam
Marking Scale 7-step scale
Censorship No censorship
Exam Period Autumn Term
Aids Open Book, Written and Electronic Aid is permitted
Duration 4 Hours

Course Content

The aim of the course is to provide capabilities of practical implementation of financial theory using real world data. Moreover, the course participants will gain a deeper understanding of the financial theory as well as improve their Excel and programming skills.

Financial topics:

  1. Stock portfolio analysis, US data, (The efficient frontier with or without short-sale, testing the CAPM, evaluating portfolio strategies with Monte Carlo).
  2. Performance evaluation of Danish investment funds.
  3. Options pricing using, US data, (Black-Scholes pricing, implied volatility, binomial grids for European, American, and Bermuda options, Monte Carlo methods for exotic options).
  4. Volatility prediction models, US stock data, (Moving average, ARCH, GARCH).
  5. Bond analysis, Danish data, (Immunization strategies, term structure estimation, callable bond pricing in binomial grids).

Spreadsheet topics:

  1. Normal spreadsheet skills (Names, array functions, matrix/vector calculation, the Excel-solver, regression analysis etc.),
  2. Introduction to Visual Basic Application function programming.

Online import of empirical data to Excel via the CBS-connection to Datastream.

Teaching Methods
The course is practically oriented. For each of the 14 topics dealt with, there will be Excel-exercises to solve using empirical data. Guiding solutions will be available.
For each of the 14 topics, there will be approx. 2 hours of lectures introducing or reviewing the financial theory and spreadsheet techniques necessary for solving the exercises. These lectures will be available as online-lectures via the internet.
For discussions, problem solving, and individual guidance, 2x15 hours are scheduled.
Further Information

The computational methods used in Financial Models in Excel supplement introductory courses in portfolio theory, fixed income, option pricing. Hence, the course provides a stronger starting point for understanding and working with advanced issues in courses like, for instance, Asset Allocation.

Literature

Benninga, S., "Financial Modelling, using Excel", 3rd edition, 2009, MIT-press.

Notes and exercises