I am working on a project for my thesis, which has to do with the capitalization of Research & Development (R&D) expenses for a data set of companies that I have.
For those who are not familiar with financial terminology, I am trying to accumulate the values of each year's R&D expenses with the following ones by decaying its value (or "depreciating" it) every time period.
I was able to apply the following code to get the gist of the operation:
df['rd_capital'] = [(df['r&d_exp'].iloc[:i] * (1 - df['dep_rate'].iloc[:i]*np.arange(i)[::-1])).sum() for i in range(1,len(df)+1)]
However, there is a major flaw with this method, which is that it continues to take away the depreciation rate once the value has reached zero, therefore going into negative territory.
For example if we have Apple's R&D expenses for 5 years at a constant depreciation rate of 20%, the code above gives me the following result:
year r&d_exp dep_rate r&d_capital
0 1999 10 0.2 10
1 2000 8 0.2 16
2 2001 12 0.2 24.4
3 2002 7 0.2 25.4
4 2003 15 0.2 33
5 2004 8 0.2 30.6
6 2005 11 0.2 29.6
However, the value for the year 2005 is incorrect as it should be 31.6!
If it was not clear, r&d_capital is retrieved the following way:
2000 = 10*(1-0.2) + 8
2001 = 10*(1-0.4) + 8*(1-0.2) + 12
2002 = 10*(1-0.6) + 8*(1-0.4) + 12*(1-0.2) + 7
2003 = 10*(1-0.8) + 8*(1-0.6) + 12*(1-0.4) + 7*(1-0.2) + 15
the key problem comes here as the code above does the following:
2004 = 10*(1-1) + 8*(1-0.8) + 12*(1-0.6) + 7*(1-0.4) + 15*(1-0.2) + 8
2005 = 10*(1-1.2) + 8*(1-1) + 12*(1-0.8) + 7*(1-0.6) + 15*(1-0.4) + 8*(0.2) + 11
Instead it should discard the values once the value reaches zero, just like this:
2004 = 8*(1-0.8) + 12*(1-0.6) + 7*(1-0.4) + 15*(1-0.2) + 8
2005 = 12*(1-0.8) + 7*(1-0.6) + 15*(1-0.4) + 8*(0.2) + 11
Thank you in advance for any help that you will give, really appreciate it :)
A possible way would be to compute the residual part for each investment. The assumption is that there a finite and known number of years after which any investment is fully depreciated. Here I will use 6 years (5 would be enough but it demonstrates how to avoid negative depreciations):
# cumulated depreciation rates:
cum_rate = pd.DataFrame(index = df.index)
for i in range(2, 7):
cum_rate['cum_rate' + str(i)] = df['dep_rate'].rolling(i).sum().shift(1 - i)
cum_rate['cum_rate1'] = df['dep_rate']
cum_rate[cum_rate > 1] = 1 # avoid negative rates
# residual values
resid = pd.DataFrame(index = df.index)
for i in range(1, 7):
resid['r' + str(i)] = (df['r&d_exp'] * (1 - cum_rate['cum_rate' + str(i)])
).shift(i)
# compute the capital
df['r&d_capital'] = resid.apply('sum', axis=1) + df['r&d_exp']
It gives as expected:
year r&d_exp dep_rate r&d_capital
0 1999 10 0.2 10.0
1 2000 8 0.2 16.0
2 2001 12 0.2 24.4
3 2002 7 0.2 25.4
4 2003 15 0.2 33.0
5 2004 8 0.2 30.6
6 2005 11 0.2 31.6