9 Replies Latest reply: Jan 5, 2013 5:06 PM by Etbin RSS

    Calculating Loss/Growth in single formula

    metalray
      Hello Guys,

      I am trying to write a formula:
      to calculate the loss/growth of investments.

      Now I have the following forumla:

      IF Today_value > Beginn_Value THEN
      Today_value/ (Beginn_Value/100)

      IF Beginn_Value > Today_value THEN
      (Beginn_Value - Today_value) / Beginn_Value

      Is there a way to combine those two
      into a single formula?

      Thanks
        • 1. Re: Calculating Loss/Growth in single formula
          sb92075
          metalray wrote:
          Hello Guys,

          I am trying to write a formula:
          to calculate the loss/growth of investments.

          Now I have the following forumla:

          IF Today_value > Beginn_Value THEN
          Today_value/ (Beginn_Value/100)

          IF Beginn_Value > Today_value THEN
          (Beginn_Value - Today_value) / Beginn_Value

          Is there a way to combine those two
          into a single formula?

          Thanks
          what is formula when BEGIN_VALUE = TODAY_VALUE?
          • 2. Re: Calculating Loss/Growth in single formula
            ranit B
            not tested
            select 
              case 
              when (Today_value > Beginn_Value) then
                  Today_value/(Beginn_Value/100) 
              when (Beginn_Value > Today_value) then
                  (Beginn_Value - Today_value)/Beginn_Value
              when (Beginn_Value = Today_value) then
                  0
              end case
            from xx;
            • 3. Re: Calculating Loss/Growth in single formula
              stefan nebesnak
              Or you can use DECODE (not tested).
              SELECT DECODE ( LEAST (Beginn_Value, Today_value),
                                     Beginn_Value, (Today_value / (Beginn_Value/100)),
                              MAX   (Beginn_Value, Today_value),
                                     Beginn_Value, (Beginn_Value - Today_value) / Beginn_Value,
                              NULL ) FROM TABLE;
              • 4. Re: Calculating Loss/Growth in single formula
                O.Developer
                Hello Guys,

                I am trying to write a formula:
                to calculate the loss/growth of investments.

                Now I have the following forumla:

                IF Today_value > Beginn_Value THEN
                Today_value/ (Beginn_Value/100)

                IF Beginn_Value > Today_value THEN
                (Beginn_Value - Today_value) / Beginn_Value

                Is there a way to combine those two
                into a single formula?

                Thanks

                ------------ You will get what you want by below sample way

                If COndition 1 Then
                Calculation 1
                Elsif Condtion 2 Then
                Calcualtin 2
                Elsif COndtion 3 then
                Calculation 3

                End if;
                • 5. Re: Calculating Loss/Growth in single formula
                  metalray
                  Thanks, I was hoping for a simple, one liner but I guess that does not work. Decode is not an option since its not understoond by the SAS SQL Oracle interpreter. Probably because its not "standard" sql.
                  • 6. Re: Calculating Loss/Growth in single formula
                    Etbin
                    If you insist on one line maybe:
                    ((1-sign(B-T))*100*T/B+(1+sign(B-T))*(B-T)/B)/2
                    Regards

                    Etbin
                    • 7. Re: Calculating Loss/Growth in single formula
                      metalray
                      that is what I was looking for because it saves me a LOT of IF ELSE coding and at the end the code looks nicer.
                      but there is a problem.


                      --T 100
                      --b 20
                      select ((1-sign(20-100))*100*100/20+(1+sign(20-100))*(20-100)/20)/2 from dual ;
                      -- 500 (% inrease)


                      --T 20
                      --b 100
                      select ((1-sign(100-20))*100*20/100+(1+sign(100-20))*(100-20)/100)/2 from dual ;
                      -- .8 (%)

                      the bottom result, .8 does not seem to make sense. it should be a 80% decrese, right?
                      so I modified the last bit of the formula
                      select ((1-sign(100-20))*100*20/100+(1+sign(100-20))*(100-20))/2 from dual ;
                      • 8. Re: Calculating Loss/Growth in single formula
                        John Spencer
                        metalray wrote:
                        that is what I was looking for because it saves me a LOT of IF ELSE coding and at the end the code looks nicer.
                        but there is a problem.


                        --T 100
                        --b 20
                        select ((1-sign(20-100))*100*100/20+(1+sign(20-100))*(20-100)/20)/2 from dual ;
                        -- 500 (% inrease)


                        --T 20
                        --b 100
                        select ((1-sign(100-20))*100*20/100+(1+sign(100-20))*(100-20)/100)/2 from dual ;
                        -- .8 (%)

                        the bottom result, .8 does not seem to make sense. it should be a 80% decrese, right?
                        so I modified the last bit of the formula
                        select ((1-sign(100-20))*100*20/100+(1+sign(100-20))*(100-20))/2 from dual ;
                        No, your gain case is not a 500% increase, it is a 400% increase, you just have 500% of the money you had at the beginning. Your loss case is an 80% loss. One of the easiest ways to calulate a rate of return is the Simple Dietz formula which is:
                        (End Value - Begin Value - Net contributions) / (Begin value + (Net contributions / 2))
                        Since you do not appear to want/need to take contributions or withdrawals into account, this simplifies into:
                        (End Value - Begin Value) / Begin value
                        which is similar to your loss version, but actually shows the negative value. Using the numbers from above, see the differences:
                        SQL> select 100/(20/100) your_gain, (100 - 20)/100 your_loss,
                               (100 - 20)/20 dietz_gain, (20 - 100)/100 dietz_loss
                        from dual;  2    3
                        
                         YOUR_GAIN  YOUR_LOSS DIETZ_GAIN DIETZ_LOSS
                        ---------- ---------- ---------- ----------
                               500         .8          4        -.8
                        If you want to show the numbers as an percentage figure instead of a decimal figure, just multiply by 100
                        SQL> select 100/(20/100) your_gain, ((100 - 20)/100) * 100 your_loss,
                          2         ((100 - 20)/20) * 100 dietz_gain, ((20 - 100)/100) * 100 dietz_loss
                          3  from dual;
                        
                         YOUR_GAIN  YOUR_LOSS DIETZ_GAIN DIETZ_LOSS
                        ---------- ---------- ---------- ----------
                               500         80        400        -80
                        John
                        • 9. Re: Calculating Loss/Growth in single formula
                          Etbin
                          that is what I was looking for because it saves me a LOT of IF ELSE coding and at the end the code looks nicer.
                          case when B>T then (B-T)/B when B<T then 100*T/B end
                          compared to
                          ((1-sign(B-T))*100*T/B+(1+sign(B-T))*(B-T)/B)/2
                          is indeed longer (but seems easier to type), about the look ... [url http://en.wikipedia.org/wiki/De_gustibus_non_est_disputandum]de gustibus ... the first might look more readable/understandable even to mathematicians but IMHO efficiency should be the main concern: one or two comparisons plus a single formula evaluation are needed to get the result of the first expression while for the second both formulas must be evaluated plus there are two function calls, two subtractions, two additions, two multiplications and the final division.
                          It's up to you to use the appropriate formulas.
                          Curuiosly enough I noticed (at least around here) ther's quite a mess when talking about percentages:
                          For the same work/position on average women are paid 15% less than men is much too often considered the same as on average men are receiving 15% more than women
                          <tt>W := (1 - 0.15) * M</tt> is not the same as <tt>M := (1 + 0.15) * W</tt> unless <tt>M</tt> and <tt>W</tt> are both <tt>0</tt>
                          Too many being offered lets say a 20% raise followed by the same percent deduction applied on any amount they are willing to place on the table reply:
                          "what's the point since I'll end with the same amount" but <tt>A * (1 + 0.2) * (1 - 0.2) := A * (1 - 0.04) &lt; A</tt>

                          Regards

                          Etbin