Principles of Finance

# 16.6Using Excel to Make Company Investment Decisions

Principles of Finance16.6 Using Excel to Make Company Investment Decisions

## Learning Outcomes

By the end of this section, you will be able to:

• Calculate NPV using Excel.
• Calculate IRR using Excel.
• Create an NPV profile using Excel.

A Microsoft Excel spreadsheet provides an alternative to using a financial calculator to automate the arithmetic necessary to calculate NPV and IRR. An advantage of using Excel is that you can quickly change any assumptions or numbers in your problem and recalculate NPV or IRR based on that updated information. Excel is a versatile tool with more than one way to set up most problems. We will consider a couple of straightforward examples of using Excel to calculate NPV and IRR.

Suppose your company is considering a project that will cost $30,000 this year. The cash inflow from this project is expected to be$6,000 next year and $8,000 the following year. The cash inflow is expected to increase by$2,000 yearly, resulting in a cash inflow of $18,000 in year 7, the final year of the project. You know that your company’s cost of funds is 9%. Your company would like to evaluate this project. ## Calculating NPV Using Excel To calculate NPV using Excel, you would begin by placing each year’s expected cash flows in a sheet, as in row 5 in Figure 16.4. One approach to calculating NPV is to use the formula for discounting future cash flows, as is shown in row 6. Figure 16.4 Inserting Present Cash Flows Using Excel ($ except Cost of Funds)

Figure 16.5 shows the present value of each year’s cash flow resulting from the formula. The NPV is then calculated by summing the present values of the cash flows.

Figure 16.5 NPV Calculated by Summing Present Values of Cash Flows ($except Cost of Funds) Alternatively, Excel is programmed with financial functions, including a calculation for NPV. The NPV formula is shown in cell J7 in Figure 16.6 below. However, it is important to pay attention to how Excel defines NPV. The Excel NPV function calculates the sum of the present values of the cash flows occurring from period 1 through the end of the project using the designated discount rate, but it fails to include the initial investment at time period zero at the beginning of the project. The NPV function in cell J6 will return$56,947 for this project. You must subtract the initial cash outflow of $30,000 that occurs at time 0 to get the NPV of$26,947 for the project.

When entering the Excel-programmed NPV function, you must remember to include references only to the cells that contain cash flows from year 1 to the end of the project. Then, subtract the initial investment of year 0 to calculate NPV according to the standard definition of NPV—the present values of the cash inflows minus the present value of the cash outflow. Note: Because of the nonstandard use of the term NPV by Excel, many users prefer to use the method described above rather than this predefined function.