I'm using PHP 7.2. I'm in the process of converting an Excel spreadsheet into our financial analysis application. For one of the calculations, I'm literally copying the text in the cell, pasting it into my PHP application, then substituting the cell references with variables. There are no built-in Excel functions being used--it's just a straight formula. And I'm not physically changing the structure of the formula--it's a literal copy/paste of what is in the spreadsheet. This is the formula in Excel:
=(A38*(1-(1+(G38/U38))^-N38)/(G38/U38))/((1+(G38/U38))^(((AB38-$I$5)/(365/U38))-1))
This is how it's converted in my application:
$x = ($payment_amount * (1 - (1 + ($discount_rate /
$payments_per_year))^-$payments_in_series) /
($discount_rate / $payments_per_year)) /
((1 + ($discount_rate / $payments_per_year))^((($days_between_dates) /
(365 / $payments_per_year)) - 1));
There is one minor difference between mine and the one in Excel and that's (AB38-$I$5). In Excel, this calculates the number of days between two dates. In PHP, I used DateTime to find the difference in days for the two dates and just substituted it with $days_between_dates. I have verified that PHP matches what Excel calculates.
Let's assume these values:
$payment_amount = 30000;
$discount_rate = .08;
$payments_in_series = 20;
$payments_per_year = 1;
$days_between_dates = 398;
In Excel, I get the following value for this calculation: 292502.06
In PHP, I get the following value for this calculation: -7500000. When I use var_dump to examine the result, it identifies it as a float.
I plugged this function into WolframAlpha and get the same result from Excel.
I tried this same calculation in Ideone and get the same result as the PHP calculation.
Where I'm messing up? I'm all out of ideas and don't even know where else to troubleshoot. Thanks!
