EPPlus - Unable to get values of NPV and IRR functions from excel

Question

I have a model in excel and trying to feed inputs through ASP.NET to the excel and returning the back the model results over the grid in the front end. I'm using EPPlus package in ASP.NET as it doesn't need MS Office to be installed.

I have trouble getting the value of the cell where NPV [Net Present Value] and IRR [Internal rate of return] functions have been used. It always returns #VALUE or #NAME. As a workaround, I was able to calculate NPV manually but I'm having a tough time with IRR.

Is there a way in EPPlus to paste all the cells as values in the worksheet? Or is there any other alternative?

Please help and thanks in advance

Update : I have EPPlus 4.5.3.1 and Visual studio 2013.

string strfilepath = Server.MapPath(destPath);
FileInfo fileInfo = new FileInfo(strfilepath);
ExcelPackage p = new ExcelPackage(fileInfo);
ExcelWorksheet myWorksheet = p.Workbook.Worksheets["Financial"];
GridView1.Rows[0].Cells[2].Text = float.Parse(myWorksheet1.Cells["f57"].Value.ToString()).ToString("N2");

Answer 1

I have tried all possible ways in EPPlus but I was not able to calculate IRR. I came up with a solution in SQL and it gives the result close to excel 's IRR function.

CREATE FUNCTION [dbo].[Ufn_irr1] (@strIDs1 VARCHAR(10), 
                                  @strIDs2 VARCHAR(10), 
                                  @guess   DECIMAL(30, 10)) 
returns DECIMAL(30, 10) 
AS 
  BEGIN 
      DECLARE @t_IDs TABLE 
        ( a
           id    INT IDENTITY(0, 1), 
           value DECIMAL(30, 10) 
        ) 
      DECLARE @strIDs VARCHAR(max) 

      SET @strIDs = @strIDs1 + ',' + @strIDs2 

      DECLARE @strID  VARCHAR(12), 
              @sepPos INT, 
              @NPV    DECIMAL(30, 10) 

      SET @strIDs = COALESCE(@strIDs + ',', '') 
      SET @sepPos = Charindex(',', @strIDs) 

      WHILE @sepPos > 0 
        BEGIN 
            SET @strID = LEFT(@strIDs, @sepPos - 1) 

            INSERT INTO @t_IDs 
                        (value) 
            SELECT ( Cast(@strID AS DECIMAL(20, 10)) ) 
            WHERE  Isnumeric(@strID) = 1 

            SET @strIDs = RIGHT(@strIDs, Datalength(@strIDs) - @sepPos) 
            SET @sepPos = Charindex(',', @strIDs) 
        END 

      SET @guess = CASE 
                     WHEN Isnull(@guess, 0) <= 0 THEN 0.00001 
                     ELSE @guess 
                   END 

      SELECT @NPV = Sum(value / Power(1 + @guess, id)) 
      FROM   @t_IDs 

      WHILE @NPV > 0 
        BEGIN 
            SET @guess = @guess + 0.00001 

            SELECT @NPV = Sum(value / Power(1 + @guess, id)) 
            FROM   @t_IDs 
        END 

      RETURN @guess 
  END