Finance and Financial Management Commons^™

Deep Analysis In Excel Without Vba: Creating Transparent Portable User-Defined Functions With =Lambda, Tom Arnold, Timothy Falcon Crack

Finance Faculty Publications

You can exploit your current knowledge of Excel's formula language to build customized Excel functions. These user-defined functions are so fast and transparent that they may be useful for validating AI answers or replacing them altogether. You can now use loops that previously required VBA or JavaScript. The functions use Excel's new =LAMBDA and =MAKEARRAY functions. You can assign personalized names to functions and their input variables, and they are portable between Workbooks. We present simple examples showing basic syntax, building up to a compact fixed income example: a user-defined function using a Taylor series to estimate the change in …

Creating User-Defined Financial Statement Analysis Functions In Excel, Tom Arnold, Xia (Summer) Liu, Cassandra D. Marshall

Finance Faculty Publications

KEY TAKEAWAYS:

Using Excel’s =LAMBDA and =MAKEARRAY functions, user-defined functions are created to generate (1) sets of ratios covering net working capital, DuPont analysis, and leverage, and (2) a set of related cash flow calculations.

Computing and presenting related ratios and cash flows as a unified set allows for more efficient financial statement analysis.

User-defined functions can be copied from a referenced source file into any new spreadsheet, allowing immediate use in the new file because all associated programming is transferred.

Generating A Bond Option Binomial Tree Model In Excel, Tom Arnold, C. Mitchell Conover, Joseph Farizo, Nancy Tran

Finance Faculty Publications

We demonstrate how to adjust the Katolay, Williams, and Fabozzi (1993) binomial tree model for pricing option-embedded bonds to have separate binomial trees for the forward rate process and for the component bond prices (actual bond prices or derived from a yield curve).  This reduces computational complexity at a given node within a single binomial tree.  Further, the calibration method is performed on “spine” values for the forward rate binomial tree, which is more consistent with the actual forward rates that are utilized as initial values in the calibration process.  The entire model is produced in Excel and uses new …

Epi Dataset, Nancy Tran

Finance Faculty Publications

EPI Dataset.

[Abstract For] Bond Pricing Mechanics Beyond Yield-To-Maturity Based Calculations, Maura Alexander, Tom Arnold, Xiu (Summer) Liu

Finance Faculty Publications

Three methods for pricing bonds are presented: using a constant yield to maturity, using the yield curve with discount factors, and using implied forward rates from the yield curve with backward induction.  By moving beyond the first method, more dynamic duration and convexity analyses emerge.  Further, logical connections and extensions are made in regard to pricing bonds with embedded options, the calculation of a swap rate, and the “bond bootstrapping” process for building a yield curve.

Forthcoming in the Journal of Wealth Management

Stringency Of Family Firms And Owner-Managers In The Transition To Low-Carbon Emissions, Morten Bennedsen, John Doukas, Halit Gonenc

Finance Faculty Publications

This study examines the relationship between carbon emissions and stock returns, with a focus on the role of family ownership and management. Using a sample of 435 publicly listed firms from 14 Western European countries over the period 2010–2020, we explore whether financial markets perceive family-controlled firms, particularly those led by family-member CEOs, differently in terms of their commitment to sustainable practices. Consistent with prior research, our results indicate that firms with higher emissions earn higher stock returns while simultaneously experiencing lower market valuations, consistent with the presence of a carbon risk premium driven by elevated carbon transition risk. Crucially, …

Machine Learning: Thematic Feature Grouping, And The Magnificent Seven: A Forecasting Analysis, Mirarmia Jalali, Mohammad Najand, Andrew Cohen

Finance Faculty Publications

This study examines the predictability of monthly excess returns for the “Magnificent Seven” U.S. technology firms using machine learning and economically motivated thematic feature grouping. Framed as a focused study of the most systemically consequential equity panel in modern markets—seven firms representing over 30% of the S&P 500—the analysis confronts a small-N, large-P environment where economically structured dimensionality reduction is essential. Using 154 firm-level characteristics categorized into 13 economic themes, we evaluate linear, penalized, tree-based, and neural network models in a small-N, large-P setting. Unrestricted models suffer substantial overfitting and fail to outperform the historical average benchmark out-of-sample. In contrast, …

Does Bidder Complexity Affect Market Reactions To M&A Decisions?, Rajib Chowdhury, John A. Doukas

Finance Faculty Publications

We examine whether and how bidder complexity influences investor reactions to merger and acquisition (M&A) announcements. Using an established measure of complexity, we find a significant positive relationship between acquiring firm complexity and cumulative abnormal returns (CAR). This suggests that investors perceive more complex firms as capable and value-enhancing participants in M&A activities. The association is particularly strong for bidders with high operating risk, greater R&D intensity, and larger firm size. We also find that complex bidders tend to offer higher takeover premiums. Overall, our study contributes to the literature by demonstrating that bidder complexity is an important determinant of …

International Evidence On The Relationship Between Financial Literacy And Corporate Cash Holdings, Kenneth Yung, Reza Kahibavil, Su Li, Arian Amidi

Finance Faculty Publications

This study is the first investigation of the relationship between corporate cash holdings and financial literacy (FL). We find that a one‐standard‐deviation increase in FL is associated with an 8%–27% reduction in corporate cash holdings, depending on the measure of FL used. Moreover, higher FL reduces the market value of excess cash, indicating that financially literate environments discourage value‐destroying cash accumulation. The results remain robust across alternative definitions of FL and cash holdings, estimation methods, and country subsamples. We further rule out the influence of endogeneity, refinancing risk, and variation in cash needs. Additional analyses identify agency costs as the …

Natural Resource Rents And Energy Poverty Nexus In Next Eleven Economies, Muhammad Salah Uddin, Ayub Ali, Zobayer Ahmed, Md Nasir Uddin Sikdar, Ahsan Habib, Abdullah Elah Al-Mahde

Finance Faculty Publications

Energy poverty (EP) remains a persistent global challenge with important implications for economic development, public health, and social welfare. While natural resources, particularly oil and gas, are often viewed as key sources of energy access, their effectiveness in mitigating EP remains underexplored in emerging economies. This study examines the relationship between natural resource rents (NRR), specifically oil rents (OR) and natural gas rents (NGR), and energy poverty (EP) in the Next Eleven (N-11) countries from 2000 to 2020. The primary objective is to assess how NRR influences EP at various levels of poverty using the Method of Moments Quantile Regression …

How Operational And Disclosure Complexity Shapes The Conglomerate Discount, Feng Dong, John A. Doukas, Son Wilson, Rongyao Zhang

Finance Faculty Publications

This study examines how firm operational diversification and disclosure complexity jointly drive the conglomerate discount. We disentangle disclosure complexity, which is determined by managerially influenced financial transparency, from accounting complexity, which is a direct informational consequence of operational complexity. We find that operational diversification is consistently associated with significant conglomerate discounts, while disclosure complexity independently reduces firm value and amplifies the magnitude of the discount when diversification is high. These findings suggest that transparent disclosures can mitigate valuation costs, offering important implications for managers seeking effective communication, investors assessing complex firms, and policymakers aiming to enhance disclosure quality and market …

Multiple Application Models For Monte Carlo Analysis In Excel, Tom Arnold, Deniz Besik, Xia (Summer) Liu, Nancy Tran

Finance Faculty Publications

Monte Carlo simulation techniques are performed in Excel with multiple applications for option pricing: a single Geometric Brownian motion (GBM) process, a GBM process with volatility bootstrapping, and two correlated GBM processes.  This leads to the creation of a “generic” Monte Carlo simulation framework that can be applied to many different models in addition to the demonstrated commercial real estate valuation/transaction model.   Because the models are all programmed in Excel with minimal VBA programming, the techniques become very accessible for practitioners.

KEY TAKEAWAYS:

Various Monte Carlo simulation techniques are implemented in Excel to perform option pricing applications with (1) a …

Does Digital Finance Foster Corporate Innovation? Evidence From China, Ning Gu, Ye Wang, Yu Liu, Chengbo Fu

Finance Faculty Publications

This study examines how digital finance foster corporate innovation. Unlike prior research focused on statistical associations, we investigate the underlying economic mechanisms driving that relationship. Using panel data of Chinese listed firms from 2013–2023, we show that digital finance stimulates corporate innovation through two primary channels. On the supply side, digital finance supports innovation by optimizing resource allocation, improving risk management, and providing high-quality information. On the demand side, digital finance stimulates the innovation needs of enterprises by improving their dynamic capabilities. Moreover, a favorable governance environment further amplifies these effects. Heterogeneity analysis reveals stronger impacts for large firms, firms …

Monte Carlo Analysis For A Private Equity Transaction In Excel, Tom Arnold, Xia (Summer) Liu, Cassandra D. Marshall

Finance Faculty Publications

Monte Carlo Analysis for a Private Equity Transaction in Excel

This article demonstrates a Monte Carlo analysis in Excel for a private equity transaction. The approach provides a probability-based assessment of future outcomes (i.e., waterfall calculations), with key model inputs randomly sampled from defined probability distributions. With minimal VBA programming, numerous Monte Carlo trials are generated and recorded in a second worksheet. This output can then be analyzed to assess the riskiness of the transaction. Further, because the analysis is performed entirely in Excel, it is immediately accessible to the practitioner. It is also very “transparent” in its execution, unlike …

[Abstract For] Modeling Private Equity Waterfalls: Incentives, Performance Measurement, And Excel Implementation, Tom Arnold, Summer Liu, Cassandra D. Marshall

Finance Faculty Publications

KEY TAKEAWAYS:

Using Excel’s =LAMBDA and =MAKEARRAY functions allows for the creation of user-defined functions for generating waterfall calculations for a private equity deal, without needing VBA or macro programming.

The three primary waterfall functions determine the waterfall distributions and related performance metrics, including internal rates of return (IRRs) and multiples of invested capital (MOICs), both with and without carried interest.

Copying the cell that uses a waterfall function from the source file to a new file automatically transfers the function and its related intermediate functions. As a result, these functions can be used immediately in other models.

The Dark Side Of Ceo Inside Debt: Evidence From Stock Price Crash Risk, Amir Gholami, Ahmed M. Elnahas

Finance Faculty Publications

Despite being thought of as a governance mechanism, CEO inside debt seems to distort firms' information environment. Our results indicate that CEO inside debt alters managerial orientation and incentives in a way that increases stock price crash risk. Our results are robust after addressing endogeneity using the instrumental variable (IV) approach, a difference-in-differences test based on the implementation of Internal Revenue Code Section 409 A Final Regulations, and Oster's omitted variable diagnostic test, and selection bias using the propensity score matching (PSM) and Entropy balancing (EB) approaches. The results are stronger for firms that are poorly governed, operate in less …

[Abstract And Associated Files For] Multi-Variable Monte Carlo Simulation For Portfolio Analysis In Excel, Tom Arnold, C. Mitchell Conover, Joseph Farizo, Nancy Tran

Finance Faculty Publications

Monte Carlo (MC) simulation has several applications in finance, including risk management, valuation, and portfolio management. In this paper, we use multiple correlated Geometric Brownian motion (GBM) processes that allow for very robust portfolio analysis. In this treatment, five correlated funds make up a portfolio. MC is used to not only forecast possible future portfolio and fund outcomes, but to also evaluate the benefits and costs of a hedging strategy and to consider the effects of different correlation structures between the funds on portfolio performance. Because the programming is in Excel, the analysis is very accessible and available to analysts …

Geopolitical Risk And Corporate Capital Structure, Md Shahedur R. Chowdhury, Mohsen Aram, Siamak Javadi, Ali Nejadmalayeri

Finance Faculty Publications

Using a news-based index of geopolitical risk (GPR) and over 62 years of data, we find that GPR has a long-lasting negative impact on leverage. Our result is robust to different model specifications, different proxies for leverage, a battery of robustness tests, and survives after addressing endogeneity concerns. Further, we provide evidence that the effect is channeled through declining shifts in both the demand and supply of credit. Cross-sectional tests indicate that the effect is stronger for firms with higher existing leverage, those with more irreversible investment, and those whose stock returns are more sensitive to GPR. Overall, our analysis …

House Divided: Executive Political Heterogeneity And Corporate Social Responsibility, Yongdong Wang, Ahmed M. Elnahas

Finance Faculty Publications

Empirical research in financial economics has been focused on studying the impact of CEO characteristics on corporate decision-making and performance. This practice overlooks the leadership and organizational research which postulates that for a senior leader to make a strategic change, managers at subordinate levels must support and reinforce such change. We investigate the effect of the top management team (TMT) political heterogeneity on the CEO’s ability to implant her/his ideology onto the firm’s CSR policies. We present evidence that a CEO’s ideology can shape CSR policies only in the existence of a politically homogeneous TMT. This result is robust to …

Are Environmentally Sensitive Firms More Likely To Release Corporate Environmental Disclosures? Evidence From Environmental Risk Management, Chune Young Chung, Incheol Kim, Rong Yang

Finance Faculty Publications

This study examines the environmental risk management practices of US firms operating in environmentally sensitive industries. Using two proxy variables for ex ante environmental policy risk—firm-level toxic chemical emissions and climate-related keywords in annual financial statements—we demonstrate that firms facing high ex ante environmental policy risk tend to increase the frequency and length of their voluntary sustainability reports. Additionally, we find that proactive sustainability disclosures significantly mitigate firm-level systematic risk (beta) and associated litigation risk. This risk reduction is more pronounced among firms with substantial green innovations and stronger corporate governance practices. Overall, this study highlights the causes and consequences …

The Dark Side Of Competition In Developing Economies: Evidence From Closely Held Smes, Siamak Javadi, Mark Kroll, Yu Liu

Finance Faculty Publications

This paper investigates how product market competition affects the performance of closely held small and medium enterprises (SMEs) in developing economies. In contrast to prior findings that focus on large publicly traded companies in developed economies, we find that market competition has a negative effect on firm performance. Our findings are robust to different measures of competition and firm performance and survive after addressing endogeneity issues. We provide evidence that the adverse effect of competition is channeled through increased corruption. Our findings further suggest that firms respond to competition by attempting to acquire more financial resources and government support, adopt …

[Abstract And Associated Excel File For] Quickly Generated Binomial Tree Applications For Options And Var In Excel Without Vba, Tom Arnold, C. Mitchell Conover, Joseph Farizo, Nancy Tran

Finance Faculty Publications

KEY TAKEAWAYS:

Combining Excel’s =LAMBDA and =MAKEARRAY functions allows for the creation of user-defined functions for producing large binomial trees in less than a second.

Further applications of the user-defined functions allow for very quick valuations of European and American style options with large binomial trees. Applying similar techniques allows for the calculation of value-at-risk (VaR) measures.

Because the binomial tree has a certain structure, a ratio can be computed that determines how the portfolio is performing through time relative to the VaR calculation. A high (low) ratio relative to the amount of the time horizon that has elapsed indicates …

[Associated Excel File For] Facilitating Portfolio Construction Using Ex-Ante Information Ratios And Maximum Sharpe Ratios, Tom Arnold, Joseph Farizo, David S. North

Finance Faculty Publications

Investors should consider Sharpe ratios and correlations together when adding a new fund to their existing portfolio. The ex-ante information ratio (also known as the appraisal ratio) is effective in informing this decision. However, a maximum Sharpe ratio calculation is equally effective in this decision while providing the additional benefit of producing optimal portfolio weights for the combined portfolio. In this paper, we demonstrate how to compute these measures to help facilitate portfolio construction.

The Impact Of Short-Term Rental Activity On House Prices: Evidence From Coastal Virginia, Navid Safari, Lei Zhang, Timothy M. Komarek

Finance Faculty Publications

Short-term rentals such as Airbnb have grown substantially in recent years. In this study, we investigated the effect of Airbnb rentals on neighboring house prices in Hampton Roads, Virginia. We focus on three different measures of Airbnb activity to obtain more robust results. Our measures include active Airbnb listings, active listings with at least one reservation, and the sum of reservations over three different periods. Consistent with previous literature, our main results show that prior Airbnb rental activity has a positive impact on the surrounding housing sales prices. Furthermore, the localized impact diminishes over geographic space (i.e., distances greater than …

Labor Heterogeneity, Labor Adjustment Costs, And Externally Financed Firm Growth, Enxi An, Kenneth Yung

Finance Faculty Publications

Highlights

• Because skilled workers entail higher hiring and firing expenses, firms tend to retain skilled workers in the face of the high labor adjustment costs (LACs).

• The retention of skilled workers imposes inflexibility on business operations and exposes firms to cash flow shocks.

• Firms with high LACs hoard precautionary cash in the face of higher cash flow risk.

• We find a positive relationship between LACs and externally financed firm growth.

• Equity is the major source of external funds for firm growth in the face of high LACs.

• The retention of skilled workers elevates conflicts …

The Role Of Intangible Assets In Shaping Firm Value, Feng Dong, John Doukas

Finance Faculty Publications

This study introduces a new metric to evaluate a firm's intangible asset intensity, focusing on its ability to generate revenue from nonphysical assets. It finds a strong positive correlation between firm performance and both internally generated and externally acquired intangible assets. Firms with high intangible intensity outperform peers by 3% annually. The oversight of intangible assets is identified as a factor in value stocks' underperformance. Rigorous tests, including endogeneity checks, confirm these firms exhibit superior accounting quality, labour investment efficiency and acquisition returns. A framework highlights how managerial attributes enhance firm value through decision-making.

Artificial Intelligence And Digital Technologies In Finance: A Comprehensive Review, Soudeh Pazouki, Mohamad Jamshidi, Mirarmia Jalali, Arya Tafreshi

Finance Faculty Publications

This study explores the transformative impact of artificial intelligence (AI) and digital technologies on the financial technology (FinTech) industry, highlighting their role in fostering business growth, operational efficiency, and enhanced customer engagement. AI-driven strategies have unlocked new avenues for streamlining workflows, boosting productivity, and expanding financial inclusion by reaching underrepresented populations. However, these advancements also pose challenges, including navigating complex regulatory frameworks and adapting to the rapidly evolving technological landscape. This paper delves into the macroeconomic effects of AI, examining its influence on labor markets, consumer behavior, and organizational success. Furthermore, the paper discusses blockchain applications and their potential to …

Does The Acquirer's Complexity Affect The Market Reaction To M&A Decisions?, Rajib Chowdhury, John A. Doukas

Finance Faculty Publications

We examine whether and how acquiring firms' complexity affects investor perception at the announcement of M&A decisions. Using well-established data of firm complexity developed through combining machine learning and a lexicon of words, we find a significant positive relationship between acquiring firm complexity and cumulative abnormal returns (CAR), indicating that more intricate firms are viewed as more valuable or competent in mergers and acquisitions. This association is stronger for acquirers with high operating risk, organizational capital, R&D intensity, large and independent boards, and larger targets. We also present evidence that high-complexity bidders offer higher takeover premiums. Our study contributes to …

The Effects Of Antitrust Laws On Horizontal Mergers: International Evidence, Chune Young Chung, Iftekhar Hasan, Jihoon Hwang, Incheol Kim

Finance Faculty Publications

This study examines how antitrust law adoptions affect horizontal merger and acquisition (M&A) outcomes. Using the staggered introduction of competition laws in 20 countries, we find antitrust regulation decreases acquirers’ five-day cumulative abnormal returns surrounding horizontal merger announcements. A decrease in deal value, target book assets, and industry peers' announcement returns are consistent with the market power hypothesis. Exploiting antitrust law adoptions addresses a downward bias to an estimated effect of antitrust enforcement (Baker (2003)). The potential bias from heterogeneous treatment effects does not nullify our results. Overall, antitrust policies seem to deter post-merger monopolistic gains, potentially improving customer welfare.

[Abstract For] Excel Functions For Option Prices And Greeks Without Vba, Tom Arnold, Joseph Farizo, Jonathan M. Godbey

Finance Faculty Publications

The =LAMBDA function within Excel provides a powerful new tool for investors and analysts. In this treatment, we demonstrate =LAMBDA functions that calculate an option’s intrinsic value, price, and Greeks based on the Black-Scholes model. We additionally construct an =ALLGREEKS function within Excel’s built-in =MAKEARRAY function that quickly produces option prices and Greeks given six parameters: the underlying spot price (S), strike price (X), annual return volatility (V), days to maturity (DTM), days in a year (DIY), and the annual risk-free rate (RF). In addition to writing this guide on how to create these useful functions, we provide a downloadable …