XNPV calculation in C# to match excel formula

Viewed 36

I'm having a bit of trouble with a calculation in Excel that needs to be written in C#.

The calculation in Excel is this: (I work at an investment company)

=-XNPV(B1,B12:K12,B11:K11)*(1+B1)^((C1-B11)/365) Here is a screenshot showing what fits what cell: enter image description here

Here is the total value as of today (July 12th, 2022): $8,871.29

I need to create the same formula using C#. This is what I am currently using:

 List<FinanceFormulas.XNPVFlow> l = new List<FinanceFormulas.XNPVFlow>
        {
            new FinanceFormulas.XNPVFlow(new DateTime(2021, 3, 28), 8000),
            new FinanceFormulas.XNPVFlow(new DateTime(2021, 7, 10), 40),
            new FinanceFormulas.XNPVFlow(new DateTime(2021, 8, 10), 40),
            new FinanceFormulas.XNPVFlow(new DateTime(2021, 9, 10), 40),
            new FinanceFormulas.XNPVFlow(new DateTime(2021, 10, 10), 40),
            new FinanceFormulas.XNPVFlow(new DateTime(2021, 11, 10), 40),
            new FinanceFormulas.XNPVFlow(new DateTime(2021, 12, 10), 40),
            new FinanceFormulas.XNPVFlow(new DateTime(2022, 1, 10), 40),
            new FinanceFormulas.XNPVFlow(new DateTime(2022, 2, 10), 40),
            new FinanceFormulas.XNPVFlow(new DateTime(2022, 3, 10), 40),
            new FinanceFormulas.XNPVFlow(new DateTime(2022, 4, 10), 40),
            new FinanceFormulas.XNPVFlow(new DateTime(2022, 5, 10), 40),
            new FinanceFormulas.XNPVFlow(new DateTime(2022, 6, 10), 40),
            new FinanceFormulas.XNPVFlow(new DateTime(2022, 7, 10), 40)
        };
        double val = FinanceFormulas.XNPV(0.12, l, new DateTime(2021, 3, 28)) * Math.Pow(1.12, (DateTime.Today - new DateTime(2021, 3, 28)).TotalDays / 365);

Here is the result it gives me: 9648.299

Any ideas?

The finance formula looks like this:

public static class FinanceFormulas
{
    public static double XNPV(decimal[] receipts, DateTime[] dates, double dRate, DateTime issueDate, decimal cf/*, int x*/)
    {
        double sum = 0;

        for (int i = 0; i < dates.Length; i++)
        {
            TimeSpan ts = dates[i].Subtract(issueDate);
            sum += (double)receipts[i] / Math.Pow((1 + dRate / (double)cf), ((ts.TotalDays / 365) * (double)cf));
        }
        return sum;
    }

    // XNPV(B1,B12:K12,B11:K11)*(1+B1)^((C1-B11)/365)
    // XNPV(12.00, new[] { new xnpvFlow(new DateTime(2021, 3, 28), 8000) /*, ... */ }, new DateTime(2021, 3, 28))
    // XNPV * (1 + RATE) ^ ((TODAY - STARTDATE) / 365)
    public static double XNPV(double rate, List<XNPVFlow> Cashflows, DateTime? StartDate = null)
    {
        if (Cashflows == null || Cashflows.Count == 0) return 0;
        if (StartDate == null)
        {
            StartDate = (from XNPVFlow flow in Cashflows select flow.FlowDate).Min();
        }
        double _xnpv = 0;
        foreach (XNPVFlow flow in Cashflows)
        {
            _xnpv += (double)flow.FlowAmount / Math.Pow((1 + rate), (double)(flow.FlowDate - (DateTime)StartDate).Days / 365);
        }
        return _xnpv;
    }
    public class XNPVFlow
    {
        public XNPVFlow(DateTime _FlowDate, decimal _flowAmount)
        {
            FlowAmount = _flowAmount;
            FlowDate = _FlowDate;
        }
        public DateTime FlowDate { get; }
        public decimal FlowAmount { get; }
    }

}

}

1 Answers

There look to be at least two issues - the first is that the initial $8,000 cash flow should be negative (note that in the Excel sheet it's displayed as (8,000.00) - the parentheses indicating it is a negative number). The second is that the Excel sheet only has cash flows up to and including 2022-03-10, whereas your C# version includes cash flows for April - July.

With the above two issues fixed, I get a result of -8871.29 which looks to be what you want (albeit negative, though perhaps the Excel sheet shows that too but is formatted as (8,871.29)?).

Other less significant issues - you use .TotalDays in the days calculation in the first code block, but .Days in the XNPV(...) method, the former returns a double and will include partial days, whereas the latter is an int and represents whole days.

Related