1

I generated all 30 million portfolios in Excel 2016 (Windows 7), and currently can graph about 5 million of them but it takes forever. I have 1-12 assets in each portfolio, and the annual total return series for each asset. For any given time period N (say 10 years) I need to be able to easily calculate the portfolio risk & returns based on the weights:

risk = array formula {STDEV(MMULT(assets' weights, TRANSPOSE(assets' annual returns over N years)))}
= {STDEV(N years of annual returns for a given portfolio)} return = array formula {PRODUCT(1+MMULT(assets' weights, TRANSPOSE(assets' annual returns over N years)))^(1/N years)-1}
= {PRODUCT(1+N years of annual returns for a given portfolio)^(1/N years)-1}

I also need to be able to overlay benchmarks' performance on the graph. Excel really slows down with 5 million portfolios but I like the charting, customization and flexibility. People have suggested Python or matplotlib but I am unfamiliar with these and need to know they could handle both the calculations and graphing using the existing portfolios and asset return series. I've seen an MVO in Python but don't know where it got the return data and portfolios (and the chart did not look complete). Any suggestions? Thanks!

1

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Browse other questions tagged or ask your own question.