For anyone else looking for the correct formula I found another post were someone was having the exact issue I was.

Follow this link for the corrected script

http://stackoverflow.com/questions/20972209/xirr-calc-in-sql

This works...

Thanks for the help. I would check and upgrade the service pack for that instance but since we are migrating to sql 2014 I wont really worry about that...

When ever I open the GUI Profiler in SQL 2014 to a 2005 server I get an error that tells me it only works with sql 2005 or newer. ...

I tried

SET @query=

'SELECT * Into #tempTable FROM

(SELECT RepID, State

FROM RepBDState) src

PIVOT (count(State) FOR State IN ('+@listCol+')) AS pvt'

EXECUTE (@Query)

select * from #tempTable

and when run I get the following

(236 row(s)...