Share
Explore BrainMass

Stock Portfolio Value

Don has a portfolio containing 3 stocks. The stock, the number of shares, and current trading prices are detailed in the attahced XLS spreadsheet.

Don wants to estimate what his portfolio will be worth one year from today. We assume his shares will have the following values ONE YEAR FROM TODAY:

TCB: will have an expected value of $50/share, with a standard deviation of $2/share

RayWatch: will have a value between $20 and $26, with a share value of $24 being most likely.

BlingTime: will have a share value between $14 and $18.

Don WILL NOT be buying or selling any shares in the next year.

PROBLEM: USE SPREADSHEET SIMULATION IN EXCEL, or Crystal Ball, and Excel Add-in to determine:

1) What is the probability that Don's portfolio will be worth more TODAY than it will ONE YEAR FROM TODAY?

2) What is the probability that Don's portfolio will be worth MORE THAN $17,000 ONE YEAR FROM TODAY?

3) What is the probability that Don's portfolio will be worth LESS THAN $14,500 ONE YEAR FROM TODAY?

4) Waht is the 95% confidence interval for Don's portfolio value one year from today?

Attachments

Solution Preview

* Firstly, we should setup the Crystal Ball, and make forecast.
<br>In assumption, we insert all the information given by the question:
<br>For TCB, it is a normal distribution with mean 50 and standard deviation of 2.
<br>And I put a lower bound of 0, because the stock price can't be negative.
<br>
<br>For Ray Watch, its distribution is tilted, we might use extreme value distribution with a Mode of $24. we can set the range between $20 and $26. In the "scale", I use 0.01, which stands for 1 cent, the smallest unit of money.
<br>
<br>For BlingTime, we have no other information than its range between $14 and $18, so we have to assume it's probability is evenly distributed within the ...

Solution Summary

Don has a portfolio containing 3 stocks. The stock, the number of shares, and current trading prices are detailed in the attahced XLS spreadsheet.

Don wants to estimate what his portfolio will be worth one year from today. We assume his shares will have the following values ONE YEAR FROM TODAY:

TCB: will have an expected value of $50/share, with a standard deviation of $2/share

RayWatch: will have a value between $20 and $26, with a share value of $24 being most likely.

BlingTime: will have a share value between $14 and $18.

Don WILL NOT be buying or selling any shares in the next year.

PROBLEM: USE SPREADSHEET SIMULATION IN EXCEL, or Crystal Ball, and Excel Add-in to determine:

1) What is the probability that Don's portfolio will be worth more TODAY than it will ONE YEAR FROM TODAY?

2) What is the probability that Don's portfolio will be worth MORE THAN $17,000 ONE YEAR FROM TODAY?

3) What is the probability that Don's portfolio will be worth LESS THAN $14,500 ONE YEAR FROM TODAY?

4) What is the 95% confidence interval for Don's portfolio value one year from today?

$2.19