Excel Basics for Finance - Boston College



Excel Appendix E1: Excel (2007) Basics for Finance

Overview and Objectives

The purpose of this Appendix is to offer tips on how to use some basic features of Excel that you may find useful in this course. The Appendix is intended for those with little experience using Excel or those whose skills may be rusty. After reading this appendix (it is recommended that you set up your own spreadsheet and follow along with the examples), you should understand the basic elements of:

• Entering and copying cell formulas

• Formatting numbers

• Relative and absolute cell references

• Graphing the results from a set of calculations

• Creating variable names

• Using Excel’s built-in finance functions

• Using the Data Table feature in Excel

• Using the Solver and Goal Seek tools in Excel

The following common finance problem is used to introduce these features:

Problem: You start with $10,000 in a bank account, which pays interest at the rate of 5% per year. If you make no withdrawals, how much money will you have in your account at the end of each year for the next ten years?

A. Entering a Formula in a Cell

We begin by setting up the following spreadsheet:

[pic]

Figure E1.1. Initial spreadsheet set-up

One way to make the calculation called for would be to type in the year numbers 1, 2, … 10, one by one, in Column A. The bank pays us 5% interest each year on the beginning-of-year balance. Furthermore, the balance at the beginning of each year is equal to the balance at the end of the previous year. Thus, to calculate the balance at the end of Year 1, we could click on Cell B5 and type the formula “=B4*(1+5%)”. Then, press the “Enter” key to activate the formula:

[pic]

Figure E1.2. Entering the formula for compound interest over one year.

The formula appears in the formula bar, labeled fx, and the result from the calculation is shown in cell B5 in the spreadsheet. More generally, write any formula by beginning with the “equals” sign. Arithmetic operations are add (+), subtract (-), multiply (*), divide (/) and raise to a power (^). The order of operations follows standard algebraic practice (e.g., multiplication or division first, followed by addition or subtraction, except any operations contained in parentheses are carried out first). Thus, in the formula in Cell B5 above, 1 and 5% are first added, and the resulting sum is then multiplied by the number in Cell B4.

We could complete the calculations for the problem by entering the analogous formula in each of Cells B6:B14. For example, the formula in Cell B6 would read “=B5*(1+5%). This would correctly complete the problem, but several alternative steps would streamline our task.

B. Copying Cell Formulas

First, instead of entering the year numbers one by one, we could note that each year number equals the previous year number plus one. Thus we could enter the formula “=A4+1” in Cell A5 as shown below:

[pic]

Figure E1.3. Entering “=A4+1” in cell A5.

Next, note that the lower right-hand corner of Cell A5 has a small square, called the “handle.” By left-clicking on the handle and dragging it down to Cell A14, we can copy the analogous formula into each of cells A6:A14. We could also do the same thing by left-clicking on the handle of Cell B5 and dragging it down to Cell B14. Or, to make things even a little quicker, we could select both Cells A5 and B5 simultaneously and then drag the handle on Cell B5 down to Cell B14. This will copy the formulas in both cells down, as shown below:

[pic]

Figure E1.4. Copying the formulas in Cells A5 and B5 down to Cells A14 and B14.

C. Formatting numbers

While this accurately completes the required calculations, we may feel that there are too many unsightly digits after the decimal point in the later-year bank balances. After all, the bank will only allow us to withdraw amounts equal to the nearest penny. To express our results in dollar terms, accurate to two decimal places, we can select Cells B4:B14. Then, go to the Home tab on the ribbon, and in the number group, bring down the menu and click on “Currency.” This formats the numbers in the selected cells as (U.S.) dollar figures, rounded off to the nearest penny:

[pic]

Figure E1.5. Dialog box for formatting the way in which numbers are displayed.

D. Relative and Absolute Cell References

In the above, we entered the interest rate as a number (5%). Whenever we have a parameter value like this, it is generally better to give it a label and then enter the specific number right next to the label. The reason for this is to facilitate performing a sensitivity analysis to see how our year-end balances would change if the interest rate changed.

As shown below, we can enter the label “RATE” (for interest rate) in Cell A1 and the specific value, 5%, in Cell B1. We can then rewrite the formula in cell B5 as “=B4*(1+B1),” so that the formula now refers to the interest rate value in Cell B1. Writing the formula in this form has a further advantage: Instead of typing in the reference “B1”, we can write “=(B4*1+” and then click on Cell B1 and add a close parentheses ). This automatically enters the correct reference in the formula and reduces potential errors from typing the cell reference incorrectly. But now, when we drag the handle from Cell B5 down to Cell B14, something goes wrong:

[pic]

Figure E1.6. Erroneously copying formula with relative cell reference

The problem is that B1 by itself is a relative cell reference. For example, when we copy the formula down, the formula in Cell B6 reads “=B5*(1+B2)”, instead of “=B4*(1+B1).” When we click on Cell B5 and write “=B4*(1+B1), the spreadsheet interprets this as “multiply the number in the cell immediately above this one by the quantity one plus the number in the cell that is 4 rows above this one”. The cell references thus refer to relative positions in the spreadsheet. When we copy the formula in Cell B5 down to Cell B6, the spreadsheet takes the ending balance in the cell immediately above, B5, but then multiplies this by the quantity one plus the number in the cell four rows above, or Cell B2. Since there is nothing in Cell B2, the spreadsheet interprets this as an interest rate of 0%.

We need instead to make sure that the formula refers absolutely to the number in Cell B1 and not its relative position in the spreadsheet. This can be accomplished by writing the cell reference as $B$1, which is called an absolute cell reference. Furthermore, instead of typing in the dollar signs, we can click on Cell B1 when entering the formula and then press the F4 key. This enters the dollar signs automatically.

Following these steps and dragging the handle on Cell B5 down to Cell B14 then fills in the spreadsheet as follows:

[pic]

Figure E1.7. Correctly copying formula with absolute cell reference $B$1.

Now, if we wish to see what happens to the yearly balances when the interest rate changes to 10%, we can simply type “10%” in Cell B1 and all of the calculations will be automatically redone with the 10% interest rate:

[pic]

Figure E1.8. Changing the spreadsheet in Figure E1.7 to incorporate a 10% interest rate.

E. Graphing Results

Now select all of the data in both the “Year” (Cells A4:A14) and “Year-End Balance” (Cells B4:B14) columns. Then go to the Insert tab on the ribbon, and in the Charts group, bring down the Scatter menu, and click on the scatter diagram with smooth lines:

[pic]

Figure E1.9. Selecting the chart type for the selected data.

This inserts a smooth-line graph in the spreadsheet:

[pic]

Figure E1.10. The unedited scatter diagram

Clicking next on the Layout tab, we can edit the chart to suit our purposes. For example, the legend serves no purpose here, so under the Labels group, bring down the Legend menu and select “None.” Similarly, we can bring down the Chart Title and Axis Titles menus and enter appropriate titles (you can go back to the Home tab and change the font size for these labels if you don’t like the font size that appears automatically). The finished graph looks like this:

[pic]

Figure E1.11. The finished chart displayed in the spreadsheet

Other editing is also possible. For example, bringing down the Plot Area menu under the Background group on the Layout tab, you can select a different color for the plot area if desired.

F. Creating Variable Names

In the example thus far, we have expressed the balance at the end of each year as the beginning balance for that year times the quantity one plus the interest rate. While this is accurate, there may be times when we would prefer to calculate the ending balance after a certain number of years all in one step as a function of the initial balance, the interest rate and the number of years for which the funds remain on deposit, without knowing the balance at the beginning of each year. To do so, let’s go to a new worksheet and enter labels and numerical values for each of these three parameters. We will use the names RATE, INITIAL and NPER for the interest rate, Year 0 initial balance and number of years, respectively. The fourth variable, ENDBAL, is what we want to solve for.

Algebraically, the value of ENDBAL, is obtained by taking the initial balance and compounding it forward for NPER years at the given interest rate:

[pic] (E1.1)

We can enter this formula in cell B4 in the spreadsheet by clicking on the cells containing the data for the three inputs:

[pic]

Figure E1.12. The formula for ENDBAL expressed in terms of cell references

For this relatively simple formula, this is perfectly satisfactory, and the answer is consistent with the year-by-year calculations above. If we were trying to enter a more complex formula in cell B4, however, it might be easier to spot any errors in the formula if it looked more like its algebraic version. We can accomplish this by “Creating Names.”

To do so, select the Cells A1:B3, including the three variable names and the data. Then go to the Formulas tab, and in the Defined Names group, click on “Create from Selection.” This brings up the following dialog box:

[pic]

Figure E1.13. Dialog box for “Create Names from Selection.”

Checking “Left column” and clicking “OK,” means that the three names in the left-hand column of the selected area will be associated with the three data values in the corresponding cells to the right of the names. Now enter the formula for ENDBAL exactly as we did before by clicking on the relevant data cells:

[pic]

Figure E1.14. The formula for ENDBAL expressed in terms of variable names

This time, when we click on the cells to enter the formula in Cell B4, the three bits of data are identified in the formula bar by the variable names we have created, rather than by their cell references. Since the formula in the formula bar now has the same look as the algebraic formula (E1.1), it is easier to spot any errors we may have made in entering the formula in the spreadsheet.

G. Using Excel’s Built-In Functions

Excel has a number of built-in functions that perform common calculations in Excel. This relieves us of the need to enter our own formulas in many cases.

The problem we have just been working on is equivalent to finding the future compound value of an initial amount when interest is paid over a specified number of periods. This calculation is made by the “FV” (for future value) built-in function.

To illustrate, delete the formula that you entered in Cell B4 above for ENDVAL. With the now-blank Cell B4 selected, click on the fx to the left of the formula bar. This brings up a dialog box with a drop-down menu that offers you the choice of different functions listed by category. The FV function is contained under “Financial” functions, and once you select “Financial,” an alphabetic list of individual functions comes up[1]. Scroll down the list and select “FV,” noting that, once you’ve done so, a brief description of what the function does and its required inputs comes up underneath the function:

[pic]

Figure E1.15. Choosing a function from the “Insert Function” dialog box

Instead of clicking on fx, you can also find the function FV by going to the Formulas tab, clicking on “Financial” in the Function Library group and then selecting FV from the menu of financial functions. Once you’ve selected the FV function, click the “OK” button, which brings up another dialog box in which you can enter the inputs. This can in turn be done by simply clicking the cells that contain the relevant data (since we previously created variable names, these names show up in the dialog box when you click on the corresponding cells:

[pic]

Figure E1.16. Entering inputs for the FV function

Notice that two inputs have been left blank: “Pmt” and “Type.” Pmt allows you to specify an intermediate payment, to be made each year. For example, if you wanted to withdraw $500 per year from your bank account and then the remainder at the end of Year 10, you would enter “500” for Pmt. Type allows you to specify that any payments occur at the beginning rather than at the end of the year. Leaving the “Type” box blank denotes end-of-year payments, which is what we want here, since we are determining the ending balance. If we inserted a “1” in the “Type” box, the calculation would be made for beginning-of-period payments.

Note also that a minus sign has been inserted in front of “INITIAL” in the dialog box. This is because the Excel functions adopt the convention that, if you receive a positive amount in the future, you will have to invest something now in return for that future payment. Your initial deposit of $10,000 can be thought of as an outflow to you today, since you are forgoing the use of that money for the next ten years. The initial negative cash flow is then offset by the future positive flow, when you withdraw your money from the account ten years from now.

Finally, click the “OK” button in the dialog box to enter the formula in cell B4 and to display the result of the function calculation:

[pic]

Figure E1.17. Calculated value of ENDBAL using built-in function FV

The use of other Excel financial functions is discussed in detail in Excel Appendix E2, “Guide to Useful Built-In Functions in Excel” and the accompanying numerical examples.

H. Using the Data Table Feature

Now that we have calculated ENDVAL for RATE = 5%, INITIAL = $10,000 and NPER = 10 years, we might now want to see at a glance how the results would change with different values of the inputs. Suppose, for example, that you wanted to compare the ending balances after 10 years for interest rates of 2%, 4%, 5%, 6%, 8% and 10%. First, go to the spreadsheet and enter the interest rate values you want to use in a column. This has been done in Cells D4:D9 below. In the adjacent column, where you will want to display the corresponding ending balances, click on Cell E3 and enter the formula “=B4:”

[pic]

Figure E1.18. Set-up for constructing a Data Table

Thus, whatever calculation is performed in Cell B4 will be duplicated in Cell E3. Now, select the array of Cells D3:E9 (including the blank cell D3). Then, go to the Data tab, in the Data Tools group, click on “What-If Analysis” and select “Data Table” from the drop-down menu. When the dialog box comes up, click on Cell B1 in the “Column input cell” box:

[pic]

Figure E1.19. Entering RATE (Cell B1) as the column input for the Data Table

What you have done is instruct the spreadsheet to take the interest rate values running down the left-hand column of the selected area (hence, column input cell, rather than row input cell) and substitute these values successively into Cell B1. Since the ENDBAL cell, D4, depends on the interest rate value in B1, this will generate six separate ENDBAL calculations, one for each interest rate. Then, since the selected Cell E3 duplicates the ENDBAL calculation in D4, once we click the “OK” button these six ENDBAL values will display down column E, with each ENDBAL value next to its corresponding interest rate:

[pic]

Figure E1.20. Results from the completed Data Table

To make our table look more professional, we might wish to reformat Cells E4:E9 to show dollar amounts, rounded to two decimal places (this has been done in Figure E1.20). If, say, we wanted to paste this table into a Word document, it would also look better if the sample calculation in Cell E3 did not show up. This we can accomplish by clicking on cell E3, going back to the Home tab and clicking on the little arrow that points downward and to the right in the lower right-hand corner of the Number group. This brings up the “Format Cells” dialog box. Select “Custom” in the list of format categories. In the “Type” box to the right, erase any contents that come up and type in a semicolon (;):

[pic]

Figure E1.21. Formatting the sample calculation cell (E3) to appear blank

When you then click the “OK” button, the calculated value in Cell E3 no longer displays. Note that, when you click on Cell E3, the formula still appears in the formula bar, so the cell is still doing its work, but calculation no longer displays in Cell E3, and our table has a less cluttered look:

[pic]

Figure E1.22. Data Table with Cell E3 formatted to appear blank

You can also create a two-dimensional data table, showing, say, how ENDBAL varies with changes in both RATE and NPER. To do so, leave the “=B4” formula in Cell E3 (still formatted to appear blank). This will again serve as the “sample calculation.” Enter the interest rate values you want to use in column E, starting with Cell E4, and enter the various numbers of periods you want to use in Row 3, starting with Cell F3. Now select the entire area E3:I9 (including the sample calculation Cell E3) and select “Table from the Data menu. This again brings up the dialog box, but this time we will enter both row and column input cells. The row input cell in this case is B3, the number of periods. This instructs the table to successively substitute the values of NPER arrayed across Row 3 into Cell B3 and then recalculate B4, which in turn feeds into E3. Next, click on cell B1 as the column input cell to simultaneously substitute the values of RATE running down Column E into Cell B1, recalculating B4 and thus E3:

[pic]

Figure E1.23. Creating a two-dimensional Data Table

When we click on the “OK” button, the table then fills in, showing the ENDBAL value that corresponds to each (RATE, NPER) combination:

[pic]

Figure E1.24. Completed two-dimensional Data Table

I. Using the Solver and Goal Seek Features

Finally, let’s return to the spreadsheet in Figure E1.17, which shows the solution for ENDBAL, given that the investor deposits $10,000 initially and earns 5% interest per year for 10 years. Suppose we know in advance that we will need $30,000 ten years from now. If the investor still deposits an initial $10,000 for ten years, what interest rate will the investor need to earn in order for the ending balance to reach $30,000 ten years from now?

One way to solve this problem is to use the Solver feature in Excel. Solver is found under the Data tab in the Analysis group[2]. To set up the problem, enter the label “REQUIRED” in Cell A5 and enter the $30,000 required ending balance in Cell B5. Then create the label “Diff” in Cell A6 and enter the formula “=B4-B5” in Cell B6. Cell B6 represents the difference between actual and required ending balance when $10,000 is invested at 5% for 10 years:

[pic]

Figure E1.25. Difference between actual and required ending balance

Now, click on Cell B6, go to the Data tab, and select “Solver” in the Analysis group. This brings up a dialog box in which Cell B6 is the “target cell”. We have a choice of trying to maximize, minimize or achieve a particular target value. In the dialog box “Value of:” has been checked and the value zero has been entered in the box just to the right. That is, we want to set the difference between the actual and required ending balances equal to zero, which will achieve our target. Finally, since the interest rate is the variable, we are trying to solve for, we enter Cell B1 as the cell we want to change in order to reach our target:

[pic]

Figure E1.26. Entering the Solver parameters

When we click on “Solve,” the Solver searches iteratively for and finds a solution:

[pic]

Figure E1.27. The Solver solution

If we then click on “OK,” the solution is saved, and we know that it will require an 11.61% annual interest rate to achieve a required ending balance of $30,000 in ten years.

As is often the case in Excel, there is more than one way to find this solution. An alternative is to leave the spreadsheet set-up unchanged, and with Cell B6 still selected, go to the Data Tools group on the Data tab, bring down the drop-down menu under What-If Analysis, and select “Goal Seek.” When the dialog box comes up, we instruct Goal Seek to set Cell B6 equal to a value of zero (which means we will have achieved our required ending balance) by changing the Cell B1 containing the interest rate value. Clicking “OK” then gives us the solution, which is the same as what we found with Solver[3].

[pic]

Figure E1.28. Set-up to use Goal Seek to solve for the interest rate

[pic]

Figure E1.29. The Goal Seek solution

Solver can do anything that Goal Seek can, but Solver can also do more. As can be seen from the Solver Parameters dialog box in Figure A1.27, Solver also allows us to find the maximum or minimum possible values of a particular variable, and it allows us to change more than one parameter at a time or to impose constraints on any of the parameter values.

Note that we can also solve the problem at hand using the built-in RATE function[4]. This function gives us the rate of return that will allow a given initial balance to grow to a specified ending balance within a specified number of years. To use RATE, go back to the spreadsheet in Figure E1.18. Now, in place of the original contents of Cell B4 (ENDBAL), enter “$30,000.” Delete also the original contents of Cell B1 (RATE), and, with Cell B1 selected, go to the Formulas tab and select RATE from the menu of Financial functions. Then, fill in the resulting dialog box as follows:

[pic]

Figure E1.30. Filling in the RATE dialog box to find the required interest rate

As when we used the FV function in Section G above, Pmt and Type can be ignored in this example, and the initial balance in Cell B2 has been entered with a negative sign, since present value, Pv, and future value, Fv, must have opposite signs. Again, this reflects the assumption that money must be invested today (an outflow) in order to yield a positive inflow in the future. If we then click “OK” in the dialog box, the RATE solution, which is consistent with both the Solver and Goal Seek solutions, appears:

[pic]

Figure E1.31. The RATE function solution for the required interest rate

This completes our discussion of some basic features in Excel that you may find useful in working through a variety of problems in finance and displaying your results. So far, however, we have only seen a hint of the built-in Excel functions that simplify many common calculations in finance. A more detailed discussion of these functions and their use is found in Excel Appendix E2.

-----------------------

[1] If you see only an abbreviated list of functions, click on the Office button in the upper left-hand corner of the screen. Then click on the Excel Options button at the bottom of the drop-down menu. Next, click on “Add-Ins,” in the left-hand column of the Excel Options menu. This will bring up a list of add-ins. If the Analysis ToolPak shows up in the inactive list, select it and then click the Go button. This brings up a list of add-ins with check boxes. Check the box for Analysis ToolPak. While you’re at it, make sure that “Solver Add-in” is also checked, as we will use this later. Then click “OK.” If these two features have not yet been installed, you will receive prompts to install them.

[2]If you don’t see Solver in the Analysis group, go back to Footnote 1 and follow the instructions to make sure Solver has been installed and activated.

[3] In this case, we could equally well have used Cell B4, the ending balance, as our target and have set the target equal to $30,000. This would give us the same solution (try it). In more complex problems, however, Goal Seek and Solver seem to have an easier time setting a cell equal to zero, rather than to a specific, non-zero number. Thus, you may find it convenient to set problems up in terms of setting a difference between two cells equal to zero.

[4] Although neither Solver nor Goal Seek is needed to solve this particular problem, an appropriate built-in function is not always available. Thus, it is useful to know how to work with Solver and Goal Seek as well.

-----------------------

Click “None”

;

................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download