Post reply

Financial calcs like NPV and IRR

  • November 21, 2005 at 10:47 am

    #92300

    Hello,

    I'd like to use some financial calculations in sql server like NPV and IRR. I have read http://www.intelligententerprise.com/print_article_flat.jhtml?article=/online_only/celko/030303_1.jhtml and I'm thinking about following that guide to creating my own functions, but I was curious if anyone had other ideas on how to implement these calculations.

    Thanks,

    Greg

  • November 21, 2005 at 11:49 am

    #605031

    Personally I don't know what they are so I cannot tell you if SQL already has something built-in to handle this.

    Can you expand on what those functions are?



    Good Hunting!

    AJ Ahrens


    webmaster@kritter.net

  • November 21, 2005 at 11:57 am

    #605038

    NPV: Net Present Value

    IRR: Internal Rate of Return

    They are explained in greater detail in the article at http://www.intelligententerprise.com/print_article_flat.jhtml?article=/online_only/celko/030303_1.jhtml

    Regards,

    Greg

  • November 22, 2005 at 7:04 am

    #605215

    I haven't worked with NPV or IRR calculations but I have done quite a bit of work with Time-Weighted rates of return.  Time-weighted returns are basically daily returns that are then geometrically linked.
     
    Daily calc: (Ending Market Value - Beginning Market Value - Cash Flow) / Beginning Market Value
    Geo.Link: ((1+R1)*(1+R2)*(1+R3)*(1+R4))-1
     
    What I found is that the most efficient means of calculating returns is through a combination of SQL and .NET.  T-SQL is just not the best means of doing procedural logic.  Whether you utilize CLR in SQL 2005 or pure .NET code as I did (looped through records in .NET dataset) you'll find that stepping through the raw inputs is far faster using .NET than T-SQL.  In my case I load all of the necessary inputs from stored procedures into a .NET dataset.  I then loop through this dataset, striking returns and inserting the resulting records into another .NET dataset that my web page or SSRS report then consumes.
     
     

  • November 22, 2005 at 8:55 am

    #605251

    The .NET approach (or at least ASP.NET) is actually viable since this will be presented in a web page, but I kind of want other applications within my database server to expose the results of the NPV/IRR calcs to other databases on the server. That desire of availability to other databases means that I want to keep it in T-SQL - unless there is a better way, of course.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply