SmartHelping / Valuation / Excel + Google Sheets
DCF Analysis with Sensitivity Tables
Compare the net present value of expected cash flows across six discount rates and up to four scenarios. Review NPV sensitivity tables, IRR results, and charts to see how your investment assumptions affect the analysis.
Google Sheets version accessible through the Excel file.
Immediate download after purchase. By purchasing, you agree to the Terms of Service.
See the template in action
Walk through the DCF sensitivity analysis.
Two tabs included
Analyze one cash flow case or compare four scenarios.
Tab 1: Single cash flow case
Enter one set of expected cash contributions and distributions, then compare the results across your required returns.
- Up to 10 years of annual cash flow inputs, including Year 0
- Up to six discount rate inputs
- Six NPV results in a chart and sensitivity table
- Positive and negative NPV conditional formatting
- IRR for the entered cash flows
Tab 2: Four cash flow scenarios
Enter up to four sets of cash contributions and distributions to compare alternate forecasts or separate projects.
- The same annual input structure, including Year 0
- Up to four scenarios and six discount rates
- Up to 24 NPV results in a sensitivity table and chart
- Conditional formatting for positive and negative NPV
- IRR comparison table and chart for the cash flow scenarios
Cash flows and required returns
Start with your expected contributions and distributions.
Annual cash contributions
Enter expected cash invested over the analysis period, including the Year 0 amount.
Annual cash distributions
Enter expected cash received over the analysis period to build the net cash flow for each case.
Six discount rate inputs
Define up to six required rates of return to compare the NPV of the same expected cash flows.
Inputs marked in blue text
Editable cells are marked with blue text. Enter your assumptions and review the automatically populated tables and charts.
NPV sensitivity and cash flow returns
See the effect of discount rates and different forecasts.
Six NPVs for one case
The first tab evaluates a single set of net cash flows at up to six discount rates, showing the resulting NPVs together in a sensitivity table and visual.
Up to 24 scenario results
The second tab compares four sets of net cash flows across six discount rates. Its NPV chart uses diamond markers, with a corresponding table that highlights positive and negative values.
IRR by cash flow scenario
Review the IRR for the cash flows you enter. The four-scenario tab includes an IRR comparison table and its own chart, alongside the NPV sensitivity analysis.
Two ways to use the scenario comparison
Compare alternate forecasts or different investments.
Different cases for one project
Compare upside, base, downside, or another forecast for the same project using up to four sets of cash flows.
Different projects side by side
Use each scenario for a separate project and compare its NPV across the discount rate inputs, together with its IRR.
Excel download with Google Sheets access
Use the included spreadsheet format that fits your workflow.
Excel template
Download the Excel file after purchase and work through the two analysis tabs.
Google Sheets version
Access the Google Sheets version through the link provided inside the Excel file.
Also included in the Valuation bundle
Explore more valuation and investment analysis tools.
Valuation and DCF Analysis Spreadsheets
Explore templates for business valuation, discounted cash flow analysis, investment returns, and sensitivity analysis.
View the Valuation bundleSupporting article and template
Explore discount rates and distribution structures.
Questions before you start
A few useful details.
How many cash flow scenarios can I compare?
The first tab analyzes one set of cash flows. The second tab supports up to four cash flow scenarios.
How many NPV results does the template calculate?
The single-case tab calculates up to six NPVs across six discount rates. The four-scenario tab calculates up to 24 NPVs across the four cases and six rates.
Can the scenarios represent different projects?
Yes. They can represent alternate forecasts for one project or separate projects you want to compare side by side.
Does the template include IRR?
Yes. The first tab includes an IRR result. The second tab includes an IRR comparison table and chart for the four cash flow scenarios.
How do I access the Google Sheets version?
The Google Sheets version is accessible through the Excel file supplied after purchase.
Compare cash flows and required returns
See your DCF sensitivity in one place.
DCF Sensitivity Template — $45, with Excel and Google Sheets access.
