Using Excel Spin Buttons to Enhance the Visual Presentation of Financial Concepts

Using Excel Spin Buttons to Enhance the Visual Presentation of Financial Concepts

David S. Allen

Associate Professor of Finance

Box 15066

Northern Arizona University

Flagstaff, AZ 86011

(928) 523-7378

Abstract

Spin buttons are Visual Basic object that may be placed on an Excel spreadsheet and linked to cells whose value will change as the spin button is clicked. They are especially useful in performing sensitivity analysis and visually displaying the results. For less mathematically sophisticated students, the use of spin buttons can reveal relationships between financial variables that may otherwise not be apparent. This paper describes the use of spin buttons in general and then shows their application in five typical finance problems, including: bond pricing, option pricing, and portfolio analysis.

Introduction

We often hear it said that today’s students are “visual learners.” In addition to examining the underlying mathematical relationships, many financial relationships can be graphed to enhance the understanding by these visual learners. However, both old-school chalkboard charts and new-school spreadsheet created charts suffer from their static nature. This paper demonstrates how to use Excel spin buttons to create dynamic charts that can simultaneously show the influence of several effects on the outcome.

Inserting Spin Buttons into a Worksheet

In order to insert spin buttons or other Visual Basic controls into a spreadsheet, it is first necessary to ensure that the “Control Toolbox” toolbar in enabled in Excel.

To create and edit the properties of a control, you must be in “design mode.” The top-left button in the Control Toolbox is used to switch between Design Mode and run mode. In Design Mode, clicking on a spin button will allow you to edit the button. In run mode, clicking on the spin button will change the value of the linked cell.

To insert a spin button, click on the spin button icon in the Control Toolbox, then move the cursor to the desired place in the spreadsheet and click-and-drag with the mouse until the desired size is reached. The spin button may be placed anywhere within the spreadsheet, and may be smaller or larger than the spreadsheet cells.

The spin button control may be oriented vertically or horizontally, as seen below. To change the orientation of an existing spin button, right-click on it, then change the “orientation” property as desired. The button is linked to a spreadsheet cell that will increase or decrease by a predefined amount each time the up (right) or down (left) portion of the control is clicked.

After inserting the spin button, right click on it and chose the Properties setting to reveal the Properties dialog box:

There are four properties that you will need to specify:

1)  The LinkedCell property indicates the cell whose value will change when the spin button is clicked.

2)  The Max property is the highest possible value for the linked cell. Once this value is attained, further clicks on the spin button will not increment the cell. It is set to 100 by default, but can contain any integer value.

3)  The Min property is the lowest possible value for the linked cell. Once this value is attained, further clicks on the spin button will not decrement the cell. It is set to 0 by default, but can contain any integer value.

4)  The SmallChange property is the amount by which the linked cell will increment or decrement for each mouse click. It is set to 1 by default. Note that holding down the mouse button will cause the linked cell to change rapidly in value.

Application #1 – Bond price versus yield

Spin buttons are especially useful for charting multivariate relationships. For example, we know that the price of a coupon paying bond is determined by four variables:

·  The face value of the bond.

·  The number of years to maturity.

·  The coupon rate.

·  The yield to maturity.

The bond’s price is usually charted as a function of the yield:

What we cannot easily see, however, is how this relationship is affected by the other variables such as year to maturity or the coupon rate.

The following spreadsheet demonstrates how to use spin buttons to enhance the above chart:

The upper spin button (in cell C3) is linked to cell B3. As we spin this button, the maturity “n” changes, and the chart dynamically changes to show the effect of maturity on the price-yield relationship. The data used to create the chart is just below the chart itself, and references the highlighted cells in the inputs section of the worksheet.

Recall that the spin button can only have an integer value. For the coupon rate on the bond, it would be nice to have fractional values. The solution is to link the spin button to a cell containing its integer value, and then reference that integer in another equation to convert it to a fractional value. For example, the lower spin button (in cell C4) is linked to cell C4, which is hidden beneath the button itself. The coupon rate in cell B4 is just an equation referencing the hidden linked cell C4: =C4/1000.

As we spin this button, the coupon rate changes, and the chart dynamically changes to show the effect of the coupon rate on the price-yield relationship.

For example, if we spin bond’s maturity to ten years, the chart changes to:

With these dynamics charts, students can visually see several of Malkiel’s Bond Theorems in action. For example, by changing the coupon rate, its effect on both bond price and convexity are immediate obvious.

Application #2 – Portfolio Mean-Variance

The use of spin button is especially helpful in visualizing the two-asset portfolio possibilities curve. The student can easily change the correlation between the asset returns and observe the effect on both the portfolio possibilities curve and the minimum variance portfolio.

Application #3 – Black-Scholes Option Pricing Model

Spin buttons make a useful addition to the typical option pricing spreadsheet, regardless of whether or not a chart is used.

The spreadsheet below uses spin buttons to enter the input values for the Black-Scholes option pricing model. The user can change any of the five input variables, and observe the effect not only on the option’s premium, but also on the intrinsic value, time value and delta. As an example of the benefit of spin buttons over a static spreadsheet, the student spin the stock price and observe that the option’s time value is maximized when the option is at the money, and lower when either in-the-money or out-of-the-money.

Students can also use spin buttons to quickly determine the implied volatility for the underlying asset. Just spin the standard deviation cell until the option price in cell B12 is equal to the option’s market price.

Application #4 – Black-Scholes Option Pricing Model Chart

The worksheet below allows students to visually examine the effect of option price inputs on the option’s premium and intrinsic value. In addition, they can visually determine how the time value if affected by the various inputs by observing the vertical difference between the premium and intrinsic value.

Application #5 – Gordon Growth Model

The final example is used to visually examine the Gordon (constant) growth model for common stock valuation.

Student can see how the stock price (value) is affected by dividends and the growth rate in dividends. They can also see that the stock price goes to infinity as the growth rate approaches the required return.

Summary and Conclusion

Spin buttons are an easy to use yet powerful Excel object that can be used to perform sensitivity analysis and visually display the results. They allow students to quickly examine and determine the relationships between financial variables that may not otherwise be readily apparent. This paper shows how to use spin buttons in several settings, most requiring no programming skills at all on the part of the developer or user.

12