CODEWITHSUNDEEP
sqlsql-serverdatabasesql-server-2014
JPaulPunzalan
JPaulPunzalan

Reputation: 427

Raising negative base numbers to decimal power using SQL Server POWER function

I'm using SQL Server 2014 and I ran through an error using the POWER function.

Below is the arithmetic formula that I am trying to do in my query.

growth rate = ((presentnetincome/pastnetincome) ^ (1/(presentyear-pastyear+ 1))) - 1

Below is the query that I have

WITH i AS
( 
    SELECT DISTINCT
        stockid,
        first_value([year]) over (partition by stockid order by [year]) AS year_first,
        first_value([year]) over (partition by stockid order by [year] desc) AS year_last,
        first_value(netincomeaftertax) over (partition by stockid order by [year]) AS netincome_first,
        first_value(netincomeaftertax) over (partition by stockid order by [year] desc) AS netincome_last
    FROM 
        incomestatements i
)
SELECT 
    stockid, 
    POWER(netincome_first / CAST(netincome_last AS FLOAT), 0.2) - 1 AS growthRate 
FROM i

However, I get an error

An invalid floating point operation occurred

when the row's netincome_first is a negative number since they recorded a NET LOSS that year.

Are there any workarounds to this situation? Kindly help me investigate. Thanks!

Upvotes: 1

Views: 568

Answers (1)

toonice
toonice

Reputation: 2236

WITH i AS ( SELECT DISTINCT stockid,
                   FIRST_VALUE( [year] ) OVER ( PARTITION BY stockid ORDER BY [year] ) AS year_first,
                   FIRST_VALUE( [year] ) OVER ( PARTITION BY stockid ORDER BY [year] DESC ) AS year_last,
                   FIRST_VALUE( netincomeaftertax ) OVER ( PARTITION BY stockid ORDER BY [year] ) AS netincome_first,
                   FIRST_VALUE( netincomeaftertax ) OVER ( PARTITION BY stockid ORDER BY [year] DESC ) AS netincome_last
            FROM incomestatements i )
SELECT stockid,
       SIGN( netincome_last -
                 netincome_first ) *
           ( POWER( ABS( ( netincome_last -
                                netincome_first ) /
                             netincome_first
                       ) + 1,
                       1 / ( year_last -
                               year_first )
                  ) - 1 ) AS growthShrinkageRate

Firstly, the given formula has an error in it, namely that presentyear - pastyear + 1 should read presentyear - pastyear. To illustrate, please consider starting with $200.00 in the year 2012, then consider this amount tripling / growing by 200% in 2013 to $600.00, and this amount again tripling to $1800.00 in 2014. We know that the growth rate is 200% for both years.

If we use the given formula of...

( ( presentnetincome / pastnetincome ) ^ ( 1 / ( presentyear - pastyear + 1 ) ) ) - 1

... we get...

( ( 1800 / 200 ) ^ ( 1 / ( 2014 - 2012 + 1 ) ) ) - 1 = ( 9 ^ ( 1 / 3 ) ) - 1 = 1.080...

... instead of the 2 that we expect. If you drop the + 1 from the formula, we instead get...

( ( 1800 / 200 ) ^ ( 1 / ( 2014 - 2012 ) ) ) - 1 = ( 9 ^ ( 1 / 2 ) ) - 1 = 2

Thus we can correct the formula to...

( ( presentnetincome / pastnetincome ) ^ ( 1 / ( presentyear - pastyear ) ) ) - 1

This formula will give the correct answer if both numbers are positive and the more recent is larger than the former.

If the values are both negative and the more recent is smaller (such as with $-200.00 followed immediately by $-1,800.00, i.e. if a loss has been taken, the same result is received as if the numbers had both been positive and the latter larger, such as with $200.00 followed by $1,800.00. Both cases yield the same number, but the former reflects a rate of loss, i.e. negative growth.

If one of the values is negative then the formula will try to calculate the root of a negative number, which in half of all cases won't work with real numbers (the sort that we are using here) and in the other half would give a real, but wrong, number.

Thus a modified statement will need to be developed that will be able to handle all combinations of negative and positive amounts.

The single adapted formula I developed is...

SIGN( netincome_last -
          netincome_first ) *
    ( POWER( ABS( ( netincome_last -
                        netincome_first ) /
                      netincome_first
                ) + 1,
                1 / ( year_last -
                        year_first )
           ) - 1 )

If you would prefer it in a single line, it is...

    SIGN( netincome_last - netincome_first ) * ( POWER( ABS( ( netincome_last - netincome_first ) / netincome_first ) + 1, 1 / ( year_last - year_first ) ) - 1 )

The first point to note is that there is a pair of subtractions based on netincome_first and netincome_last. This is to be expected as to measure growth you will sooner or later have to measure the difference between two values.

Secondly, the first part of our formula is used to indicate the direction of the change in netincome_last from netincome_last. Here SIGN() will return -1 if the number is negative and 1 if it positive. When we multiply this number by the magnitude of the change we will be able to indicate if growth or loss has occurred and the size of that change.

Thirdly, the first argument of POWER() is...

ABS( ( netincome_last -
           netincome_first ) / netincome_first ) + 1

This formula measures the how many times larger netincome_last is in relation to the size of netincome_first for the number of periods covered, irrespective of whether or not growth or loss has occurred and what the signs of netincome_first and netincome_last are.

We add + 1 to the result of the division to compensate for the loss of 1 value of netincome_first caused by calculating the difference

To illustrate, for netincome_first = 200 and netincome_last = 1800 it gives...

ABS( ( 1800 - 200 ) / 200 ) + 1      = ABS( 1600 / 200 ) + 1   = ABS( 8 ) + 1  = 8 + 1 = 9

For netincome_first = 200 and netincome_last = -1400 it gives...

ABS( ( -1400 - 200 ) / 200 ) + 1      = ABS( -1600 / 200 ) + 1 = ABS( -8 ) + 1 = 8 + 1 = 9

For netincome_first = -200 and netincome_last = 1400 it gives...

ABS( ( 1400 - ( -200 ) / -200 ) + 1  = ABS( 1600 / -200 ) + 1  = ABS( -8 ) + 1 = 8 + 1 = 9

For netincome_first = -200 and netincome_last = -1800 it gives...

ABS( ( -1800 - ( -200 ) / -200 ) + 1 = ABS( -1600 / -200 ) + 1 = ABS( 8 ) + 1  = 8 + 1 = 9

The second argument of POWER() is the inverse of the number of periods covered. For instance, if the above examples were for the period of 2012 through to 2014 then we would be considering 2 periods (2 years). In each of these periods the nett income would have tripled, i.e. increased to 3 times each previous level. We can calculate this value by finding the square root of 9, i.e. by finding 9 ^ ( 1 1=/ 2 ).

A value is said to have grown by a rate equal to how any times larger it is subtract one. For example, where it increases to 3 times its previous level it is said to have a growth rate of 2 (i.e. it has grown 200%). Hence the - 1 just after the POWER() calculation.

If you have any questions or comments, then please feel free to post a Comment accordingly.

Upvotes: 1

Related Questions