PHP Excel RATE with annually increasing payment(pmt)

xdyibdwo  于 2023-08-02  发布在  PHP
关注(0)|答案(1)|浏览(108)

我正在使用Excels RATE function的PHP版本来计算年金的利率。
一切都如预期的那样。
我现在的问题是,我是否可以以某种方式处理一个变量,每年增加$pmt值。
示例如下:
第1年:付款($pmt):$1,200,年增长10%,剩余期限:二十年
第2年:付款($pmt):$1,320($1,200 + 10%),剩余期限:十九年
第3年:付款($pmt):$1,452($1,320 + 10%),剩余期限:18岁
等等
我不能用总付款额除以年数来得到平均$pmt值,因为这会扰乱RATE()函数的利息计算,并产生不准确的结果
理想情况下,我可以做这样的事情:利率(60,10,-1200,0,80000),其中10是1200支付的年增加。

function RATE($nper, $pmt, $pv, $fv = 0.0, $type = 0, $guess = 0.1) {
    $financial_max_iterations = 20;
    $financial_precision = 0.00000008;

    $rate = $guess;
    if (abs($rate) < $financial_precision) {
        $y = $pv * (1 + $nper * $rate) + $pmt * (1 + $rate * $type) * $nper + $fv;
    } else {
        $f = exp($nper * log(1 + $rate));
        $y = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv;
    }
    $y0 = $pv + $pmt * $nper + $fv;
    $y1 = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv;

    // find root by secant method
    $i  = $x0 = 0.0;
    $x1 = $rate;
    while ((abs($y0 - $y1) > $financial_precision) && ($i < $financial_max_iterations)) {
        $rate = ($y1 * $x0 - $y0 * $x1) / ($y1 - $y0);
        $x0 = $x1;
        $x1 = $rate;

        if (abs($rate) < $financial_precision) {
            $y = $pv * (1 + $nper * $rate) + $pmt * (1 + $rate * $type) * $nper + $fv;
        } else {
            $f = exp($nper * log(1 + $rate));
            $y = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv;
        }

        $y0 = $y1;
        $y1 = $y;
        ++$i;
    }
    return $rate;
}

字符串

ddrv8njm

ddrv8njm1#

碰巧的是,这种支付设置导致了原始公式的良好推广。
对于参数standard meaning$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

字符串

  • 如果$type == 1,则第一笔付款是即时的,因此每次付款将应用+1 $rate s:
$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_VP1中使用了这个很好的结果。然而,人们可以看到,这些总和是相当脆弱的;例如,可以通过四舍五入来抵消这些费用。因此,我还选择了一种更实用的解决方案,尽管效率较低,它只是在代码中计算总和,而不是使用数学结果。这在函数RATE_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);
    }
    
    $rate = $guess;
    $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 = $pv + array_sum($pmts) + $fv;
    $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;
    }
    
    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));
    $rate = $guess;
    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 * $nper + $fv; 
        }
        else{
            $y = $pv * $f + $pmt0 * (1 + $rate * $type) * ($f - $f_pmt)/($rate - $rate_pmt) + $fv;
        }
    }
    if(abs($rate_pmt) < $financial_precision){
        $y0 = $pv + $pmt0 *  $nper + $fv;
    }
    else{
        $y0 = $pv + $pmt0 * ($f_pmt-1)/$rate_pmt * $nper + $fv;
    }
    $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;
    }
    
    return $rate;
}


OP中的示例:

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


或者是

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


我使用了与原始RATE函数中使用的完全相同的模式,尽管可以进行一些改进(例如,避免代码重复)。
Excel的IRR函数可以用来检查结果,这里有一个google sheets version,除了IRR的模型不包括fv-未来值,也不包括type=1-期初付款,所以它们应该有默认的零值。
同样为了验证的目的,我通过函数rate_detailedPHP sandbox中引入了结果计算的详细打印。

相关问题