AFC - Abacus Formula Compiler for Java

Adding A New Vector Computation To AFC

A vector computation is a (typically financial) function that computes a single value from a vector (array) of values. Here, we shall look at how I implemented NPV(). So let’s assume NPV() has not been implemented yet.

Note: You must have read the topics on adding primitive functions and high-level functions to understand this section.

Docs and Tests

As always, we start with the announcement in the release notes, and the tests:

1 Expected
Actual Inputs # of Inputs Name Highlight Excel says Skip for
2 1.188 1.188
10% -10 3 4.2 6.8 5 NPV NPV
3 1.922 1.922
8% -40 8 9.2 10 12 14.5 7 long, bigdecimal


Making NPV() known to the parser is much like what we did for ABS(). Array functions, however, need to accept vector arguments, not just simple values. These are denoted by shapedRangeExpr():

|	"NPV" open() expr() <SEP> shapedRangeExpr() closeFun( Function.NPV )

This means:

  • open() – open parenthesis, start collecting function arguments.
  • expr() – single-valued expression.
  • shapedRangeExpr() – vector range expression with shape information (width, height) available to compiler.
  • closeFun( Function.NPV ) – packs collected function arguments into a function expression node.

Mathematical Definition

The function NPV() is defined in the Excel help file as follows:

i =1 n values i ( 1+rate) i

What we need here is a folding function that gives us access to the current index i. The fold we already encountered does not support this, but the very similar iterate does.

Rewriting It

Since NPV takes a vector as an argument, we need to apply the fold using apply... to vectors {v1, ..., vn} instead of apply... to list xs. And we tell the rewrite parser about this by suffixing the vector argument with #:

rewrite npv( rate, vs# ) =
	let rate1 = rate + 1
			iterate with
				r = 0
			index i
			each vi as
				r = r + vi / rate1 ^ i
		to vectors {vs}

Note how I used let here to move the common subexpression out of the folding loop.

Multiple Vectors

As the syntax above suggests, apply... to vectors {v1, ..., vn} can be used to fold multiple vectors in parallel. This demonstrated by COVAR:

rewrite covar( xs#, ys# ) =
	if COUNT( xs ) <> COUNT( ys ) then NA() else
		fold with
			sx = 0,
			sy = 0,
			sxy = 0
		each xi, yi as
			sx = sx + xi,
			sy = sy + yi,
			sxy = sxy + xi * yi
		with count n into
			(sxy - sx*sy/n) / n
		when empty err( "#DIV/0! because list doesn't contain numbers in COVAR" ) ::NUMERIC
	to vectors {xs, ys}