Returns a compound annual growth (CAGR) or equivalent interest rate (RRI) for the growth of an investment.

As documented at
https://www.investopedia.com/terms/c/cagr.asp

As found in
https://github.com/DataExperts/dexih.transforms/blob/master/src/dexih.functions.financial/FinancialFunctions.cs

As tested in
https://github.com/DataExperts/dexih.transforms/blob/master/test/dexih.functions.financial.tests/Financial.cs

From todo.txt:

"The interaction of Bisection and Newton algo in findRoot needs review. It seems like it is working now, but it could use some love. Maybe I should switch to a better root finding algo (i.e. Rudder or Brent)"

Issues seems like a better place for this. Will remove todo.txt.

Add support for .NET Core

I understand that there are differences between the way the library and Excel perform some calculations, however, I'm not seeing any reason an exception should be thrown when the coupon is 0. The requirement on Excel's yield function is that the rate parameter is greater than or equal to 0.

Description

I have some IRR calculations with wildly different results compared with Excel.

I have included some example C# with some cases of my mismatching data. Note that for some examples, Excel returns a NUM! error, in which cases I have supplied a double.NaN as the expected value. I'm not sure what the expected behaviour of the library in cases where Excel returns this error.

Repro steps

Run the code below:

var nums = new double[][] {
    //Control from example: https://support.microsoft.com/en-us/office/irr-function-64925eaa-9988-495b-b290-3ad0c163c1bc
    new double[] { -70000,12000,15000,18000,21000,26000 },
    //My examples
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-264.4016315,188.9045976,543.716092,530.2272019,604.528632,534.241247,542.0029841,479.1036244,418.0742638,357.6408251,343.7344172,-81.33203134,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0},
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-43.161,-430.389,-501.96,937.3308129,1779.965058,1908.268878,1502.484889,1058.628376,1210.615764,1322.572013,1294.614828,1040.674621,888.9149407,876.4030213,905.8048006,1040.772848,1101.466178,1030.015334,812.3926072,884.0664324},
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-28.704,-75.504,-23.02606893,1.185710897,13.92458582,27.67665869,10.03008021,11.19371069,9.084245582,5.21520105,-6.686665091,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0},
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-91.08311155,642.6094366,1190.794681,1242.259461,1099.434363,959.1485074,822.4738264,805.0219758,772.0441847,688.4331595,526.7929387,148.1610195,0,0,0,0,0,0,0,0,0,0,0,0},
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-43.6746279,295.7212113,327.4711176,292.1510936,257.2684481,221.3013225,230.8746166,240.5736588,246.3812007,252.6002921,259.2265278,265.9834272,379.167634,389.1791145,399.2526461,409.3782695,419.6130945,429.8745746,699.2714037,710.7886706,722.4094988,734.1335718,745.9547144},
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-50.544,-18.70128,-5,-5,-20.81759411,-1.770116165,0.503289685,0.314113056,0.771195008,-6.760070328,-1.148506289,-5.468591741,-5.463712308,-5.961486897,-77.9481861,0,0,0,0,0,0,0,0,0,0,0,0,0,0},
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,386.7071313,358.8012443,412.1902946,406.1510305,575.7373662,434.2232677,482.4196839,326.8542996,148.1786251,166.3984516,165.1862579,299.0548516,433.2516591,274.6848952,-57.7222301,-77.64704325,-74.51874336,-72.47051425,-175.5111645,0,0,0,0,0,0,0,0},
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-86,-48,-48,-14.44851464,21.91564362,43.38578906,52.94575179,-12.91690729,1.592347847,-59.42631116,-51.25464708,4.561745518,-58.86849133,-41.74956104,-48.2415346,-50.71649086,0,0,0,0,0,0,0,0,0,0,0,0},
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,188.2654506,258.853252,248.190251,415.1319939,352.9407094,313.9594741,245.2171731,169.7541284,152.8011487,-10.12116163,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0},
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-11.72006017,48.20251141,90.2234982,110.7320377,97.53736511,84.90423777,72.61183825,59.93543912,47.21437118,47.50882332,47.59134869,47.99650759,51.13068874,51.59539354,49.42200214,0,0,0,0,0,0,0,0,0,0,0},
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,-100,-100,-200,187.100265,253.9146864,147.551264,34.92061992,-64.04128041,-208.5295737,-300.0898697,-341.5536358,-386.6206075,-429.4765567,-468.1390525,-520.189004,-10.04343545,-85.70649279,0,0,0,0,0,0,0,0,0},
    //This example does not contain a positive and negative number and so is not expected to return a result
    new double[] {0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,259.4212097,590.9890307,659.3935863,746.5125155,868.4792077,724.4262136,651.6032203,570.292362,482.2883783,374.9513019,374.8559331,355.5442713,504.803386,498.9507628,493.491597,487.9682785,482.3713156,474.1003335,465.5522816,456.6731588,447.4077869,437.6865137,427.4187315,416.6237142,405.2395334,393.1984566,380.2299029},
};

var expected = new double[]
{
    //Control
    0.086631,
    //My examples
    1.299489258,
    0.900688878,
    -0.1201593755,
    7.747939368,
    6.848641279,
    double.NaN,
    double.NaN,
    double.NaN,
    double.NaN,
    4.778677157,
    double.NaN,
    //No positive and negative number
    double.NaN
};

var actual = nums.Select(n => Excel.FinancialFunctions.Financial.Irr(n)).ToArray();


//Compare up to a precision of 6 decimal places
var matches = actual.Select((a,index) => Math.Round(expected[index], 6) == Math.Round(a,6)).ToArray();

Expected behavior

It is expected that the matches array should be true for all indices except where expected contains double.NaN (Note that by design double.NaN == will return false in C#).

Note that it is not known what the expected behaviour is where Excel would return NUM! error.

Actual behavior

Only matches[0] provides a true result

Known workarounds

No known work arounds

Related information

• Operating system: Windows 11
• Branch: Versions 3.1.0 and 3.2.0
• .NET Runtime, CoreCLR or Mono Version: .Net 6.0
• Performance information, links to performance testing scripts

Description

I cannot find documentation on the library, nor is there any documentation on how to generate the documentation.

Repro steps

1. The API Reference link is broken. It produces an empty page. http://fsprojects.github.io/ExcelFinancialFunctions/reference/index.html

2. The "/docs/content" folder has some text files that say there is a way to generate documentation but no instructions on how to do so.

Expected behavior

I would expect a way to generate / view documentation

Actual behavior

Current links are broken, not instrucitons on generating docs.

Known workarounds

Related information

• Operating system: Windows 7
• Branch: master
• .NET Runtime, CoreCLR or Mono Version: All
• Performance information, links to performance testing scripts: N/A

This has been posted to the NuGet Gallery. The issue remains open to track any problems or concerns that anyone may have.

Here's what's new

• Removes needless constraint on 0-value inputs to FV & PMT. (PR #67)

Description

All functions work fine when used with C#, however, if I try to come with rate I get the error:

System.IO.FileLoadException occurred
HResult=0x80131040
Message=Could not load file or assembly 'FSharp.Core, Version=3.78.4.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a'. The located assembly's manifest definition does not match the assembly reference. (Exception from HRESULT: 0x80131040)
Source=
StackTrace:
at Excel.FinancialFunctions.Tvm.calcRate(Double nper, Double pmt, Double pv, Double fv, PaymentDue pd, Double guess)
at dFin0._002.TVM.JurosButton_Click(Object sender, RoutedEventArgs e) in C:\Users\difio\Documents\Visual Studio 2017\Projects\dFin0.002\dFin0.002\TVM.xaml.cs:line 79

Repro steps

Just running with hardcoded values is well enought:

i = Financial.Rate(12, 0, -100, 313.84, PaymentDue.BeginningOfPeriod);

Expected behavior

Should return a value to "i" variable

Actual behavior

Error message above

Known workarounds

Not known

Related information

• Windows 10 Pro* Branch
• .NET 4.6.2

I would like to publish a release candidate to nuget, with version 3.0.0-rc1. Moving to .NET Standard will break projects using .NET Framework versions prior to 4.6.1, ergo a major version bump. Would like a prerelease so I can do some prerelease testing especially around upgrading in-place projects.

@dsyme , thoughts?

As far as process, I'd like to set up a GitHub action which pushes published releases to nuget. So the release process is: Push Tag -> Create Release -> Publish Release -> Relax. Also planning to use the github tag as the build/package version.

For this I'll need access to the nuget API secret. (Pretty please? 😄)

For the record, this release will contain:

• #9

Have downloaded the code and trying to compile from the command prompt using the batch file as I don't have vs 2015 installed (although, could open the project in vs 2013).

While compiling from command below is the error shown:

1. Building D:\Sandbox\ExcelFinancialFunctions\ExcelFinancialFunctions.sln failed with exitcode 1.
2. FS0078: D:\Sandbox\ExcelFinancialFunctions\src\ExcelFinancialFunctions\unknown(1,1): Unable to find the file 'System.Runtime.InteropServices.dll' in any of� C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework.NETPortable\v4.5\Profile\Profile259� D:\Sandbox\ExcelFinancialFunctions\src\ExcelFinancialFunctions� D:\Sandbox\ExcelFinancialFunctions\packages\FSharp.Core\lib\portable-net45+netcore45+wpa81+wp8

This folder (C:\Program Files (x86)\Reference Assemblies\Microsoft\Framework.NETPortable\v4.5\Profile\Profile259) exists but the dll is missing.

Please help me out as have to make nuget pkg without any dependency to use in 4.5.1 web project. have seen the nuget gallery but is dependent on FSharp.Core

As described here https://support.microsoft.com/en-us/office/pduration-function-44f33460-5be5-4c90-b857-22308892adaf

Description

Returns the number of periods required by an investment to reach a specified value.

Syntax

PDURATION(rate, pv, fv)

The PDURATION function syntax has the following arguments.

• Rate Required. Rate is the interest rate per period.
• Pv Required. Pv is the present value of the investment.
• Fv Required. Fv is the desired future value of the investment.

The test named "mduration shouldn't be greater than maturity()" fails occasionally. Will disable for now, troubleshoot in the future when I understand FsCheck better.

Release 3.1.0

This has been posted to the NuGet Gallery. The issue remains open to track any problems or concerns that anyone may have.

Here's what's new:

This release closes all known issues and implements newer functions which were added to Excel since the library was originally releases.

• Adds PDURATION function. Returns the number of periods required by an investment to reach a specified value. (Resolves #62)
• Adds RRI function. Also used for CAGR. Returns an equivalent interest rate for the growth of an investment. (Resolves #60)
• Improves XIRR function by reducing the precision required before an answer is returned. (Fixes #27)
• Improves ACCRINT function by allowing first interest date on the settlement date. (Fixes #22)
• Adds PriceAllowNegativeYield function, which operates like the PRICE function except that it allows negative yield inputs. This is experimental. We'd love feedback on how this works for folks. (Fixes #13)

Not working: https://fsprojects.github.io/ExcelFinancialFunctions/

First of all, nice work on getting this cleaned up and published at FsProjects!!

I was wondering if it would make sense to change the top-level namespace for the project? For me, System implies that it is standard part of .NET.

As this is aimed at .NET in general (a bit like Deedle), using FShrap.Xyz probably would not be a good choice either. I don't really have a good alternative name, so sorry for not-very-useful feedback!

Hi,

I'm trying to update FSharp.Core to 4.3.4 version using Nuget and getting error below.

Unable to resolve dependencies. 'FSharp.Core 4.3.4' is not compatible with 'ExcelFinancialFunctions 2.4.0 constraint: FSharp.Core (>= 4.0.0.1 && < 4.0.1)'.

Maybe it is good idea to relax upper version requirement?

Regards,
Vlad Kirienko

Hi,
Downloaded the code. Opened up the main solution (ExcelFinancialFunctions.sln) with VS 2017 on my win 7 box. Tried to compile. Noticed FSHARP.CORE was missing, made a reference to one in c:\program files(x86)\microsoft\visual studio\2017\professional\common7\ide\publicassemblies\fsharp.core.dll.
Tried to compile but getting all sorts of error (146) e.g. a unique overload for method 'min' could not be determined based on type information prior to this program point.

Do you require any other references. as a note, i can use DLL fine, but would like to compile the code myself so i can step thru the code.

Thanks in advance,

Jeremy

This works different from Excel. In Excel the following returns -0.67429 but using the FinancialFunctions it returns 0.04534. Any date in the second parameter within 6 months will return a similiar value (11/15/2015 returns .04551).

DateTime testsettle = Convert.ToDateTime("9/21/2015");
DateTime testmaturity = Convert.ToDateTime("10/15/2015");

Financial.Yield(testsettle, testmaturity, .04625, 105.124, 100, Excel.FinancialFunctions.Frequency.SemiAnnual, DayCountBasis.UsPsa30_360);

This is a really great tool and this is the final item I need to complete the project I'm working on.

This issue tracks Release 3.0.1.

My thought here is that we should get 3.0.0 out into the wild with ONLY the netcore framework change, rather than release framework changes and bug fixes in the same go. Once we get some uptake on 3.0.0 to ensure all is well, then 3.0.1 should go out with bug fixes.

The following issues have tested fixes waiting in feature branches. This will clean out the issues list of all known bugs.

• #22
• #27
• #13

Hello,

This is great work and that I would like to port it to C# as a .NET core project. Do you have any preference in what I should call the port and who should receive credit. I'm looking to port to C# for learning purposes, and to use on my blog.

Thanks, Kevin

Hello,

The following Rate function call results in an exception, while the behavior in Excel is to return the value 0.00043296:

Financial.Rate(260,-60, 13500, 1400, 0);

Exception Message: FindBounds: one of the values (-1.000000, 3.211696) cannot be used to evaluate the objective function

Stack trace:

at Excel.FinancialFunctions.Common.rfindBounds@59(FSharpFunc`2 f, Double minBound, Double maxBound, Double precision, Double low, Double up, Int32 tries)
at Excel.FinancialFunctions.Common.findBounds(FSharpFunc`2 f, Double guess, Double minBound, Double maxBound, Double precision)
at Excel.FinancialFunctions.Common.findRoot(FSharpFunc`2 f, Double guess)

I'm using the version 2.2.0 from NuGet.

Thanks,

-cms

This issue tracks changes which will be included in the next release.

Here's what's new

• Adds strong name assembly signing. (PR #69)

Description

Can you please let me know the ExcelFinancialFunctions equivalent for Percentile.Inc?

Repro steps

Please see steps in XL:
https://support.office.com/en-us/article/PERCENTILE-INC-function-680f9539-45eb-410b-9a5e-c1355e5fe2ed

Expected behavior

Would like to get a method that does the same in the library. Please help.

Actual behavior

Don't see a function for this.

Known workarounds

I have been able to use the following in C# code successfully:

private double Percentile(double[] sequence, double excelPercentile) { Array.Sort(sequence); int N = sequence.Length; double n = (N - 1) * excelPercentile + 1; // Another method: double n = (N + 1) * excelPercentile; if (n == 1d) return sequence[0]; else if (n == N) return sequence[N - 1]; else { int k = (int)n; double d = n - k; return sequence[k - 1] + d * (sequence[k] - sequence[k - 1]); } }

Related information

Description

Can't calculate price with a negative yield. This matches current Excel capabilities, but this is a valid calculation for computing price of a bond.

Repro steps

Please provide the steps required to reproduce the problem

1. Call Calculators.CalcPrice(settlement, maturity, coupon, yield, 100), using a negative yield value (-0.002)

Expected behavior

return value of Price.
http://www.investopedia.com/ask/answers/06/negativeyieldbond.asp

Actual behavior

Exception thrown: 'System.Exception' in ExcelFinancialFunctions.dll
Additional information: yld must be more than 0

Known workarounds

None.

Related information

• Windows 10
• Master Branch
• .NET Runtime

NUnit 4 brings breaking changes.

#101 will require changes in order to merge

Almost every calculation I have tried matches Excel, but I have a few such as the following which are way off and I'm not sure why

Yield("09/19/2014","10/20/2014",5.25/100,100.171,100,2,2) = 0.031569 Yield("09/09/2014","10/20/2014",5.25/100,100.305,100,2,2) = 0.024695

Financial.Yield(new DateTime(2014,09,19), new DateTime(2014,10,20), 5.25/100, 100.171, 100, Frequency.SemiAnnual, DayCountBasis.Actual360);  // 0.0513614870373403
Financial.Yield(new DateTime(2014,09,09), new DateTime(2014,10,20), 5.25/100, 100.305, 100, Frequency.SemiAnnual, DayCountBasis.Actual360); // 0.0514348695758675

Would anyone like to be a second maintainer on this project? Please let me know :)

Also, is anyone able to do the work to simplify this to use .NET SDK project files?

Release 3.0.0

This has been posted to the NuGet Gallery. This issue tracks any problems or concerns that anyone may have.

Here's what's new:

Retarget library onto .NET Standard 2.0.

• Adds explicit support for .NET Core 2.0 and higher including 5.0 and 6.0
• Removes support for full .NET Framework 4.6 and lower

Description

Calling the AccrInt(issue, firstInterest, settlement, rate, par, frequency, basis, calcMethod = AccrIntCalcMethod.FromIssueToSettlement) function with firstInterest <= settlement results in an exception. This does not occur in Excel (tested 2007 and 2013).

Repro steps

1. Add DLL to a C# project

2. Execute following code:

using Excel.FinancialFunctions;

            DateTime issue = DateTime.Parse("08/15/18");
            DateTime firstInterest = DateTime.Parse("02/15/19");
            DateTime settlement = DateTime.Parse("02/15/19");
            double rate = 0.02125;
            double par = 100;
            Frequency freq = Frequency.SemiAnnual;
            DayCountBasis basis = DayCountBasis.ActualActual;
            var ret = Financial.AccrInt(issue, firstInterest, settlement, rate, par, freq, basis).ToString();
            Console.WriteLine("Result of test is: " + ret.ToString());

This results in an exception.

Expected behavior

We expect this to match the output of the Excel function call =ACCRINT(08/15/18, 02/15/19, 02/15/19, 0.02125, 100, 2, 1), which evaluates to 1.0625.

Actual behavior

We get the following exception:

System.Exception: firstInterest must be after settlement at Excel.FinancialFunctions.Bonds.calcAccrInt(DateTime issue, DateTime firstInterest, DateTime settlement, Double rate, Double par, Frequency frequency, DayCountBasis basis, AccrIntCalcMethod calcMethod)

Known workarounds

Comment out the line in bonds.fs under let calcAccrInt issue firstInterest settlement rate par (frequency:Frequency) basis (calcMethod:AccrIntCalcMethod) = that looks like this:

        (firstInterest > settlement)    |> elseThrow "firstInterest must be after settlement"

For example, it's currently (as of writing) at this line in the code.
I'm not sure how this might affect other functionality or expectations. Compiling and testing this change doesn't seem to break any of the ~200000 test cases on my machine though.

Related information

• Operating system: CentOS 7
• Branch: master
• .NET Core Version: 2.2

Thanks for authoring this fantastic library!

Description

Microsoft's documentation for the XIRR function states under the Remarks section that they guarantee 1x10^-6 accuracy; however, this library is using 1x10^-7 accuracy in common.fs findRoot function. Removing one zero would align with Microsoft's standards: https://support.microsoft.com/en-us/office/xirr-function-de1242ec-6477-445b-b11b-a303ad9adc9d

Repro steps

Please provide the steps required to reproduce the problem

1. Call the financial.XIrr function with the following parameters. These dates are July 3rd, 2020 and Feb 25th, 2021:
   DateTime[] dateTimes = { new DateTime(2020,7,3), new DateTime(2021,2,25)};
   double[] doubles = {-177900000, 8799805.85};
   Financial.XIrr( doubles, dateTimes, 0.1);

Expected behavior

A value should be returned representing the IRR amount, which should be -0.990247691899517.

Actual behavior

Exception is generated saying that the root could not be found.

Known workarounds

N/a
This issue can be fixed by changing the precision value in common.fs's findRoot method from 1x10^-7 to 1x10^-6.

Related information

• Using a Windows 10 OS
• On the Master branch
• .NET Runtime