Commits

intgr  committed b61fc43

Add optional 3rd argument: xirr(amt, time, guess)

  • Participants
  • Parent commits 8528052

Comments (0)

Files changed (6)

File financial.control

 # "financial" extension
 comment = 'Financial aggregate functions'
-default_version = '0.0.1'
+default_version = '0.0.2'
 module_pathname = '$libdir/financial'
 relocatable = true

File sql/financial--0.0.1--0.0.2.sql

+-- xirr(amount, time, guess)
+CREATE FUNCTION xirr_tstz_transfn (internal, float8, timestamptz, float8)
+RETURNS internal IMMUTABLE LANGUAGE C AS 'financial';
+
+CREATE AGGREGATE xirr (float8, timestamptz, float8) (
+    SFUNC = xirr_tstz_transfn,
+    STYPE = internal,
+    FINALFUNC = xirr_tstz_finalfn
+);
+

File sql/financial.sql

+-- xirr(amount, time)
 CREATE FUNCTION xirr_tstz_transfn (internal, float8, timestamptz)
 RETURNS internal IMMUTABLE LANGUAGE C AS 'financial';
 
     STYPE = internal,
     FINALFUNC = xirr_tstz_finalfn
 );
+
+-- xirr(amount, time, guess)
+CREATE FUNCTION xirr_tstz_transfn (internal, float8, timestamptz, float8)
+RETURNS internal IMMUTABLE LANGUAGE C AS 'financial';
+
+CREATE AGGREGATE xirr (float8, timestamptz, float8) (
+    SFUNC = xirr_tstz_transfn,
+    STYPE = internal,
+    FINALFUNC = xirr_tstz_finalfn
+);
+
 		state->alen = initlen;
 		state->nelems = 0;
 
+		/* Optional "guess" argument */
+		if (PG_NARGS() >= 4 && !PG_ARGISNULL(3))
+			state->guess = PG_GETARG_FLOAT8(3);
+		else
+			state->guess = NAN;
+
 		MemoryContextSwitchTo(oldcontext);
 	}
 	else
 	if (state->nelems < 2)
 		PG_RETURN_NULL();
 
-	state->guess = calculate_annualized_return(state);
+	/* Guess not provided as argument? */
+	if (isnan(state->guess))
+		state->guess = calculate_annualized_return(state);
 
 	elog(DEBUG1, "Calculating XIRR over %d records, %ld MB memory, guess=%g",
 		 state->nelems, (long)((state->nelems*sizeof(XirrItem))/(1024*1024)),

File test/expected/xirr.out

 (1 row)
 
 -- Excel's default guess of 0.1 would fail here, but hey, we're smarter than that
-SELECT xirr(amt, ts) FROM (VALUES(-100::float, '2013-01-01'::timestamptz), (20, '2014-01-01')) x(amt, ts);
+SELECT xirr(amt, ts, null) FROM (VALUES(-100::float, '2013-01-01'::timestamptz), (20, '2014-01-01')) x(amt, ts);
+ xirr 
+------
+ -0.8
+(1 row)
+
+SELECT xirr(amt, ts, 0.1) FROM (VALUES(-100::float, '2013-01-01'::timestamptz), (20, '2014-01-01')) x(amt, ts);
+ xirr 
+------
+     
+(1 row)
+
+SELECT xirr(amt, ts, -0.9999) FROM (VALUES(-100::float, '2013-01-01'::timestamptz), (20, '2014-01-01')) x(amt, ts);
  xirr 
 ------
  -0.8

File test/sql/xirr.sql

 SELECT xirr(amt, ts) FROM (SELECT amt, ts FROM transactions ORDER BY ts, random()) subq;
 
 -- Excel's default guess of 0.1 would fail here, but hey, we're smarter than that
-SELECT xirr(amt, ts) FROM (VALUES(-100::float, '2013-01-01'::timestamptz), (20, '2014-01-01')) x(amt, ts);
+SELECT xirr(amt, ts, null) FROM (VALUES(-100::float, '2013-01-01'::timestamptz), (20, '2014-01-01')) x(amt, ts);
+SELECT xirr(amt, ts, 0.1) FROM (VALUES(-100::float, '2013-01-01'::timestamptz), (20, '2014-01-01')) x(amt, ts);
+SELECT xirr(amt, ts, -0.9999) FROM (VALUES(-100::float, '2013-01-01'::timestamptz), (20, '2014-01-01')) x(amt, ts);