Sure! Let's break this down **step by step** so you can follow along in Excel: --- ## 1. **Given Data** - **Market Portfolio (S&P 500):** - Expected Return (\( r_M \)) = **12%** - Standard Deviation (\( \sigma_M \)) = **20%** - **Risk-free Rate (\( r_f \))** = **5%** - **Risk Aversion Coefficient (A)** = **2.5** --- ## 2. **Capital Allocation Line (CAL) Equation** The CAL shows all possible combinations of the risk-free asset and the market portfolio: \[ E(r_C) = r_f + \frac{E(r_M) - r_f}{\sigma_M} \cdot \sigma_C \] Where: - \( E(r_C) \): Expected return of complete portfolio - \( \sigma_C \): Standard deviation of complete portfolio Plug in the given numbers: \[ \text{CAL Slope} = \frac{0.12 - 0.05}{0.20} = \frac{0.07}{0.20} = 0.35 \] \[ E(r_C) = 0.05 + 0.35 \cdot \sigma_C \] --- ## 3. **Investor's Indifference Curve** The equation for an indifference curve (utility) is: \[ U = E(r_C) - 0.5 A \sigma_C^2 \] --- ## 4. **Find the Optimal Complete Portfolio** Maximize utility by choosing the optimal \( y^* \) (proportion in market portfolio): \[ y^* = \frac{E(r_M) - r_f}{A \cdot \sigma_M^2} \] Plug in values: \[ y^* = \frac{0.12 - 0.05}{2.5 \times (0.20)^2} = \frac{0.07}{2.5 \times 0.04} = \frac{0.07}{0.10} = 0.7 \] So, **70% in the market, 30% in risk-free asset**. - \( E(r_C^*) = r_f + y^* \times [E(r_M) - r_f] = 0.05 + 0.7 \times 0.07 = 0.05 + 0.049 = 0.099 = 9.9\% \) - \( \sigma_C^* = y^* \times \sigma_M = 0.7 \times 0.20 = 0.14 = 14\% \) - \( U^* = 0.099 - 0.5 \times 2.5 \times (0.14)^2 = 0.099 - 1.25 \times 0.0196 = 0.099 - 0.0245 = 0.0745 \) --- ## 5. **How to Plot in Excel** **A. Create the CAL Line:** 1. In Excel, create a table: - Column A: Risk (\( \sigma_C \)), from 0% to 25% (e.g., 0, 0.05, 0.10, 0.15, 0.20, 0.25) - Column B: Return (\( E(r_C) \)), use: `=0.05 + 0.35 * [risk cell]` **B. Create Indifference Curves:** 2. Pick several utility levels around the optimal one (say, 0.065, 0.075, 0.085), or just plot the optimal. - Column C: For each risk value, use: `=[return cell] - 0.5 * 2.5 * ([risk cell])^2` or if you want a specific utility, rearrange for \( E(r_C) \): \[ E(r_C) = U + 0.5A\sigma_C^2 \] - For optimal utility (\( U^* = 0.0745 \)), use: `=0.0745 + 0.5*2.5*([risk cell])^2` **C. Mark the Optimal Portfolio** 3. The optimal complete portfolio is at (\( \sigma_C^* = 0.14 \), \( E(r_C^*) = 0.099 \)). Add this as a separate data point. --- ## 6. **Sample Excel Table** | Risk (σ) | CAL Return | Indifference Curve (U = 0.0745) | |----------|------------|----------------------------------| | 0.00 | 0.050 | 0.0745 | | 0.05 | 0.0675 | 0.0807 | | 0.10 | 0.0850 | 0.0870 | | 0.14 | 0.0990 | 0.0937 | | 0.15 | 0.1025 | 0.0958 | | 0.20 | 0.1200 | 0.1095 | | 0.25 | 0.1375 | 0.1271 | --- ## 7. **Scatter Plot** - Select all data and insert an **XY Scatter plot with smooth lines**. - Plot **CAL** and **Indifference Curve** on the same graph. - Add the **optimal portfolio point** at (0.14, 0.099) (insert as a marker, or add a data label). --- ## 8. **Excel Formula Examples** - **CAL**: `=0.05 + 0.35 * [risk cell]` - **Indifference curve** (for optimal U): `=0.0745 + 0.5 * 2.5 * ([risk cell])^2` --- ## 9. **Summary Table** | Portfolio | Risk (σ) | Return (E[r]) | Utility (U) | |-----------------------------|----------|---------------|-------------| | Optimal Complete Portfolio | 0.14 | 0.099 | 0.0745 | --- ## 10. **Final Notes** - The **optimal complete portfolio** is where the **indifference curve is tangent to the CAL**. - On the plot, this is at **(σ=0.14, E[r]=0.099)**. --- ### **If you need a sample Excel file, let me know!** You can now create the table and plot as described above in Excel. If you need a sample screenshot or more help, just ask!

1. Introduction Understanding the relationship between risk and return is fundamental in portfolio theory. The Capital Market Line (CAL) represents the set of optimal portfolios that combine the risk-free asset with the market portfolio, providing the highest possible expected return for a given level of risk. The slope of the CAL reflects the market price of risk and is derived from the excess return of the market portfolio over the risk-free rate divided by the portfolio’s risk (standard deviation). Investors also have preferences characterized by their risk aversion coefficient, which influences their optimal portfolio choice. The indifference curve, representing an investor’s utility level, illustrates the trade-off between risk and return that maximizes their satisfaction given their risk appetite. The point where the CAL is tangent to an indifference curve signifies the optimal complete portfolio, balancing risk and return according to the investor's preferences. The problem involves identifying this optimal portfolio, calculating its expected return, risk, and utility, and visualizing these elements through plots. The key concepts include the CAL, which guides the risk-return trade-off, and indifference curves, which reflect individual risk preferences. Understanding these allows for precise determination of the optimal investment mix. **Explanation:** This introduction outlines the core concepts of CAL and investor preferences essential for analyzing portfolio optimization. It clarifies how combining the market portfolio with the risk-free asset creates an efficient frontier, and how investor risk aversion shapes the optimal portfolio. This foundational knowledge is necessary to interpret the subsequent calculations and graphical representations, enabling a comprehensive understanding of the portfolio selection process. --- 2. Presentation of Relevant Formulas Required To Solve The Question a) **Capital Allocation Line (CAL):** \[ E(r_C) = r_f + \frac{E(r_M) - r_f}{\sigma_M} \times \sigma_C \] - **Description:** This formula calculates the expected return of a complete portfolio \( E(r_C) \) with risk (standard deviation) \( \sigma_C \) by linearly combining the risk-free asset and the market portfolio. The slope \(\frac{E(r_M) - r_f}{\sigma_M}\) represents the market price of risk, indicating the excess return per unit of risk. b) **Optimal Portfolio Proportion (tangency portfolio):** \[ y^* = \frac{E(r_M) - r_f}{A \times \sigma_M^2} \] - **Description:** This formula determines the fraction of wealth to invest in the risky market portfolio to maximize the investor’s utility, considering their risk aversion \(A\). It arises from utility maximization where the investor balances expected return against risk. c) **Expected Return and Risk of the Optimal Portfolio:** \[ E(r_{opt}) = r_f + y^* \times (E(r_M) - r_f) \] \[ \sigma_{opt} = y^* \times \sigma_M \] - **Description:** These formulas compute the expected return and risk of the investor’s optimal complete portfolio, based on the proportion \( y^* \) in the market portfolio. d) **Investor Utility Function:** \[ U = E(r_C) - \frac{1}{2}A \sigma_C^2 \] - **Description:** This quadratic utility function reflects the investor's preference, balancing expected return against risk, scaled by their risk aversion \(A\). The maximum utility corresponds to the optimal portfolio. e) **Indifference Curve Equation:** \[ E(r_C) = U + \frac{1}{2}A \sigma_C^2 \] - **Description:** This rearranged form shows the expected return for any given risk level at a specific utility level, representing the investor’s indifference curve. **Explanation:** These formulas are fundamental tools for solving the portfolio optimization problem. They are derived from principles of mean-variance analysis and utility maximization, enabling the quantification of risk-return trade-offs, optimal allocations, and investor preferences. Their application leads to precise calculations of the optimal portfolio’s risk, return, and utility. --- 3. A Detailed Step-by-Step Solution **Step 1: Calculate the slope of the CAL** \[ \text{Slope} = \frac{E(r_M) - r_f}{\sigma_M} = \frac{0.12 - 0.05}{0.20} = \frac{0.07}{0.20} = 0.35 \] *Explanation:* This slope indicates the additional expected return per unit of risk when combining the risk-free asset with the market portfolio, serving as the efficient frontier's tangent line. --- **Step 2: Determine the optimal proportion \( y^* \) in the risky asset** \[ y^* = \frac{E(r_M) - r_f}{A \times \sigma_M^2} = \frac{0.07}{2.5 \times (0.20)^2} = \frac{0.07}{2.5 \times 0.04} = \frac{0.07}{0.10} = 0.7 \] *Explanation:* This proportion reflects the investor’s optimal investment in the market portfolio to maximize utility, considering their risk aversion. --- **Step 3: Calculate the expected return of the optimal complete portfolio** \[ E(r_{opt}) = r_f + y^* \times (E(r_M) - r_f) = 0.05 + 0.7 \times 0.07 = 0.05 + 0.049 = 0.099 = 9.9\% \] *Explanation:* This is the expected return of the portfolio that best aligns with the investor’s risk preferences, lying on the CAL. --- **Step 4: Calculate the risk (standard deviation) of the optimal complete portfolio** \[ \sigma_{opt} = y^* \times \sigma_M = 0.7 \times 0.20 = 0.14 = 14\% \] *Explanation:* This risk measure quantifies the variability in returns for the optimal portfolio, consistent with the chosen proportion in the risky asset. --- **Step 5: Calculate the utility of the optimal portfolio** \[ U^* = E(r_{opt}) - \frac{1}{2}A \times \sigma_{opt}^2 \] \[ U^* = 0.099 - 0.5 \times 2.5 \times (0.14)^2 = 0.099 - 1.25 \times 0.0196 = 0.099 - 0.0245 = 0.0745 \] *Explanation:* Utility quantifies investor satisfaction, balancing return and risk, with the maximum utility indicating the optimal portfolio. --- **Step 6: Graphical representation** - Plot the CAL: \( E(r) = 0.05 + 0.35 \times \sigma \) over \( \sigma \) from 0 to 0.25. - Plot the indifference curve at utility level \( U^* = 0.0745 \): \[ E(r) = U^* + \frac{1}{2}A \sigma^2 = 0.0745 + 1.25 \times \sigma^2 \] - Mark the point (\( \sigma=0.14, E(r)=0.099 \)) as the optimal portfolio. *Explanation:* The intersection point of the CAL and the indifference curve indicates the optimal portfolio, which maximizes utility given the risk-return trade-off. --- **Step 7: Final results** - **Optimal Portfolio Risk:** 14% - **Expected Return:** 9.9% - **Utility:** 0.0745 **Conclusion:** The investor’s optimal complete portfolio involves investing approximately 70% in the market portfolio and 30% in the risk-free asset, resulting in an expected return of 9.9%, a risk of 14%, and a utility level of approximately 0.0745. The graphical plot confirms this by showing the point of tangency between the CAL and the investor’s indifference curve. --- **Note:** This comprehensive solution integrates the theoretical framework, precise calculations, and graphical interpretation, ensuring a deep understanding of the portfolio optimization process.