Finance and Financial Management Commons^™

Deep Analysis In Excel Without Vba: New Functionality Could Validate Ai Answers Or Replace Them Altogether, 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

100 Conversation Starters To Develop Student Critical Thinking In Intermediate And Financial Accounting Courses, Joe B. Hoyle

Bookshelf

INTRODUCTION

Why create a book containing nothing but accounting problems?

The genesis of an idea is often difficult to recall, but I can trace the creation of this project to a single sentence found in a specific book.  One recent summer, to help prepare myself for the upcoming academic year, I began reading Why Don’t Students Like School? by Daniel T. Willingham.  On page 16 of the dog-eared copy I acquired on the Internet, I was drawn to a key sentence like a moth to a flame.  The words were buried rather unobtrusively within a long paragraph but, for me, …

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 …

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.

[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 …

[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.

[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 …

Portfolio Optimization Tools In Excel, Tom Arnold, Joseph Farizo, Terry D. Nixon

Finance Faculty Publications

The calculus and matrix algebra associated with finding the optimal portfolio weights for a set of securities is tedious. However, Excel tools make the computations simple, with minimal programming needed to arrive at optimal portfolio weights for securities in a portfolio. We provide this Excel template and techniques for acquiring optimal weights, which is useful for personal and institutional investors alike.

Takeaways:

• The calculus and matrix algebra associated with optimizing portfolio weights is tedious, but not necessary for finding optimal portfolio weights
• Based on the results of the associated mathematics for optimizing portfolio weights, a very accessible Excel template can …

[Abstract For] Using Excel’S “Lambda” Function To Compute Modified Duration, Dollar Duration, And Bond Convexity, Tom Arnold, Joseph Farizo, Andrew C. Szakmary, Nancy Tran

Finance Faculty Publications

We create a modified duration function that is more accessible than Excel’s current Macaulay duration function (=DURATION) which requires several details about the bond.

The dollar duration and convexity functions are not currently available in Excel’s default functions. When these functions are copied to a second Excel file, the functions automatically become available as functions within the second file without the need for recreating the functions.

We provide instructions on how to implement the =LAMBDA function to more than just the creation of the bond application functions.

Crypto Currency Exchange And Mining Excel Simulations, Tom Arnold, Joseph Farizo, Jonathan M. Godbey

Finance Faculty Publications

The mathematics underlying blockchain-based cryptocurrencies is beyond the scope of most undergraduate finance programs. However, students should understand the intuition behind blockchain so that they might better understand how to apply this technology to future cases. In this paper, we develop a mathematically simple digital signature example and a mathematically simple proof-of-work simulation for classroom use.

Converting Npv And Irr Cash Flows Into A Financial Calculator Using An Excel Template, Tom Arnold, Summer Liu, Cassandra D. Marshall

Finance Faculty Publications

An Excel template is developed that converts a series of cash flows on a timeline into the associated keystrokes for the TI BAII-Plus financial calculator in order to calculate NPV and IRR. Unlike videos and other presentations, the student is able to see, all at once, the keystrokes required for the financial calculator within the template after the student enters the correct inputs for how the cash flows occur through time. Many times, this crucial link of translating the cash flows through time into the financial calculator is lost. Further exercises are provided to reinforce proficiency.

The Abcs Of Modified Bond Duration And Wxyzs Of Bond Convexity, Tom Arnold, Andrew C. Szakmary

Finance Faculty Publications

By breaking the mathematical derivation of Macaulay Duration, Modified Duration, and Bond Convexity into smaller easily calculated component parts, a more manageable means of calculation for these bond measures emerges for the student. Further, an Excel spreadsheet or an algorithm within a programming language can also be implemented using these smaller component calculations. The Excel template provided can be made into an assignment or used as a resource for the student.

Black-Scholes Option Pricing And Greeks Using Excel’S “Lambda” Function, 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 show how to create a function that calculates an option’s intrinsic value, price, and delta based on the Black-Scholes model. Other option Greek functions and calculations are available in a downloadable file. The LAMBDA function is not limited to the Black-Scholes model and has important advantages over Excel’s previous solution of creating user-defined functions in VBA.

Required Minimum Distribution (Rmd) Spreadsheet Calculators Based On The Secure Act Of 2022, Tom Arnold, John H. Earl, Jr., Cassandra D. Marshall

Finance Faculty Publications

Required Minimum Distribution (RMD) Spreadsheet Calculators

Based on the SECURE Act of 2022

The Setting Every Community Up for Retirement Enhancement Act (SECURE Act) of 2022 made a second round of changes (relative to the SECURE Act of 2019) to the required minimum distribution (RMD) schedule for individual retirement accounts (IRAs) and defined contribution retirement plans. Excel spreadsheet calculators are developed to calculate the new annual RMD cash flows throughout retirement for those who are retired and for those who are planning to retire. The spreadsheet calculators also allow savings to accrue with interest if the RMD is in excess …

Tiger Investment Partners. “To Stream Or Not To Stream” Case Study, Roger R. Schnorbus

Robins School of Business White Paper Series, 1980-2022

Tiger Investment Partners (TIP) TIP is a boutique technology investment firm in Richmond, VA. It was founded in 2012 by its three principal partners and is managing over $300 million in investment funds. Its stated mission is to someday be as successful as Berkshire Hathaway with a focus on firms that were or would be major disruptors in their given industries.

The Effect Of Accounting Conservatism On Measures Of Financial Constraints, Taewoo Kim, Brandon Byunghwan Lee, Bo Meng, Daniel Paik

Accounting Faculty Publications

This study examines the relationship between accounting conservatism and measures of financial status. We find that, in general, a higher level of accounting conservatism is associated with a lower level of financial constraints – thereby making external funds less costly. The results also show that for a firm with a higher bid-ask spread or a higher likelihood of bankruptcy, this negative relationship between conservative financial reporting and financial constraints is intensified. In other words, a higher level of accounting conservatism is likely to make external funds less costly especially for those firms with a higher level of bid-ask spread or …

Simplified Portfolio Optimization Using Cramer’S Rule In Excel, Tom Arnold, Joseph Farizo, Terry D. Nixon

Finance Faculty Publications

The matrix algebra associated with finding minimum variance portfolio weights, mapping the efficient frontier, and determining the tangency portfolio weights is greatly simplified in Excel by applying Cramer’s Rule. Only a scant knowledge of linear algebra is necessary for producing a very intuitive presentation for a multi-asset portfolio. The technique is very easily replicated for an assignment or for providing a classroom resource.

Monte Carlo And Bootstrapping Carry Trade Simulations In Excel, Tom Arnold, C. Mitchell Conover, Joseph Farizo

Finance Faculty Publications

In a currency carry trade, an investor borrows money in a low interest rate currency and invests in a high interest rate currency. The trade is profitable if the future exchange rate does not adjust to the interest rate differential. After downloading exchange rate data, a Monte Carlo simulation of a carry trade is performed in Excel based on a normal distribution and the data’s mean and standard deviation. A bootstrapping carry trade simulation exercise is also generated by randomly selecting observations from the historical data. In contrast to the Monte Carlo simulation, the bootstrapping exercise preserves the skewness within …

Using Excel To Simulate A Financial Calculator And Excel Tvm Formulas, Maura Alexander, Tom Arnold, Joseph Farizo

Finance Faculty Publications

Excel is used to build a simulation of the TI BAII-Plus financial calculator to illustrate the N, I/Y, PV, PMT, and FV inputs. Unlike other financial calculator simulators, this template also displays the corresponding Excel functions to aid in transitioning the student to using Excel for financial analysis.

Visual Timelines In Excel To Illustrate Tvm Calculations, Maura Alexander, Tom Arnold, Joseph Farizo

Finance Faculty Publications

Time value of money calculations are illustrated through developing a timeline with cash flow graphics in Excel. The cash flow graphics can be used in the live or virtual classroom and as a resource for students outside of the classroom. Further, the graphic is readily adjustable to different scenarios making it useful for multiple time value of money topics.

Visual Presentation Of Mirr And Mnpv Calculations, Tom Arnold, Joseph Farizo

Finance Faculty Publications

Project cash flows and modified cash flows are presented in an illustrative graphic within Excel for the live or virtual classroom. Further, the graphic computes and displays the relevant modified internal rate of return (MIRR) and modified net present value (MNPV), with associated formulas. The presentation allows for a discussion of the reinvestment assumption attributed to the internal rate of return (IRR) and the net present value (NPV) calculations.

Chronic Disease Management: How It And Analytics Create Healthcare Value Through The Temporal Displacement Of Care, Steve M. Thompson, Johnathan Whitaker, Rajiv Kohli, Craig Jones

Finance Faculty Publications

The treatment of chronic diseases consumes 86% of U.S. healthcare costs. While healthcare organizations have traditionally focused on treating the complications of chronic diseases, advances in information technology (IT) and analytics can help clinicians and patients manage and slow the progression of chronic diseases to result in higher quality of life for patients and lower healthcare costs.

We build on prior research to introduce the notion of temporal displacement of care (TDC), in which IT and analytics create healthcare value by displacing the time at which providers and patients make interventions to improve healthcare outcomes and reduce costs. We propose …

[Introduction To] Fundamentals Of Advanced Accounting: Seventh Edition, Joe B. Hoyle, Thomas F. Schaefer, Timothy S. Doupnik

Bookshelf

Fundamentals of Advanced Accounting, 7th edition, is ideal for those schools wanting to cover twelve chapters in their advanced accounting course. This concise text allows students to think critically about accounting, just as they will do preparing for the CPA exam. The text continues to show the development of financial reporting as a product of intense and considered debate that continues today and will into the future.

Anticipation And Reaction To Going Concern Modified Audit Opinions By Sophisticated Investors, Marshall A. Geiger, Abdullah Kumas

Accounting Faculty Publications

The purpose of this paper is to examine whether institutional investors (i) anticipate a distressed firm's receipt of a first‐time going‐concern modified audit opinion, and (ii) react to a first‐time going‐concern modified opinion by engaging in abnormal net selling of firm shares. Using a proprietary database of US institutional investor trades, we find that institutional investors are net sellers of first‐time going‐concern opinion firms beginning 6 months before the release of the report and remain net sellers through the subsequent 3 months. We also find that the severity of the reasons auditors modify their opinions is associated with increased trading …