Sql Financial Accounting Cracked

By: Andy Novick | Updated: 2010-05-06 | Comments (8) | Related: More >T-SQL

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.

Download sql financial accounting for free. Business software downloads - SQL Financial Accounting Import by E Stream Software Sdn Bhd and many more programs are available for instant and free download. Sql Financial Accounting Activation Code. Correct Accounting Software is a very powerful financial accounting software for small and medium businesses. Royalty free fully featured SQL database with nearly 1 million installations worldwide. Lost Activation Code: FileHungry Search (launch count, launch date, runtime limitations). Apr 15, 2021 SQL Financial Accounting 4.2. Choose the most popular programs from Business software. 5.0 (1 vote) 4.2016.789. E Stream Software. Review Comments Questions.

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 payment_i 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:

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:

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:

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.

Last Updated: 2010-05-06

About the author

Andy Novick is a SQL Server Developer in the Boston area with 25 years of database and application development experience.
View all my tips

SQL Accounting is introduces, the integrated business backbone delivers an unrivaled combination of power (features), control (multi dimension reporting) and innovative EIS tools, riding on fast and unbeatable database to accelerate business flexibility.

SQL Accounting – Features

SQL Accounting – Interactive, features rich, feasible, client server, cross database, able to sit on Linux, fast and stable. Basically, SQL Accounting consists of six core modules which are GL, Customer, Supplier, Stock, Sales, and Purchase. Spend some time to review the following list of features available to you when using our SQL Accounting and judge for yourself.

Getting things done more