Code. Models. Analysis. Decisions.

Finance Functions and Modeling in Excel

For versions of Excel: Excel for Office 365, Excel for Office 365 for Mac, Excel 2016, Excel 2016 for Mac, Excel 2013, Excel 2011 for Mac, Excel 2010, Excel 2008 for Mac, Excel 2007

NPV (Net Present Value)

Net Present Value is one of the core evaluation methods utilized in corporate finance for capital budgeting; used to determine the value of future cash flows. It is a robust method to determine whether a project will exceed a rate of return and add value to a firm. In general, NPV yields a single cash value in terms of "today's" dollars from a stream of cash flows that will occur in the future based on an interest rate that can be earned in lieu of investing in the project. This interest rate is known as the cost of capial. An NPV of $0 indicates that the stream of cash flows is earning exactly the cost of capital, while postive NPV indicates earning in excess of the cost of capital. It may go without saying that that negative NPV streams should be avoided. All things equal, a higher NPV project is "better" than a lower one.

IRR (Internal Rate of Return)

Internal Rate of Return is another method of evaluating cash flows, however it is not as robust as NPV. IRR can be defined as the cost of capital that will cause the NPV of a stream of cash flows to be $0. This seems to suggest that a higher rate of return IRR is to be desired. However, IRR is only reliable in cases where a net negative outflow occurs in the first, or "zero" period.

Many projects require additional investment in subsquent periods causing negative net cash flows. When this occurs there is actually more than one IRR making this an unreliable measurement to evaluate projects with.

ROI (Return on Investment)

There are numerous ways to calculate ROI. It can be defined as the net gain on investment divided by the total cost incurred. The method of calculating ROI demonstrated in this tutorial divides the NPV by the total discounted cash outflows.

Capital Budgeting in Excel

Tutorials demonstrating how to calculate NPV, IRR, ROI and payback period or break-even for an investment. Demonstrates manual calculation of present values as well as the use of NPV and IRR functions in Excel.

Excel Financial Functions

Video tutorials show how to use and calculate:

  • Excel NPV Function
  • Excel IRR Function
  • Return on Investment (ROI)
  • Payback Period or Break-even

The spreadsheet used in the video can be downloaded here.

Packback period or Break-even