Don’t Believe !

Don’t believe in a news, just because you have heard it.
Don’t believe in a tradition, just because it has been done through generations.
Don’t believe in something, just because it has been talked about and rumored by many people.
Don’t believe in something, just because it has been written in holy books.
Don’t believe in something, just because it has been taught by teachers or elders.

But after making observations and in depth analyses,
you found out that the issues being talked about are in accordance with good and useful reason,
then it is not disgraceful if the things are continued which will bring forth happiness,
that is why, it is proper that you will receive it and live according to the said principles.

(Anguttara Nikaya, Vol.I, 188 – 193)

IRR (Internal Rate of Return) function using Oracle

Hi All,
Because I want to use the Excel IRR function in one of my Oracle Function, then I’ve been looking for from various sources on the internet that discusses the functions of the IRR.

From various sources, I’ve tried to copy, edit it to get the same results with the results from Excel. But not yet see results. The final decision is to follow what is described in Wikipedia, and succeeded!

Here are the results, please use if it is useful to you all ^ _ ^


/*
by william xhi
15-December-2010
*/
CREATE OR REPLACE FUNCTION mis.f_irr (
p_table_name VARCHAR2,
p_column_name VARCHAR2,
p_where_condition VARCHAR2 DEFAULT NULL,
p_precision NUMBER DEFAULT 0.00000001,
p_max_iteration NUMBER DEFAULT 20
)
RETURN NUMBER
AS
v_sum_pos NUMBER; --sum positive value from table
v_count_pos NUMBER; --count positive value from table
v_c0 NUMBER; --negative value from table
v_tmp NUMBER; --store (1+r)^n
v_count NUMBER := 0; --iteration count
v_guess NUMBER; --irr

v_npv NUMBER; --store npv with negative value
v_npv_nin NUMBER; --store npv without negative value

v_log NUMBER; --store log(A/|C0|) / log(A/NPV1,in)
v_sql VARCHAR2 (1000); --store temporary sql string

TYPE refcurtyp IS REF CURSOR;

CV refcurtyp;
cv_prd NUMBER; --PERIODS
cv_amt NUMBER; --AMOUNT
BEGIN
--** begin - section 1 - only for the first time **----------------------------

/* section 1.A
GET SUM AND COUNT POSITIVE VALUE FROM THE TABLE
INTO VARIABLE V_SUM_POS, V_COUNT_POS */
v_sql :=
'SELECT SUM (' || p_column_name || '), COUNT (1) FROM ' || p_table_name;
v_sql := v_sql || ' WHERE ';

IF NVL (p_where_condition, 'x') <> 'x'
THEN
v_sql :=
v_sql || p_where_condition || ' AND ' || p_column_name || ' > 0';
ELSE
v_sql := v_sql || p_column_name || ' > 0;';
END IF;

EXECUTE IMMEDIATE v_sql
INTO v_sum_pos, v_count_pos; --SUM AND COUNT POSITIVE VALUE

/* section 1.B
GET THE NEGATIVE VALUE FROM THE TABLE
INTO VARIABLE V_C0
*/
v_sql := 'SELECT MIN (' || p_column_name || ') FROM ' || p_table_name;

IF NVL (p_where_condition, 'x') <> 'x'
THEN
v_sql := v_sql || ' WHERE ' || p_where_condition;
END IF;

EXECUTE IMMEDIATE v_sql
INTO v_c0; --GET NEGATIVE VALUE - VC0

/*
GET THE FIRST INITIAL GUESS
*/
v_guess := POWER ((v_sum_pos / ABS (v_c0)), (2 / (v_count_pos + 1))) - 1;

--** end - section 1 **--------------------------------------------------------

--** begin - section 2 - iteration **---------------------------------------
LOOP
v_npv_nin := 0;
v_sql := 'SELECT ROWNUM - 1, ' || p_column_name;
v_sql := v_sql || ' FROM ' || p_table_name;

IF NVL (p_where_condition, 'x') <> 'x'
THEN
v_sql := v_sql || ' WHERE ' || p_where_condition;
END IF;

OPEN CV FOR v_sql;

LOOP
FETCH CV
INTO cv_prd, cv_amt;

EXIT WHEN CV%NOTFOUND;

IF cv_prd = 0
THEN
v_tmp := 0;
v_npv_nin := 0;
v_npv := cv_amt;
ELSE
v_tmp := POWER ((1 + v_guess), cv_prd);
v_npv_nin := v_npv_nin + (cv_amt / v_tmp);
v_npv := v_npv + cv_amt / v_tmp;
END IF;
END LOOP;

v_log := LOG (10, (v_sum_pos / ABS (v_c0))) / LOG (10, (v_sum_pos / v_npv_nin));

--guess irr
v_guess := POWER ((1 + v_guess), v_log) - 1;

IF v_count <= p_max_iteration
THEN
v_count := v_count + 1;
END IF;

--check if the limit of precision has been passed or looping has reached the max
EXIT WHEN ABS (v_npv) < p_precision OR v_count > p_max_iteration;
END LOOP;

--** end - section 2 **--------------------------------------------------------
RETURN v_guess;
EXCEPTION
WHEN OTHERS
THEN
RETURN 999; --it means an error
END;
/

You can get it from this box( left sidebar)

irr function