Intelligent Enterprise

Better Insight for Business Decisions

RSS
Webcasts
Digital Library
Subscribe
Home

Web Exclusive

March 3, 2003

Net Present Value Calculations

by Joe Celko

For no particular reason I know, there was a sudden burst of requests for doing financial calculations in SQL on the newsgroups in February. Actually, one financial calculation in particular showed up on ACCESS, FoxPro, and SQL Server newsgroups: the internal rate of return (IRR). I suspect that someone made a class assignment somewhere, and everyone was trying to get someone else to do their homework for them — but perhaps I'm too cynical.

As a quick review, let's start with the net present value (NPV) calculation. Imagine that you just got an email from some poor Nigerian civil servant who wants you to send him a small investment now on the promise that he will send you a series of payments over time. Obviously, you would want the total of the cash flow to be at least equal to the initial investment or the money isn't worth lending. We can assume that you're making a little profit at the end of investment. But is this a good investment? That is, if I took that cash flow and invested it at a given interest rate, what would the result be? That's called the net present value (NPV) and you'll want to do at least as well as this value.

To make this even more concrete, let's show a little code.

CREATE TABLE CashFlows (project_id CHAR(15) NOT NULL, time_period INTEGER NOT NULL, CHECK (time_period > = 0), amount DECIMAL(12,4) NOT NULL, PRIMARY KEY (project_id, time_period)); INSERT INTO CashFlows VALUES ('Acme', 0, -1000.0000); INSERT INTO CashFlows VALUES ('Acme', 1, 500.0000); INSERT INTO CashFlows VALUES ('Acme', 2,400.0000); INSERT INTO CashFlows VALUES ('Acme', 3, 200.0000); INSERT INTO CashFlows VALUES ('Acme',4, 200.0000); INSERT INTO CashFlows VALUES ('Beta', 0, -1000.0000); INSERT INTO CashFlows VALUES ('Beta', 1, 100.0000); INSERT INTO CashFlows VALUES ('Beta', 2, 200.0000); INSERT INTO CashFlows VALUES ('Beta', 3, 200.0000); INSERT INTO CashFlows VALUES ('Beta',4, 700.0000);

I invest $1,000 at the start of each project (time period is zero and the amount is always negative), then every year I get a different amount back on my investment so that at the end of the fourth year, I've received a total of $13,000 on the Acme project less my initial $1,000 for a profit of $12,000. Likewise the Beta project returns $15,000 at the end.

Beta looks like a better investment. Let's assume we can get 10 percent return on an investment and that we put our cash flows into that investment. The net present value function is

where CF is the cash flows, t is the time period (t=0 is the initial investment and it is always negative) and r is the interest rate.

When we run them through the equation, we find that Acme has an NPV of $71.9896 and Beta is worth $-115.4293, so Acme is really the better project. We can get more out of the Acme cash flows than the Beta cash flow.

The IRR depends on the NPV. I'll get back to that shortly, but let me stop and ask, how would you program the NPV in SQL?

The answer posted on most newsgroup replies was to write a procedure directly from the equation in the vendor-specific 4GL language and then call it. The first problem was that people used FLOAT or REAL for the interest rates or even the money amounts. This isn't just a bad idea: It's a violation of Generally Accepted Accounting Practices because the floating-point numbers can have serious rounding errors.

The next problem was that nobody thought about a nonprocedural solution. The usual answers were done with cursors inside a stored procedure, for example:

BEGIN DECLARE :resultsum DECIMAL (12,4); DECLARE :this_time_period INTEGER; DECLARE :this_amount DECIMAL (12,4); DECLARE :rate DECIMAL (6,4); DECLARE :my_project_id CHAR(15); DECLARE CashFlows CURSOR FOR SELECT time_period, amount FROM Cashflows WHERE project_id = :my_project_id ORDER BY time_period ASC FOR READ ONLY; OPEN CashFlows; WHILE SQLSTATE = 00000 DO FETCH NEXT FROM CashFlows INTO :this_time_period, :this_amount SET :resultsum = :resultsum + (:this_amount/POWER((1.0 + :rate), :this_time_period)); END WHILE; -- :resultsum now has the answer! CLOSE CashFlows; DEALLOCATE CashFlows; END;

We used to write such basic "hang in a loop until you hit the end of file" programs in COBOL in the late 1950s. Cursors can be orders of magnitude slower than pure SQL code, and a stored procedure or function written for doing one calculation at a time has to be invoked over and over to process a set of cash flows.