问题描述
我有一个不可预测的现金流量和不可预测的期间长度的DataFrame，并且我需要生成一个后向IRR.
I have a DataFrame of unpredictable cashflows and unpredictable period lengths, and I need to generate a backward-looking IRR.
使用求解器在Excel中进行操作非常简单，想知道是否存在在Python中实现它的好方法. (我认为我可以利用openpyxl来使求解器在python中以excel的形式工作，但这感觉不必要地麻烦.)
Doing it in Excel is pretty straightforward using the solver, wondering if there's a good way to pull it off in Python. (I think I could leverage openpyxl to get solver to work in excel from python, but that feels unnecessarily cumbersome).
问题很简单:
目标:找到IRR，其中SUM(NPV)= 0
GOAL: Find IRR where SUM(NPV) = 0
我的数据框看起来像这样:
My dataframe looks something like this:
cash_flow |years_ago
-----------------------
-3.60837e+06 |4.09167
31462 |4.09167
1.05956e+06 |3.63333
-1.32718e+06 |3.28056
-4.46554e+06 |3.03889
似乎其他IRR计算器(例如numpy.irr)都假定严格的截止期限(每3个月，1年等)，这是行不通的.另一个选择似乎是迭代路线，在该路线中，我不断猜测，检查和迭代，但这似乎是解决此问题的错误方法.理想情况下，我正在寻找可以做到这一点的东西:
It seems as though other IRR calculators (such as numpy.irr) assume strict period cutoffs (every 3 months, 1 year, etc), which won't work. The other option seems to be the iterative route, where I continually guess, check, and iterate, but that feels like the wrong way to tackle this. Ideally, I'm looking for something that would do this:
irr = calc_irr((cash_flow1,years_ago1),(cash_flow2,years_ago2),etc)
这是我从中运行问题的代码.我有一个交易列表，我选择按ID创建临时表.
Here is the code I'm running the problem from. I have a list of transactions, and I've chosen to create temporary tables by id.
for id in df_tran.id.unique():
temp_df = df_tran[df_tran.id == id]
cash_flow = temp_df.cash_flows.values
years = temp_df.years.values
print(id, cash_flow)
print(years)
#irr_calc = irr(cfs=cash_flow, yrs=years,x0=0.100000)
#print(sid, irr_calc)
其中df_tran(temp_df所基于的)看起来像:
where df_tran (which temp_df is based on) looks like:
cash_flow |years |id
0 -3.60837e+06 4.09167 978237
1 31462 4.09167 978237
4 1.05956e+06 3.63333 978237
6 -1.32718e+06 3.28056 978237
8 -4.46554e+06 3.03889 978237
10 -3.16163e+06 2.81944 978237
12 -5.07288e+06 2.58889 978237
14 268833 2.46667 978237
17 -4.74703e+06 1.79167 978237
20 -964987 1.40556 978237
22 -142920 1.12222 978237
24 163894 0.947222 978237
26 -2.2064e+06 0.655556 978237
27 1.23804e+06 0.566667 978237
29 180655 0.430556 978237
30 -85297 0.336111 978237
34 -2.3529e+07 0.758333 1329483
36 21935 0.636111 1329483
38 -3.55067e+06 0.366667 1329483
41 -4e+06 4.14167 1365051
temp_df看起来与df_tran相同，只不过它仅保存单个ID的事务.
temp_df looks identical to df_tran, except it only holds transactions for a single id.
推荐答案
您可以使用 scipy.optimize.fsolve :
You can use scipy.optimize.fsolve:
首先定义将成为fsolve的func参数的函数.由于您的内部收益率，现金流量和年限，这是净现值. (使用NumPy矢量化.)
First define the function that will be the func parameter to fsolve. This is NPV as a result of your IRR, cash flows, and years. (Vectorize with NumPy.)
import numpy as np
def npv(irr, cfs, yrs):
return np.sum(cfs / (1. + irr) ** yrs)
一个例子:
cash_flow = np.array([-2., .5, .75, 1.35])
years = np.arange(4)
# A guess
print(npv(irr=0.10, cfs=cash_flow, yrs=years))
0.0886551465064
现在可以使用fsolve:
from scipy.optimize import fsolve
def irr(cfs, yrs, x0):
return np.asscalar(fsolve(npv, x0=x0, args=(cfs, yrs)))
您的内部收益率是:
print(irr(cfs=cash_flow, yrs=years, x0=0.10))
0.12129650313214262
您可以确认这使您的NPV为0:
And you can confirm that this gets you to a 0 NPV:
res = irr(cfs=cash_flow, yrs=years, x0=0.10)
print(np.allclose(npv(res, cash_flow, years), 0.))
True
所有代码加在一起:
import numpy as np
from scipy.optimize import fsolve
def npv(irr, cfs, yrs):
return np.sum(cfs / (1. + irr) ** yrs)
def irr(cfs, yrs, x0, **kwargs):
return np.asscalar(fsolve(npv, x0=x0, args=(cfs, yrs), **kwargs))
要使其与您的熊猫示例兼容，只需使用
To make this compatible with your pandas example, just use
cash_flow = df.cash_flow.values
years = df.years_ago.values
更新:您的问题中的值似乎有点荒谬(如果您的IRR甚至存在，您的IRR将会是一个天文数字)，但是这是您的运行方式:
Update: the values in your question seem a bit nonsensical (your IRR is going to be some astronomical number if it even exists) but here is how you'd run:
cash_flow = np.array([-3.60837e+06, 31462, 1.05956e+06, -1.32718e+06, -4.46554e+06])
years_ago = np.array([4.09167, 4.09167, 3.63333, 3.28056, 3.03889])
print(irr(cash_flow, years_ago, x0=0.10, maxfev=10000))
1.3977721900669127e+82
第二次更新:您的代码中有一些次要的错别字，您的$实际流量和计时结果对无意义的IRR起作用，但是下面是您想要做的事情.例如，请注意，您有一个ID，其中有一个单笔负交易，即一个负无限的IRR.
Second update: there are a couple minor typos in your code, and your actual flows of $ and timing work out to nonsensical IRRs, but here's what you're looking to do, below. For instance, notice you have one id with one single negative transaction, a negatively infinite IRR.
for i, df in df_tran.groupby('id'):
cash_flow = df.cash_flow.values
years = df.years.values
print('id:', i, 'irr:', irr(cash_flow, years, x0=0.))
id: 978237 irr: 347.8254979851405
id: 1329483 irr: 3.2921314448062817e+114
id: 1365051 irr: 1.0444951674872467e+25
这篇关于用Python(pandas)计算可变现金流量IRR的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！