Воссоздайте функцию Excel RATE, используя метод Ньютона

Я работаю над преобразованием ипотечного калькулятора в PHP, но мне не обязательно нужно решение PHP. Я ищу логику, необходимую для воспроизведения функции Excel RATE. Я нашел решение, которое использует деление пополам, и если станет хуже, я использую его.

Я знаю, что кто-то в мире межсетей знает о такой функции, поэтому я хотел бы получить простой ответ, а не создавать решение с нуля.

Рекомендации:

Спасибо


php function excel financial
person Exit    schedule 07.07.2010    source источник

Ответы (4)


arrow_upward
9
arrow_downward

Реализация функции MS Excel RATE() с использованием метода секущих (конечно-разностная аппроксимация метода Ньютона), взятого из PHPExcel:

define('FINANCIAL_MAX_ITERATIONS', 128);
define('FINANCIAL_PRECISION', 1.0e-08);


function RATE($nper, $pmt, $pv, $fv = 0.0, $type = 0, $guess = 0.1) {

    $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;
}   //  function RATE()
person Mark Baker    schedule 07.07.2010
comment
Совершенно идеально! Просто помните, что люди должны подключить либо несколько операторов DEFINE для FINANCIAL_PRECISION и FINANCIAL_MAX_ITERATIONS, либо заменить их этими статическими значениями 0,0000001 и 20 соответственно. Я нашел эти значения на веб-сайте MS, указанном выше. - person Exit; 08.07.2010
comment
У этого метода есть проблемы со сходимостью, см. периоды"> stackoverflow.com/questions/14031208/ - person sled; 25.09.2017
comment
@Exit - Тогда, возможно, вы захотите предложить лучшее решение, обмен знаниями принесет пользу всем нам. - person Mark Baker; 25.09.2017
comment
@Выход - извините - person Mark Baker; 26.09.2017

arrow_upward
0
arrow_downward

Я попытался использовать приведенный выше код, но результаты просто не такие, как в Excel (или в электронной таблице Google).

Я не знаю, нужно ли вам еще реализовать эту функцию, но в любом случае я посмотрел, как был построен этот алгоритм, и хотя я не смог получить доступ к исходному коду excel (или листу google), я обнаружил, что это не так. простой расчет. Подробнее об этой математике можно прочитать здесь:

https://brownmath.com/bsci/loan.htm#Eq8

Функция в PHP может быть примерно такой:

function rate($nprest, $vlrparc, $vp, $guess = 0.25) {
    $maxit = 100;
    $precision = 14;
    $guess = round($guess,$precision);
    for ($i=0 ; $i<$maxit ; $i++) {
        $divdnd = $vlrparc - ( $vlrparc * (pow(1 + $guess , -$nprest)) ) - ($vp * $guess);
        $divisor = $nprest * $vlrparc * pow(1 + $guess , (-$nprest - 1)) - $vp;
        $newguess = $guess - ( $divdnd / $divisor );
        $newguess = round($newguess, $precision);
        if ($newguess == $guess) {
            return $newguess;
        } else {
            $guess = $newguess;
        }
    }
    return null;
}
person chuckedw    schedule 26.10.2017

arrow_upward
0
arrow_downward

Для Laravel используйте ту же функцию, но вы удаляете определение

define('FINANCIAL_MAX_ITERATIONS', 128);
define('FINANCIAL_PRECISION', 1.0e-08);

и Financial_max_iterations = 20; -> тот же эксель

Код:

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;
}

это сработало для меня

person David Martin Del    schedule 03.06.2019

arrow_upward
0
arrow_downward

TL;DR: вот версия SQL Server. Это не работает для некоторых значений, и приведенный выше PHP-код, вероятно, не работает для тех же значений.

ДЛИННЫЙ ОТВЕТ: мне нужна была функция RATE для SQL Server. Используя приведенный выше ответ PHPExcel и используя https://charlottecredittechnology.blogspot.com/2013/05/sql-2008-excel-like-rate-function-part.html Я написал скалярную функцию SQL Server:

ALTER function [dbo].[Rate](
  @nper integer, @pmt float, @pv float, @fv float, @type bit = 0, @guess float = 0.1
) returns numeric(38,10) as
/*
Calculate the effective interest rate of a sequence of regular payments.
*/
begin    
  declare @returns numeric(38,10) = 0;
  if @type is null set @type = 0;

  declare @i integer;
  declare @rate float = @guess;
  declare @FINANCIAL_MAX_ITERATIONS integer = 100;
  declare @FINANCIAL_PRECISION float = 0.0000001;
  declare @y float, @y0 float, @y1 float, @f float, @x0 float, @x1 float;

  set @rate = @guess;
  if Abs(@rate) < @FINANCIAL_PRECISION
  begin
    set @f = 0;
    set @y = @pv * (1+@nper*@rate) + @pmt * (1+@rate*@type) * @nper + @fv;
  end
  else
  begin
    set @f = Exp(@nper * Log(1+@rate));
    set @y = @pv * @f + @pmt * (1/@rate + @type) * (@f-1) + @fv;
  end;
  set @y0 = @pv + @pmt * @nper + @fv;
  set @y1 = @pv * @f + @pmt * (1/@rate + @type) * (@f-1) + @fv;

  -- Newton secant method.
  set @i = 0;
  set @x0 = 0;
  set @x1 = @rate;
  while Abs(@y0-@y1) > @FINANCIAL_PRECISION and @i < @FINANCIAL_MAX_ITERATIONS
  begin
    set @rate = (@y1 * @x0 - @y0 * @x1) / (@y1-@y0);
    set @x0 = @x1;
    set @x1 = @rate;
    if Abs(@rate) < @FINANCIAL_PRECISION
    begin
      set @y = @pv * (1+@nper*@rate) + @pmt * (1+@rate*@type) * @nper + @fv;
    end
    else
    begin
      set @f = Exp(@nper * Log(1+@rate));
      set @y = @pv * @f + @pmt * (1/@rate + @type) * (@f-1) + @fv;
    end;
    set @y0 = @y1;
    set @y1 = @y;
    set @i = @i + 1;
  end;
  return Convert(numeric(38,10), @rate);
end;

К сожалению, это не всегда работает. Вот результаты некоторых тестов, которые я собрал и проверил с помощью Excel:

-- (1) OK
select dbo.RATE(4*12, -200, 8000, 0, default, default) * 12  -- SQL formula
0.0924 (9.24%)                                               -- SQL result
=RATE(4*12, -200, 8000, 0) * 12                              -- Excel formula
9.24%                                                        -- Excel result

-- (2) OK
select dbo.RATE(12, -1000, 12000, 0, default, default) * 12  -- SQL formula
0 (0%)                                                       -- SQL result
=RATE(12, -1000, 12000, 0) * 12                              -- Excel formula
0%                                                           -- Excel result

-- (3) OK
select dbo.RATE(30, -400, 4000, 0, 1, default)    -- SQL formula
0.10496 (10.496%)                                 -- SQL result
=RATE(30, -400, 4000, 0, 1)                       -- Excel formula
10.4964%                                          -- Excel result

-- (4) OK
select dbo.RATE(120, 28.1, -2400, 0, default, default)  -- SQL formula
0.0059905810 (0.599%)                                   -- SQL result
=RATE(120, 28.1, -2400, 0)                              -- Excel formula
0.5991%                                                 -- Excel result

-- (5) OK
select dbo.RATE(10, -1000, 10000, -10000, default, default)  -- SQL formula
0.1 (10%)                                                    -- SQL result
=RATE(10, -1000, 10000, -10000)                              -- Excel formula
10%                                                          -- Excel result

-- (6) WRONG ANSWER (unless you set @guess to 0.01)
select dbo.RATE(475, -1022.93, 272779.21, 0, default, default)  -- SQL formula
0                                                               -- SQL result
=RATE(475, -1022.93, 272779.21, 0, 0)                           -- Excel formula
0.2716%                                                         -- Excel result

-- (7) ERROR
select dbo.RATE(252, -29002.85, 2500000, 0, default, default)  -- SQL formula
invalid floating point operation                               -- SQL result
=RATE(252, -29002.85, 2500000, 0)                              -- Excel formula
1.0833%                                                        -- Excel result

-- (8) OK
select dbo.RATE(24, -46.14, 1000, 0, default, default)    -- SQL formula
0.0083244385 (0.83244%)                                   -- SQL result
=RATE(24, -46.14, 1000, 0)                                -- Excel formula
0.8324%                                                   -- Excel result

Тесты (7) и (8) были взяты из RATE Function из EXCEL в Swift дает разные результаты и ищет ответ, используя метод Ньютона-Рафсона.

person Ubercoder    schedule 01.01.2020