10-Year DCF Analysis with Robust Sensitivity Tables and IRR
Originally published: 05/05/2021 08:49
Publication number: ELQ-27565-1
View all versions & Certificate

10-Year DCF Analysis with Robust Sensitivity Tables and IRR

Input expected cash flows for up to 10 periods and up to 6 discount rates to view the resulting DCF analysis.

This is a very straight forward financial model with much work put into visuals and ease of use. It is also included in google sheets (link in template).

There are two tabs for summary analysis.

The first tab has 10 periods plus a year 0 input for cash expected to be contributed and cash expected to be returned. There are also inputs for 6 discount rates (required returns). Based on those inputs, the NPV (net present value) will be displayed for each discount rate. This is also displayed in a visual bar chart. The IRR (internal rate of return) of the expected cash flows is also displayed.

The second tab goes a step further and allows for scenario analysis. The user can input up to 4 cash flow streams. This could be to measure completely different projects side-by-side or different potential outcomes of the same project. Based on those 4 cash flow streams and 6 discount rates, 24 NPV will be displayed in a table. The table includes conditional formatting for +/- resulting NPV.

The coolest part of this model is the visual for the multiple scenario tab. It has a scatter plot style look to it where all 24 NPV are displayed for each scenario and each discount rate. Also, in the 2nd tab there are 4 different cash flow streams so that means 4 different IRR results as well. That is then displayed in a separate bar chart.

This Best Practice includes
1 Excel model and 1 Tutorial Video

Acquire business license for $45.00

Add to cart

Add to bookmarks


Further information

Display various NPV results based on input data.

Up to 10 years of cash flow.

Cash flows greater than 10 years.


  • Be the first to review this Downloadable Best Practice

    Write a review


More Best Practices from Jason Varner

See all

Any questions on 10-Year DCF Analysis With Robust Sensitivity Tables And IRR?

The user community and author are here to help. Go ahead!

0.0 / 5 (0 votes)

please wait...