Portfolio Optimization in Excel Project
An Analysis of Exchange-Traded Funds in Excel: Delving into Portfolio Optimization and Efficient Frontier Techniques. intermediate
With Ivan Kitov
Type: Course project
Duration: 4 Hours
Case Description
As an investment analyst, your task is to construct an optimized portfolio using Exchange-Traded Funds (ETFs) across multiple asset classes.
Your goal? To maximize returns while managing risk, just as professional portfolio managers do.
This project explores portfolio optimization and efficient frontier analysis using historical data from eight ETFs spanning equities, bonds, real estate, and commodities. By working with real financial data, you’ll gain practical insights into asset allocation and risk management in a complex investment environment.
What You’ll Learn:
✔ Calculate portfolio returns and risks using Excel
✔ Optimize asset allocation to balance risk and return
✔ Apply efficient frontier analysis to identify the best portfolio mix
✔ Use Excel’s Solver add-in for advanced optimization
By the end, you’ll have hands-on experience in investment portfolio management, equipping you with essential skills for real-world financial decision-making.
Project requirements
To complete this project, you’ll need Microsoft Excel 2007 or later with the Solver add-in enabled.
How to Enable Solver in Excel:
- Open Excel and go to File > Options.
- Select Add-Ins, then choose Excel Add-ins from the "Manage" dropdown and click Go.
- Check Solver Add-in and click OK.
- You’ll find Solver under the Data tab in the Analysis group.
With Solver enabled, you can optimize portfolio returns and risk by setting constraints and solving for the best asset allocation.
Project files
You’ll work with historical monthly price data for eight Exchange-Traded Funds (ETFs) from January 1, 2020, to January 1, 2022, sourced from Yahoo Finance. The ETFs include:
- Vanguard Total Stock Market ETF (VTI)
- iShares S&P SmallCap 600 Value Index Fund ETF (IJS)
- iShares MSCI EAFE Index Fund ETF (EFA)
- Vanguard Emerging Markets ETF (VWO)
- Vanguard Real Estate ETF (VNQ)
- iShares 20-Year Treasury Bond ETF (TLT)
- iShares Corporate Bond ETF (LQD)
- iShares Gold Strategy ETF (IAUF)
Available Data Points:
✔ Date – Trading date
✔ Open, High, Low, Close Prices – Price movements within a trading day
✔ Adjusted Close Price – Adjusted for dividends and stock splits
✔ Volume – Total shares traded
Use the Portfolio Optimization in Excel.xlsx file to complete the analysis.
- 1 Project file
- Guided and unguided instructions
- Part 1: Portfolio Return
- Part 2: Portfolio Risk
- Part 3: Minimum-Variance Portfolio
- Part 4: Optimal Risky Portfolio
- Quiz
