LUBS5011M – Applied Finance

LUBS5011M – Applied Finance

University of Leeds | Leeds University Business School

🎯 Course Overview

This module provides students with practical skills in applying quantitative techniques to financial problems using Microsoft Excel. The course covers financial arithmetic, time value of money, portfolio theory, and the Capital Asset Pricing Model (CAPM). Students will learn to calculate stock returns, construct stock indices, analyze return distributions, and evaluate investment performance using real-world financial data.

📅 Weekly Lecture Themes & Content Focus

  • Week 1: Introduction and Use of MS-Excel
    • Excel basics: formulas, functions, and graphing
    • Relative vs. absolute cell references
    • Data visualization techniques
  • Week 2: Financial Arithmetic
    • Calculating stock returns (simple and log returns)
    • Adjusting for dividends and stock splits
    • Constructing stock indices (price-weighted, value-weighted, equally-weighted)
  • Week 3: Time Value of Money
    • Present and future value calculations
    • Excel functions: PV, FV, NPV, PMT, NPER
    • Goal Seek for financial problem solving
  • Week 4: Project Appraisal
    • Net Present Value (NPV) and Internal Rate of Return (IRR)
    • VLOOKUP for financial data analysis
    • Cash flow organization and presentation
  • Week 5: Return Distribution
    • Descriptive statistics of returns
    • Skewness and kurtosis analysis
    • Covariance and correlation calculations
  • Week 6: Portfolio Theory I
    • Principles of diversification
    • Portfolio mean and variance calculations
    • Efficient frontier construction
  • Week 7: Portfolio Theory II
    • Capital Market Line
    • Sharpe ratio and market portfolio
    • Security Market Line
  • Week 8: Performance Comparison
    • Application of t-tests in Excel
    • Hypothesis testing for financial data
    • Performance evaluation metrics
  • Week 9: Capital Asset Pricing Model (CAPM)
    • Estimating alpha and beta
    • Regression analysis in Excel
    • Evaluating investment performance with CAPM
  • Week 10: Revision
    • Comprehensive review of key concepts
    • Assignment preparation and guidance
    • Open Q&A session

📚 Key Readings

  • Core Textbook: Benninga, S. (2011). Principles of Finance with Excel (2nd ed.) – Chapters 8,9,12
  • Additional Readings:
    • Bodie, Z., Kane, A., & Marcus, A. (2014). Investments (10th ed.) – Chapters 1-4,9
    • Fama-French Research Data Factors

📝 Assessment

The module is assessed through a 100% coursework assignment consisting of two parts:

  • Part I: Excel Calculations (40%)
    • Stock return calculations and adjustments
    • Basic statistics of returns
    • Portfolio analysis and CAPM implementation
  • Part II: Written Report (60%, 2000 words)
    • Comparison of stock performances
    • Application of t-tests
    • CAPM analysis with different sample periods

Evaluation Criteria: Accuracy of calculations, appropriate use of Excel functions, critical analysis, and presentation quality.

🔍 Assignment Preparation Guide

Key Skills to Master

  • Data Retrieval: Downloading and organizing stock price data from Yahoo Finance
  • Financial Calculations: Simple returns, log returns, compounded annual gains
  • Statistical Analysis: Mean, variance, standard deviation, skewness, kurtosis
  • Portfolio Construction: Efficient frontier, optimal risky portfolio, Capital Market Line
  • Regression Analysis: Estimating beta and alpha, interpreting results

Report Structure Tips

  1. Introduction: Overview of the two stocks being analyzed
  2. Performance Analysis: Descriptive statistics and graphical representations
  3. Comparative Analysis: t-tests results and interpretation
  4. CAPM Analysis: Beta and alpha estimates, performance evaluation
  5. Conclusion: Summary of findings and investment implications

📝 Mock Assignment Brief

Part I: Excel Calculations

1. Calculate simple returns for two given stocks from 2000-2021

Format negative returns in blue and positive returns in red. Create hyperlinks to your data worksheets.

2. Calculate and compare the following for both stocks:

  • Overall buy-and-hold returns (2000-2021)
  • Compounded annual gains
  • Years when Stock A outperformed Stock B

3. Construct the market frontier assuming only these two stocks exist

Assume a risk-free rate of 5%. Identify the optimal risky portfolio and draw the Capital Market Line.

4. For monthly data (2000-2021):

  • Calculate basic statistics of excess returns (mean, median, min, max, etc.)
  • Identify months with highest/lowest excess returns
  • Estimate correlations between stocks and market
  • Calculate beta and alpha using CAPM

Part II: Written Report (2000 words)

Title: Comparing the Stock Performances of [Stock A] and [Stock B]

1. Description and analysis of stock performances

 

2. Using t-tests to compare monthly performances

 

3. CAPM analysis of stock performances

 

Assessment Criteria:
  • Accuracy of calculations (40%)
  • Appropriate use of Excel functions (20%)
  • Critical analysis and interpretation (30%)
  • Presentation and formatting (10%)