
Financial Functions in TSQL
I am working with a financial product and am tinkering with a stored procedure of theirs that calculates an IRR. <BR>The string that I pass into the function is the listed below. The first part before the '^' is the dollar amount followed by a date and subsequently followed by another cash flow. <BR>Is there a financial function in TSQL that can calculate IRR, which I suppose would be the same as the XIRR function within Excel. <BR><BR><BR>150150^03/12/1997;136^12/31/1997;48055^11/18/1999;1699^04/05/2000;21029^12/31/2000;375512^12/31/2001

Here are all the functions:
http://msdn.microsoft.com/library/enus/tsqlref/ts_fafz_9dyr.asp<BR><BR>If it ain't there, then you get to write it.<BR><BR>Why would you do this in TSQL, though, of all crazy things????<BR><BR>Do you store the date/amount pairs in a table for later update and retrieval? If so, then where do you store the IRR? Or do you mean you want to get the rate of return *from* an existing table? (That makes at least some sense to me; but passing in the values? Why? You could more easily do that in VBScript code in ASP.)<BR><BR>

RE: Here are all the functions:
Basically, I want to get an IRR from a table.

I could code it in VBS...
...but I'm not enough of a TSQL person to do it there.<BR><BR>No matter where you do it, the basic algorithm is the same:<BR><BR>Assume a given RR. Get the net PV using that RR.<BR>If the difference between the net PV and zero is less than some delta (typically, less than $0.001), then the current RR is the answer. Return.<BR>If the net PV is > 0, then decrease the RR.<BR>If the net PV is < 0, then increase the RR.<BR>Loop<BR><BR>There's no forumulaic way to do. Brute force iteration works best. <BR><BR>Assuming that the IRR will be between 0% and 100%, you start with 50% as the RR and 50% as the "addon". Each time, you cut the "addon" in half.<BR><BR><BR>

How I'd do it...
<%<BR>SQL = "SELECT when, amount, 0 FROM table ORDER BY when"<BR>Set RS = conn.Execute( SQL )<BR>data = RS.GetRows( )<BR>RS.Close<BR><BR>CONST COL_when = 0<BR>CONST COL_amt = 1<BR>CONST COL_days = 2<BR><BR>firstdate = data( COL_when, 0 ) <BR>maxRow = UBound( data, 2 )<BR><BR>' change the zero in last column to number of days from<BR>' the first date to the date of the occurrence<BR>' at the same time, determine if this will be positive or <BR>' negative RR<BR><BR>total = 0<BR>For row = 0 To maxRow<BR> data( COL_days, row ) = DateDiff("d",firstdate,data(COL_when,row))<BR> total = total + data( COL_amt, row )<BR>Next<BR><BR>' easiest thing to do here, if RR would be negative, is <BR>' flip the sign of all the amounts. Calculate a positive RR.<BR>' then just remember you did this and flip the sign of final result<BR><BR>' and here you start the iterations...<BR>...<BR>%><BR><BR>You don't *have* to precalculate the number of days in each row, but it's an expensive enough operation that it's much better to do it just once.<BR><BR>Ditto the negative RR. But doing that on the fly just really complicates the algorithm.<BR><BR>

HEY, LARS! DUTCH!
Now this one is a fun challenge. Care to try to write a rateofreturn finder in TSQL? <shudder/><BR><BR>

RE: HEY, LARS! DUTCH!
Call me stupid but what is an IRR???<BR><BR>But I guess I can figure the 'formula' from your VBS example.

RE: HEY, LARS! DUTCH!
Heh,<BR>>>> But I guess I can figure the 'formula' from your VBS example<BR><BR>No I can't. Some more info?<BR><BR>But poster: keep in mind SQL Server is not the place you want to do mathematical calculations. It doesn't correct floating point rounding problems

Internal Rate of Return
so I at least figured out what it is.<BR><BR>IRR equals the percentage rate by which you have to discount the net benefits for your time period until the point that they equal the initial costs. IRR is closely related to net present value. The rate of return calculated by IRR is the discount rate you would need to apply to your benefits to obtain a net present value of zero<BR><BR>I'll see if I can come up with something just for kicks but it's going to be ugly I think and it really is best to do this in your code (or even better in a COM object)

RE: Internal Rate of Return
But this is an Excel function. According to the help menu, you can invoke Excel financial functions TSQL. I, however, have not seen any documentation, and this is true even if I go to the MSDN web site.<BR><BR>Thanks,<BR><BR>Vipul<BR>
Posting Permissions
 You may not post new threads
 You may not post replies
 You may not post attachments
 You may not edit your posts

Forum Rules

