Diminishing returns for more effort

You will be very familiar with the law of diminishing returns, where you get a lot of payback for your initial efforts (sometimes called ‘low hanging fruit’), then you have to work harder for smaller rewards. An example for IT professionals might be a server or application consolidation project. Hacking and Stacking produces good early $ savings, but as you go through the project, you need to work hard to get less reward.- then the ’80/20′ rule comes in to play. This will reveal a simple formula you can use at the early stages of forecasting results in such a project.

Expressing as a formula

In this kind of scenario its hard to come up with a ‘finger in the air’ estimate of what the savings could be when embarking on such a project, since the savings are not going to be linear. I was looking for a simple formula that could be generally applied to such projects and came up with this. (a1 is the value to be diminished, and E1 is the ‘aggressivity’ to apply.

=( SIGN(A2) * $E$1 * (SQRT(2 * (SIGN(A2) * A2 / $E$1) + 1) - 1) )

This can be expressed as a user defined function as

Function diminishingReturn(val As Double, Optional s As Double = 10) As Double
    diminishingReturn = Sgn(val) * s * (Sqr(2 * (Sgn(val) * val / s) + 1) - 1)
End Function


Here is some test data, and you can see the return on investment shown by the red line tails off the higher the investment. You can tune the aggressivity with about 100 starting coming close to a straight line. The only difference between the ‘diminished’ column and the ‘udf’ column is that one uses the formula, the other the UDF version of the formula (the results .. I hope.. are identical)

Downloadable workbook with this example can be found here