Question

PLEASE SHOW WORK IN EXCEL WITH FORMULA

You are constructing a two stock portfolio based on the information provided below. What dollar amount will you invest in each stock to achieve the desired return goal?

	Stock X	Stock Y
Expected Return	14.0%	9.0%

Goal Return of Portfolio: 10.00%

Dollar Amount to Invest: $20,000

Answer 1

Let's first type the given details in an excel sheet.

Carefully look at cell D3. It is the the formula used for Goal Return, that is (Return on Stock X + Return on Stock Y) / (Investment in Stock X + Investment in Stock Y)

Also, we have kept the Amount in Stock X as blank, and Amount in Stock Y = 20000-Amount in Stock X

You can see it as below

Now, we will use the Goal Seek function in What If Analysis to find the dollar amount to be invested in each stock.

So, Go to Data tab. Click on What If Analysis and select the Goal Seek

Here, 'Set cell' asks the cell which contains the result which we want. So, we select the Goal Return cell, which contains the formula for return on portfolio.

To Value - is the value which we want as the result in the set cell. As we want Goal return to be 10%, we type 10%.

By changing cell - is the cell which we allow to be changed by the excel function, so that the result in 'Set cell' is 10%. So we select the blank cell of Amount Invested in Stock X.

The result is

So, Invest $4000 in stock X and remaining $16000 in Stock Y to earn 10% portfolio return.

This is the simplest way to calculate the mix for any portfolio return required. You may try to find the mix for any desired return goal which is atleast 9% and at most 14%.

______________________________________________________________________________________

Mathematically, we can calculate the same using a simple formula.

Let the amount to be invested in Stock X be X

So, Amount to be invested in Stock Y = 20,000-X

The required equation is

14%*X + 9%*(20000-X) = 10%*20000

=>0.14X + 1800 - 0.09X = 2000

=>0.05X = 2000-1800

=> X = 200 / 0.05

=> X = 4000

So, Invest $4000 in stock X and remaining $16000 in Stock Y to earn 10% portfolio return.