Let’s look at another example of this using the idea of purchasing a piece of machinery.
Here are the facts of the example:
- The new machine costs $50,000
- The new machine is expected to produce $15,000 in productivity savings over the next 4 years
Ignoring the return percentage for the moment, the formulas to calculate cash flows are:
Years 1 through 4 (cells E9 through H9)
Year 0 (cell D9)
A quick SUM of the values (D9:H9) yields a profit of $10,000. A $10,000 return sounds like a great investment.
But here’s another factor for our decision making; The Shareholders expect a return of at least 8% on their investment, so we only want to invest in opportunities that yield at least this return.
Playing the “What If” Game at 8%
We’ll place a value of 8.0% in our spreadsheet (cell D5). Using Excel’s NPV function, we create the following formula in cell D11:
This gives us a result of $49,681.90.
Now we’ll modify the formula to deduct the original amount invested (cell D9). NOTE: Since the value in cell D9 is negative, we’ll add it to the result in cell D11.
=NPV(D5, E9:H9) + D9
This gives us a result of -$318.10. We know that the project will fail to return the minimum investment that The Shareholders expect.
What if we lower the expected return to 6.0%?
Now we see a return of $1,976.58. The Net Present Value is positive which makes this an attractive offer.