Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports

1. Member
Join Date
29th January 2004
Location
Tauranga
Posts
23

## Portfolio Standard Deviation

Hi There

Could someone please help me with the calculation of a standard deviation for a portfolio consisting of 6 asset classes. I have the expected returns, standard deviations and correlations for all the asset classes. My problem is I can't find the specific formula to use when you want to calculate the SD for a portfolio that contains more than 2 asset classes.

Help would be GREATLY appreciated.

Thanks
Regards
Ursula

Excel Video Tutorials / Excel Dashboards Reports

2. Attached is an example for 4 classes. The logic is good for your 6 or however many you may later have. Just expand all of the tables once you see the formulas (The array formulas were for my convenience, so you can delete them before expanding once you realize what they were doing).

Excel Video Tutorials / Excel Dashboards Reports

3. Member
Join Date
29th January 2004
Location
Tauranga
Posts
23
Hi Derk

Thanks very much for the spreadsheet. I am probably being a bit dim but I assume that sigma is another name for standard deviation? Also, I have correlations for my asset classes but I do not have covariances and I am not sure how to generate them. Any help on that?

Thanks
Ursula

Excel Video Tutorials / Excel Dashboards Reports

4. Ursula,
Yes sigma is another name for standard deviation. The covariance matrix is calculated from the correlation matrix in my example workbook (actually the correlations are initially calculated from the covariances, but then just the correlations are published so we have to calculate the covariances again from the correlations). All you need to do is 1) supply the standard deviatios and the correlations, and 2) adjust the remaining formulas for the proper dimensions. The formulas should all extend properly by just dragging them. Post back if you have trouble.

Excel Video Tutorials / Excel Dashboards Reports

5. Member
Join Date
29th January 2004
Location
Tauranga
Posts
23
Hi Derk

Thanks very much for that, I think that has solved the problem for me. I will amend the formulas accordingly and will get back if I get stuck again.

How do you mark problems as sloved as I don't see the solved button any more?

Cheers
Ursula

Excel Video Tutorials / Excel Dashboards Reports

6. There is no longer a solved button. You can always edit the title of your original post and insert SOLVED; however, I believe Dave's current prefeernce is to not do that. That way, more people will be apt to read the threads and perhaps contribute their thoughts or learn something new.

Excel Video Tutorials / Excel Dashboards Reports

7. Member
Join Date
29th January 2004
Location
Tauranga
Posts
23
Hi Derk

Thanks for the info, it makes sense to do it like that. I still have not had time to apply your spreadsheet but I will do it soon.

Thanks again, I think this forum is a GREAT idea and I appreciate everyone's willingness to help!

Cheers
Ursula

Excel Video Tutorials / Excel Dashboards Reports

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•
porno