# Calculating and verifying financial values in SQL Server

By:   |   Comments (8)   |   Related: More > 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                   ) GOCREATE 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.8133```

Each 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
• 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. Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.

View all my tips

 Tuesday, August 11, 2015 - 8:59:57 AM - Darek Back To Top (38420) To get more accurate results I'd first change the formula in such a way that it doesn't perform any divisions, just multiplication (multiply the formula on both sides by the term with the highest power in the denominator of the original formula, then sum up, and then and only then divide by the same term).

 Saturday, June 14, 2014 - 9:47:36 PM - Jorge Cordero Back To Top (32245) would you help me to design a DB for managing loans (payments , capital, interest, discounts)   tks Jorge Cordero

 Wednesday, February 26, 2014 - 8:48:47 AM - Amit Back To Top (29582) Dear andy,   I found your article very interesting and a good source of knowledge , especially  in the wake of  so many people writing about t-sql  but not on actual real world scenarios using t-sql   . I recently worked on an asset liability management system and had to code a lot in sql server .You article really makes it simple and I wished I would have read it while I was working on the code. Anyways can you pl come up with more posts on performing financial calculations using t-sql .

 Wednesday, January 26, 2011 - 6:24:23 PM - Jay Back To Top (12722) Here's a twist....I need to include a conditional for payments made in Advance vs. Arrears.  The logic is no problem, but what I'm trying to figure out is how the modify the calculation in the UDF to calculate the present value of an amount at the beginning of a period, vs the end.  Effectively, this reduces the amount periods by one month, but then you also have to factor in the initial month where the period would be zero. Been trying to hash that out.  Any ideas?

 Tuesday, May 11, 2010 - 12:15:41 PM - Andrew Novick Back To Top (5353) That should be "Add the line" DECLARE @pmt decimal (38,2) = 2500000.0

 Tuesday, May 11, 2010 - 12:15:01 PM - Andrew Novick Back To Top (5352) Yes the declaration is missing. At the line  DECLARE @pmt decimal (38,2) = 2500000.0 at the top of the 2nd insert, or you can put it at the topof the batch  if you execute it all at once.

 Friday, May 7, 2010 - 12:53:08 PM - eliassal Back To Top (5336) Hi, I find your article very interesting and started to play with it. 1st question, in your 2nd insert, last value is @pmt which can not be inserted , I am getting the following error Msg 137, Level 15, State 2, Line 8 Must declare the scalar variable "@pmt". Thanks