1 / 12

Prepared by Diane Tanner University of North Florida

Chapter 16. Using Excel to Determine NPV and IRR. Prepared by Diane Tanner University of North Florida. Financial Functions with Excel 2007. Excel’s built-in financial functions Useful for calculating NPV and IRR Accessed through the Formulas ribbon Utilizes a function wizard

landry
Download Presentation

Prepared by Diane Tanner University of North Florida

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 16 Using Excel to Determine NPV and IRR Prepared by Diane Tanner University of North Florida

  2. Financial Functions with Excel 2007 • Excel’s built-in financial functions • Useful for calculating NPV and IRR • Accessed through the Formulas ribbon • Utilizes a function wizard • Makes it easy to enter information

  3. Calculating NPV with Excel 2007 • Type data into the worksheet: 2. Click Formulas, then choose Insert Function.

  4. Calculating NPV with Excel 2007 continued • The Function Wizard will be displayed. • Type NPV in the search field. Select NPV from the list displayed.        Search field

  5. Calculating NPV with Excel 2007 continued Click OK to display the Function Argument Wizard.         • The Wizard displays 3 fields: • Rate • Value1 • Value2

  6. NPV with Excel, Continued 6. Rate is the interest rate per period. Input with a percent sign or decimal format, e.g. 8.5%, or .085  .085

  7. NPV with Excel, Continued 7. There are 2 options for future cash flows: Enter each cash flow separately by year, where: Value1is the cash flow expected for period 1 Value2is the cash flow expected for period 2, etc. OR Select all cash flow amounts from contiguous cells for year 1 through the end of the useful life.   WARNING: DO NOT select the acquisition cost as one of the cash flows (i.e. year 0) .085 C6:E6

  8. NPV with Excel, Continued 8.  Click OK and the present value of all the cash inflows will appear in a worksheet cell. The NPV formula is displayed in the formula bar as: =NPV(0.085,C6:E6) Click your cell pointer at the end of the NPV formula which appears in the formula bar. Type + and the cell reference of the year 0 cash flow amount. This will add the cash outflow that appears in cell B6 as a negative. =NPV(0.085,C6:E6)+B6

  9. Calculating IRR with Excel 2007 Access the Function Wizard thru the Formulas ribbon. Type IRR in the search field. Select IRR from the list displayed.        Search field

  10. Calculating IRR with Excel 2007 continued 3. Click OK to display the IRR Function Argument Wizard.         • The Wizard displays 2 fields: • Values • Guess

  11. IRR with Excel, Continued 4. Values is the range of cells that contains ALL of the cash flows from year 0 to the end of the useful life.  5. Select all the cash flows in cells B6 to E6. 6. Ignore Guess for most instances. 7. Click OK and the IRR of the investment will appear. Because IRR is a decimal, format using 2 decimals and percentage number formatting, i.e., 4.31%

  12. The End

More Related