This discussion is archived
9 Replies Latest reply: Jan 5, 2013 3:06 PM by Etbin RSS

Calculating Loss/Growth in single formula

metalray Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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