Formula values do not match

Question

I’m trying to do the Excel formula VF(future value) in PHP, but the values are not matching, IE, in Excel is a value for the column and in the calculation of PHP come out other values but almost identical values, far from R $ 1,50 to R $ 3,00.

index php.

<link rel="stylesheet" type="text/css" href="bootstrap.min.css">
<div class="container">
  <div class="row">
    <div class="col-md-8 col-md-offset-2">
      <table class="table table-bordered table-sac">
        <style type="text/css">
          .table-sac thead tr th,
          tbody tr td {
            text-align: center;
          }
        </style>
        <thead>
          <tr>
            <th>Mes</th>
            <th>Se for investir</th>
            <th>Juros no finan.</th>
            <th>Amortização</th>
            <th>VT</th>
            <th>Valor futuro</th>
          </tr>
        </thead>
        <tbody>
          <?php require_once('calcs.php'); ?>
          <?php for($i = 0; $i <= $mes; $i++): ?>
          <tr>
            <td>
              <?php echo $i; ?>
            </td>
            <td>
              <?php echo number_format($sac['G'][$i], 2, ',', '.'); ?>
            </td>
            <td>
              <?php echo number_format($sac['H'][$i], 2, ',', '.'); ?>
            </td>
            <td>
              <?php echo number_format($sac['I'][$i], 2, ',', '.'); ?>
            </td>
            <td>
              <?php echo number_format($sac['J'][$i], 2, ',', '.'); ?>
            </td>
            <td>
              <?php echo $sac['K']['vf'][$i]; ?>
            </td>
          </tr>
          <?php endfor; ?>
        </tbody>
      </table>
    </div>
  </div>
  <!-- row -->
</div>
<!-- container -->

calcs.php

<?php
// F = P. (1+i)° + M.[(1+i)° - 1]/i
function vf($taxa,$n,$pgto,$vlr_presente=0, $iterador)
{
    if($iterador > 0):
        // $first  = bcmul($GLOBALS['ul_vf'],   $taxa);
        // $second = bcadd($GLOBALS['ul_pgto'], $first);
        // $vf     = bcadd($GLOBALS['ul_vf'],   $second);
        // $vf = ($GLOBALS['ul_vf']) + ($GLOBALS['ul_vf'] * $taxa + $GLOBALS['ul_pgto']);
        $first  = bcadd(1, $taxa);
        $second = bcpow($first, $n);
        $third  = bcmul($GLOBALS['ul_vf'], $second); 

        $fourth = bcsub($second, 1);
        $fifth  = bcdiv($fourth, $taxa);
        $sixth  = bcmul($fifth, $GLOBALS['ul_pgto']);

        $seventh = bcadd($third, $sixth);
        $vf = $seventh;

        $GLOBALS['ul_vf']   = $vf;
        $GLOBALS['ul_pgto'] = $pgto;
    else:
        $vf = $vlr_presente;
        $GLOBALS['ul_vf']   = $vf;
        $GLOBALS['ul_pgto'] = $pgto;
    endif;
    return $vf;
}




$vlr_imovel   = 144000; //var_dump($vlr_imovel);
$vlr_aluguel  = 400.00; //var_dump($vlr_imovel);
$vlr_entrada  = 43200.00; //var_dump($vlr_imovel);
$vlr_encargos = 500.00; //var_dump($vlr_imovel);
$mes = 360;

$taxa = (0.54/100);
$se_for_investir  = $vlr_encargos + $vlr_entrada;

for($i = 0; $i <= $mes; $i++):

    $juros_no_finan    = (($vlr_imovel - $vlr_entrada) - ((($vlr_imovel - $vlr_entrada)/$mes)*$i))*0.007;

    $amortizacao      = ($vlr_imovel - $vlr_entrada)/$mes;

    $vlr_total        = $amortizacao + $juros_no_finan;

    //VALOR FUTURO -------------------------
    // Como: FV = PV x ( 1 + i ) ^ n
    $n            = 1; 
    $pgto         = $vlr_total - $vlr_aluguel;
    $vlr_presente = $vlr_entrada + $vlr_encargos;
    $tipo         = 0;
    $one = 0.96;
    $two = 0.42;
    $taxa2        = 0.54/100;

    $vf           = vf($taxa2, $n, $pgto, $vlr_presente, $i);
    //VALOR FUTURO -------------------------


    $sac['G'][] = $se_for_investir;
    $sac['H'][] = $juros_no_finan;
    $sac['I'][] = $amortizacao;
    $sac['J'][] = $vlr_total;


    $sac['K']['pgto'][] = $pgto;
    $sac['K']['vf'][]   = $vf;

endfor;

Explanation of the code.

1 - I loop in the months, 360 months in all.

2 - In the first loop(0) the function FV is called, FV receives the parameters: ($taxa, $parcelas, $pagamento, $vlr_presente, $iterador_atual) These brackets are from the current loop.

3 - In FV(function) we have conditionals that check whether the $iterador(which was passed by the parameter) is > 1, example:

if(iterador > 1):
    //aqui eu faço a formula com as variaveis globais no else abaixo
    //e insiro variaveis globais para ser aproveitadas no proximo loop
else:
    //aqui é o primeiro loop, ou seja a primeira linha do excel(o mês 0)
    //variavel global desse valor passado(será aproveitada no proximo loop)
    //variavel global do pagamento passado(será aproveitada no proximo loop)
endif;
return $valorFuturo;

4 - Now, in Calcs.php we have the future value that will be passed to the view;

---

The Error here is that the values are not identical, almost, but not identical. I thought it could be the floats, which were disturbing but it is not.

I put the excel file on google drive.

Answer 1

Given the following formula for calculating compound interest given an initial value and a monthly investment value:

Source: https://money.stackexchange.com/a/26187

It would be something like this:

function valorFuturo($valorInicial, $taxaJuros, $periodo, $investimentoMensal)
{
    $jurosComposto = (1 + $taxaJuros) ** $periodo;
    $v1 = $valorInicial * $jurosComposto;
    $v2 = $investimentoMensal * ( ( $jurosComposto - 1 ) / $taxaJuros ) * ( 1 + $taxaJuros );
    return number_format($v1 + $v2, 2, ",", ".");
}

echo "R$ ".valorFuturo(2500, .00333333, 60, 100)."<br/>";

For lack of example, I tested using the same values:

valorInicial: 2500
taxaJuros: 0.04 anual que mensal vira 0.003333333
periodo: 5 anos ou 6 meses
investimentoMensal: 100

The amount matched what expected: R$ 9704,49

See working on ideone

Detail !important!

For calculations of very large values of financial nature, always choose to use as many houses as possible after the comma, see in your example uses: 0.54/100 which is equal to 0.0054000000000

If you extend the boxes after the comma in excel you will see that the actual rate used for the calculation is:

Think big numbers, just like you did

100.000 * 0.0054 = 540
100.000 * 0.00543169 = 543.169

A difference of +3 units, and the higher the values, the greater the divergence in the final value.

Answer 2

Solution

https://money.stackexchange.com/a/16517
The future value of the payment is made, and then the future value passed (previous line, or previous iterator).

Future value of payment

<ul>
<li>$pmt: Pagamento</li>
<li>$i: Juros</li>
<li>$n: Número de períodos</li>
</ul>
Valor futuro da última linha(valor futuro passado, linha anterior).
<ul>
<li>$vl: Valor futuro</li>
<li>$i: Juros</li>
<li>$n: Número de períodos</li>
</ul>

Now the sum of these two formulas is made. You can see in the code the functioning.

function valorFuturo($vl, $i, $n, $pmt, $iterator)
{
  if($iterator > 0):
  $first  = $GLOBALS['ul_vf'] * (1+$i)**$n;
  $second = $GLOBALS['ul_pgto'] * ((1+$i)**$n-1)/$i;
  $vf     = $first + $second;

  $GLOBALS['ul_vf']   = $vf;
  $GLOBALS['ul_pgto'] = $pmt;
 else:
  $vf                 = $vl;
  $GLOBALS['ul_vf']   = $vf;
  $GLOBALS['ul_pgto'] = $pmt;
 endif;
 return $vf;
}




$vlr_imovel   = 144000; 
$vlr_aluguel  = 400.00; 
$vlr_entrada  = 43200.00; 
$vlr_encargos = 500.00;
$mes = 360;

$taxa = (0.54/100);
$se_for_investir  = $vlr_encargos + $vlr_entrada;

for($i = 0; $i <= $mes; $i++):

  $juros_no_finan    = (($vlr_imovel - $vlr_entrada) - ((($vlr_imovel - $vlr_entrada)/$mes)*$i))*0.007;

  $amortizacao      = ($vlr_imovel - $vlr_entrada)/$mes;

  $vlr_total        = $amortizacao + $juros_no_finan;

  //VALOR FUTURO -------------------------
  // Como: FV = PV x ( 1 + i ) ^ n
  $n            = 1; 
  $pgto         = $vlr_total - $vlr_aluguel;
  $vlr_presente = $vlr_entrada + $vlr_encargos;
  $tipo         = 0;
  $taxa2        = 0.54316901573592300/100;

  $vf           = valorFuturo($vlr_presente, $taxa2, $n, $pgto, $i);
  //VALOR FUTURO -------------------------


  $sac['G'][] = $se_for_investir;
  $sac['H'][] = $juros_no_finan;
  $sac['I'][] = $amortizacao;
  $sac['J'][] = $vlr_total;


  $sac['K']['pgto'][] = $pgto;
  $sac['K']['vf'][]   = $vf;

endfor;