Abstract
According to the modern portfolio theory, investors should not limit themselves to the expected risk and return of one particular stock. Investors should reduce risks through diversification because the risk in a portfolio of diverse stocks will be less than the risk of any one of the stocks. The key is diversification. Diversification is not as simple at owning ten different stocks, but to comprise assets that are relatively unrelated in order to mitigate risks. This is important when one sector performs poorly, while other sectors are performs well. In classrooms, finance students learn the principles of investment analysis and investment courses cover topics of asset management utilizing Markowitz portfolio theory, Capital Market theory, and other investment strategies. However, most of these finance courses do not provide the skills you need to build these financial models for private or professional use. There are four basic steps involved to construct optimal portfolios that offer the maximum possible expected return for a given level of risk; security valuation, performance measurement, asset allocation, and portfolio optimization. By using Excel, Visual Basics for Applications (VBA), and data from Yahoo Finance, you will able to analyze and build an investment portfolio. This document will be helpful for those who want to expand their knowledge in finance and investments analysis by implementing these theories in Microsoft Excel. It will take time to initially set-up the spreadsheets with the desired data and entering the necessary formulas and VBA code, but once done correctly, comparing different equity securities will be relatively simple. Excel is a superb data analysis tool. Also, Yahoo Finance is a reliable source of free financial data information. It supports bulk data downloads which simplifies the task of downloading the historical financial data from finance.yahoo.com to Excel. This document is a hands-on approach to apply complex financial theories using Excel and VBA as a medium. The following chapters are written to be purposefully easy to understand to be suitable for anyone with a basic understanding of finance and Excel. The easy-to-follow guide will effectively walk you through the process of completing various Excel spreadsheets to develop a graphical depiction of the trade-off between risk and return for an efficient portfolio.