Question

Investment	Forecasted Returns for Boom Economy	Forecasted Returns for Stable Growth Economy	Forecasted Returns for Stagnant Economy	Forecasted Returns for Recession Economy
Stock	26%	11%	7%	-15%
Corporate bond	9%	7%	5%	4%
Government bond	8%	6%	4%	3%

Variance and standard deviation

​(expected).

Bacon and​ Associates, a famous Northwest think​ tank, has provided probability estimates for the four potential economic states for the coming year in the following​ table: The probability of a boom economy is 21%​, the probability of a stable growth economy is 45​%, the probability of a stagnant economy is 24%​, and the probability of a recession is 10​%. Calculate the variance and the standard deviation of the three​ investments: stock, corporate​ bond, and government bond. If the estimates for both the probabilities of the economy and the returns in each state of the economy are​ correct, which investment would you​ choose, considering both risk and return?  

​Hint: Make sure to round all intermediate calculations to at least seven​ (7) decimal places. The input​ instructions, phrases in parenthesis after each answer​ box, only apply for the answers you will type.

*Please show work in Excel*

Answer 1

Stock variance is 2.86916667 and standard deviation is 16.93861466

Corporate bond variance is 0.04916667 and standard deviation is 2.217355783

and government bond variance is 0.0491667 and standard deviation is 2.217355783

the following is the steps of calculation variance and standard deviation in excel also at the end of this explanation excel sheet will be attached for further clarification.

Particulars	Forcasted return
Investment	Boom Economy	Stable Economy	Stagnant Economy	Recession Economy
Stock	26%	11%	7%	-15%
Corporate bond	9%	7%	5%	4%
Government bond	8%	6%	4%	3%
Variance and standard deviation
Stock	Forcasted return	Mean	Variance	Standard deviation
Boom Economy	26%
Stable Economy	11%
Stagnant Economy	7%
Recession Economy	-15%
Avg(Mean)	7.25%	7.25%	2.86916667	16.93861466
Formula	average(B9:B12)		var(B9:B12)*100	stdev(B9:B12)*100
Corporate bond	Forcasted return	Mean	Variance	Standard deviation
Boom Economy	9%
Stable Economy	7%
Stagnant Economy	5%
Recession Economy	4%
Avg(Mean)	6.25%	6.25%	0.04916667	2.217355783
	average(B17:B20)		var(B17:B20)*100	stdev(B17:B20)*100
Government Bond	Forcasted return	Mean	Variance	Standard deviation
Boom Economy	8%
Stable Economy	6%
Stagnant Economy	4%
Recession Economy	3%
Avg(Mean)	5.25%	5.25%	0.0491667	2.217355783
	average(B25:B28)		var(B25:B28)*100	stdev(B25:B28)*100

Now the return of different investment opportunity according to the probability of economic situation.

	Stock	Forcasted return	Probability	Return probability
	Boom Economy	26%	21%	5%	I9*J9
	Stable Economy	11%	45%	5%
	Stagnant Economy	7%	24%	2%
	Recession Economy	-15%	10%	-2%
				10.59%	sum(K9:K12)
	Corporate bond	Forcasted return	Probability	Return probability
	Boom Economy	9%	21%	2%	I17*J17
	Stable Economy	7%	45%	3%
	Stagnant Economy	5%	24%	1%
	Recession Economy	4%	10%	0%
				6.64%	Sum(K17:K20)
	Goverment bond	Forcasted return	Probability	Return probability
	Boom Economy	8%	21%	2%
	Stable Economy	6%	45%	3%
	Stagnant Economy	4%	24%	1%
	Recession Economy	3%	10%	0%
				5.64%	sum(K25:K28)

Now as we have to determine the best investment opportunity according to risk reward ration therefore,

Stock	Forcasted return	Probability	Return probability		Risk reutrn ratio
Boom Economy	26%	21%	5%	I9*J9	potential risk/return
Stable Economy	11%	45%	5%		it determine per unit of return we are getting for per unit of risk
Stagnant Economy	7%	24%	2%
Recession Economy	-15%	10%	-2%
			10.59%	sum(K9:K12)	1.598677998	16.93/10.59
Corporate bond	Forcasted return	Probability	Return probability
Boom Economy	9%	21%	2%	I17*J17
Stable Economy	7%	45%	3%
Stagnant Economy	5%	24%	1%
Recession Economy	4%	10%	0%
			6.64%	Sum(K17:K20)	0.3338855422	2.217/6.64
Goverment bond	Forcasted return	Probability	Return probability
Boom Economy	8%	21%	2%
Stable Economy	6%	45%	3%
Stagnant Economy	4%	24%	1%
Recession Economy	3%	10%	0%
			5.64%	sum(K25:K28)	0.3918439716	2.21/5.64

after this ration we decided to invest in "Corporate bond"with the given economic situation as its risk to reward ratio is lowest ie 0.3338855422. and risk to reward ration help to determine per unit of risk we are getting for per unit of return.

Please refer to attached file for steps, explanation and calculation along with formula and cell referred.