Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Calculating and verifying financial values in SQL Server


By:   |   Read Comments (8)   |   Related Tips: More > T-SQL

New Whitepaper > 10 Ways We Can Steal Your Data >> download now


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:

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. 

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

Or in T-SQL:

SELECT 10000.00 POWER(0.00666712)

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_idcash_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_idinceptionmonthly_ratenamecust_id
VALUES ('LN001''2010-02-01'0.006667'Big Mall''0001')
GO

INSERT INTO loan_cash_flow (loan_idcash_flow_date
                          
disbursement_or_paymentamount
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_dateGETDATE())
                         , 
@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(MONTHl.inception cf.cash_flow_dateperiods 
     cf.disbursement_or_payment [D or P] 
     ROUND(1.0 /POWER(l.monthly_rate 
                 ,DATEDIFF(MONTHl.inception 
                 cf.cash_flow_date)),8discount 
     cf.amount 
     dbo.cash_flow_present_value(l.inceptionl.monthly_rate 
                                  cf.cash_flow_date 
                                  ,cf.disbursement_or_payment 
                                  cf.amountPV 
   FROM dbo.loan_cash_flow cf 
        INNER JOIN dbo.loan l ON cf.loan_id l.loan_id 
   ORDER BY cf.loan_idcf.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.

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

Asking Alpha for the present value of a payment works well

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.


Last Update:


signup button

next tip button



About the author
MSSQLTips author Andy Novick Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Tuesday, August 11, 2015 - 8:59:57 AM - Darek Back To Top

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

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

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

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?


Thursday, May 13, 2010 - 12:58:48 PM - admin Back To Top

The script has been updated to reflect this.


Tuesday, May 11, 2010 - 12:15:41 PM - Andrew Novick Back To Top

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

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 top
of the batch  if you execute it all at once.


Friday, May 07, 2010 - 12:53:08 PM - eliassal Back To Top

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


Learn more about SQL Server tools