How to Do the BF2219 Excel Assignment: A Task-by-Task Guide for NTU Students
The BF2219 Excel assignment asks your group to download ten years of Bloomberg data for three stocks, then run descriptive statistics, a Solver-optimised portfolio, a capital allocation line, CAPM regressions and a 2026 out-of-sample test. Marks reward correct spreadsheets and clear discussion equally, so plan both from day one.
In the 2026 brief the stocks are ExxonMobil, Johnson & Johnson and AMD, the deadline is 11:59 pm on 9 October, and the report runs 8 to 15 pages. If you have just opened the brief and felt your stomach drop, you are in good company. Most groups lose time in the first worksheet, not the last.
Short on time? Send your BF2219 brief to a finance expert for guidance.
What the BF2219 Excel assignment actually tests
According to the brief your group received, the Excel assignment is a group component worth 15% of BF2219 Investments at Nanyang Business School. Groups of four to six students submit a written report and an Excel file through NTU Learn. The closest live brief page on our site is the BF2201/BF2219 NTU investments assignment page, which uses a different stock set but the same structure.
The six worksheets follow the course’s own order: data, statistics, optimal portfolio, CAL, alpha and beta, then realised 2026 returns. Each maps to a learning outcome from Bodie, Kane and Marcus’s Essentials of Investments, the set textbook.
Key requirements at a glance
| Item | 2026 requirement |
|---|---|
| Stocks | XOM, JNJ, AMD |
| Market proxy | S&P 500 (SPX) |
| Risk-free rate | One-month T-bill, Kenneth French data library |
| Sample | Jan 2016 – Dec 2025, 120 monthly returns, plus 2026 to date |
| Report | 8–15 pages plus title page, one-page executive summary, APA bibliography |
| Marking | 17 task items × 3 marks, plus five report attributes × 5 marks |
That last row matters more than students think. A one-line answer to a “discuss” prompt earns the same ceiling as a whole Solver build. Never skip a “discuss” prompt.
Step 1: Get the data right before anything else
Download 121 month-end prices, from December 2015, so that you end up with 120 returns. Pull total returns (dividends reinvested) and PX_LAST prices separately; the price-only series is needed for the dividend comparison chart.
The supporting Bloomberg guide shows two routes: BDH formulas through the Excel add-in, or an HP table exported from the terminal. Either works. What matters is that every column is in USD and on the same month-end dates.
- Convert Bloomberg and French percentages into decimals.
- Line up dates, then check for blanks or duplicated months.
- Build the US$1 growth series with previous value × (1 + return).
- Use XOM or JNJ for the dividend chart. AMD pays no dividend, so it cannot show the effect.
In my experience, half of all “wrong” Sharpe ratios come from the risk-free column still being in percent. Check row one before you build anything on top of it.
Step 2: Means, volatility and correlations
Use AVERAGE for the arithmetic mean and GEOMEAN on (1 + r), minus one, for the geometric mean. STDEV.S gives the monthly standard deviation. The brief names COVARIANCE.S and CORREL, so use exactly those functions; markers look for them.
The geometric mean should be lower, by roughly half the variance. If AMD shows the biggest gap, your sheet is probably working. Then explain correlations by industry: energy, healthcare and semiconductors share the US economy but little else.
Excel functions compared
| Task | Function | Common slip |
|---|---|---|
| Arithmetic mean | AVERAGE | Including the starting blank row |
| Geometric mean | GEOMEAN(1+range)−1 | Applying GEOMEAN to raw returns (fails on negatives) |
| Volatility | STDEV.S | Using STDEV.P without saying so |
| Covariance | COVARIANCE.S | Using raw rather than excess returns |
| Correlation | CORREL | Reporting only the matrix, with no industry discussion |
| Portfolio variance | MMULT with TRANSPOSE | Weights in a column when the formula expects a row |
| Alpha and beta | Analysis ToolPak Regression | Swapping the X and Y ranges |
Step 3: How to set up Excel Solver to maximise the Sharpe ratio
To maximise the Sharpe ratio in Excel Solver, set the objective to the Sharpe cell, choose Max, change the three weight cells, and add constraints that each weight is between 0 and 1 and that they sum to 1. Select GRG Nonlinear, because the Sharpe ratio is not linear in the weights.
The brief insists on a starting point of 1, 0, 0. Students often find Solver “does nothing” from there. Usually the cause is a Sharpe formula that does not actually reference the weight cells, or Simplex LP left selected. If Solver stops at a corner, try Multistart in the GRG options and confirm the answer is stable.
Then compare the optimal portfolio’s Sharpe ratio with the S&P 500’s. Three stocks frequently cannot beat a 500-stock index, and saying so plainly is a stronger answer than forcing a positive spin.
Step 4: The capital allocation line in the BF2219 Excel assignment
The share of wealth in the risky portfolio is y = target σ ÷ σ of the risky portfolio. Because the target equals the market’s volatility, y is 1 for the market case and usually below 1 for the three-stock portfolio. The rest sits in the risk-free asset, at zero cost as the brief allows.
For the $10,000 question, state your compounding method. Expected monthly return compounded over 120 months is the simplest defensible approach. The difference in ending wealth comes from one place only: the slope of each CAL, which is the Sharpe ratio. Chart both lines on one graph and the discussion almost writes itself.
Step 5: CAPM alpha and beta, and why reliability matters
Run four regressions: XOM, JNJ, AMD and the fixed-weight optimal portfolio, each against market excess returns. The intercept is alpha and the X-variable coefficient is beta. Copy the t-statistic and p-value for alpha too.
The alpha question is where many reports go soft. A monthly alpha of 0.5% sounds impressive until you see a t-stat of 0.7. With ten years of data, most single-stock alphas are statistically indistinguishable from zero. Say that, then judge whether a market investor should switch.
Step 6: The 2026 test and the naïve portfolio
Hold the December 2025 weights through the 2026 months available (the brief notes data may stop in August or September). Compute the mean, standard deviation and Sharpe ratio, then repeat for a 1/3-each portfolio.
Do not be alarmed if the naïve portfolio wins. DeMiguel, Garlappi and Uppal (2009) found that the simple 1/N rule is hard to beat out of sample, because optimised weights chase noisy historical means. That is a mature answer to the “why” question, and markers tend to reward it.
Student worries, answered briefly
The same questions keep returning: why Solver returns the starting weights, whether to annualise, what to do when one stock dominates, and how to share Bloomberg time across a group.
- Annualising is optional unless you say otherwise. Monthly figures are fine if every table is labelled.
- A corner solution (one weight at 0) is not an error. Explain it.
- Book your Bloomberg access early in the week. Terminals get busy as deadlines approach.
Mark-maximising checklist
- 120 monthly returns per asset, all in decimals and USD
- Worksheets named exactly as the brief specifies
- Every table has a self-explanatory title and labelled units
- Each rubric item answered with numbers and a discussion
- Report figures match the spreadsheet cell for cell
- One-page executive summary on page two
- APA in-text citations and a bibliography
- Files named as the brief requires
Where groups lose marks
| Mistake | Why it happens | Fix |
|---|---|---|
| RF left in percent | The French file is in percent | Divide by 100 in a helper column |
| Dividend chart drawn with AMD | Picking the “interesting” stock | Use XOM or JNJ |
| Alpha reported without t-stat | Wanting a clear story | Report and interpret significance |
| Executive summary as an introduction | Written first, not last | Write it last, lead with findings |
| Missing citations | Treating Excel work as “not an essay” | Cite the textbook and data sources in APA |
Getting help the right way
If your group is stuck, our finance experts provide guidance, worked explanations and model calculations you can learn from. You remain responsible for your own analysis and for NTU’s academic integrity rules. Your files and identity are kept confidential under our privacy policy.
For a reference model of the six worksheets, see our BF2219 Excel assignment sample (for reference only). Our homework help for university modules page explains how support works.
Send your BF2219 brief today so one broken worksheet does not cost your group the CAL, CAPM and 2026 tasks that depend on it.
Frequently asked questions
How many data points do I need for the BF2219 Excel assignment?
You need 120 monthly returns from January 2016 to December 2025 for each stock and the S&P 500. To calculate price-only returns you also need the December 2015 closing price, so download 121 monthly prices. The 2026 test then uses whatever 2026 months are available.
Why does Excel Solver keep returning 1, 0, 0 as the optimal weights?
Usually the Sharpe ratio cell does not reference the weight cells, or Simplex LP is selected. Check that portfolio mean and variance formulas use the weights, choose GRG Nonlinear and, if needed, enable Multistart. A genuine corner solution is possible but should be explained.
Should I annualise the Sharpe ratio?
The brief does not require it. Monthly Sharpe ratios are acceptable if they are labelled clearly. If you annualise, multiply the monthly Sharpe ratio by the square root of 12 and apply the same convention to every asset so comparisons stay valid.
Which stock should I use for the dividend comparison chart?
Use a dividend payer such as ExxonMobil or Johnson & Johnson. AMD does not pay a dividend, so its total-return and price-only lines would be almost identical and the chart would not show anything useful.
What if the naïve 1/3 portfolio beats the optimal portfolio in 2026?
That is a common and defensible result. Optimised weights depend on estimated means, which are noisy, so out-of-sample performance often disappoints. Discuss estimation error and cite research on the 1/N strategy rather than treating it as a mistake.
Do I need references in an Excel assignment?
Yes. The brief warns that missing or improper citations can be treated as plagiarism and allows APA style. Cite the textbook, the Kenneth French data library and any research you use, and include a bibliography at the end of the report.

Written by
My Assignment Help SG Editorial Team
Our editorial team works with students in Singapore and sees the questions they ask most often. This guide is built from those real queries: where students get stuck, what markers look for and how to plan the work step by step.
