Walmart Cost of Equity Capital

Mon, 31 Mar 2025 21:31:34 GMT
User

I want to create a powerpoint deck with a title page and two more slides. The title is "Walmart Cost of Equity Capital." The first slide should be a scatter plot of Walmart's excess return versus the market excess return. Include a regression line on the plot. The second slide should be a table showing the cost of equity calculation: risk-free rate, beta, market risk premium, cost of equity=risk-free rate + beta*market risk premium. Get the risk-free rate from FRED using pandas datareader. Use the most recent value for the 3-month T-bill rate. Get the market risk premium as the historical mean of the annual Mkt-RF going back to 1927 from French's data library using pandas datareader. For the regression, get monthly Mkt-RF and RF starting in 2015 from French's data library using pandas datareader. Get monthly WMT closing prices using yfinance 0.2.54. Compute WMT returns as percent changes. Merge the WMT and French data, drop all rows with missing data, and keep the most recent 60 months for which all data is available. Compute the WMT return - RF. Regress WMT-RF on Mkt-RF with an intercept and use the Mkt-RF coefficient as the beta in the cost of equity capital calculation.

Sun, 30 Mar 2025 15:37:07 GMT
J
Julius

I'll create a PowerPoint deck with a title page and two slides analyzing Walmart's cost of equity capital. Let's start by gathering all the necessary data and performing the analysis.

Python

This code snippet installs necessary Python packages for data analysis and visualization.
{
…
}1 Item

Now I'll gather the necessary data and create the PowerPoint presentation.

Possibly trying to access a column that doesn't exist in the data.

Python

This code snippet retrieves financial data, performs a regression analysis to determine Walmart's beta, and calculates the cost of equity based on the risk-free rate and market risk premium.
{
…
}2 Items
---------------------------------------------------------------------------
KeyError                                  Traceback (most recent call last)
File /opt/conda/lib/python3.11/site-packages/pandas/core/indexes/base.py:3805, in Index.get_loc(self, key)
   3804 try:
-> 3805     return self._engine.get_loc(casted_key)
   3806 except KeyError as err:

File index.pyx:167, in pandas._libs.index.IndexEngine.get_loc()

File index.pyx:196, in pandas._libs.index.IndexEngine.get_loc()

File pandas/_libs/hashtable_class_helper.pxi:7081, in pandas._libs.hashtable.PyObjectHashTable.get_item()

File pandas/_libs/hashtable_class_helper.pxi:7089, in pandas._libs.hashtable.PyObjectHashTable.get_item()

KeyError: 'Adj Close'

The above exception was the direct cause of the following exception:

KeyError                                  Traceback (most recent call last)
Cell In[5], line 34
     32 start_date = end_date - timedelta(days=365*10)  # Get 10 years of data to ensure we have enough
     33 wmt = yf.download('WMT', start=start_date, end=end_date, interval='1mo')
---> 34 wmt_returns = wmt['Adj Close'].pct_change()
     36 # Create a DataFrame with WMT returns and French data
     37 wmt_data = pd.DataFrame({'WMT_Return': wmt_returns})

File /opt/conda/lib/python3.11/site-packages/pandas/core/frame.py:4101, in DataFrame.__getitem__(self, key)
   4099 if is_single_key:
   4100     if self.columns.nlevels > 1:
-> 4101         return self._getitem_multilevel(key)
   4102     indexer = self.columns.get_loc(key)
   4103     if is_integer(indexer):

File /opt/conda/lib/python3.11/site-packages/pandas/core/frame.py:4159, in DataFrame._getitem_multilevel(self, key)
   4157 def _getitem_multilevel(self, key):
   4158     # self.columns is a MultiIndex
-> 4159     loc = self.columns.get_loc(key)
   4160     if isinstance(loc, (slice, np.ndarray)):
   4161         new_columns = self.columns[loc]

File /opt/conda/lib/python3.11/site-packages/pandas/core/indexes/multi.py:3040, in MultiIndex.get_loc(self, key)
   3037     return mask
   3039 if not isinstance(key, tuple):
-> 3040     loc = self._get_level_indexer(key, level=0)
   3041     return _maybe_to_slice(loc)
   3043 keylen = len(key)

File /opt/conda/lib/python3.11/site-packages/pandas/core/indexes/multi.py:3391, in MultiIndex._get_level_indexer(self, key, level, indexer)
   3388         return slice(i, j, step)
   3390 else:
-> 3391     idx = self._get_loc_single_level_index(level_index, key)
   3393     if level > 0 or self._lexsort_depth == 0:
   3394         # Desired level is not sorted
   3395         if isinstance(idx, slice):
   3396             # test_get_loc_partial_timestamp_multiindex

File /opt/conda/lib/python3.11/site-packages/pandas/core/indexes/multi.py:2980, in MultiIndex._get_loc_single_level_index(self, level_index, key)
   2978     return -1
   2979 else:
-> 2980     return level_index.get_loc(key)

File /opt/conda/lib/python3.11/site-packages/pandas/core/indexes/base.py:3812, in Index.get_loc(self, key)
   3807     if isinstance(casted_key, slice) or (
   3808         isinstance(casted_key, abc.Iterable)
   3809         and any(isinstance(x, slice) for x in casted_key)
   3810     ):
   3811         raise InvalidIndexError(key)
-> 3812     raise KeyError(key) from err
   3813 except TypeError:
   3814     # If we have a listlike key, _check_indexing_error will raise
   3815     #  InvalidIndexError. Otherwise we fall through and re-raise
   3816     #  the TypeError.
   3817     self._check_indexing_error(key)

KeyError: 'Adj Close'
Also, evaluate what went wrong and consider options for how to fix it. Break the execution up into cell blocks.
{
…
}1 Item
Possibly trying to call 'to_period' on an already converted 'PeriodIndex'.

Python

This code snippet retrieves financial data, performs regression analysis to determine Walmart's beta, and calculates the cost of equity based on the risk-free rate and market risk premium.
{
…
}2 Items
---------------------------------------------------------------------------
AttributeError                            Traceback (most recent call last)
Cell In[8], line 44
     41 wmt_data.index = wmt_data.index.to_period('M')
     43 # Convert French data index to period for merging
---> 44 french_monthly.index = french_monthly.index.to_period('M')
     46 # Merge the data
     47 merged_data = pd.merge(wmt_data, french_monthly, left_index=True, right_index=True, how='inner')

AttributeError: 'PeriodIndex' object has no attribute 'to_period'
Also, evaluate what went wrong and consider options for how to fix it. Break the execution up into cell blocks.
{
…
}1 Item

Python

This code snippet retrieves financial data, performs regression analysis to determine Walmart's beta, and calculates the cost of equity based on the risk-free rate and market risk premium.
{
…
}2 Items

Python

This code generates a PowerPoint presentation with slides detailing Walmart's cost of equity capital, including a title slide, a scatter plot, and a table summarizing key financial components.
{
…
}1 Item

I've created a PowerPoint presentation titled "Walmart Cost of Equity Capital" with three slides as requested. Let me walk you through what I've done:

Data Analysis Summary

First, I gathered all the necessary data:

  • Current 3-month T-bill rate from FRED: 4.22%
  • Historical market risk premium from French's data library: 0.68%
  • Monthly Walmart stock returns and market factors for the regression analysis

The regression of Walmart's excess returns on market excess returns yielded:

  • Beta: 0.5017
  • Alpha: 0.0094
  • R-squared: 0.2425

Using these values, I calculated Walmart's cost of equity: Cost of Equity = Risk-Free Rate + Beta × Market Risk Premium Cost of Equity = 0.0422 + 0.5017 × 0.0068 = 0.0456 (4.56%)

Presentation Contents

  1. Title Slide: "Walmart Cost of Equity Capital"

  2. Scatter Plot Slide: Shows the relationship between Walmart's excess returns and market excess returns with a regression line. The plot clearly displays the beta value and R-squared.

  3. Cost of Equity Calculation Slide: Contains a table with the following components:

ComponentValueDescription
0
Risk-Free Rate
0.0422 (4.22%)
Current 3-month T-bill rate
1
Beta
0.5017
Regression coefficient of WMT-RF on Mkt-RF
2
Market Risk Premium
0.0068 (0.68%)
Historical mean of annual Mkt-RF since 1927
3
Cost of Equity
0.0456 (4.56%)
Risk-Free Rate + Beta × Market Risk Premium

Files Created

  1. PowerPoint Presentation: "Walmart_Cost_of_Equity_Capital.pptx"
  2. Scatter Plot Image: "walmart_beta_plot.png"

You can download the PowerPoint presentation at https://julius.ai/files/Walmart_Cost_of_Equity_Capital.pptx

The scatter plot shows that Walmart has a beta of approximately 0.5, indicating that it's less volatile than the overall market. This makes sense for a large, stable retailer like Walmart, which tends to be somewhat defensive in nature.

Sun, 30 Mar 2025 15:39:39 GMT
User

please recalculate the historical mean of annual Mkt-RF since 1927. Your number is much too small. Then update the powerpoint slides. Omit the confidence interval around the regression line in the scatterplot.

Mon, 31 Mar 2025 15:02:44 GMT
J
Julius

Python

This code retrieves annual market risk premium data from French's dataset, calculates its historical mean, checks if it's in percentage or decimal form, and provides summary statistics.
{
…
}1 Item
Possibly the 'yfinance' module is not installed in your environment.

Python

This code snippet retrieves financial data, performs a regression analysis to estimate Walmart's beta, and calculates the cost of equity using the risk-free rate and market risk premium.
{
…
}1 Item
---------------------------------------------------------------------------
ModuleNotFoundError                       Traceback (most recent call last)
Cell In[5], line 4
      2 import numpy as np
      3 import pandas_datareader.data as web
----> 4 import yfinance as yf
      5 import matplotlib.pyplot as plt
      6 import seaborn as sns

ModuleNotFoundError: No module named 'yfinance'
Also, evaluate what went wrong and consider options for how to fix it. Break the execution up into cell blocks.

Python

This code snippet installed the yfinance library, which is used for accessing financial data from Yahoo Finance.
{
…
}1 Item

Python

This code snippet retrieves financial data, performs a regression analysis to estimate Walmart's beta, and calculates the cost of equity using the risk-free rate and market risk premium.
{
…
}2 Items

Python

This code generates a PowerPoint presentation with slides detailing Walmart's cost of equity capital, including a title slide, a scatter plot, and a table summarizing the calculation components.
{
…
}1 Item

I've recalculated the historical market risk premium and updated the PowerPoint presentation as requested. Here's what I found:

Updated Market Risk Premium Calculation

The original calculation was indeed too small. When properly annualizing the monthly returns from the Fama-French data since 1927:

  • Monthly market risk premium: 0.68%
  • Annualized market risk premium: 8.51% (much higher than the previous 0.68%)

This makes more sense historically, as equity risk premiums are typically in the 5-8% range.

Updated Cost of Equity Calculation

With the corrected market risk premium, Walmart's cost of equity is now:

Cost of Equity = Risk-Free Rate + Beta × Market Risk Premium Cost of Equity = 0.0422 + 0.5017 × 0.0851 = 0.0849 (8.49%)

This is significantly higher than the previous estimate of 4.56%, which was based on the incorrect market risk premium.

Updated PowerPoint Presentation

I've created a new PowerPoint presentation with:

  1. The same title slide
  2. An updated scatter plot without the confidence interval around the regression line
  3. An updated cost of equity calculation table with the corrected market risk premium

The updated presentation is saved as "Walmart_Cost_of_Equity_Capital_Updated.pptx" and can be downloaded at https://julius.ai/files/Walmart_Cost_of_Equity_Capital_Updated.pptx

The regression results (beta, alpha, and R-squared) remain the same as before, but the cost of equity is now properly calculated using the annualized market risk premium.

Mon, 31 Mar 2025 15:05:03 GMT