PHP Excel RATE年均递增付款（pmt）

碰巧的是，这种付款设置会带来一个很好的 原始公式的泛化。

用参数的标准含义，、、、、、 除了我们接受一个数字数组， 给出速率的等式是：$nper$pmt$pv$fv$typeguess$pmt$nper$rate

• 如果$type == 0

$pv * (1 + $rate)**$nper + // present value after $nper
  $pmt[0] * (1 + $rate)**($nper-1) + // 1st payment, after $nper-1
  $pmt[1] * (1 + $rate)**($nper-2) + // 2nd payment, after $nper-2 
  // ................................\
  $pmt[n-2] * (1 + $rate)**1 + //  payment n-1, after 1
  $pmt[n-1] + // 2nd payment, after 0
     $fv // final value
     === 0

• 如果 ，第一笔付款是立即支付的，因此 每次付款都会应用 +1 秒：$type == 1$rate

$pv * (1 + $rate)**$nper + // present value after $nper
  $pmt[0] * (1 + $rate)**$nper + // 1st payment, after $nper
  $pmt[1] * (1 + $rate)**($nper-2) + // 2nd payment, after $nper-1 
  // ................................\
  $pmt[n-2] * (1 + $rate)**2 + //  payment n-1, after 2
  $pmt[n-1] * (1 + $rate)**1 + // 2nd payment, after 1
     $fv // final value
     === 0

现在，正如问题所设定的那样，付款是 给出者 ， 其中是第一笔付款，是付款率，两者都作为参数给出。$pmt[$i] = $pmt0 * (1 + $rate_pmt)**$i$pmt0$$rate_pmt

这样，公式简化为：

$pv * (1 + $rate)**$nper +
  (1 + $rate*$type)*((1+$rate)**$nper - (1+$rate_pmt)**$nper)/($rate-$rate_pmt)+
   $fv 
   === 0

这个不错的结果用于下面的函数中。然而 可以看出，这些款项相当脆弱;一个可以设置 例如，通过四舍五入付款来消除它们。因此，我也 选择了一种更务实的解决方案，尽管效率较低， 只需在代码中计算总和，而不是使用数学 结果。这是在函数中实现的。二 函数具有相同的签名，并且应该（并且确实）给出 相同的结果。RATE_VP1RATE_VP

/**
 * RATE_VP
 *
 * The variable payment version of excel's RATE
 *
 * @param    float    $nper       The total number of payment periods
 * @param    float    $rate_pmt   The rate by which each payment increases
 *                                wrt the previous one (percent)
 * @param    float    $pmt0       The value of the first payment
 * @param    float    $pv         The present value (see RATE)  
 * @param    float    $fv         The future value (see RATE)
 * @param    integer  $type       The number 0 or 1 and indicates 
 *                                when payments are due.
 * @param    float    $guess      Initial guess of the result
 *                                
 * @return    float
 */
function RATE_VP($nper, $rate_pmt, $pmt0, $pv, $fv = 0.0, $type = 0, $guess = 0.1) {
    // computing the sums in code
    $financial_max_iterations = 20;
    $financial_precision = 0.00000008;
    
    $pmts = array_fill(0, $nper, $pmt0);
    for($i = 1; $i < $nper; $i++){
        $pmts[$i] = $pmts[$i-1] * (1+$rate_pmt);
    }
    $y0 = $pv + array_sum($pmts) + $fv;
    
    $guess_strategy = [$guess, 2*$guess, $guess/2, 5*$guess, $guess/5, $guess];
    foreach($guess_strategy as $rate){
      $f = (abs($rate) < $financial_precision) ? 1 + $rate*$nper : exp($nper * log(1 + $rate));
      $y = $f * $pv;
      $fact = $type == 0 ? 1 : 1 + $rate;
      for($j = $nper - 1; $j >= 0; $j--){
          $y += $pmts[$j] * $fact;
          $fact *= 1 + $rate;
      }
      $y += $fv;
      $rate1 = - $y0 * $rate / ($y - $y0);
      if($rate1 > -1){
        break;
      }
    }
    
    $y1 = $y;
    
    // find root by secant method
    $i = $x0 = 0.0;
    $x1 = $rate;
    while ((abs($y0 - $y1) > $financial_precision) and ($i < $financial_max_iterations)) {
        $rate = ($y1 * $x0 - $y0 * $x1) / ($y1 - $y0);
        $x0 = $x1;
        $x1 = $rate;

        $f = abs($rate) < $financial_precision ? 1 + $rate*$nper : exp($nper * log(1 + $rate));
        $y = $f * $pv;
        $fact = $type == 0 ? 1 : 1 + $rate;
        for($j = $nper - 1; $j >= 0; $j--){
            $y += $pmts[$j] * $fact;
            $fact *= 1 + $rate;
        }
        $y += $fv;
        
        $y0 = $y1;
        $y1 = $y;
        ++$i;
    }
    if(abs($y) > $financial_precision){
      throw new ErrorException("RATE_VP diverges", 0);
    }
    return $rate;
} 

和

function RATE_VP1($nper, $rate_pmt, $pmt0, $pv, $fv = 0.0, $type = 0, $guess = 0.1) {
    // using mathematical summation
    $financial_max_iterations = 20;
    $financial_precision = 0.00000008;
    
    $f_pmt = (abs($rate_pmt) < $financial_precision) ? 1 + $rate_pmt*$nper : exp($nper * log(1 + $rate_pmt));
    if(abs($rate_pmt) < $financial_precision){
        $y0 = $pv + $pmt0 *  $nper + $fv;
    }
    else{
        $y0 = $pv + $pmt0 * ($f_pmt-1)/$rate_pmt  + $fv;
    }

    $rate = $guess;    
    $guess_strategy = [$guess, 2*$guess, $guess/2, 5*$guess, $guess/5, $guess];
    foreach($guess_strategy as $rate){
      if (abs($rate) < $financial_precision && abs($rate_pmt) < $financial_precision){
          $y = $pv * (1 + $rate*$nper) + (1 + $rate*$type)*($rate-$rate_pmt)*($nper-1) + $fv; 
      }
      else{
          $f = (abs($rate) < $financial_precision) ? 1 + $rate*$nper : exp($nper * log(1 + $rate));
          if (abs($rate - $rate_pmt) < $financial_precision){
              $y = $pv * $f + $pmt0 * (1 + $rate * $type) * $f/(1+$rate) *$nper + $fv; 
          }
          else{
              $y = $pv * $f + $pmt0 * (1 + $rate * $type) * ($f - $f_pmt)/($rate - $rate_pmt) + $fv;
          }
      }
      $rate1 = - $y0 * $rate / ($y - $y0);
      if($rate1 > -1){
        break;
      }
    }
    
    $y1 = $y;
    
    // find root by secant method
    $i  = $x0 = 0.0;
    $x1 = $rate;
    while ((abs($y0 - $y1) > $financial_precision) and ($i < $financial_max_iterations)) {
        $rate = ($y1 * $x0 - $y0 * $x1) / ($y1 - $y0);
        $x0 = $x1;
        $x1 = $rate;

        if (abs($rate) < $financial_precision && abs($rate_pmt) < $financial_precision){
            $y = $pv * (1 + $rate*$nper) + (1 + $rate*$type)*($rate-$rate_pmt)*($nper-1) + $fv; 
        }
        else{
            $f = (abs($rate) < $financial_precision) ? 1 + $rate*$nper : exp($nper * log(1 + $rate));
            $y = $pv * $f + $pmt0 * (1 + $rate * $type) * ($f - $f_pmt)/($rate - $rate_pmt) + $fv;
        }

        $y0 = $y1;
        $y1 = $y;
        ++$i;
    }
    if(abs($y) > $financial_precision){
      throw new ErrorException("RATE_VP1 diverges", 0);
    }
    return $rate;
} 

OP 中的示例：

RATE_VP(20, 0.1, -1200, 80000)*100

或

RATE_VP1(20, 0.1, -1200, 80000)*100

我使用了与原始函数相同的完全相同的模式， 尽管有一些改进（例如，避免代码重复）是 想得到的。RATE

Excel的功能可用于检查结果， 这是 Google 表格版本， 除了 的模型不包括 -future 值， 也不是 - 在期初付款，所以那些应该有 默认值为零。IRRIRRfvtype=1

此外，出于验证目的，我介绍了详细的打印 通过函数在此PHP沙箱中计算的结果。rate_detailed

在更新中：我将代码和phpsandbox链接更改为

• 添加对找到解决方案的失败检测，方法是抛出ErrorException
• 添加“猜测策略”，即参数的一组变体，以避免在迭代的第一步导致失败的棘手或边缘情况。$guess
• 更正了 中初始猜测的一些公式。RATE_VP1