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

# Calculating Loss/Growth in single formula

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
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
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
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
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
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
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
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
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
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