IPS/VAR It is your first day as a financial advisor in the far off lands of Westeros. You meet Daenerys Targaryen, 26, and her husband, Jon, 25, who are considering what to do with a recent windfall they received after the untimely death of Daenerys’s father, the mad king. The windfall is estimated to be 5,780,000 gold coins (after taxes). Daenerys is currently the self-declared queen of the free men but only commands a salary of 70,000 gold coins annually (armies are expensive to feed). Jon is, and always has been, a non profit warrior, drawing a minimal salary of 35,000 gold coins per year. The Targaryens do not have any children…yet, but suspect that they might have a bundle of join coming in the next nine months. By design, the Targaryens owe no debt and pay their expenses on a monthly basis. Family expenses last year amounted to approximately 80,000 gold coins and expected to grow the next year thereafter at the rate of inflation given the pending surprise.
In addition to the inheritance they will receive, the Targaryens have an additional 1,435,000 gold coins in savings. Daenerys and Jon have approached you, for assistance in managing their portfolio. The Targaryens made the following statements at a recent client discovery meeting:
• “One of our goals at this stage in our lives is to pay for the college education of our child. We would like him to go to Jon’s alma mater, which is a prestigious Northern University.”
• “We expect our annual expenses to increase at the general rate of inflation of 3.92%.”
• “We abdicate the throne to our child at the ages of 65 and 66 and to be able to live comfortably, but not extravagantly.”
• “We are taxed at 28.5% on income and 16.5% on capital gains.”
• “We believe our portfolio should never suffer an annual loss of more than 7%. In addition, we do not want to invest in any individual investment or security that is too risky.”
• “We do not foresee any unusual expenses over the short term. As always, we would like to have enough cash on hand for emergencies.”
i) Determine the Targaryen’s willingness and ability to tolerate risk, their overall risk tolerance. (5 pts)
ii) Now let’s calculate the required return (from the portfolio) for the next year assuming now that everything else being the same, living expenses were 322,000 coins (hint, this will actually be a number and you need to take into account income taxes and inflation into the returns). These numbers are only relevant for this question. (5 pts)
iii) Determine the Targaryen’s time horizon, liquidity needs (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)
2) 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):
i) Return objective
ii) Risk tolerance
iii) Time horizon
3) VaR Similar to what we did in class, you will calculate the VaR for the DAX (German Stock Exchange. You will be required to do the following in Excel:
• Download all the relevant Dax Index from December 29, 1987 to January 10, 2018 (inclusive)
o You may have to eliminate any data that is considered noise
• 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)
i) What is the quick keys to sort a set of data in excel? (1 pt)
ii) What do you need to do with the price data in order to actually calculate the standard deviation? (1 pt)
iii) What do you use for this calculation, close or adjusted close and why? (1 pt)
iv) What is the data table for all the calculations requested above (think about the table we built in class). Please post a screenshot here and include a working excel.