Search code examples
phpexcelexcel-formulaphpexcelworksheet-function

Excel and PHPExcel return different results from CUMIPMT function

I am calculating CUMIPMT (cumulative interest between periods) and am getting different outputs from MS Excel and PHPExcel.

In MS Excel, if I enter: 

=CUMIPMT(0.015,31.57,2000,1,31.57,0)

the output is -524.94.

Whereas using PHPExcel the output from the same input values is -515.60.

Here is my PHP code:

require_once 'Classes/PHPExcel/Calculation/Financial.php';
$objPHPExcel = new PHPExcel_Calculation_Financial();
$interest_paid = $objPHPExcel->CUMIPMT('0.015','31.57','2000','1','31.57','0');
Solution

  • According to the Excel 2003 documentation, Nper, start_period, end_period, and type are truncated to integers. - this is what the PHP version is doing. In reality, Excel (2013 at least) does not truncate, though the documentation there is slightly different: Payment periods are numbered beginning with 1. - as for 2003 - but no mention of truncation.

    Please try 31 for periods in both.

    Effectively, "different Excel versions".