EXCEL PROJECTS

MARKOWItz - EFFICIENT FRONTIER

Harry Markowitz, through his pioneering work in Modern Portfolio Theory (MPT), was the first to  introduce the concept of portfolio diversification as a way to maximize returns while minimizing the risk. He developed this theory in the 1950s, for which he was later awarded the Nobel Prize in Economic Sciences in 1990. He demonstrated how investors could achieve the same level of returns by taking much less risk by simply diversifying across assets which had very low correlation, hence cutting down the exposure to Unsystematic Risk. Through his work he fundamentally conveys the idea of - "Do not put all your eggs in one basket!"

He introduced the concept of 'Efficient Frontier' which represented the optimal set of portfolios selected through Mean-Variance Optimization. His goal was to find a combination of assets which minimizes portfolio risk for a given level of expected return.

This project explores the Markowitz Efficient Frontier to prove how an optimally diversified portfolio can achieve the highest possible return for a given level of risk. By utilizing Mean-Variance Optimization, the study demonstrates how different asset allocations affect the overall risk-return profile of a portfolio.

VALUE-AT-RISK MODEL

Value at risk (VaR) is a key risk management tool used to quantify potential portfolio losses over a specified time period (e.g., a day, a quarter, or a year) at a given level of probability (often 0.05 or 0.01). Suppose we specify a one-day time horizon and a level of probability of 0.05 (5%), which would be called a 95% one-day VaR. If this VaR equaled $5 million for a portfolio, there would be a 0.05 (5%) probability that the portfolio would lose $5 million or more in a single day (assuming our assumptions were correct).

This project explores the model, using different methods of calculating VaR - Historical Method, Monte Carlo simulation & Bootstrap Sampling Technique.

binomial-tree option pricing

The Binomial Tree Option Pricing Model is a fundamental method for valuing options by modeling the possible price movements of an underlying asset over time. It was first introduced by Cox, Ross, and Rubinstein in 1979 as a discrete-time model for pricing options. Through this model, they fundamentally convey the idea that options prices evolve in a stepwise manner over multiple periods, rather than changing in a single jump.

The Binomial Tree approach breaks time into small intervals, where in each period, the asset price can either move up or down by certain fixed factors. The model uses assumes that investors are indifferent to risk which allows for the calculation of option prices without requiring knowledge of investor risk preferences. The Binomial Tree Model works for both European and American options but it is particularly well-suited for pricing European options because 

  • European options can only be exercised at expiration, which aligns perfectly with the binomial model’s backward induction process.

  • Since early exercise is not allowed for European options, there’s no need to check at each step whether it’s optimal to exercise the option early (which is necessary for American options).

This project explores the pricing of a European Option using the Binomial Tree Option Pricing Model to demonstrate how stepwise asset price movements influence the option’s value. By implementing a multi-period binomial tree, the study calculates the option’s fair value at expiration and works backward through the tree to determine its present value. The model incorporates key parameters such as stock price, strike price, time to maturity, volatility, and the risk-free rate, using risk-neutral probabilities to estimate expected payoffs.

Connect with me