I am using the PHP Version of Excels RATE function in order to calculate the interest rate of an annuity.
This is working out as expected.
My question now is if I can somehow work with a variable, anually increasing $pmt value.
Example:
Year 1: Payment($pmt): $1,200, anual increase 10%, term remaining: 20years
Year 2: Payment($pmt): $1,320 ($1,200 + 10%), term remaining: 19years
Year 3: Payment($pmt): $1,452 ($1,320 + 10%), term remaining: 18years
etc...
I can't use the total amount of payments and then divide by the amount of years in order to get a averaged $pmt value, since this will mess with the interest calculations of the RATE() function and produce inaccurate results
So ideally I could do something like: RATE(60,10,-1200,0,80000), where 10 is the anual increase of the 1200 payment.
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;
}
As it happens, this setting of payments results in a nice generalization of the original formula.
With the standard meaning
of the parameters, $nper, $pmt, $pv, $fv, $type, guess,
except we take $pmt to be an array of $nper numbers,
the equation that gives the rate $rate is:
$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, the first payment is immediate so
each payment gets +1 $rates applied:$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
Now, as the question sets it, the payments are
given by $pmt[$i] = $pmt0 * (1 + $rate_pmt)**$i,
where $pmt0$ is the first payment and $rate_pmt
is the rate of payment, both given as arguments.
With this, the formulae reduce to:
$pv * (1 + $rate)**$nper +
(1 + $rate*$type)*((1+$rate)**$nper - (1+$rate_pmt)**$nper)/($rate-$rate_pmt)+
$fv
=== 0
This nice result is used in the function RATE_VP1 below. However,
one can see that these sums are rather fragile; one may set
them off for instance, by rounding off payments. Thus, I also
opted to a more pragmatic solution, albeit less efficient, that
simply computes the sums in code, rather than use the mathematical
result. That is implemented in the function RATE_VP. The two
functions have the same signature and should (and do) give the
same results.
/**
* 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;
}
and
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;
}
The example in the OP:
RATE_VP(20, 0.1, -1200, 80000)*100
or
RATE_VP1(20, 0.1, -1200, 80000)*100
I used the same exact pattern used in the original RATE function,
although some improvements (e.g., to avoid code duplication) are
conceivable.
Excel's IRR function can be used to check the results,
here's a google sheets version,
except IRR's model doesn't include the fv-future value,
nor type=1 - payment at the start of the period so those should have
the default zero values.
Also for verification purposes I introduced a detailed print of
the resulting calculation in this PHP sandbox
through the function rate_detailed.
IN UPDATE: I changed the code and phpsandbox link to
ErrorException$guess parameter to avoid tricky or edge cases that lead to failure at the very first step of the iteration.RATE_VP1.