By: Andy Novick | Updated: 2010-05-06 | Comments (8) | Related: > TSQL
Problem
When working with cash flow calculations in SQL Server one of the key concepts is the Net Present Value of a stream of payments. In a database of payment information, how can the Net Present Value of a stream of payments be calculated in a way that is easy for users to request? Just as important, once the number is calculated, is being sure that the answer is correct? How does the coder or tester know?
Solution
Net-Present-Value (NPV) calculations are used in several areas of finance such as mortgage calculation and the financing of bonds and loans. All NPV calculations answer one particular question: What is the total value today of a stream of payments. The "Net" in NPV refers to the fact that there can be both positive and negative payments involved. From a bank's point of view a loan often consists of a negative payment, the principal of the loan, followed by a series of positive payments as the loan is paid back in installments with the possibility of a final balloon payment of outstanding principal. If the series of payments are at regular intervals, such as at the beginning of each month, then the NPV of the loan can be calculated by a formula and implemented in T-SQL as a scalar function.
The formula for the NPV of a loan looks like this:
Where -Principal represents paying the principal of the loan to the borrower, rate is the interest rate per period, and paymenti is a loan payment at the end of period i, which could be any time period as long as the information stays consistent. This article uses a monthly rate.
While this works well enough for simple loans like car loans and most mortgages, real-world commercial loans are a mixture of principal amounts dispersed at various dates during the early life of the loan followed by a stream of loan payments including a balloon payment at the end of the loan. To represent this complexity we can rely on a much simpler formula for the value of any individual payment. The Present Value (PV) of any cash flow is the value of the payment discounted by the interest rate given in this formula.
For an example, let's say that there is a payment of $10,000 due exactly 12 months from now. The yearly interest rate that we're using to evaluate the loan is 8% (0.08) so the monthly interest rate is 0.00667. If we plug in the numbers the formula is:
Or in T-SQL:
SELECT 10000.00 / POWER(1 + 0.006667, 12)Which gives us an answer of 9,233.57, which is to say that receiving $10,000 in 12 months is worth $9,233.57 today. The difference is referred to as the "time value of money." Maybe that's why J. Wellington Wimpy would always rather pay Popeye on Tuesday to get a hamburger today.
To get the NPV of the collection of cash flows as of a date, evaluate every payment for its value on that date and then sum the present values. That'll be the approach. Let's start with some test data. The following code sets up two tables: loan and loan_cash_flow. Each loan_cash_flow could be positive or negative depending on whether it was a Disbursement or a Payment, which is indicated by the disbursement_or_payment_column.
CREATE TABLE loan (loan_id CHAR(8) NOT NULL PRIMARY KEY CLUSTERED
,inception date NOT NULL
,monthly_rate decimal(28,9) NOT NULL
,name VARCHAR(100)
,cust_id VARCHAR(24) NOT NULL
)
GO
CREATE TABLE loan_cash_flow
(loan_id CHAR(8) NOT NULL
,cash_flow_date date NOT NULL
,disbursement_or_payment CHAR(1) NOT NULL
CHECK (disbursement_or_payment IN
('D', 'P'))
,amount money NOT NULL
,PRIMARY KEY CLUSTERED (loan_id, cash_flow_date
,disbursement_or_payment)
)
GO For example data let's insert rows for one loan, the "Big Mall" construction project. The project begins with several disbursements, presumably to finance the construction. It then starts with regular payments until the balloon payment at the end of the loan to repay the principal.
DECLARE @pmt decimal (38,2) = 2500000.0
INSERT INTO loan (loan_id, inception, monthly_rate, name, cust_id)
VALUES ('LN001', '2010-02-01', 0.006667, 'Big Mall', '0001')
GO
INSERT INTO loan_cash_flow (loan_id, cash_flow_date
, disbursement_or_payment, amount)
VALUES -- 3 Disbursements of 97 Million
('LN001', '2010-02-01', 'D', 40000000)
,('LN001', '2010-08-01', 'D', 32000000)
,('LN001', '2011-02-01', 'D', 27000000)
-- 11 monthly loan payments
,('LN001', '2012-03-01', 'P', @pmt)
,('LN001', '2012-04-01', 'P', @pmt)
,('LN001', '2012-05-01', 'P', @pmt)
,('LN001', '2012-06-01', 'P', @pmt)
,('LN001', '2012-07-01', 'P', @pmt)
,('LN001', '2012-08-01', 'P', @pmt)
,('LN001', '2012-09-01', 'P', @pmt)
,('LN001', '2012-10-01', 'P', @pmt)
,('LN001', '2012-11-01', 'P', @pmt)
,('LN001', '2012-12-01', 'P', @pmt)
,('LN001', '2013-01-01', 'P', @pmt)
-- balloon payment when construction is done
,('LN001', '2013-02-01', 'P', 97000000)
GO
To calculate the Present Value (PV) of any individual payment a scalar function is sufficient. The following function, cash_flow_present_value, does the trick by applying the present value formula given above. It's important to be careful about the number of periods to discount the payment. To make the examples more understandable the calculations are based on a monthly rate. In practice the calculation might be done on a daily rate.
CREATE FUNCTION dbo.cash_flow_present_value (
@as_of_date date = NULL -- evaluate as of this date
-- null for today
,@monthly_rate float -- ex 0.01 for 12%/yr
,@payment_date date -- Date payment scheduled
,@disbursement_or_payment CHAR(1) -- D or P
,@cash_flow_amount money
) RETURNS money
AS BEGIN
DECLARE @periods integer
SET @periods = DATEDIFF(MONTH
, ISNULL(@as_of_date, GETDATE())
, @payment_date)
RETURN CASE @disbursement_or_payment
WHEN 'D' THEN -1.0 -- disbursement
ELSE 1.0 -- payment
END
* @cash_flow_amount
/ POWER (1.0 + @monthly_rate, @periods)
END
GO
GRANT EXEC ON dbo.cash_flow_present_value TO PUBLIC
GO
To be confident in the calculation let's take a look at a present value calculation for each payment. It's not usually used this way, but it's instructive and by including the periods and discount columns that repeat portions of the functions formula it becomes easier to ensure that the calculation is correct:
SELECT cf.loan_id [loan], cf.cash_flow_date [date] , DATEDIFF(MONTH, l.inception , cf.cash_flow_date) periods , cf.disbursement_or_payment [D or P] , ROUND(1.0 /POWER(1 + l.monthly_rate ,DATEDIFF(MONTH, l.inception , cf.cash_flow_date)),8) discount , cf.amount , dbo.cash_flow_present_value(l.inception, l.monthly_rate , cf.cash_flow_date ,cf.disbursement_or_payment , cf.amount) PV FROM dbo.loan_cash_flow cf INNER JOIN dbo.loan l ON cf.loan_id = l.loan_id ORDER BY cf.loan_id,cf.cash_flow_date GO
loan date periods D or P discount amount PV
------ ---------- ------- ------ ------------ ------------- ---------------
LN001 2010-02-01 0 D 1.00000000 40000000.00 -40000000.0000
LN001 2010-08-01 6 D 0.96091508 32000000.00 -30749282.4719
LN001 2011-02-01 12 D 0.92335779 27000000.00 -24930660.2134
LN001 2012-03-01 25 P 0.84694303 2500000.00 2117357.5780
LN001 2012-04-01 26 P 0.84133386 2500000.00 2103334.6459
LN001 2012-05-01 27 P 0.83576183 2500000.00 2089404.5855
LN001 2012-06-01 28 P 0.83022671 2500000.00 2075566.7818
LN001 2012-07-01 29 P 0.82472825 2500000.00 2061820.6237
LN001 2012-08-01 30 P 0.81926620 2500000.00 2048165.5043
LN001 2012-09-01 31 P 0.81384033 2500000.00 2034600.8206
LN001 2012-10-01 32 P 0.80845039 2500000.00 2021125.9737
LN001 2012-11-01 33 P 0.80309615 2500000.00 2007740.3687
LN001 2012-12-01 34 P 0.79777737 2500000.00 1994443.4144
LN001 2013-01-01 35 P 0.79249381 2500000.00 1981234.5239
LN001 2013-02-01 36 P 0.78724525 97000000.00 76362788.8133Each row represents one cash flow, either a disbursement to the loan recipient or a payment back to the bank. Disbursements are negative cash flows. Since the loan is being evaluated as of its inception date, 2010-02-01, the $40,000,000 disbursed on the first day of the loan has a discount of 1.0 and thus a present value of $40,000,000. Each disbursement or payment in the future is worth less, on the day of the loan inception, by the discount from the day of payment.
When evaluating investments the present value of any individual payments isn't that important. What's most important is the total of all present values including any cash flows at day zero. So a typical use of cash_flow_present_value would be in a SUM aggregate. Here's the calculation of the net present value of the loan:
SELECT loan.loan_id
, SUM(dbo.cash_flow_present_value(
loan.inception
,loan.monthly_rate
,cf.cash_flow_date
,cf.disbursement_or_payment
,cf.amount
)
) NPV
FROM dbo.loan
LEFT OUTER JOIN dbo.loan_cash_flow cf
ON loan.loan_id = cf.loan_id
GROUP BY loan.loan_id
ORDER BY loan.loan_id
GO loan_id NPV -------- --------------------- LN001 3217640.9485
The result tells us that the Net Present Value of the loan to the bank is $3,217,640.95. That's great, now we know: LOAN APPROVED! But is that correct? How would we know?
When verifying a calculation we can always redo the calculation with pen and paper or we might want to look to another trusted source to do the calculation. When it comes to trusting calculations more than I trust my own code there are two sources that I look at. The first source you probably know, is an Excel spreadsheet. The second source may be unfamiliar, it's the Wolfram Alpha web site. Let's start by simulating the calculation in Excel.
Excel offers a NPV function that can be used to calculate the discounted value of a series of cash flows both negative, for disbursements, and positive, for payments. I've copied the cash flow data from the loan_payment table into a spreadsheet to do the calculation. The formula for calculating the NPV of the loan cash flows is in cell B1 and it's shown as text in cell C1.
When using Excel be very careful to read the documentation in order to get the calculation correct. The first argument to the NPV function is the rate expressed either as a decimal number or with a percentage sign. That's easy, use the 0.006667 monthly rate. Following the rate are a series of payments that come at the end of each period. The payments are stored in the spreadsheet range B4:B40. Since the first disbursement in our loan of $40,000,000 on 2010-02--01 is made at day zero of the evaluation, it shouldn't be included in the calculation. That's why the formula in B1 specifies a range of B5:B40. Since the payments are at the end of the period the formula gives us the correct calculation. As you can see the calculation in Excel is identical to the SQL Server calculation down to the penny. I consider the calculation verified.
Another trusted source of calculations is the Wolfram Alpha web site. Stephen Wolfram and his company Wolfram Research have been making the Mathimatica product for twenty years. In 2009 they made public the Alpha web site which is a combination of information database and search engine with the Mathamatica software and a natural language engine that understands written requests in many domains of knowledge. The key to getting what you are looking for is to express the request in a way that Alpha understands. That's not always obvious, at least to me.
Asking Alpha for the present value of a payment works well. Alpha will restate your original question in its formula language and the following picture shows the present value calculation for the balloon payment
Notice that I had to adapt the calculation to the way that Alpha expects input. The interest rate had to be stated as a yearly percentage, 8%, instead of the monthly rate of 0.0006667. The term had to be stated as years. The calculation matches the calculation made by the cash_flow_present_value function for the balloon payment on 2013-02-01 seen above.
Wolfram Alpha has a lot of potential to verify calculations, because of its inclusion of the Mathamatica capabilities. It also has other interesting capabilities including tons of data such as currency conversion rates, population statistics, historical events and astronomical information. The data is in a form that can be graphed or used in computations.
Next Steps
- Add the cash_flow_present_value function to your database
- Look for opportunities where evaluating cash flows with Net Present Value calculation is meaningful, such as in investments and projects.
- When testing code that does calculations, use a trusted program to verify that your T-SQL calculations are correct.
- Check out the Wolfram Alpha web site and ask it a few questions. Try using it to verify your next calculation.
About the author
Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2010-05-06
