IRR - Internal Rate of Return. Given an investment and the resulting cash-flows determine the rate of return on the investment.
I've recently been working with clients in the financial services industry and have been tasked with moving some spreadsheets into the database. Problem is that they use the Microsoft Excel IRR function which the SQL Server T-SQL language doesn't possess.
I searched around for SQL solutions but found them limiting at best. After a suitable amount of musing, I determined that the best option would be a SQL CLR function as it would obviously be easy. Wrong. It seems that IRR calculation is actually quite hard, certainly beyond my math skills.
So I set about Googling until I came across this website which talks about the problem and has a link to a C# project which solves the problem 👌 You can also look here if you'd like some background on the math used to solve the problem. Personally I found it fascinating even though I only half understand it.
After downloading the project I tested it out and compared the results with Excel, which were great, although it does start to break down after eight places of decimal - I can live with that for my requirements.
Next up was to create a SQL Database project and add the various classes and interfaces from the C# project. After a couple of false starts I got a signed assembly deployed to my local SQL Server, enabled CLR functions and started testing. Results match Excel - Bingo.
If this is any use to you, you can go to my GitHub repository here.
Next up - I'll probably have to do a NPV function.