How can I replicate the Excel FV function in PHP?

I'm converting some rather complex calculations from an Excel spreadsheet, to PHP. I'm stuck on the conversion of Excel's FV function, which is defined thusly:

FV( interest_rate, number_payments, payment, PV, Type )
开发者_如何学C

I've been working on this for 2 hours now, and there has to be something I'm missing. Essentially, I need to replicate this functionality into an equivalent PHP function, taking all of the aforementioned arguments.

Any help would be greatly appreciated.

---

Slightly modified from the PHPExcel function library:

/**
 * FV
 *
 * Returns the Future Value of a cash flow with constant payments and interest rate (annuities).
 *
 * @param   float   $rate   Interest rate per period
 * @param   int     $nper   Number of periods
 * @param   float   $pmt    Periodic payment (annuity)
 * @param   float   $pv     Present Value
 * @param   int     $type   Payment type: 0 = at the end of each period, 1 = at the beginning of each period
 * @return  float
 */
function FV($rate = 0, $nper = 0, $pmt = 0, $pv = 0, $type = 0) {

    // Validate parameters
    if ($type != 0 && $type != 1) {
        return False;
    }

    // Calculate
    if ($rate != 0.0) {
        return -$pv * pow(1 + $rate, $nper) - $pmt * (1 + $rate * $type) * (pow(1 + $rate, $nper) - 1) / $rate;
    } else {
        return -$pv - $pmt * $nper;
    }
}   //  function FV()


echo FV(0.0149562574418, 4, 43.875, -250);

returns 85.818510876629

// Unit test

class ExcelTest extends \PHPUnit_Framework_TestCase
{

    public function test_it_calculates_fv_value()
    {
        $test_data = [
            [ 0.005,          10, -200,  -500,    1,    2581.4033740601 ],
            [ 0.01,           12, -1000, null,    null, 12682.503013197 ],
            [ 0.009166666667, 35, -2000, null,    1,    82846.246372418 ],
            [ 0.005,          12, -100,  -1000,   1,    2301.4018303409 ],
            [ 0.004166666667, 60, -1000, null,    null, 68006.082841536 ],
            [ 0.025,          16, -2000, 0,       1,    39729.460894166 ],
            [ 0.0,            12, -100,  -100,    null, 1300            ]
        ];

        $test_case_id = 0;
        foreach($test_data as $test_case) {
            $test_case_id++;
            list($rate, $nper, $pmt, $pv, $type, $expected_result) = $test_case;
            $this->assertEquals($expected_result, Excel::FV($rate, $nper, $pmt, $pv, $type), "Test case $test_case_id failed", 0.0000001);
        }
    }

}

---

function fv($r,$n,$p,$pv=0)
{
   $sum = $pv;
   for ( $i=0;$i<$n;$i++ )
   {
       $sum += $sum*$r + $p;
   }
   return $sum;
}

echo fv(0.1,4,10,100);

The extension of the Type parameter is left as an excercise to the reader.

---

This is the modified function of the MathPHP library.

<?php
/**
 * Future value for a loan or annuity with compound interest.
 *
 * Same as the =FV() function in most spreadsheet software.
 *
 * The basic future-value formula derivation:
 * https://en.wikipedia.org/wiki/Future_value
 *
 *                   PMT*((1+r)ᴺ - 1)
 * FV = -PV*(1+r)ᴺ - ----------------
 *                          r
 *
 * The (1+r*when) factor adjusts the payment to the beginning or end
 * of the period. In the common case of a payment at the end of a period,
 * the factor is 1 and reduces to the formula above. Setting when=1 computes
 * an "annuity due" with an immediate payment.
 *
 * Examples:
 * The future value in 5 years on a 30-year fixed mortgage note of $265000
 * at 3.5% interest paid at the end of every month. This is how much loan
 * principle would be outstanding:
 *   fv(0.035/12, 5*12, 1189.97, -265000, false)
 *
 * The present_value is negative indicating money borrowed for the mortgage,
 * whereas payment is positive, indicating money that will be paid to the
 * mortgage.
 *
 * @param float $rate
 * @param int $periods
 * @param float $payment
 * @param float $present_value
 * @param bool $beginning adjust the payment to the beginning or end of the period
 *
 * @return float
 */
function fv(float $rate, int $periods, float $payment, float $present_value, bool $beginning = false)
{
    $when = $beginning ? 1 : 0;

    if ($rate == 0) {
        $fv = -($present_value + ($payment * $periods));
        return $fv;
    }

    $initial = 1 + ($rate * $when);
    $compound = pow(1 + $rate, $periods);
    $fv = -(($present_value * $compound) + (($payment * $initial * ($compound - 1)) / $rate));

    return $fv;
}

It worked very well, I hope it helps.

Reference: https://github.com/markrogoyski/math-php/blob/master/src/Finance.php

---

I was facing the similar issue and got help from someone on this post, I hope someone else finds it helpful too

---

I had the same problem and found this really nice function. These two above did not work for my needs...

$R for the rate, $n number of period, $pmt is the amount paid

function PV($R,$n,$pmt,$m=1) {

$Z = 1 / (1 + ($R/$m));

return ($pmt * $Z * (1 - pow($Z,$n)))/(1 - $Z);

}

echo PV(0,00333333, 180, 1221);

Don't forget, if want to know for example how much you will get after 15 years if you pay in 1221€ per month with an annual rate of 4%, you will have to divide per 12 the rate and put 180 months.