Assessment Task: VAR & IPS 

  • Post category:Post
  • Post comments:0 Comments

Assessment Task: VAR & IPS 


You have recently settled into the Ozarks to set up your financial advisory business.  You meet Ruth (19 yrs old) and Wyatt (18 yrs old) Langmore, two cousins who have decided to combine their assets together to support the remainder of their family.  They miraculously generated a sum of $3,200,000.  Ruth currently is the operating a dance studio and earns $25,000 this past year while Wyatt is still in high school and manages to scrape by with $5,000 this past year.  The current income pays off their expenses, leaving 15% of whatever income they have after taxes, which they believe will stay at that rate.  The family currently has about $8,000 in loan shark debt at about 20% interest.  Furthermore, Wyatt plans to go to college during the next year which will balloon expenses by another $30,000 a year for the next four years. Assessment Task: VAR & IPS 

The Langmores have no savings prior to this due to an obscure relative disappearing with all the money.  You take some time to interview them to college a little information which can be broken down into the following excerpts:

  • “We don’t really want to change our lifestyles, as we would like to avoid the unwanted attention.”
  • “Given what has happened to our savings previously, we are not aiming for high returns and we would like to minimize volatile investments”
  • “We would like the portfolio to at least combat inflation”
  • “We would both like to continue to work despite the new found money. We don’t really see ourselves retiring until 65+”
  • “Our income tax level is around 21.5% while our capital gains is taxed at 35%”
  • “If possible, we would like to fund college for some of our cousins who should be entering college in 15 years”

Determine the Langmore’s willingness and ability to tolerate risk, their overall risk tolerance. (5 pts)


Let’s assume that inflation is roughly 3.2%. Now calculate the following:

  • What are their expenses for the next year?
  • What is their net income for the next year?
  • What is the required return on the portfolio for the next year?

Determine the Langmore’s time horizon, liquidity needs for the next year (hint, part of this is a number related to expenses), and legal, regulatory and tax considerations. Assume the details still hold from part (i) and living expenses have not increased as they did in part (ii)(5 pts)

Institutional IPS

Alexander Ellington, President of Ellington Foods, has contacted your firm to discuss the company’s defined-benefit pension plan.  He has provided the following information about the company and its pension plan:

  • Ellington Foods has annual sales of $300 million
  • The annual payroll is about $100 million
  • The average age of the work force is 43 years
  • 30% of the plan participants are now retired
  • Company profits last year were $10 million and have been growing at 10% annually. The Ellington Foods pension plan has $80 million in assets and is currently overfunded by 10%
  • The duration of the plans liabilities is 15 years
  • The discount rate applied to the liabilities is 6%
  • Fund trustees wish to maintain 5% of plan assets in cash

Ellington would like to achieve a rate of return of 6.5% on its pension fund (which is less than the 8.7% the fund has historically achieved).  Ellington would like to be able to reduce contributions to the pension fund and possibly increase employee benefits.

Formulate and justify investment policy objectives for the Ellington Foods pension plan in the following three areas (15 pts total):

  • Return objective
  • Risk tolerance
  • Time horizon

3) VaR

Similar to what we did in class, you will calculate the VaR for the Kospi2 (Korean Stock Exchange).  You will be required to do the following in Excel:

  • Data is already provided
  • Calculate the daily VaR at the 95% and 99% confidence interval
  • Calculate the Annual VaR at the 95% and 99% confidence interval
  • Calculate the $ VaR based on a $25,000,000 portfolio at the 95% and 99% confidence interval

What you will need to provide is a snapshot summary of the requested results above as well as a short paragraph interpreting those results.  Please also include your working excel sheet (formulas and all) when submitting.  I have posted an example online of what we already did for the S&P500.  (20 pts). Assessment Task: VAR & IPS 

  1. What are the excel quick keys for sorting data? (1 pt)
    • What do you use for calculations based off Yahoo data, close or adjusted close and why? (1 pt)What do you need to do with the price data in order to actually calculate the standard deviation? (1 pt)
    • What is the data table summarizing everything?



Leave a Reply