Choose the $$most optimal & diversified$$ group of investments to meet the client$$s goals. While the analysis will be to determine the stocks or bond funds, you may choose to keep some of the investments in cash or US Treasuries, if desired or necessary.

Part $1.$ The analysis should also be submitted as an excel workbook showing all of the calculations & assumptions $($if possible show to do it i excel$).$

Part $2.$ Assuming the returns for the next $5$ years are as good as the historical performance of your investments, provide a chart as shown below:

Forecasted Results Year $12024|$Year $22025|$Year $32026|$Year $42027|$Year $52028$

Initial value of assets

Plus: Added investments by client

Plus: Growth in value

Less: Withdrawals$*$ by client for specific goals

Value of assets at end of year

Part $3.$ Choose five funds among different asset classes $($domestic stock and bond funds, foreign stock and bond funds, ETFs, etc.$)$ and perform the following:

$1.$ Obtain $5$ years monthly price data for each investment fund;

$2.$ Calculate average return and standard deviation of the return for each fund;

$3.$ Calculate variance$-$covariance and correlation matrices;

$4.$ Calculate the return and standard deviation of the portfolio comprising:

$$ all of the funds with equal weights

$$ the highest and lowest return funds with the following weights $(1$;$0),(0\mathrm{.}9$;$0\mathrm{.}1),(0\mathrm{.}8$;$0\mathrm{.}2),$ etc.

$$ funds with the lowest correlation coefficient using following weights $(1$;$0),(0\mathrm{.}9$;$0\mathrm{.}1),(0\mathrm{.}8$;$0\mathrm{.}2),$ etc.

$$ funds with the highest correlation coefficient using following weights $(1$;$0),(0\mathrm{.}9$;$0\mathrm{.}1),(0\mathrm{.}8$;$0\mathrm{.}2),$ etc.

Part $4.$ Assuming the current risk free rate equal to a yield on a three$-$month treasuries, determine the weights of the funds in an optimal portfolio.

Part $5.$ Based on part $4,$ Calculate the betas of all five funds, plot them on a risk$-$return space, show the security market line and determine which of the securities are undervalued and overvalued. Assume the current risk free rate equal to a yield on a three$-$month treasuries.

Additonal Info

Risk free rate $=4\mathrm{.}7\%$

Anual Average Expected Return $=$ Average yield

Taxes: Assume the client profile puts them in a $25\%$ tax bracket for ordinary income and a $15\%$ tax on capital gains and a $5\%$ state income tax bracket.

Client:

Ms$.$ F $(75)$ is a widow who has a comfortable life and is well provided for with the savings her husband left her. She has $2$ grown children and $5$ grandchildren. She has a portfolio of $$5$ Million from whose earnings she lives a simple life, supplemented by social security. She is open to having you manage her portfolio to help meet her primary financial goals.

Her primary financial goals as she has explained to you are:

$1.$ Provide sufficient income every year to meet her daily needs of life.

$2.$ Be able to gift each of her grandchildren $$15,000$ each year.

$3.$ Convert some $($you decide$)$ of her portfolio into a trust for each of her $2$ children and then invest that trust in a separate portfolio which they$$d inherit after her.