The Optimal Portfolio and the Efficient Frontier
Likelihood of Loss and the Value at Risk
Module Author Saurav Roychoudhury Assistant Professor of Finance School of Management Capital University [email protected] 614-236-7230 Year: 2010 Funding Source: NSF (DUE 0618252)
Any opinions, findings, and conclusions or recommendations expressed in this material are those of the author and do not necessarily reflect the views of the National Science Foundation.
Module Prerequisites The module covers topics in finance, statistics and economics. Students should have basic knowledge of probability and statistics and know the basic properties of Normal and Lognormal distributions and have done preliminary hypothesis testing. A background in economics and finance is helpful but not necessary. A course in computational science (Computational Science I) is beneficial in giving students the background in modeling techniques prior to beginning this module. It is expected that the students are familiar with working with Excel 2007. Required math level and Science Level: Intermediate.
Overview Financial loss impairs our ability to invest, save, consume, borrow, retire and sustain our well-being. It is possible to model, at least within a reasonable bound, the likelihood of loss and the maximum loss that can befall an investment. The mathematical models can be solved by applying statistical techniques, using historical data, and Monte-Carlo simulations. This module uses mathematical models to estimate the probability or likelihood of a given loss and the maximum potential loss to an investment over a defined period of time. The module uses common statistical techniques to industry standard Value-at-Risk models.
This module is designed primarily for undergraduate students in finance, economics, and statistics. However the module can also cater to undergraduate students in mathematics, physics, computer science, engineering and geology who wish to take a course in computational finance. The module can be used as an integrated part of a computational finance course, a stand-alone component or an add-on to a typical investments or a risk management course in finance. The module can be taught in both the classroom and the computer lab. For advanced students, the module has a section which uses matrix algebra. For more details on using matrix algebra to construct portfolios in excel, refer to the
2
computational finance module “Allocation of investment portfolios along the efficient frontier”.
Keywords Value-at-Risk, VaR, Likelihood of Loss, Monte Carlo Simulation, Lognormal returns, Portfolio hedging.
Introduction There is a saying in life that there is no gain without pain. It is very true in financial markets, In order to get a higher return than a return on a government bond or a bank certificate of deposit, an investor will have to put money in risky securities which can range from a highly rated municipal bond to stocks and the riskiest of options. Financial loss matters as it affects everyone and one of the most important and profound question that confronts any investor is what is the worst that can happen? An investor can be a person saving for retirement or on the down payment on a house or both; investor can be a commercial bank, an insurance company, the government, a large corporation, a small business or simply a speculator. An investor could face bankruptcy; financial traders who trade with huge amounts of borrowed money can face financial ruin if they fail to “exit” their position beyond a limit, a bank can have its equity wiped out if its assets drop below a certain amount.
Problem Statement Given the risks, the questions which an investor may typically ask can be:
What is the most I can lose on this investment?
What is the likelihood that I may lose half of my investment in a given year?
How much money do I have to set aside so that my equity is not wiped out?
Am I saving enough for my retirement?
This module will attempt to provide answer to these questions within the framework of mathematical models.
3
But first things first, we need to know some basic background material before we can start answering these questions.
Background Information Returns Simple Periodic Returns Rates of return are the building blocks of quantitative finance. A simple periodic rate of return,
on any investment can be calculated by using the general formula
Example 1: If an investment of $100 grows to $115 in year 1 and $125 in year 2, what are the 1 period simple periodic returns for years 1 & 2. Solution: Using the above equation for simple periodic return, Simple Investment Periodic Year Value Return 0 $ 100.00 1 $ 115.00 15.00% Options>Calculation). Before using the SimulationTable macro, a range must be selected in which the output to be tabulated is in the top row, but not in the top-left cell. The output from repeated recalculations of the model then fills the lower rows of the selected range below these output cells. The leftmost column of the selected range is filled with fractile numbers, indicating (in each row of the simulation table) what fraction of the simulation data is above this row. SimulationTable stores the output data as values that are not recalculated whenever the spreadsheet changes.
i
http://www.computerworld.com/s/article/9028228/Excel_2007_Cheat_Sheet
41
Module Author Saurav Roychoudhury Assistant Professor of Finance School of Management Capital University [email protected] 614-236-7230 Year: 2010 Funding Source: NSF (DUE 0618252)
Any opinions, findings, and conclusions or recommendations expressed in this material are those of the author and do not necessarily reflect the views of the National Science Foundation.
Module Prerequisites The module covers topics in finance, statistics and economics. Students should have basic knowledge of probability and statistics and know the basic properties of Normal and Lognormal distributions and have done preliminary hypothesis testing. A background in economics and finance is helpful but not necessary. A course in computational science (Computational Science I) is beneficial in giving students the background in modeling techniques prior to beginning this module. It is expected that the students are familiar with working with Excel 2007. Required math level and Science Level: Intermediate.
Overview Financial loss impairs our ability to invest, save, consume, borrow, retire and sustain our well-being. It is possible to model, at least within a reasonable bound, the likelihood of loss and the maximum loss that can befall an investment. The mathematical models can be solved by applying statistical techniques, using historical data, and Monte-Carlo simulations. This module uses mathematical models to estimate the probability or likelihood of a given loss and the maximum potential loss to an investment over a defined period of time. The module uses common statistical techniques to industry standard Value-at-Risk models.
This module is designed primarily for undergraduate students in finance, economics, and statistics. However the module can also cater to undergraduate students in mathematics, physics, computer science, engineering and geology who wish to take a course in computational finance. The module can be used as an integrated part of a computational finance course, a stand-alone component or an add-on to a typical investments or a risk management course in finance. The module can be taught in both the classroom and the computer lab. For advanced students, the module has a section which uses matrix algebra. For more details on using matrix algebra to construct portfolios in excel, refer to the
2
computational finance module “Allocation of investment portfolios along the efficient frontier”.
Keywords Value-at-Risk, VaR, Likelihood of Loss, Monte Carlo Simulation, Lognormal returns, Portfolio hedging.
Introduction There is a saying in life that there is no gain without pain. It is very true in financial markets, In order to get a higher return than a return on a government bond or a bank certificate of deposit, an investor will have to put money in risky securities which can range from a highly rated municipal bond to stocks and the riskiest of options. Financial loss matters as it affects everyone and one of the most important and profound question that confronts any investor is what is the worst that can happen? An investor can be a person saving for retirement or on the down payment on a house or both; investor can be a commercial bank, an insurance company, the government, a large corporation, a small business or simply a speculator. An investor could face bankruptcy; financial traders who trade with huge amounts of borrowed money can face financial ruin if they fail to “exit” their position beyond a limit, a bank can have its equity wiped out if its assets drop below a certain amount.
Problem Statement Given the risks, the questions which an investor may typically ask can be:
What is the most I can lose on this investment?
What is the likelihood that I may lose half of my investment in a given year?
How much money do I have to set aside so that my equity is not wiped out?
Am I saving enough for my retirement?
This module will attempt to provide answer to these questions within the framework of mathematical models.
3
But first things first, we need to know some basic background material before we can start answering these questions.
Background Information Returns Simple Periodic Returns Rates of return are the building blocks of quantitative finance. A simple periodic rate of return,
on any investment can be calculated by using the general formula
Example 1: If an investment of $100 grows to $115 in year 1 and $125 in year 2, what are the 1 period simple periodic returns for years 1 & 2. Solution: Using the above equation for simple periodic return, Simple Investment Periodic Year Value Return 0 $ 100.00 1 $ 115.00 15.00% Options>Calculation). Before using the SimulationTable macro, a range must be selected in which the output to be tabulated is in the top row, but not in the top-left cell. The output from repeated recalculations of the model then fills the lower rows of the selected range below these output cells. The leftmost column of the selected range is filled with fractile numbers, indicating (in each row of the simulation table) what fraction of the simulation data is above this row. SimulationTable stores the output data as values that are not recalculated whenever the spreadsheet changes.
i
http://www.computerworld.com/s/article/9028228/Excel_2007_Cheat_Sheet
41
