Build a one-variable sensitivity table

Sensitivity analysis tests how a key output changes when you vary one input assumption. In project finance, running NPV across a range of discount rates is one of the most common sensitivity tests: it reveals how much the discount rate assumption matters for whether a project looks attractive.

Excel's NPV function calculates the present value of a series of future cash flows, discounted at a given rate:

=NPV(rate, value1, value2, ...)

• rate: the discount rate per period (annual, for annual cash flows)
• value1, value2, ...: the future cash flows for each period, starting with period 1

The cash flow row shows the initial investment in Year 0 and projected cash inflows in Years 1 through 5. Excel's NPV function discounts the future cash flows only, so the Year 0 investment needs to be handled separately.

The base model uses the discount rate in B1 and compares the project to the required NPV threshold in B2. The sensitivity table then repeats the NPV calculation at five discount rates, so you can see where the decision changes.

Lower discount rates usually mean a lower required return or lower risk. They make NPV higher, but they are not automatically better assumptions. Use the table to see whether the project still works at higher discount rates.

Your task:

1. In B9, calculate the base case NPV using the base discount rate in B1.
2. In B10, return "Accept" if the base case NPV meets the required NPV threshold, otherwise return "Reject".
3. In B13 to B17, calculate project NPV for each discount rate listed in column A.
4. In C13 to C17, return "Yes" when the scenario NPV meets the required NPV threshold and "No" otherwise.
5. In B19, identify the highest discount rate in the table that still meets the required NPV threshold.