Calculating XIRR in SQL?

[gopalg]

Hi,

Does anybody know how to perform XIRR calculations in SQL, please?

Thanks
Gopal

 

[xcata]

Hi,

What do you mean by XIRR calculations ?

 

[gopalg]

It seems difficult to write SQL for the calculation of XIRR which is a financial function that measures the internal rate of return for cash flows over a period of time. Microsoft Excel has this function (XIRR). Could I maybe use this Excel function (use its library) from within my SQL OLAP services? if so how would I go about it? Any suggestions would be greatly appreciated.

Thanks for your response.

GG

 

[MinAmiChief]

Yes.

1. VB front end application that uses the Excel library.
2. Access,Excel VBA macro.
3. C external stored procedure.

I think there is a 80% chance that you will have a front end application of some kind. In the front end before you show the IRR calculate it.

Without a front end.
I would write a VBA macro in Access.
a. Include the MS Excel library under tools reference.
b. Connect to SQL using ADO
c. Use VBA to update a field in SQL Server that stores the IRR
d. Save the module as MyModule1
c. Create a macro called Autoexec. This macro will simply run the MyModule1 that you created and then close the database.
d. Save the database C:\Temp\Access_IRR.mdb
c. Call Access database to work using
exec xp_cmdshell "C:\Temp\Access_IRR.mdb"
within a SQL Server stored procedure. This will simply open C:\Temp\Access_IRR.mdb and envoke the Autoexec macro.

Note: To open the mdb without envoking the Autoexec macro hold down the shift key and then press enter in File Explorer.

The chief


Be the change that you want to see in the world - Mahatma Gandhi

 

[gopalg]

Chief,

Thank you so much for your response. I'm still figuring this out now but before that is it even possible to simply register this Excel library which includes this XIRR within SQL 7.0 OLAP services and use it as an external MDX function? Do I have to use Access for this?

Thanks again,
GG

 

[MinAmiChief]

Dude,

I take back what i said before.

I think the best way is to write a store procedure that will calculate the IRR in SQL Server. I come from a finance background and the formula is fairly simple. It may be a little tricky calculating in a database but it will not be difficult. Just make the code flexible enough for reuse.

IRR = (Sum (Financial benefit)^n - Cost)/ cost
Be the change that you want to see in the world - Mahatma Gandhi