# 2013/2014  KAN-CCMVV1616U  Financial Models in Excel (Quarter version)

 English Title Financial Models in Excel (Quarter version)

# Course information

Language English
Exam ECTS 7.5 ECTS
Type Elective
Level Full Degree Master
Duration One Quarter
Course period Autumn, First Quarter
Chages in course schedule may occur
Monday 15.20-18.50, 36
Monday 09.50-13.20, week 37,40, 41, 42
Monday 08.00-11.30, week 38, 39
Monday 09.50-14.15, week 43
Time Table Please see course schedule at e-Campus
Study board
Course coordinator
• Claus Parum - Department of Finance (FI)
Teacher: Peter Raahauge - pr.fi@cbs.dk
• Finance
Last updated on 07-04-2014
Learning objectives
Besides gaining a deeper understanding of the relevant financial theory as well as improving their Excel and programming skills, the students should be able to solve in Excel problems within the topics dealt with during the course at a level similar to the exercises solved.

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
Course prerequisites
The course is oriented towards a second year master student (at CBS) with the following background:

1. Master course in portfolio theory,
2. Master course in bond and option analysis
3. Undergraduate course in math (matrix algebra and optimization) and statistics. 4. Very basic Excel-skills.
Examination
 Financial Models in Excel, (Quarter version): Examination form Written sit-in exam Individual or group exam Individual Assignment type Written assignment Duration 4 hours Grading scale 7-step scale Examiner(s) One internal examiner Exam period Autumn Term Aids allowed to bring to the exam Limited aids, see the list below and the exam plan/guidelines for further information: Books and compendia brought by the examineeNotes brought by the examineeAllowed calculatorsAllowed dictionaries Make-up exam/re-exam Same examination form as the ordinary exam If the number of registered candidates for the make-up examination/re-take examination warrants that it may most appropriately be held as an oral examination, the programme office will inform the students that the make-up examination/re-take examination will be held as an oral examination instead. Description of the exam procedure PC exam on CBS computers without print. It is not allowed to bring your own PC and printer. Hand in on CD-ROM provided by CBS. No access to the internet and LEARN. Access to personal S:/drive. Before the exam starts information can also be uploaded from a USB-key to PC, then the USB-Key should be put away during exam.
Course content and structure

The aim of the course is to provide capabilities of practical implementation of financial theory using real world data.

The course is very exercises based, and the main workload consists of solving exercises in Excel for each of the 14 topics dealt with during the course.

The course provide lectures online as screencasts and physical lessons. The former will assist the students understanding and ability to solve the exercises. The latter will be driven by problems and questions raised by the students.

The 14 topics/exercises dealt with in the course are as follows:

1. Introduction to Excel (Names, array functions, matrix/vector calculation, the Excel-solver, regression analysis etc.)
2. Datastream, mean values and standard deviations of stock portfolios
3. Portfolios at the efficient frontier
4. The eff. frontier without short-sale and empirical test of CAPM
5. Performance evaluation of investment funds.
6. Black-Scholes and implied volatility
7. Volatility predictions (Moving average, ARCH, GARCH)
8. European, American, and Bermuda options in binomial grids
9. European and Asian option prices based on Monte Carlo
10. Portfolio choice under parameter uncertainty
11. Bonds, duration, and immunization strategies
12. Term structure estimation and advanced immunization
13. Term structure estimation, cubic spline
14. Interest rates in binomial grids and callable bond prices
Teaching methods
The course is very exercises based, and the main workload consists of solving exercises in Excel for each of the 14 topics dealt with during the course.

The course provide lectures online as screencasts and physical lessons. The former will assist the students understanding and ability to solve the exercises. The latter will be driven by problems and questions raised by the students.