How to copy Excel FV function in PHP?

Question

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

FV( interest_rate, number_payments, payment, PV, Type )

I've been working on this for 2 hours and I'm definitely missing something. Essentially, I need to copy this functionality into an equivalent PHP function, taking all the parameters mentioned above.

Any help would be very appreciated.

Answer 1

Slightly modify 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);

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

}