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");

Seems to work fine for me. What version of EPPlus are you using? Post some code so others can try. — Ernie S, Jun 18 '19 at 21:42
You haven't posted code that creates an Excell sheet, much less a formula that uses `NPV` or `IRR`. Where do you load the data and where do you use `NPV` or `IRR` ? If you try to load an already existing Excel sheet, `#VALUE` is returned because the *original* formula contains an error. `Epplus` can't fix a bad formula. Or did you expect Epplus to *recalculate* the values? — Panagiotis Kanavos, Jun 19 '19 at 11:14
Hello @PanagiotisKanavos, thanks for your response. It is an already existing excel and worksheet calculates IRR, from cells in a different sheet, with the IRR() in-built function. I'm able to see the calculated value in the excel and it s not a bad formula. I get the result #VALUE only when i try to fetch the result from excel to the grid. — Chendur, Jun 19 '19 at 11:28
@Chendur which means that `#VALUE` is stored in that Excel sheet to begin with. Epplus won't recalculate formulas unless you ask it to with `.Workbook.Calculate()`. Even then, it may fail because it doesn't try to emulate the Excel calculation engine 100%. That's not its job — Panagiotis Kanavos, Jun 19 '19 at 11:29
I have the line {p.Workbook.Calculate();} so that every single formula in the entire workbook is calculated. Am i missing something? — Chendur, Jun 19 '19 at 11:33
Those functions [aren't in the list of supported functions](https://github.com/JanKallman/EPPlus/wiki/Supported-Functions). It's possible to [add your own implementation though](https://github.com/JanKallman/EPPlus/wiki/Implementing-missing-or-new-Excel-functions) — Panagiotis Kanavos, Jun 19 '19 at 11:33
Damn, thanks. Also, Is there a way to paste the entire sheet as values though? — Chendur, Jun 19 '19 at 11:35
What does that even mean? There's no desktop in a web server. There's no *Excel* running, Eppluss is a library — Panagiotis Kanavos, Jun 19 '19 at 11:36
On the other hand, you should use `myWorksheet1.Cells["f57"].Value` **only** to retrieve a value, not that double conversion to string then to float then to string — Panagiotis Kanavos, Jun 19 '19 at 11:37
Yes, I understand there isn't an MS excel running. I was just wondering if EPPlus could copy and paste the cell as values, as it can do so many things. Thanks for your time anyway. — Chendur, Jun 19 '19 at 11:37
`if EPPlus could copy and paste the cell as values` that doesn't mean anything. And the values are *already* there. If cell `F57` contains a number, `myWorksheet1.Cells["f57"].Value` will return it wrapped as an `object`. You can cast it to get the `float`. There's *no* reason to convert that to string then to float then back to string. — Panagiotis Kanavos, Jun 19 '19 at 11:38
Debug your code and check what `.Value` contains, eg by hovering over it or in a watch variable. You'll probably see it's a `double` or `decimal` already — Panagiotis Kanavos, Jun 19 '19 at 11:40
By copy paste as values - I mean currently the cell is a formula and EPPlus couldnt support it. If the cell, after computing the formula is put up as values [and not a formula's result] I would be able to retrieve the value. When I hover over .Value, it shows me the value as {#NAME?} — Chendur, Jun 19 '19 at 13:43

score 0 · Accepted Answer · answered Jan 09 '20 at 12:21

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

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

1 Answers1