Calculating rolling XNPV in Python

Question

I need to calculate XNPV of cash flows at different future dates. Is there any function to do this in numpy, pandas or plain python?

Consider a dataframe like so:

import pandas as pd


df = pd.DataFrame({'ptf_id': [1,1,1,1,1],
                'date': pd.date_range("2022-06-05", periods=5, freq="M"), 
                'cf': [10000,12000, 8000,7000,11000], 
                'rate': [0.12,0.12,0.12,0.12,0.12]})

df = df.append(pd.DataFrame(({'ptf_id': [2,2,2,2,2],
                'date': pd.date_range("2022-07-11", periods=5, freq="M"), 
                'cf': [15000,12000, 10000,8000,7000], 
                'rate': [0.15,0.15,0.15,0.15,0.15]})))

ptf_id  date        cf      rate
1       2022-06-30  10000   0.12
1       2022-07-31  12000   0.12
1       2022-08-31  8000    0.12
1       2022-09-30  7000    0.12
1       2022-10-31  11000   0.12
2       2022-07-31  15000   0.15
2       2022-08-31  12000   0.15
2       2022-09-30  10000   0.15
2       2022-10-31  8000    0.15
2       2022-11-30  7000    0.15

Is it possible to calculate the XNPV by ptf_id at different future dates, such that the result looks like this:

ptf_id  date        cf      rate    xnpv 
1       2022-06-30  10000   0.12    37123
1       2022-07-31  12000   0.12    25482
1       2022-08-31  8000    0.12    17729
1       2022-09-30  7000    0.12    10895
1       2022-10-31  11000   0.12    0
2       2022-07-31  15000   0.15    36031
2       2022-08-31  12000   0.15    24461
2       2022-09-30  10000   0.15    14744
2       2022-10-31  8000    0.15    6920
2       2022-11-30  7000    0.15    0

Answer 1

I don't know that there is something that can handle something more complicated than the simple np.npv().

The NPV values are not the same (I'm not entirely sure what your calculations were to get those results as they do not match Excel's XNPV() either), but here is how you could do it:

def npv(date, data):
    data = data.copy()
    data["date_diff"] = (data["date"] - date) / np.timedelta64(1, 'D')
    data["discounted"] = data["cf"] / (1+data["rate"])**(data["date_diff"]/365)
    return data["discounted"].sum()

df["xnpv"] = df.apply(lambda x: npv(x["date"], df), axis=1)
df
#   ptf_id       date     cf  rate           xnpv
#0       1 2022-06-30  10000  0.12   97552.187808
#1       1 2022-07-31  12000  0.12   98610.115354
#2       1 2022-08-31   8000  0.12   99679.641288
#3       1 2022-09-30   7000  0.12  100725.830611
#4       1 2022-10-31  11000  0.12  101818.555715
#0       2 2022-07-31  15000  0.15   98610.115354
#1       2 2022-08-31  12000  0.15   99679.641288
#2       2 2022-09-30  10000  0.15  100725.830611
#3       2 2022-10-31   8000  0.15  101818.555715
#4       2 2022-11-30   7000  0.15  102887.440568

Note that this uses different discount rates for each cashflow, which is not something Excel's XNPV does.

Answer 2

I was able to reach the solution using the function below (Calculating XIRR in Python) and a for-loop statement. However, I am wondering whether there is a more efficient solution.

def xnpv(rate, cashflows):
        """
        Calculate the net present value of a series of cashflows at irregular intervals.

        Arguments
        ---------
        * rate: the discount rate to be applied to the cash flows
        * cashflows: a list object in which each element is a tuple 
                     of the form (date, amount), where date is a python 
                     datetime.date object and amount is an integer or 
                     floating point number. 
                     Cash outflows (investments) are represented with negative amounts, 
                     and cash inflows (returns) are positive amounts.

        Returns
        -------
        * returns a single value which is the NPV of the given cash flows.

        Notes
        ---------------
        * The Net Present Value is the sum of each of cash flows discounted back 
        to the date of the first cash flow. The discounted value of a given cash flow 
        is A/(1+r)**(t-t0), where A is the amount, r is the discout rate, 
        and (t-t0) is the time in years from the date of the first cash flow 
        in the series (t0) to the date of the cash flow being added to the sum (t).  
        * This function is equivalent to the Microsoft Excel function of the same name. 

        """

        chron_order = sorted(cashflows, key = lambda x: x[0])
        t0 = chron_order[0][0] #t0 is the date of the first cash flow

        return sum([cf/(1+rate)**((t-t0).days/365.0) for (t,cf) in chron_order])


tmp_xnpv = 0

for ptf_id, date in zip(df['ptf_id'], df['date']):
    
    mask = (df['ptf_id']==ptf_id) & (df['date']>=date)
    
    try:
        tmp_xnpv = xnpv(
            df.loc[mask,'rate'].iloc[0],
             df.loc[mask,['date', 'cf']].values.tolist()) \
            - df.loc[mask, 'cf'].iloc[0]
    except:
        tmp_xnpv = 0
    
    df.loc[mask,'xnpv'] = tmp_xnpv