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
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;
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;
((1-sign(B-T))*100*T/B+(1+sign(B-T))*(B-T)/B)/2
Regardsmetalray 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 ((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 ;
(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 100SQL> 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 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.