用 Python (pandas) 计算可变现金流 IRR
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/46203735/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Calculating Variable Cash-flow IRR in Python (pandas)
提问by Greg Lewis
I have a DataFrame of unpredictable cashflows and unpredictable period lengths, and I need to generate a backward-looking IRR.
我有一个不可预测的现金流和不可预测的周期长度的 DataFrame，我需要生成一个向后看的 IRR。
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).
使用求解器在 Excel 中执行此操作非常简单，想知道是否有在 Python 中实现它的好方法。（我想我可以利用 openpyxl 来让求解器在 python 中的 excel 中工作，但这感觉不必要地麻烦）。
The problem is pretty straightforward:
问题很简单：
NPV of Cash Flow = ((cash_flow)/(1+IRR)^years_ago)
现金流的 NPV = ((cash_flow)/(1+IRR)^years_ago)
GOAL: Find IRR where SUM(NPV) = 0
目标：找到 SUM(NPV) = 0 的 IRR
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
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 计算器（例如 numpy.irr）假设严格的期限截止（每 3 个月、1 年等），这是行不通的。另一种选择似乎是迭代路线，我不断地猜测、检查和迭代，但感觉这是解决这个问题的错误方法。理想情况下，我正在寻找可以做到这一点的东西：
irr = calc_irr((cash_flow1,years_ago1),(cash_flow2,years_ago2),etc)
EDIT: 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.
编辑：这是我运行问题的代码。我有一个事务列表，我选择按 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)
where df_tran (which temp_df is based on) looks like:
其中 df_tran （其中 temp_df 基于）如下所示：
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 looks identical to df_tran, except it only holds transactions for a single id.
temp_df 看起来与 df_tran 相同，除了它只保存单个 id 的事务。
回答by Brad Solomon
You can use scipy.optimize.fsolve:
您可以使用scipy.optimize.fsolve：
Return the roots of the (non-linear) equations defined by func(x) = 0 given a starting estimate.
在给定起始估计的情况下，返回由 func(x) = 0 定义的（非线性）方程的根。
First define the function that will be the funcparameter to fsolve. This is NPV as a result of your IRR, cash flows, and years. (Vectorize with NumPy.)
首先定义将作为func参数的函数fsolve。这是您的内部收益率、现金流量和年份的结果的 NPV。（使用 NumPy 进行矢量化。）
import numpy as np
def npv(irr, cfs, yrs):
return np.sum(cfs / (1. + irr) ** yrs)
An example:
一个例子：
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
Now to use fsolve:
现在使用fsolve：
from scipy.optimize import fsolve
def irr(cfs, yrs, x0):
return np.asscalar(fsolve(npv, x0=x0, args=(cfs, yrs)))
Your IRR is:
你的内部收益率是：
print(irr(cfs=cash_flow, yrs=years, x0=0.10))
0.12129650313214262
And you can confirm that this gets you to a 0 NPV:
您可以确认这使您的 NPV 为 0：
res = irr(cfs=cash_flow, yrs=years, x0=0.10)
print(np.allclose(npv(res, cash_flow, years), 0.))
True
All code together:
所有代码一起：
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
要使其与您的Pandas示例兼容，只需使用
cash_flow = df.cash_flow.values
years = df.years_ago.values
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:
更新：您问题中的值似乎有点荒谬（如果存在，您的 IRR 将是一个天文数字），但您的运行方式如下：
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
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.
第二次更新：您的代码中有几个小错别字，您的实际 $ 和时间流向无意义的 IRR 起作用，但这是您想要做的，如下所示。例如，请注意您有一个 id 和一个负交易，负无限 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
