Skip to ContentGo to accessibility pageKeyboard shortcuts menu
OpenStax Logo
Principles of Finance

16.6 Using Excel to Make Company Investment Decisions

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

Menu
Table of contents
  1. Preface
  2. 1 Introduction to Finance
    1. Why It Matters
    2. 1.1 What Is Finance?
    3. 1.2 The Role of Finance in an Organization
    4. 1.3 Importance of Data and Technology
    5. 1.4 Careers in Finance
    6. 1.5 Markets and Participants
    7. 1.6 Microeconomic and Macroeconomic Matters
    8. 1.7 Financial Instruments
    9. 1.8 Concepts of Time and Value
    10. Summary
    11. Key Terms
    12. Multiple Choice
    13. Review Questions
    14. Video Activity
  3. 2 Corporate Structure and Governance
    1. Why It Matters
    2. 2.1 Business Structures
    3. 2.2 Relationship between Shareholders and Company Management
    4. 2.3 Role of the Board of Directors
    5. 2.4 Agency Issues: Shareholders and Corporate Boards
    6. 2.5 Interacting with Investors, Intermediaries, and Other Market Participants
    7. 2.6 Companies in Domestic and Global Markets
    8. Summary
    9. Key Terms
    10. CFA Institute
    11. Multiple Choice
    12. Review Questions
    13. Video Activity
  4. 3 Economic Foundations: Money and Rates
    1. Why It Matters
    2. 3.1 Microeconomics
    3. 3.2 Macroeconomics
    4. 3.3 Business Cycles and Economic Activity
    5. 3.4 Interest Rates
    6. 3.5 Foreign Exchange Rates
    7. 3.6 Sources and Characteristics of Economic Data
    8. Summary
    9. Key Terms
    10. CFA Institute
    11. Multiple Choice
    12. Review Questions
    13. Problems
    14. Video Activity
  5. 4 Accrual Accounting Process
    1. Why It Matters
    2. 4.1 Cash versus Accrual Accounting
    3. 4.2 Economic Basis for Accrual Accounting
    4. 4.3 How Does a Company Recognize a Sale and an Expense?
    5. 4.4 When Should a Company Capitalize or Expense an Item?
    6. 4.5 What Is “Profit” versus “Loss” for the Company?
    7. Summary
    8. Key Terms
    9. Multiple Choice
    10. Review Questions
    11. Problems
    12. Video Activity
  6. 5 Financial Statements
    1. Why It Matters
    2. 5.1 The Income Statement
    3. 5.2 The Balance Sheet
    4. 5.3 The Relationship between the Balance Sheet and the Income Statement
    5. 5.4 The Statement of Owner’s Equity
    6. 5.5 The Statement of Cash Flows
    7. 5.6 Operating Cash Flow and Free Cash Flow to the Firm (FCFF)
    8. 5.7 Common-Size Statements
    9. 5.8 Reporting Financial Activity
    10. Summary
    11. Key Terms
    12. CFA Institute
    13. Multiple Choice
    14. Review Questions
    15. Problems
    16. Video Activity
  7. 6 Measures of Financial Health
    1. Why It Matters
    2. 6.1 Ratios: Condensing Information into Smaller Pieces
    3. 6.2 Operating Efficiency Ratios
    4. 6.3 Liquidity Ratios
    5. 6.4 Solvency Ratios
    6. 6.5 Market Value Ratios
    7. 6.6 Profitability Ratios and the DuPont Method
    8. Summary
    9. Key Terms
    10. CFA Institute
    11. Multiple Choice
    12. Review Questions
    13. Problems
    14. Video Activity
  8. 7 Time Value of Money I: Single Payment Value
    1. Why It Matters
    2. 7.1 Now versus Later Concepts
    3. 7.2 Time Value of Money (TVM) Basics
    4. 7.3 Methods for Solving Time Value of Money Problems
    5. 7.4 Applications of TVM in Finance
    6. Summary
    7. Key Terms
    8. CFA Institute
    9. Multiple Choice
    10. Review Questions
    11. Problems
    12. Video Activity
  9. 8 Time Value of Money II: Equal Multiple Payments
    1. Why It Matters
    2. 8.1 Perpetuities
    3. 8.2 Annuities
    4. 8.3 Loan Amortization
    5. 8.4 Stated versus Effective Rates
    6. 8.5 Equal Payments with a Financial Calculator and Excel
    7. Summary
    8. Key Terms
    9. CFA Institute
    10. Multiple Choice
    11. Problems
    12. Video Activity
  10. 9 Time Value of Money III: Unequal Multiple Payment Values
    1. Why It Matters
    2. 9.1 Timing of Cash Flows
    3. 9.2 Unequal Payments Using a Financial Calculator or Microsoft Excel
    4. Summary
    5. Key Terms
    6. CFA Institute
    7. Multiple Choice
    8. Review Questions
    9. Problems
    10. Video Activity
  11. 10 Bonds and Bond Valuation
    1. Why It Matters
    2. 10.1 Characteristics of Bonds
    3. 10.2 Bond Valuation
    4. 10.3 Using the Yield Curve
    5. 10.4 Risks of Interest Rates and Default
    6. 10.5 Using Spreadsheets to Solve Bond Problems
    7. Summary
    8. Key Terms
    9. CFA Institute
    10. Multiple Choice
    11. Review Questions
    12. Problems
    13. Video Activity
  12. 11 Stocks and Stock Valuation
    1. Why It Matters
    2. 11.1 Multiple Approaches to Stock Valuation
    3. 11.2 Dividend Discount Models (DDMs)
    4. 11.3 Discounted Cash Flow (DCF) Model
    5. 11.4 Preferred Stock
    6. 11.5 Efficient Markets
    7. Summary
    8. Key Terms
    9. CFA Institute
    10. Multiple Choice
    11. Review Questions
    12. Problems
    13. Video Activity
  13. 12 Historical Performance of US Markets
    1. Why It Matters
    2. 12.1 Overview of US Financial Markets
    3. 12.2 Historical Picture of Inflation
    4. 12.3 Historical Picture of Returns to Bonds
    5. 12.4 Historical Picture of Returns to Stocks
    6. Summary
    7. Key Terms
    8. Multiple Choice
    9. Review Questions
    10. Video Activity
  14. 13 Statistical Analysis in Finance
    1. Why It Matters
    2. 13.1 Measures of Center
    3. 13.2 Measures of Spread
    4. 13.3 Measures of Position
    5. 13.4 Statistical Distributions
    6. 13.5 Probability Distributions
    7. 13.6 Data Visualization and Graphical Displays
    8. 13.7 The R Statistical Analysis Tool
    9. Summary
    10. Key Terms
    11. CFA Institute
    12. Multiple Choice
    13. Review Questions
    14. Problems
    15. Video Activity
  15. 14 Regression Analysis in Finance
    1. Why It Matters
    2. 14.1 Correlation Analysis
    3. 14.2 Linear Regression Analysis
    4. 14.3 Best-Fit Linear Model
    5. 14.4 Regression Applications in Finance
    6. 14.5 Predictions and Prediction Intervals
    7. 14.6 Use of R Statistical Analysis Tool for Regression Analysis
    8. Summary
    9. Key Terms
    10. Multiple Choice
    11. Review Questions
    12. Problems
    13. Video Activity
  16. 15 How to Think about Investing
    1. Why It Matters
    2. 15.1 Risk and Return to an Individual Asset
    3. 15.2 Risk and Return to Multiple Assets
    4. 15.3 The Capital Asset Pricing Model (CAPM)
    5. 15.4 Applications in Performance Measurement
    6. 15.5 Using Excel to Make Investment Decisions
    7. Summary
    8. Key Terms
    9. CFA Institute
    10. Multiple Choice
    11. Review Questions
    12. Problems
    13. Video Activity
  17. 16 How Companies Think about Investing
    1. Why It Matters
    2. 16.1 Payback Period Method
    3. 16.2 Net Present Value (NPV) Method
    4. 16.3 Internal Rate of Return (IRR) Method
    5. 16.4 Alternative Methods
    6. 16.5 Choosing between Projects
    7. 16.6 Using Excel to Make Company Investment Decisions
    8. Summary
    9. Key Terms
    10. CFA Institute
    11. Multiple Choice
    12. Review Questions
    13. Problems
    14. Video Activity
  18. 17 How Firms Raise Capital
    1. Why It Matters
    2. 17.1 The Concept of Capital Structure
    3. 17.2 The Costs of Debt and Equity Capital
    4. 17.3 Calculating the Weighted Average Cost of Capital
    5. 17.4 Capital Structure Choices
    6. 17.5 Optimal Capital Structure
    7. 17.6 Alternative Sources of Funds
    8. Summary
    9. Key Terms
    10. CFA Institute
    11. Multiple Choice
    12. Review Questions
    13. Problems
    14. Video Activity
  19. 18 Financial Forecasting
    1. Why It Matters
    2. 18.1 The Importance of Forecasting
    3. 18.2 Forecasting Sales
    4. 18.3 Pro Forma Financials
    5. 18.4 Generating the Complete Forecast
    6. 18.5 Forecasting Cash Flow and Assessing the Value of Growth
    7. 18.6 Using Excel to Create the Long-Term Forecast
    8. Summary
    9. Key Terms
    10. Multiple Choice
    11. Review Questions
    12. Problems
    13. Video Activity
  20. 19 The Importance of Trade Credit and Working Capital in Planning
    1. Why It Matters
    2. 19.1 What Is Working Capital?
    3. 19.2 What Is Trade Credit?
    4. 19.3 Cash Management
    5. 19.4 Receivables Management
    6. 19.5 Inventory Management
    7. 19.6 Using Excel to Create the Short-Term Plan
    8. Summary
    9. Key Terms
    10. Multiple Choice
    11. Review Questions
    12. Video Activity
  21. 20 Risk Management and the Financial Manager
    1. Why It Matters
    2. 20.1 The Importance of Risk Management
    3. 20.2 Commodity Price Risk
    4. 20.3 Exchange Rates and Risk
    5. 20.4 Interest Rate Risk
    6. Summary
    7. Key Terms
    8. CFA Institute
    9. Multiple Choice
    10. Review Questions
    11. Problems
    12. Video Activity
  22. Index

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.

A screenshot of an Excel spreadsheet shows the cash flow and present value of cash flow for a company. The company’s cost of funds is 9%, entered into cell C2 of the spreadsheet. The year number is entered into Columns C through J of Row 4. The cash flow for years 0-7 is entered in columns C through J of Row 5. The formula to find the present value of cash flow for year 0 is entered in Row C6. The formula is =C5 slash open parenthesis 1 + dollar sign C2 close parentheses carat C4. In this formula, C2, remains constant. The other cell references change to represent the cell the data is entered in. The NPV is calculated using the formula = SUM open parenthesis C6 colon J6 close parenthesis.
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.

Download the spreadsheet file containing key Chapter 16 Excel exhibits.

A screenshot of an Excel sheet shows the results of the calculations from Figure 16.4.
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.

A screenshot of an Excel sheet showing the NPV formula, except IRR/Cost of Funds. The cash flow figures for years zero to seven are entered in columns c through J of Row 5. The formula for calculating NPV is = NPV open parenthesis C2, D5 colon J5 close parenthesis + C5.
Figure 16.6 NPV Formula ($ except IRR/Cost of Funds)

Calculating IRR Using Excel

Excel also provide a function for calculating IRR. This function is shown in Figure 16.7, cell J8. The IRR function properly uses all the project’s cash flows, including the initial cash outflow at time 0, in its calculation, unlike the NPV function. This function will correctly return the IRR of 27.7% for the project. Figure 16.8 shows the completed spreadsheet.

Screenshot of excel sheet showing the function for calculating IRR, except for IRR/Cost of Funds. The cash flow figures for years zero to seven are entered in columns c through J of Row 5. The corresponding formulae for present value of cash flow are displayed below them in row 6. The formula for NPV is equal sign SUM open parenthesis C6 colon J6 close parenthesis. The formula for I R R is equal sign I R R open parenthesis C5 colon J5 close parenthesis.
Figure 16.7 Function for Calculating IRR ($ except IRR/Cost of Funds)
Screenshot of excel sheet showing the final spreadsheet. It shows the company's cost of funds, the cash flow and present value of cash flow for years zero to seven, and the resulting NPV and IRR.  The NPV is 26,947 and the IRR is 27.7%.
Figure 16.8 Final Spreadsheet ($ except IRR/Cost of Funds)

Using Excel to Create an NPV Profile

Firms often do not know exactly what their cost of attracting capital is, so they must use estimates in their decision-making. Also, the cost of attracting capital can change with economic and market conditions. Especially if markets are volatile, a company may use an NPV profile to see how sensitive their decisions are to changes in financing costs. Excel simplifies the creation of an NPV profile.

Middleton Manufacturing is considering installing solar panels to heat water and provide lighting throughout its plant. To do so will cost the company $800,000 this year. However, this upgrade will save the company an estimated $150,000 in electrical costs each year for the next 10 years. Constructing an NPV profile of this project will allow Middleton to see how the NPV of the project changes with the cost of attracting funds.

First, the project cash flows must be placed in an Excel spreadsheet, as is shown in cells D2 through N2 in Figure 16.9. The company’s cost of funds is placed in cell B1; begin by putting in 10% for this rate. Next, the formula for NPV is placed in cell B6; cell B6 shows the NPV of the cash flows in cells D2 through N2, using the rate that is in cell B1.

For reference, compute IRR in cell B4. Calculating IRR is not necessary for creating the NPV profile. However, it gives a good reference point. Remember that if the IRR of a project is greater than the firm’s cost of attracting capital, then the NPV will be positive; if the IRR of a project is less than the firm’s cost of attracting capital, then the NPV will be negative.

An NPV profile is created by calculating the NPV of the project for a variety of possible costs of attracting capital. In other words, you want to calculate NPV using the project cash flows in cells D2 through N2, using a variety of discount rates in cell B1. This is accomplished by using the Excel data table function. The data table function shows how the outcome of an Excel formula changes when one of the cells in the spreadsheet changes. In this instance, you want to determine how the value of the NPV formula (cell B6) changes when the discount rate (cell B1) changes.

Screenshot of an Excel sheet shows how to create an NPV profile by inserting project cash flows into an Excel sheet. The discount rate is 10%. The cash flow in year zero is negative 800,000. For the remaining 10 years, the cash flow is constant at 150,000. The IRR is 13.41%. The NPV is 121,685.07. The figures for discount rates between 1% and 20% are required.
Figure 16.9 Project Cash Flows Inserted into Excel

To do this, enter the range of interest rates that you want to consider down a column, beginning in cell A7. This example shows rates from 1% to 20% entered in cells A7 through A26. Your Excel file should now look like the screenshot in Figure 16.9.

Next, highlight the cells containing the NPV calculation and the range of discount rates. Thus, you will highlight cells A6 through A26 and B6 through B26 (see Figure 16.10). Click Data at the top of the Excel menu so that you see the What-If Analysis feature. Choose Data Table. Because the various discount rates you want to use are in a column, use the “Column input cell” option. Enter “B1” in this box. You are telling Excel to calculate NPV using each of the numbers in this column as the cost of attracting funds in cell B1. Click OK.

A screenshot of an Excel sheet shows the creation of a Data Table in Excel. The screenshot is the same as Figure 16.9  except cells A6 through A26 and B6 through B26 are highlighted. A pop-up screen called Data table has two fields - Row input cell and column input cell. B1 is entered into column input cell.
Figure 16.10 Creating a Data Table in Excel

After clicking OK, the cells in column B next to the list of various discount rates will fill with the NPVs corresponding to each of the rates. This is shown in Figure 16.11.

A screenshot of an Excel sheet shows the NPV calculated for various discount rates. The screenshot is the same as Figure 16.10, however, the NPV for discount rates ranging from 1% through 20% are now calculated.
Figure 16.11 NPV Calculated for Various Discount Rates

Now that the various NPVs are calculated, you can create the NPV profile graph. To create the graph, begin by highlighting the discount rates and NPVs that are in cells A7 through A26 and B7 through B26. Next, go to the Insert tab in the menu at the top of Excel. Several different chart options will be available; choose Scatter. You will end up with a chart that looks like the one in Figure 16.12. You can customize the chart by renaming it, labeling the axes, and making other cosmetic changes if you like.

You will notice that the NPV profile crosses the x-axis between 13% and 14%; remember that the NPV will be zero when the discount rate that is used to calculate the NPV is equal to the project’s IRR, which we previously calculated to be 13.43%. If the firm’s cost of raising funds is lower than 13.43%, the NPV profile shows that the project has a positive NPV, and the project should be accepted. Conversely, if the firm’s cost of raising funds is greater than 13.43%, the NPV of this project will be negative, and the project should not be accepted.

An Excel screenshot shows how to create an NPV profile graph using Excel. The screenshot is the same as Figure 16.11, however, a scatter chart is added to the screen. Within the Insert menu the scatter chart from the Scatter sub-menu is displayed.
Figure 16.12 NPV Profile Created Using Excel

Middleton Manufacturing can use this NPV profile to evaluate its solar panel installation project. If the managers think that the cost of attracting funds for the company is 10%, then the project has a positive NPV of $121,685 and the company should install the panels. The NPV profile shows that if the managers are underestimating the cost of funds even by 30% and it will really cost Middleton 13% to attract funds, the project is still a good project. The cost of attracting funds would have to be higher than 13.43% for the solar panel project to be rejected.

Do you know how you learn best?
Kinetic by OpenStax offers access to innovative study tools designed to help you maximize your learning potential.
Order a print copy

As an Amazon Associate we earn from qualifying purchases.

Citation/Attribution

Want to cite, share, or modify this book? This book uses the Creative Commons Attribution License and you must attribute OpenStax.

Attribution information
  • If you are redistributing all or part of this book in a print format, then you must include on every physical page the following attribution:
    Access for free at https://openstax.org/books/principles-finance/pages/1-why-it-matters
  • If you are redistributing all or part of this book in a digital format, then you must include on every digital page view the following attribution:
    Access for free at https://openstax.org/books/principles-finance/pages/1-why-it-matters
Citation information

© May 20, 2022 OpenStax. Textbook content produced by OpenStax is licensed under a Creative Commons Attribution License . The OpenStax name, OpenStax logo, OpenStax book covers, OpenStax CNX name, and OpenStax CNX logo are not subject to the Creative Commons license and may not be reproduced without the prior and express written consent of Rice University.