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:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Expected =IF(Q1,"Expected","FAILED!")	Actual	Inputs							# of Inputs	Name	Highlight	Excel says	Skip for
2	1.188	1.188 =NPV(C2,D2:G2)	10%	-10	3	4.2	6.8			5	NPV	NPV
3	1.922	1.922 =NPV(C3,E3:I3)+D3	8%	-40	8	9.2	10	12	14.5	7				long, bigdecimal

Parsing

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:

\sum_{i = 1}^{n} \frac{{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
	in
		apply
			iterate with
				r = 0
			index i
			each vi as
				r = r + vi / rate1 ^ i
			end
		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
	apply
		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
		end
	to vectors {xs, ys}