For information and reference only – not to be submitted as your own work. This model extract shows method, structure and interpretation. All figures below are illustrative, created to demonstrate the calculations; they are not real Bloomberg results for XOM, JNJ or AMD. Your group must download its own data and write its own analysis in line with NTU’s academic integrity policy.

This sample follows a BF2219 Investments Excel assignment at Nanyang Technological University: three stocks (ExxonMobil, Johnson & Johnson and Advanced Micro Devices), the S&P 500 as the market proxy and the one-month T-bill as the risk-free rate. The closest live brief page is our BF2201/BF2219 NTU investments assignment page, which uses a different stock set, so treat the figures here as a worked pattern rather than a match for your data.

Executive summary (model extract)

Using 120 monthly observations from January 2016 to December 2025, we measured the risk and return of XOM, JNJ and AMD from the perspective of a US-dollar investor. In the illustrative data, AMD delivers the highest mean excess return but also the highest volatility, while JNJ is the least volatile.

A Solver-optimised, long-only portfolio holds all three stocks and reaches a monthly Sharpe ratio of about 0.21. In this illustration that is slightly below the S&P 500’s 0.22, which tells us three stocks cannot fully match a diversified index (Bodie et al., 2017).

Task 1 – Data and the growth of US$1

Worksheet layout for 1_data

The growth chart is a simple line chart of columns M to P. In our illustration AMD finishes far above the others, while XOM and JNJ move closer together and the RF line stays almost flat.

The dividend comparison

For the dividend chart we use JNJ, because AMD pays no dividend. The price-only line ends noticeably below the total-return line, and the gap widens every year. That is compounding at work: each reinvested dividend earns its own future returns.

The conclusion for the report is simple. Measuring a dividend payer, a portfolio or an index on price alone understates its performance, which is why total-return indices are the professional standard (Bodie et al., 2017).

Your data will not look like ours. Need help checking your Bloomberg download or growth chart? Get guidance from a finance expert.

Task 2 – Means, standard deviations and correlations

Formulas used

• Arithmetic mean: =AVERAGE(B3:B122)
• Geometric mean: =GEOMEAN(1+B3:B122)-1 (entered as an array formula in older Excel)
• Standard deviation: =STDEV.S(B3:B122)
• Excess return: =B3-$F3
• Sharpe ratio: mean excess return divided by the standard deviation of excess return
• Covariance and correlation: =COVARIANCE.S(…) and =CORREL(…), as the brief requires

Table 1 – Illustrative monthly excess-return statistics

Illustrative figures only. In this example AMD would be the best single-stock choice on a Sharpe basis, but none of the stocks beats the index.

The geometric mean should sit below the arithmetic mean, and the gap grows with volatility (roughly half the variance). Expect the biggest gap for AMD. Whether higher risk brought higher average return is an empirical question for your sample; energy stocks such as XOM had a difficult 2020, which can break the simple adage over a ten-year window.

Table 2 – Illustrative correlation matrix of excess returns

Low positive correlations are what you would expect from energy, healthcare and semiconductors: all three share exposure to the US economy but respond to different industry drivers. An XOM-only investor clearly gains from adding JNJ and AMD, because correlations well below 1 reduce portfolio variance (Markowitz, 1952).

Task 3 – Solver set-up for the optimal risky portfolio

1. Put the weights in cells B30:D30 and start at 1, 0, 0, as the brief requires.
2. Portfolio mean: =SUMPRODUCT(B30:D30, mean excess returns).
3. Portfolio variance: =MMULT(MMULT(B30:D30, covariance matrix), TRANSPOSE(B30:D30)).
4. Sharpe ratio cell: mean divided by SQRT(variance).
5. Solver: Set Objective = Sharpe cell, To = Max, By Changing = B30:D30.
6. Constraints: B30:D30 ≥ 0, B30:D30 ≤ 1, SUM(B30:D30) = 1. Method: GRG Nonlinear.

Table 3 – Illustrative optimal portfolio

The optimal portfolio more than doubles the Sharpe ratio of XOM alone. In this illustration it still falls just short of the market’s 0.222, so moving from the three-stock portfolio to the market would improve the Sharpe ratio. Your real numbers may point the other way, especially given AMD’s run over the decade; say clearly which way your data goes.

Task 4 – Capital allocation line

The CAL is E(r_C) = r_f + y × E(r_P − r_f), with σ_C = y × σ_P (Bodie et al., 2017). Setting σ_C equal to the market’s 4.5% gives:

Ending wealth uses $10,000 × (1 + monthly mean)^120 with zero RF cost, as the brief allows. Some groups instead use the geometric mean; state your choice. The difference between the two cases comes entirely from the slope of each CAL, which is the Sharpe ratio.

Solver stuck at 1, 0, 0 or the CAL not lining up? Work through it with our finance team, or upload your brief for a quote.

Task 5 – CAPM alpha and beta

Each regression uses Data, then Data Analysis, then Regression, with market excess returns in the X-Range and the stock or portfolio excess returns in the Y-Range. The intercept is alpha and the slope is beta (Sharpe, 1964).

Illustrative values. With t-statistics well below 2, none of the alphas is statistically reliable. A market-index investor should therefore be cautious: a positive point estimate built on one decade of three stocks is weak evidence for switching.

Task 6 – The 2026 out-of-sample test

Hold the Dec-2025 weights across the available 2026 months and compute the mean, standard deviation and Sharpe ratio of the excess returns. Then repeat with one-third in each stock. In our illustrative in-sample data the naïve portfolio’s Sharpe ratio (0.203) is already close to the optimised 0.207.

That gap is small enough for estimation error to reverse it out of sample, which is a well-documented finding: DeMiguel et al. (2009) show the 1/N rule is hard to beat once you account for errors in estimated means. This is the argument to build for the discussion of why the naïve portfolio can win.

Want to understand every step before your deadline? Read our step-by-step guide to the BF2219 Excel assignment, or submit your assignment brief for one-to-one help from a finance specialist.

About the expert

Benjamin Tan Zhi Wei is a finance expert on the My Assignment Help SG team, with areas of expertise in finance, investment analysis, risk management and financial modelling, and 16+ years of experience. For related finance samples, see the FIN353 portfolio management sample, and for writing up the report, our report writing support in Singapore and homework help for university modules.

References

Bodie, Z., Kane, A., & Marcus, A. J. (2017). Essentials of investments (10th ed.). McGraw-Hill Education.

DeMiguel, V., Garlappi, L., & Uppal, R. (2009). Optimal versus naive diversification: How inefficient is the 1/N portfolio strategy? The Review of Financial Studies, 22(5), 1915–1953. https://doi.org/10.1093/rfs/hhm075

French, K. R. (n.d.). Data library. Tuck School of Business at Dartmouth. https://mba.tuck.dartmouth.edu/pages/faculty/ken.french/data_library.html

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

Sharpe, W. F. (1964). Capital asset prices: A theory of market equilibrium under conditions of risk. The Journal of Finance, 19(3), 425–442. https://doi.org/10.1111/j.1540-6261.1964.tb02865.x