Commits

intgr committed 1ce1714

Annualized return must work with negative inputs also, like XIRR

(Also tweaked debug output)

  • Participants
  • Parent commits 6701854

Comments (0)

Files changed (3)

 		new_rate = guess - (result / deriv);
 		epsilon = fabs(new_rate - guess);
 
-		elog(DEBUG1, "Iteration %2d rate %8g [epsilon %8g]", j, new_rate, epsilon);
+		elog(DEBUG1, "Iteration %2d rate %-8g [epsilon %-8g]", j, new_rate, epsilon);
 
 		/* It's not getting any better by adding numbers to infinity */
 		if (!isfinite(new_rate))
 	int			i;
 	double		debit = 0.0;
 	double		endvalue = 0.0;
+	double		power;
 	TimestampTz	mintime, maxtime;
 
 	/* Try to be clever, input is most likely sorted by time. */
 			mintime = time;
 	}
 
-	return pow(1 + endvalue/debit, TIME_PER_YEAR/(maxtime-mintime)) - 1;
+	power = TIME_PER_YEAR/(maxtime-mintime);
+
+	/* +/- swapped */
+	if (state->array[0].amount > 0)
+		power = -power;
+	return pow(1 + endvalue/debit, power) - 1;
 }

File test/expected/xirr.out

  -0.8
 (1 row)
 
+-- Must work with both positive and negative inputs
+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)
+
 -- Window function
 SELECT xirr(amt, ts) OVER (ORDER BY ts) FROM (VALUES(-100::float, '2013-01-01'::timestamptz), (10, '2013-02-01'), (110, '2013-03-01')) x(amt, ts);
      xirr     

File test/sql/xirr.sql

 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);
+-- Must work with both positive and negative inputs
+SELECT xirr(-amt, ts, null) FROM (VALUES(-100::float, '2013-01-01'::timestamptz), (20, '2014-01-01')) x(amt, ts);
 
 -- Window function
 SELECT xirr(amt, ts) OVER (ORDER BY ts) FROM (VALUES(-100::float, '2013-01-01'::timestamptz), (10, '2013-02-01'), (110, '2013-03-01')) x(amt, ts);