DCF Analysis with Robust Sensitivity Tables

 A discounted cash flow analysis will help determine if the future cash flows are worth the risk of investment based on applying various required rates of return (discount rates) to the future cash flows. It is a widely accepted and used concept in valuation. This excel template was built to show various NPV against up to 6 discount rate inputs.

Pricing

The template will be sent to you after purchase via your PayPal/purchasing e-mail. The Google Sheets version is accessible through the Excel file. This is included in the Google Sheets template bundle.

The template comes with two tabs and editable cells are marked with blue text.

Tab 1:

  1. User inputs up to 10 years worth of expected cash contributions and distributions (including 'year 0')
  2. User inputs up to 6 discount rates.
  3. The results will then automatically populate. The output will be 6 different Net Present Values (NPV) via a visual as well as displayed in a sensitivity table with conditional formatting if positive or negative as well as an Internal Rate of Return (IRR).
Tab 2:

  1. User inputs up to 10 years worth of expected cash contributions and distributions (including 'year 0') for up to 4 different scenarios, so this means 4 sets of net cash flows.
  2. User inputs up to 6 discount rates.
  3. The results will then automatically populate. The output will be 24 different Net Present Values (NPV) via a visual that looks a bit like a scatter plot with diamond markers as well as displayed in a sensitivity table with conditional formatting if positive or negative for each cash flow scenario and discount rate.
  4. An IRR sensitivity table will also populate along with its own chart for each of the 4 cash flow scenarios.
The cash flow scenarios could be based on a single project with various upside/base/downside forecasts or it could be completely different projects that the user wants to analyze side-by-side in the context of Net Present Value outputs.