Differences Between: [Versions 400 and 401] [Versions 400 and 402] [Versions 400 and 403]
1 <?php 2 3 namespace PhpOffice\PhpSpreadsheet\Calculation\Financial\CashFlow\Variable; 4 5 use PhpOffice\PhpSpreadsheet\Calculation\Functions; 6 7 class Periodic 8 { 9 const FINANCIAL_MAX_ITERATIONS = 128; 10 11 const FINANCIAL_PRECISION = 1.0e-08; 12 13 /** 14 * IRR. 15 * 16 * Returns the internal rate of return for a series of cash flows represented by the numbers in values. 17 * These cash flows do not have to be even, as they would be for an annuity. However, the cash flows must occur 18 * at regular intervals, such as monthly or annually. The internal rate of return is the interest rate received 19 * for an investment consisting of payments (negative values) and income (positive values) that occur at regular 20 * periods. 21 * 22 * Excel Function: 23 * IRR(values[,guess]) 24 * 25 * @param mixed $values An array or a reference to cells that contain numbers for which you want 26 * to calculate the internal rate of return. 27 * Values must contain at least one positive value and one negative value to 28 * calculate the internal rate of return. 29 * @param mixed $guess A number that you guess is close to the result of IRR 30 * 31 * @return float|string 32 */ 33 public static function rate($values, $guess = 0.1) 34 { 35 if (!is_array($values)) { 36 return Functions::VALUE(); 37 } 38 $values = Functions::flattenArray($values); 39 $guess = Functions::flattenSingleValue($guess); 40 41 // create an initial range, with a root somewhere between 0 and guess 42 $x1 = 0.0; 43 $x2 = $guess; 44 $f1 = self::presentValue($x1, $values); 45 $f2 = self::presentValue($x2, $values); 46 for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) { 47 if (($f1 * $f2) < 0.0) { 48 break; 49 } 50 if (abs($f1) < abs($f2)) { 51 $f1 = self::presentValue($x1 += 1.6 * ($x1 - $x2), $values); 52 } else { 53 $f2 = self::presentValue($x2 += 1.6 * ($x2 - $x1), $values); 54 } 55 } 56 if (($f1 * $f2) > 0.0) { 57 return Functions::VALUE(); 58 } 59 60 $f = self::presentValue($x1, $values); 61 if ($f < 0.0) { 62 $rtb = $x1; 63 $dx = $x2 - $x1; 64 } else { 65 $rtb = $x2; 66 $dx = $x1 - $x2; 67 } 68 69 for ($i = 0; $i < self::FINANCIAL_MAX_ITERATIONS; ++$i) { 70 $dx *= 0.5; 71 $x_mid = $rtb + $dx; 72 $f_mid = self::presentValue($x_mid, $values); 73 if ($f_mid <= 0.0) { 74 $rtb = $x_mid; 75 } 76 if ((abs($f_mid) < self::FINANCIAL_PRECISION) || (abs($dx) < self::FINANCIAL_PRECISION)) { 77 return $x_mid; 78 } 79 } 80 81 return Functions::VALUE(); 82 } 83 84 /** 85 * MIRR. 86 * 87 * Returns the modified internal rate of return for a series of periodic cash flows. MIRR considers both 88 * the cost of the investment and the interest received on reinvestment of cash. 89 * 90 * Excel Function: 91 * MIRR(values,finance_rate, reinvestment_rate) 92 * 93 * @param mixed $values An array or a reference to cells that contain a series of payments and 94 * income occurring at regular intervals. 95 * Payments are negative value, income is positive values. 96 * @param mixed $financeRate The interest rate you pay on the money used in the cash flows 97 * @param mixed $reinvestmentRate The interest rate you receive on the cash flows as you reinvest them 98 * 99 * @return float|string Result, or a string containing an error 100 */ 101 public static function modifiedRate($values, $financeRate, $reinvestmentRate) 102 { 103 if (!is_array($values)) { 104 return Functions::VALUE(); 105 } 106 $values = Functions::flattenArray($values); 107 $financeRate = Functions::flattenSingleValue($financeRate); 108 $reinvestmentRate = Functions::flattenSingleValue($reinvestmentRate); 109 $n = count($values); 110 111 $rr = 1.0 + $reinvestmentRate; 112 $fr = 1.0 + $financeRate; 113 114 $npvPos = $npvNeg = 0.0; 115 foreach ($values as $i => $v) { 116 if ($v >= 0) { 117 $npvPos += $v / $rr ** $i; 118 } else { 119 $npvNeg += $v / $fr ** $i; 120 } 121 } 122 123 if (($npvNeg === 0.0) || ($npvPos === 0.0) || ($reinvestmentRate <= -1.0)) { 124 return Functions::VALUE(); 125 } 126 127 $mirr = ((-$npvPos * $rr ** $n) 128 / ($npvNeg * ($rr))) ** (1.0 / ($n - 1)) - 1.0; 129 130 return is_finite($mirr) ? $mirr : Functions::VALUE(); 131 } 132 133 /** 134 * NPV. 135 * 136 * Returns the Net Present Value of a cash flow series given a discount rate. 137 * 138 * @param mixed $rate 139 * 140 * @return float 141 */ 142 public static function presentValue($rate, ...$args) 143 { 144 $returnValue = 0; 145 146 $rate = Functions::flattenSingleValue($rate); 147 $aArgs = Functions::flattenArray($args); 148 149 // Calculate 150 $countArgs = count($aArgs); 151 for ($i = 1; $i <= $countArgs; ++$i) { 152 // Is it a numeric value? 153 if (is_numeric($aArgs[$i - 1])) { 154 $returnValue += $aArgs[$i - 1] / (1 + $rate) ** $i; 155 } 156 } 157 158 return $returnValue; 159 } 160 }
title
Description
Body
title
Description
Body
title
Description
Body
title
Body