Power Query中Excel RRI函数的等效实现方法

Power Query本身没有内置Excel的RRI或RATE函数，但完全可以通过两种方式实现相同的计算效果：

一、实现RRI函数计算 #

RRI函数的核心逻辑是计算投资的复合年增长率，公式为：
RRI(nper, pv, fv) = (终值/现值)^(1/期数) - 1

操作步骤： #

1. 打开Power Query编辑器，选中你的目标数据表
2. 点击顶部菜单栏的「添加列」→「自定义列」
3. 在弹出的公式输入框中，根据你的实际列名输入对应公式，比如你的数据列是PV（现值）、Nper（期数）、FV（终值），就输入：

= Number.Power([FV]/[PV], 1/[Nper]) - 1

4. 点击「确定」，即可生成包含RRI计算结果的新列

二、实现RATE函数计算 #

RATE函数用于计算年金的每期利率，Excel中是通过迭代求解实现的，Power Query中可以分两种场景处理：

场景1：在Excel的Power Query环境中（直接调用Excel函数） #

如果是在Excel里使用Power Query，可以直接调用Excel内置的RATE函数，步骤如下：

1. 同样进入「添加列」→「自定义列」
2. 输入公式，参数顺序和Excel RATE函数一致：RATE(nper, pmt, pv, [fv], [type], [guess])，替换为你的实际列名，比如：

= Excel.WorksheetFunction.Rate([Nper], [Pmt], [PV], [FV], 0, 0.1)

说明：参数type填0代表期末付款，1代表期初付款；guess是初始猜测利率，默认填0.1（10%）即可

场景2：在Power BI或独立Power Query环境中（M语言迭代实现） #

如果无法调用Excel函数，可以用M语言写一个自定义的RATE函数，通过牛顿迭代法求解利率：

1. 在Power Query编辑器中，点击「主页」→「新建源」→「空白查询」
2. 将查询重命名为RATE，然后在公式栏输入以下代码：

(nper as number, pmt as number, pv as number, optional fv as number, optional type as number, optional guess as number) as nullable number =>
let
    // 设置默认参数
    fv = if fv = null then 0 else fv,
    type = if type = null then 0 else type,
    guess = if guess = null then 0.1 else guess,
    // 迭代精度和最大次数
    Tolerance = 0.0000001,
    MaxIterations = 100,
    // 迭代逻辑
    Iterate = (currentGuess as number, iteration as number) as nullable number =>
        let
            term1 = if currentGuess = 0 then nper*pmt*(1+currentGuess*type) else pmt*(1+currentGuess*type)/currentGuess*(1-Number.Power(1+currentGuess, -nper)),
            equation = pv + term1 + fv/Number.Power(1+currentGuess, nper),
            derivativeTerm1 = if currentGuess = 0 then -nper*(nper+1)*pmt*(1+currentGuess*type)/2 else -pmt*(1+currentGuess*type)*(Number.Power(1+currentGuess, -nper-1)*nper*(1+currentGuess) + 1 - Number.Power(1+currentGuess, -nper))/Number.Power(currentGuess, 2),
            derivative = derivativeTerm1 - fv*nper/Number.Power(1+currentGuess, nper+1),
            newGuess = currentGuess - equation/derivative,
            difference = Number.Abs(newGuess - currentGuess)
        in
            if iteration >= MaxIterations or difference < Tolerance then newGuess else Iterate(newGuess, iteration + 1)
in
    Iterate(guess, 0)

3. 返回你的数据表，进入「添加列」→「自定义列」，调用这个自定义函数：

= RATE([Nper], [Pmt], [PV], [FV], [Type], [Guess])

4. 点击「确定」即可生成计算结果列

内容的提问来源于stack exchange，提问作者Moshe