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; }
}
}
}