Creating Optimal Portfolio and the Efficient Frontier Using Microsoft Excel®

  • Saurav Roychoudhury Department of Business, Capital University, Columbus, OH, USA 43209.
DOI: https://doi.org/10.29145/2018/jqm/020207
Keywords: Optimal Portfolio, Efficient Frontier, Risk, Expected Return, Risk-free asset

Abstract

Portfolio managers and investors strive to achieve the best possible trade-off between risk and return, and one of the tools they use is constructing mean-variance efficient portfolios. Finance students learn about optimal portfolios and efficient frontiers, though it is difficult to replicate them unless they have access to sophisticated software. This paper develops a teaching module that uses Microsoft Excel® to create mean-variance portfolios and traces out the efficient frontier using real-world data. In the process, the students learn to determine optimal investment allocations in a portfolio, select the optimum investment portfolio given investor’s objectives and preferences and learn about factors that influence different asset allocations. For multiple assets (N>3), the paper uses Matrix algebra in Excel®. The paper enables students and investors to learn how to construct real-world mean-variance efficient portfolios using Excel®.

Downloads

Download data is not yet available.

References

Benninga, S. (2014). Financial Modeling(4th Ed.). Cambridge, MA: The MIT Press.

Black , F. (1972). Capital market equilibrium with restricted borrowing. Journal of Business, 45(3), 444-455.

Boudreaux, D., Das, P. & Rao S. (2016). Bootstrap simulation with spreadsheet application. Journal of Economics and Finance Education, 15(1), 1-8.

Hess, K. (2005). Spreadsheet-Based Modelling for Teaching Finance and Accounting Courses Retrieved from http://ssrn.com/abstract=378680.http://dx.doi.org/10.2139/ssrn.378680

Markowitz, H., (1952). Portfolio selection. Journal of Finance, 7(1), 77–91. https://doi.org/10.1111/j.1540-6261.1952.tb01525.x.

Merton, R. C. (1972). An analytical derivation of the efficient portfolio frontier. Journal of Financial and Quantitative Analysis, 7(4), 1851-1872. https://doi.org/10.2307/2329621.

Roychoudhury, S. (2007). The Optimal Portfolio and the Efficient Frontier, National Science Foundation Working paper #DUE 0618252.

Wann, C. R., & Lamb N. H. (2016). Bond duration: Constructivist learning using Excel. Journal of Economics and Finance Education, 15(1), 30-42.

Wann, C. (2015). Black-Scholes option pricing: Implementing a hands-on assignment using Excel. Journal of Economics and Finance Education, 14(1), 22-30.

Zhang, C. (2014). Incorporating powerful excel tools into finance teaching, Journal of Financial Education, 40(3/4), 87-113.

Published
2019-03-13
How to Cite
Roychoudhury, S. (2019). Creating Optimal Portfolio and the Efficient Frontier Using Microsoft Excel®. Journal of Quantitative Methods, 2(2), 104-136. https://doi.org/10.29145/2018/jqm/020207
Issue
Vol 2 No 2 (2018): Winter
Section
Teaching Module

Copyright (c) 2019 Saurav Roychoudhury

Creative Commons License

This work is licensed under a Creative Commons Attribution 4.0 International License.

JQM follows an open-access publishing policy and full text of all published articles is available free, immediately upon publication of an issue. The journal’s contents are published and distributed under the terms of the Creative Commons Attribution 4.0 International (CC-BY 4.0) license. Thus, the work submitted to the journal implies that it is original, unpublished work of the authors (neither published previously nor accepted/under consideration for publication elsewhere). On acceptance of a manuscript for publication, a corresponding author on the behalf of all co-authors of the manuscript will sign and submit a completed Copyright and Author Consent Form.