This content has been marked as final.
Show 9 replies

1. Re: Calculating Loss/Growth in single formula
sb92075 Dec 21, 2012 9:49 AM (in response to metalray)metalray wrote:
what is formula when BEGIN_VALUE = TODAY_VALUE?
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 
2. Re: Calculating Loss/Growth in single formula
ranit B Dec 21, 2012 11:51 AM (in response to metalray)not testedselect 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 Dec 21, 2012 4:29 PM (in response to metalray)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 Dec 22, 2012 3:36 AM (in response to 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
 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 Jan 3, 2013 6:48 AM (in response to 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 Jan 3, 2013 1:21 PM (in response to metalray)If you insist on one line maybe:
Regards((1sign(BT))*100*T/B+(1+sign(BT))*(BT)/B)/2
Etbin 
7. Re: Calculating Loss/Growth in single formula
metalray Jan 4, 2013 9:36 AM (in response to 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.
but there is a problem.
T 100
b 20
select ((1sign(20100))*100*100/20+(1+sign(20100))*(20100)/20)/2 from dual ;
 500 (% inrease)
T 20
b 100
select ((1sign(10020))*100*20/100+(1+sign(10020))*(10020)/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 ((1sign(10020))*100*20/100+(1+sign(10020))*(10020))/2 from dual ; 
8. Re: Calculating Loss/Growth in single formula
John Spencer Jan 4, 2013 11:21 AM (in response to metalray)metalray wrote:
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:
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 ((1sign(20100))*100*100/20+(1+sign(20100))*(20100)/20)/2 from dual ;
 500 (% inrease)
T 20
b 100
select ((1sign(10020))*100*20/100+(1+sign(10020))*(10020)/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 ((1sign(10020))*100*20/100+(1+sign(10020))*(10020))/2 from dual ;
Since you do not appear to want/need to take contributions or withdrawals into account, this simplifies into:(End Value  Begin Value  Net contributions) / (Begin value + (Net contributions / 2))
which is similar to your loss version, but actually shows the negative value. Using the numbers from above, see the differences:(End Value  Begin Value) / Begin value
If you want to show the numbers as an percentage figure instead of a decimal figure, just multiply by 100SQL> 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
JohnSQL> 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

9. Re: Calculating Loss/Growth in single formula
Etbin Jan 5, 2013 5:06 PM (in response to 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.
compared tocase when B>T then (BT)/B when B<T then 100*T/B end
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.((1sign(BT))*100*T/B+(1+sign(BT))*(BT)/B)/2
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) < A</tt>
Regards
Etbin