the question and data set is in the attached excel. Could yo please give me the full solution for the questions asked.

The actual returns of the S&P since 1928 are in the attached file. The Assignment Your team works at a financial advisory firm and is asked to work with a client, Ms Christie, who has retired and has 400K in liquid assets. To maintain a semblance of her prior lifestyle she needs to draw down 50K per year to complement social security and other pension payments. a) Based on actual data (use the random function to select different starting years), determine the average number of years she can continue to draw down 50K before she runs out of money. b) Second, since your client understands distribution (she took this course) and comes from a family of octagenarians, she wants to know how much she can draw down if she expects to live to 82. She is 65 now. c) finally, any other advice you would like to offer t, Ms Christie, who er year to years), determine runs out of money. nd comes from a she expects to live FAQ question: wont the answer be different since it will change when i open it answer: i expect that using the random function even a couple of dozen times (since there is will be relatively stable. again i care about the analysis and model and any out of the box thinking you can bring to th please dont hesitate if you have any other questions question: how can we set the formuala to get a non-negative number? answer: the random function is used to randomize the retirement year back in history. so a in 1939 or 1972 or 1956 etc.. we need to do a simulation. what happens with your savings if you had invested in the mark actually did that year (data provided) figure out how frequently she might be broke before she dies? also see how much the frequency of going broke drops, if the cash spent is reduced hope this helps. this is a very realistic and non-academic assignment. The question is how does one count the number of years till the money goes negative. Answer: please keep in mind that the money invested on the first year of retirement earns a the market. so unless one is unlucky in retiring when thie market is about to crash, the one a simple example is that if the market returned 10% the year the person retired, then the ma investment. so even if the drawdown is $50K, it should go a longish while. the purpose of the exercise is to make it vividly clear how much of financial security is rando Question: I am wondering for the answer, do we just pick any year to work out an answer or possibilities (starting year ranging from 1928--1998)? Answer: One has to account for more than one year. One can do it for all years (since the sample is n a few dozen years and take the average of that.. Again, please don't be scared about not having the 'right' answer. In life there is rarely such thinking and work, maybe incorporating any tools and material we covered. hen i open it ozen times (since there is only 90 odd years of history) the average hinking you can bring to the issue ber? year back in history. so a random function may result in your retiring u had invested in the market and the market returned whatever it spent is reduced ent. oney goes negative. ear of retirement earns a return based on the return of that year in s about to crash, the one should be able to make do for many years. erson retired, then the market paid the retiree $40K on a 400K h while. financial security is random to work out an answer or does the answer need to account for all ears (since the sample is not too big) OR use the random function for In life there is rarely such a thing. I look forward to your best covered. Year 1928 1929 1930 1931 1932 1933 1934 1935 1936 1937 1938 1939 1940 1941 1942 1943 1944 1945 1946 1947 1948 1949 1950 1951 1952 1953 1954 1955 1956 1957 1958 1959 1960 1961 1962 1963 1964 1965 1966 1967 1968 1969 1970 1971 1972 S&P 500 43.81% -8.30% -25.12% -43.84% -8.64% 49.98% -1.19% 46.74% 31.94% -35.34% 29.28% -1.10% -10.67% -12.77% 19.17% 25.06% 19.03% 35.82% -8.43% 5.20% 5.70% 18.30% 30.81% 23.68% 18.15% -1.21% 52.56% 32.60% 7.44% -10.46% 43.72% 12.06% 0.34% 26.64% -8.81% 22.61% 16.42% 12.40% -9.97% 23.80% 10.81% -8.24% 3.56% 14.22% 18.76% 1973 1974 1975 1976 1977 1978 1979 1980 1981 1982 1983 1984 1985 1986 1987 1988 1989 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2013 2014 2015 -14.31% -25.90% 37.00% 23.83% -6.98% 6.51% 18.52% 31.74% -4.70% 20.42% 22.34% 6.15% 31.24% 18.49% 5.81% 16.54% 31.48% -3.06% 30.23% 7.49% 9.97% 1.33% 37.20% 22.68% 33.10% 28.34% 20.89% -9.03% -11.85% -21.97% 28.36% 10.74% 4.83% 15.61% 5.48% -36.55% 25.94% 14.82% 2.10% 15.89% 32.15% 13.48% 1.36%