​A selection of topics on IT and its application to finance. 
​Send me your comments, questions ​or suggestions by clicking
h​ere


elmama
How to calculate the hedge of an index using PL/SQL

 

Let’s assume you calculate the total return of an index in US dollar (USD) terms. You want to calculate a new index based on it but hedged  in a forward currency curr (e.g HKD) over a specific fwd_term time period e.g 3mths.

 

We assume we have a database table INDEX_DHIST that contains a time series of daily USD Total Return values for our established index. We also assume we have a daily history of both spot and required forward cross rates for currency curr contained in a database table called USD_RATES

 

/*

 *

 * Calculate the hedged  total return (TR) of a portfolio/index based on a  forward  *rate (fwd_term)   of currency  ( curr )

 *

 * Hedged TR = prev end of period hedged TR return * hedge return

 *

 * We get prev end of period hedged TR return from the index_dhist table and    

 * we calculate hedge return where ....

 *

 * hedge return = unhedged index performance in currency curr + hedged  

 * currency performance

 *

 * unhedged performance  in currency curr =

 * (TR for current date/fx spot rate at current date)/

 * (TR for prev date/fx spot at previous date)

 *

 * hedged currency performance =

 * (prev end of period spot cross rate/prevend_of_periodfwd cross rate)  -

 * (prev end of_period spot cross rate/((current spot  cross rate) + D *

 * (current fwd cross rate - current spot cross rate)))

 *

 * and D = (days left in period/total days in period)

 */

 

create or replace procedure hedge_portfolio

(

   the_date IN varchar2,     -- normally todays date

   mnem IN varchar2,         -- identifier of the index/portfolio you want to hedge

   mnem_hedge IN varchar2,    -- identifier of the calculated hedge

                                                 -- index/portfolio

   curr IN varchar2,                -- forward currency you are using to hedge e.g HKD

   fwd_term IN varchar2,       -- the fowrd rate we are using e.g  3 Mth, 1 Mth etc..

   update_db IN boolean := false,    -- do we update the database with our new

                                                        -- index value

   hedge_capital IN boolean := false,       -- do we calculate/update the capital

                                                                -- return also

   debug IN boolean := false                   -- do we output debug message

)

is

tot_ret_unhedged number := 0 ;

cap_ret_unhedged number := 0 ;

hedged_tr_index_prev number := 0 ;

hedged_cr_index_prev number := 0 ;

bx_mnemvarchar2(7) := null;

current_xrate number := 0;

current_fwd_xrate number := 0;

prev_xrate number := 0;

prev_fwd_xrate number := 0;

prev_date date := null ;

bigD number := 0;

currency_return number := 0;

hedged_return_tr number := 0;

hedged_return_cr number := 0 ;

hedged_tr_index number := 0 ;

hedged_cr_index number := 0 ;

period_frac number := 0 ;

smallD number := 0 ;

pdate date := null;

period number := 0 ;

trunc_paramvarchar2(4) := null ;

 

begin

 

selectto_date(the_date,'yyyymmdd')

intopdate from dual;

 

dbms_output.put_line(' Current Date : ' || pdate ) ;

 

select decode(fwd_term,'3M','q','1M','MON','1W','WW','3M')

intotrunc_param from dual;

 

dbms_output.put_line(' Date truncparam : ' || trunc_param ) ;

 

select decode(fwd_term,'3M',3,'1M',1,'1W',7,3)

into period from dual;

 

dbms_output.put_line(' Forward Period : ' || period ) ;

 

/* depending on what fwd rate term we are using, get the end period

    3 months, 1 month ago etc…

  */

selecttrunc(pdate,trunc_param) - 1 - decode(to_char(trunc(pdate,trunc_param)-1,'DY'),'SAT',1,'SUN'

intoprev_date from dual ;

 

if debug = true then

 

   dbms_output.put_line(' Current Date : ' || pdate ) ;

   dbms_output.put_line(' Date truncparam : ' || trunc_param ) ;

   dbms_output.put_line(' Forward Period : ' || period ) ;

   dbms_output.put_line(' Previous end of period date :' || prev_date ) ;

 

end if;

 

/* get the unhedged performance in currency curr

 

select

   ((b1.x_trval/b2.x_trval) * (h2.c_xrate/h1.c_xrate))

   ((b1.x_cpval/b2.x_cpval) * (h2.c_xrate/h1.c_xrate))

intotot_ret_unhedged, tot_cap_unhedged

fromindex_dhist b1,index_dhist b2 , usd_rates h1,usd_rates h2

where b1.bx_mnem = b2.bx_mnem

and b1.x_date = pdate

and h1.c_date = b1.x_date

and b2.x_date = prev_date

and h2.c_date = b2.x_date

and h1.currency = h2.currency

and h1.currency = curr

and b1.bx_mnem = mnem ;

 

 

if debug = true then

 

   dbms_output.put_line(' Current UNHEDGED TR:DATE ' || tot_ret_unhedged || ',' || pdate);

   dbms_output.put_line(' Current UNHEDGED CR:DATE ' || cap_ret_unhedged || ',' || pdate);

   dbms_output.put_line(' Current SPT XRATE:DATE ' || current_xrate || ',' || pdate);

   dbms_output.put_line(' Current FWD XRATE:DATE ' || current_fwd_xrate || ',' || pdate);

 

end if;

 

/* Now use PREV_DATE to get prev period hedged TR  */

 

/* First get TR in currency curr for date pdate */

for c1_rec in

(

select h1.c_xrate,u.cf_xrate c_xrate2

from  usd_rates h1, usd_rates u

where h1.currency = u.c_mnem

andu.cf_term = fwd_term

andu.cf_date = h1.c_date

andu.cf_date = prev_date

andu.c_mnem = curr

)

loop

   prev_xrate := c1_rec.c_xrate;

   prev_fwd_xrate := c1_rec.c_xrate2;

end loop;

 

if debug = true then

 

   dbms_output.put_line('Previous SPT XRATE:DATE ' || prev_xrate || ',' || prev_date);

   dbms_output.put_line('Previous FWD XRATE:DATE ' || prev_fwd_xrate || ',' || prev_date);

 

end if ;

 

for c1_rec in

(

selectx_trval,nvl(x_cpval ,0) x_cpval

fromindex_dhist

wherex_date = prev_date

andbx_mnem = mnem_hedge

)

loop

   hedged_tr_index_prev := c1_rec.x_trval ;

   hedged_cr_index_prev := c1_rec.x_cpval ;

end loop;

 

if debug = true then

 

   dbms_output.put_line(' Prev Period HEDGED TR:DATE ' || hedged_tr_index_prev || ',' || prev_date)

   dbms_output.put_line(' Prev Period HEDGED CR:DATE ' || hedged_cr_index_prev || ',' || prev_date)

 

end if;

 

/* Calculate big D = the days remaining in the period/ total days in period*/

 

for c1_rec in

(

selectsmalld/bigdperiod_frac,smalld , bigd

from

(

SELECT

   (add_months(trunc(pdate, trunc_param), period) -1) -

    (trunc(pdate,trunc_param) -1) bigd

,

    (add_months(trunc(pdate, trunc_param), period) -1) -

    (trunc(pdate)) smalld

FROM dual

)

)

loop

   bigD := c1_rec.bigd ; -- total nr days in period

   period_frac := c1_rec.period_frac ;

   smallD := c1_rec.smalld ; -- nr days left in period

end loop;

 

if debug = true then

 

    dbms_output.put_line(' Big D = ' || bigD ) ;

    dbms_output.put_line(' Small D = ' || smallD );

    dbms_output.put_line(' Period Frac = ' || period_frac );

 

end if;

 

/*Hedged  Currency performance */

 

/*

* currency_performance :=

* ((1/prev_xrate)/(1/prev_fwd_xrate)) -

* ((1/prev_xrate)/(((1/current_xrate)) + bigD*((1/current_fwd_xrate)-(1/current_xrate))));

*/

 

currency_return := (prev_xrate/prev_fwd_xrate) - (prev_xrate/( current_xrate + period_frac *

(current_fwd_xrate - current_xrate))) ;

 

/* hedged return = unhedged index perf in currency curr  + currency return */

 

hedged_return_tr := tot_ret_unhedged + currency_return ;

 

hedged_return_cr := cap_ret_unhedged + currency_return ;

 

/* hedged total return = current hedged return * prev end of period hedged return */

 

hedged_tr_index := hedged_tr_index_prevhedged_return_tr  ;

 

hedged_cr_index := hedged_cr_index_prevhedged_return_cr  ;

 

if debug = true then

 

   dbms_output.put_line('currency return = ' || currency_return) ;

   dbms_output.put_line('hedged return (TR) = ' || hedged_return_tr) ;

   dbms_output.put_line('hedged return (CR) = ' || hedged_return_cr) ;

   dbms_output.put_line('hedged Total Return Index = ' || hedged_tr_index) ;

   dbms_output.put_line('hedged Capital Return Index = ' || hedged_cr_index) ;

 

end if;

 

ifupdate_db = true then

 

        ifhedge_capital= true then

 

           delete from index_dhist where bx_mnem = mnem_hedge

           andx_date = pdate ;

 

           insert into index_dhist(bx_mnem,x_date,x_trval,x_cpval)

          selectmnem_hedge,pdate,hedged_tr_index,hedged_cr_index

          from dual;

 

          commit;

 

        else

 

          delete from index_dhist where bx_mnem = mnem_hedge

          andx_date = pdate ;

 

          insert into index_dhist(bx_mnem, x_date , x_trval)

          selectmnem_hedge, pdate ,hedged_tr_index from dual;

 

          commit;

 

        end if ;

 

end if;

 

end;

/

show err

exit