Source for file Financial.php
Documentation is available at Financial.php
* Copyright (c) 2006 - 2011 PHPExcel * This library is free software; you can redistribute it and/or * modify it under the terms of the GNU Lesser General Public * License as published by the Free Software Foundation; either * version 2.1 of the License, or (at your option) any later version. * This library is distributed in the hope that it will be useful, * but WITHOUT ANY WARRANTY; without even the implied warranty of * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU * Lesser General Public License for more details. * You should have received a copy of the GNU Lesser General Public * License along with this library; if not, write to the Free Software * Foundation, Inc., 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA * @package PHPExcel_Calculation * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel) * @license http://www.gnu.org/licenses/old-licenses/lgpl-2.1.txt LGPL * @version 1.7.6, 2011-02-27 /** PHPExcel root directory */ define('PHPEXCEL_ROOT', dirname(__FILE__ ) . '/../../'); require (PHPEXCEL_ROOT . 'PHPExcel/Autoloader.php');/** FINANCIAL_MAX_ITERATIONS */ define('FINANCIAL_MAX_ITERATIONS', 128); /** FINANCIAL_PRECISION */ define('FINANCIAL_PRECISION', 1.0e-08); * PHPExcel_Calculation_Financial * @package PHPExcel_Calculation * @copyright Copyright (c) 2006 - 2011 PHPExcel (http://www.codeplex.com/PHPExcel) private static function _lastDayOfMonth($testDate) { return ($date->format('d') == 1); } // function _lastDayOfMonth() private static function _firstDayOfMonth($testDate) { return ($date->format('d') == 1); } // function _lastDayOfMonth() private static function _coupFirstPeriodDate($settlement, $maturity, $frequency, $next) { $months = 12 / $frequency; $eom = self::_lastDayOfMonth($result); $result->modify('-'. $months. ' months'); $result->modify('+'. $months. ' months'); $result->modify('-1 day'); } // function _coupFirstPeriodDate() private static function _validFrequency($frequency) { if (($frequency == 1) || ($frequency == 2) || ($frequency == 4)) { (($frequency == 6) || ($frequency == 12))) { } // function _validFrequency() private static function _daysPerYear($year,$basis) { } // function _daysPerYear() private static function _interestAndPrincipal($rate= 0, $per= 0, $nper= 0, $pv= 0, $fv= 0, $type= 0) { $pmt = self::PMT($rate, $nper, $pv, $fv, $type); for ($i = 1; $i<= $per; ++ $i) { $interest = ($type && $i == 1) ? 0 : - $capital * $rate; $principal = $pmt - $interest; return array($interest, $principal); } // function _interestAndPrincipal() * Returns the discount rate for a security. * @param mixed issue The security's issue date. * @param mixed firstinter The security's first interest date. * @param mixed settlement The security's settlement date. * @param float rate The security's annual coupon rate. * @param float par The security's par value. * @param int basis The type of day count to use. * 0 or omitted US (NASD) 30/360 public static function ACCRINT($issue, $firstinter, $settlement, $rate, $par= 1000, $frequency= 1, $basis= 0) { if (($rate <= 0) || ($par <= 0)) { if (!is_numeric($daysBetweenIssueAndSettlement)) { return $daysBetweenIssueAndSettlement; return $par * $rate * $daysBetweenIssueAndSettlement; * Returns the discount rate for a security. * @param mixed issue The security's issue date. * @param mixed settlement The security's settlement date. * @param float rate The security's annual coupon rate. * @param float par The security's par value. * @param int basis The type of day count to use. * 0 or omitted US (NASD) 30/360 public static function ACCRINTM($issue, $settlement, $rate, $par= 1000, $basis= 0) { if (($rate <= 0) || ($par <= 0)) { if (!is_numeric($daysBetweenIssueAndSettlement)) { return $daysBetweenIssueAndSettlement; return $par * $rate * $daysBetweenIssueAndSettlement; public static function AMORDEGRC($cost, $purchased, $firstPeriod, $salvage, $period, $rate, $basis= 0) { } elseif ($fUsePer < 5.0) { } elseif ($fUsePer <= 6.0) { $fRest = $cost - $salvage; for ($n = 0; $n < $period; ++ $n) { $fNRate = round($rate * $cost,0); case 1 : return round($cost * 0.5,0); } // function AMORDEGRC() public static function AMORLINC($cost, $purchased, $firstPeriod, $salvage, $period, $rate, $basis= 0) { $fOneRate = $cost * $rate; $fCostDelta = $cost - $salvage; // Note, quirky variation for leap years on the YEARFRAC for this function $f0Rate = $yearFrac * $rate * $cost; $nNumOfFullPeriods = intval(($cost - $salvage - $f0Rate) / $fOneRate); } elseif ($period <= $nNumOfFullPeriods) { } elseif ($period == ($nNumOfFullPeriods + 1)) { return ($fCostDelta - $fOneRate * $nNumOfFullPeriods - $f0Rate); public static function COUPDAYBS($settlement, $maturity, $frequency, $basis= 0) { if (($settlement > $maturity) || (!self::_validFrequency($frequency)) || (($basis < 0) || ($basis > 4))) { $prev = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, False); } // function COUPDAYBS() public static function COUPDAYS($settlement, $maturity, $frequency, $basis= 0) { if (($settlement > $maturity) || (!self::_validFrequency($frequency)) || (($basis < 0) || ($basis > 4))) { return ($daysPerYear / $frequency); $prev = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, False); $next = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, True); default: // US (NASD) 30/360, Actual/360 or European 30/360 public static function COUPDAYSNC($settlement, $maturity, $frequency, $basis= 0) { if (($settlement > $maturity) || (!self::_validFrequency($frequency)) || (($basis < 0) || ($basis > 4))) { $next = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, True); } // function COUPDAYSNC() public static function COUPNCD($settlement, $maturity, $frequency, $basis= 0) { if (($settlement > $maturity) || (!self::_validFrequency($frequency)) || (($basis < 0) || ($basis > 4))) { return self::_coupFirstPeriodDate($settlement, $maturity, $frequency, True); public static function COUPNUM($settlement, $maturity, $frequency, $basis= 0) { if (($settlement > $maturity) || (!self::_validFrequency($frequency)) || (($basis < 0) || ($basis > 4))) { $settlement = self::_coupFirstPeriodDate($settlement, $maturity, $frequency, True); case 1: // annual payments return ceil($daysBetweenSettlementAndMaturity / 360); return ceil($daysBetweenSettlementAndMaturity / 180); return ceil($daysBetweenSettlementAndMaturity / 90); return ceil($daysBetweenSettlementAndMaturity / 60); return ceil($daysBetweenSettlementAndMaturity / 30); public static function COUPPCD($settlement, $maturity, $frequency, $basis= 0) { if (($settlement > $maturity) || (!self::_validFrequency($frequency)) || (($basis < 0) || ($basis > 4))) { return self::_coupFirstPeriodDate($settlement, $maturity, $frequency, False); * Returns the cumulative interest paid on a loan between start_period and end_period. * @param float $rate Interest rate per period * @param int $nper Number of periods * @param float $pv Present Value * @param int start The first period in the calculation. * Payment periods are numbered beginning with 1. * @param int end The last period in the calculation. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period public static function CUMIPMT($rate, $nper, $pv, $start, $end, $type = 0) { if ($type != 0 && $type != 1) { if ($start < 1 || $start > $end) { for ($per = $start; $per <= $end; ++ $per) { $interest += self::IPMT($rate, $per, $nper, $pv, 0, $type); * Returns the cumulative principal paid on a loan between start_period and end_period. * @param float $rate Interest rate per period * @param int $nper Number of periods * @param float $pv Present Value * @param int start The first period in the calculation. * Payment periods are numbered beginning with 1. * @param int end The last period in the calculation. * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period public static function CUMPRINC($rate, $nper, $pv, $start, $end, $type = 0) { if ($type != 0 && $type != 1) { if ($start < 1 || $start > $end) { for ($per = $start; $per <= $end; ++ $per) { $principal += self::PPMT($rate, $per, $nper, $pv, 0, $type); * Returns the depreciation of an asset for a specified period using the fixed-declining balance method. * This form of depreciation is used if you want to get a higher depreciation value at the beginning of the depreciation * (as opposed to linear depreciation). The depreciation value is reduced with every depreciation period by the * depreciation already deducted from the initial cost. * @param float cost Initial cost of the asset. * @param float salvage Value at the end of the depreciation. (Sometimes called the salvage value of the asset) * @param int life Number of periods over which the asset is depreciated. (Sometimes called the useful life of the asset) * @param int period The period for which you want to calculate the depreciation. Period must use the same units as life. * @param float month Number of months in the first year. If month is omitted, it defaults to 12. public static function DB($cost, $salvage, $life, $period, $month= 12) { } elseif (($cost < 0) || (($salvage / $cost) < 0) || ($life <= 0) || ($period < 1) || ($month < 1)) { // Set Fixed Depreciation Rate $fixedDepreciationRate = 1 - pow(($salvage / $cost), (1 / $life)); $fixedDepreciationRate = round($fixedDepreciationRate, 3); // Loop through each period calculating the depreciation $previousDepreciation = 0; for ($per = 1; $per <= $period; ++ $per) { $depreciation = $cost * $fixedDepreciationRate * $month / 12; } elseif ($per == ($life + 1)) { $depreciation = ($cost - $previousDepreciation) * $fixedDepreciationRate * (12 - $month) / 12; $depreciation = ($cost - $previousDepreciation) * $fixedDepreciationRate; $previousDepreciation += $depreciation; $depreciation = round($depreciation,2); * Returns the depreciation of an asset for a specified period using the double-declining balance method or some other method you specify. * @param float cost Initial cost of the asset. * @param float salvage Value at the end of the depreciation. (Sometimes called the salvage value of the asset) * @param int life Number of periods over which the asset is depreciated. (Sometimes called the useful life of the asset) * @param int period The period for which you want to calculate the depreciation. Period must use the same units as life. * @param float factor The rate at which the balance declines. * If factor is omitted, it is assumed to be 2 (the double-declining balance method). public static function DDB($cost, $salvage, $life, $period, $factor= 2.0) { if (($cost <= 0) || (($salvage / $cost) < 0) || ($life <= 0) || ($period < 1) || ($factor <= 0.0) || ($period > $life)) { // Set Fixed Depreciation Rate $fixedDepreciationRate = 1 - pow(($salvage / $cost), (1 / $life)); $fixedDepreciationRate = round($fixedDepreciationRate, 3); // Loop through each period calculating the depreciation $previousDepreciation = 0; for ($per = 1; $per <= $period; ++ $per) { $depreciation = min( ($cost - $previousDepreciation) * ($factor / $life), ($cost - $salvage - $previousDepreciation) ); $previousDepreciation += $depreciation; $depreciation = round($depreciation,2); * Returns the discount rate for a security. * @param mixed settlement The security's settlement date. * The security settlement date is the date after the issue date when the security is traded to the buyer. * @param mixed maturity The security's maturity date. * The maturity date is the date when the security expires. * @param int price The security's price per $100 face value. * @param int redemption the security's redemption value per $100 face value. * @param int basis The type of day count to use. * 0 or omitted US (NASD) 30/360 public static function DISC($settlement, $maturity, $price, $redemption, $basis= 0) { if (($price <= 0) || ($redemption <= 0)) { if (!is_numeric($daysBetweenSettlementAndMaturity)) { return $daysBetweenSettlementAndMaturity; return ((1 - $price / $redemption) / $daysBetweenSettlementAndMaturity); * Converts a dollar price expressed as an integer part and a fraction part into a dollar price expressed as a decimal number. * Fractional dollar numbers are sometimes used for security prices. * @param float $fractional_dollar Fractional Dollar * @param int $fraction Fraction public static function DOLLARDE($fractional_dollar = Null, $fraction = 0) { if (is_null($fractional_dollar) || $fraction < 0) { $dollars = floor($fractional_dollar); $cents = fmod($fractional_dollar,1); return $dollars + $cents; * Converts a dollar price expressed as a decimal number into a dollar price expressed as a fraction. * Fractional dollar numbers are sometimes used for security prices. * @param float $decimal_dollar Decimal Dollar * @param int $fraction Fraction public static function DOLLARFR($decimal_dollar = Null, $fraction = 0) { if (is_null($decimal_dollar) || $fraction < 0) { $dollars = floor($decimal_dollar); $cents = fmod($decimal_dollar,1); return $dollars + $cents; * Returns the effective interest rate given the nominal rate and the number of compounding payments per year. * @param float $nominal_rate Nominal interest rate * @param int $npery Number of compounding payments per year public static function EFFECT($nominal_rate = 0, $npery = 0) { if ($nominal_rate <= 0 || $npery < 1) { return pow((1 + $nominal_rate / $npery), $npery) - 1; * 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 public static function FV($rate = 0, $nper = 0, $pmt = 0, $pv = 0, $type = 0) { if ($type != 0 && $type != 1) { if (!is_null($rate) && $rate != 0) { return - $pv * pow(1 + $rate, $nper) - $pmt * (1 + $rate * $type) * (pow(1 + $rate, $nper) - 1) / $rate; return - $pv - $pmt * $nper; public static function FVSCHEDULE($principal, $schedule) { foreach($schedule as $n) { } // function FVSCHEDULE() * Returns the interest rate for a fully invested security. * @param mixed settlement The security's settlement date. * The security settlement date is the date after the issue date when the security is traded to the buyer. * @param mixed maturity The security's maturity date. * The maturity date is the date when the security expires. * @param int investment The amount invested in the security. * @param int redemption The amount to be received at maturity. * @param int basis The type of day count to use. * 0 or omitted US (NASD) 30/360 public static function INTRATE($settlement, $maturity, $investment, $redemption, $basis= 0) { if (($investment <= 0) || ($redemption <= 0)) { if (!is_numeric($daysBetweenSettlementAndMaturity)) { return $daysBetweenSettlementAndMaturity; return (($redemption / $investment) - 1) / ($daysBetweenSettlementAndMaturity); * Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate. * @param float $rate Interest rate per period * @param int $per Period for which we want to find the interest * @param int $nper Number of periods * @param float $pv Present Value * @param float $fv Future Value * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period public static function IPMT($rate, $per, $nper, $pv, $fv = 0, $type = 0) { if ($type != 0 && $type != 1) { if ($per <= 0 || $per > $nper) { $interestAndPrincipal = self::_interestAndPrincipal($rate, $per, $nper, $pv, $fv, $type); return $interestAndPrincipal[0]; public static function IRR($values, $guess = 0.1) { // create an initial range, with a root somewhere between 0 and guess $f1 = self::NPV($x1, $values); $f2 = self::NPV($x2, $values); if (($f1 * $f2) < 0.0) break; $f1 = self::NPV($x1 += 1.6 * ($x1 - $x2), $values); $f2 = self::NPV($x2 += 1.6 * ($x2 - $x1), $values); $f = self::NPV($x1, $values); $f_mid = self::NPV($x_mid, $values); if ($f_mid <= 0.0) $rtb = $x_mid; * Returns the interest payment for an investment based on an interest rate and a constant payment schedule. * =ISPMT(interest_rate, period, number_payments, PV) * interest_rate is the interest rate for the investment * period is the period to calculate the interest rate. It must be betweeen 1 and number_payments. * number_payments is the number of payments for the annuity * PV is the loan amount or present value of the payments public static function ISPMT() { $principlePayment = ($principleRemaining * 1.0) / ($numberPeriods * 1.0); for($i= 0; $i <= $period; ++ $i) { $returnValue = $interestRate * $principleRemaining * - 1; $principleRemaining -= $principlePayment; // principle needs to be 0 after the last payment, don't let floating point screw it up if($i == $numberPeriods) { public static function MIRR($values, $finance_rate, $reinvestment_rate) { $rr = 1.0 + $reinvestment_rate; $fr = 1.0 + $finance_rate; $npv_pos = $npv_neg = 0.0; foreach($values as $i => $v) { $npv_pos += $v / pow($rr, $i); $npv_neg += $v / pow($fr, $i); if (($npv_neg == 0) || ($npv_pos == 0) || ($reinvestment_rate <= - 1)) { $mirr = pow((- $npv_pos * pow($rr, $n)) / ($npv_neg * ($rr)), (1.0 / ($n - 1))) - 1.0; * Returns the nominal interest rate given the effective rate and the number of compounding payments per year. * @param float $effect_rate Effective interest rate * @param int $npery Number of compounding payments per year public static function NOMINAL($effect_rate = 0, $npery = 0) { if ($effect_rate <= 0 || $npery < 1) { return $npery * (pow($effect_rate + 1, 1 / $npery) - 1); * Returns the number of periods for a cash flow with constant periodic payments (annuities), and interest rate. * @param float $rate Interest rate per period * @param int $pmt Periodic payment (annuity) * @param float $pv Present Value * @param float $fv Future Value * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period public static function NPER($rate = 0, $pmt = 0, $pv = 0, $fv = 0, $type = 0) { if ($type != 0 && $type != 1) { if (!is_null($rate) && $rate != 0) { if ($pmt == 0 && $pv == 0) { return log(($pmt * (1 + $rate * $type) / $rate - $fv) / ($pv + $pmt * (1 + $rate * $type) / $rate)) / log(1 + $rate); return (- $pv - $fv) / $pmt; * Returns the Net Present Value of a cash flow series given a discount rate. * @param float Discount interest rate * @param array Cash flow series public static function NPV() { // Loop through arguments for ($i = 1; $i <= count($aArgs); ++ $i) { // Is it a numeric value? $returnValue += $aArgs[$i - 1] / pow(1 + $rate, $i); * Returns the constant payment (annuity) for a cash flow with a constant interest rate. * @param float $rate Interest rate per period * @param int $nper Number of periods * @param float $pv Present Value * @param float $fv Future Value * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period public static function PMT($rate = 0, $nper = 0, $pv = 0, $fv = 0, $type = 0) { if ($type != 0 && $type != 1) { if (!is_null($rate) && $rate != 0) { return (- $fv - $pv * pow(1 + $rate, $nper)) / (1 + $rate * $type) / ((pow(1 + $rate, $nper) - 1) / $rate); return (- $pv - $fv) / $nper; * Returns the interest payment for a given period for an investment based on periodic, constant payments and a constant interest rate. * @param float $rate Interest rate per period * @param int $per Period for which we want to find the interest * @param int $nper Number of periods * @param float $pv Present Value * @param float $fv Future Value * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period public static function PPMT($rate, $per, $nper, $pv, $fv = 0, $type = 0) { if ($type != 0 && $type != 1) { if ($per <= 0 || $per > $nper) { $interestAndPrincipal = self::_interestAndPrincipal($rate, $per, $nper, $pv, $fv, $type); return $interestAndPrincipal[1]; public static function PRICE($settlement, $maturity, $rate, $yield, $redemption, $frequency, $basis= 0) { if (($settlement > $maturity) || (!self::_validFrequency($frequency)) || (($basis < 0) || ($basis > 4))) { $dsc = self::COUPDAYSNC($settlement, $maturity, $frequency, $basis); $e = self::COUPDAYS($settlement, $maturity, $frequency, $basis); $n = self::COUPNUM($settlement, $maturity, $frequency, $basis); $a = self::COUPDAYBS($settlement, $maturity, $frequency, $basis); $baseYF = 1.0 + ($yield / $frequency); $rfp = 100 * ($rate / $frequency); $result = $redemption / pow($baseYF, (-- $n + $de)); for($k = 0; $k <= $n; ++ $k) { $result += $rfp / (pow($baseYF, ($k + $de))); $result -= $rfp * ($a / $e); * Returns the price per $100 face value of a discounted security. * @param mixed settlement The security's settlement date. * The security settlement date is the date after the issue date when the security is traded to the buyer. * @param mixed maturity The security's maturity date. * The maturity date is the date when the security expires. * @param int discount The security's discount rate. * @param int redemption The security's redemption value per $100 face value. * @param int basis The type of day count to use. * 0 or omitted US (NASD) 30/360 public static function PRICEDISC($settlement, $maturity, $discount, $redemption, $basis= 0) { if (($discount <= 0) || ($redemption <= 0)) { if (!is_numeric($daysBetweenSettlementAndMaturity)) { return $daysBetweenSettlementAndMaturity; return $redemption * (1 - $discount * $daysBetweenSettlementAndMaturity); } // function PRICEDISC() * Returns the price per $100 face value of a security that pays interest at maturity. * @param mixed settlement The security's settlement date. * The security's settlement date is the date after the issue date when the security is traded to the buyer. * @param mixed maturity The security's maturity date. * The maturity date is the date when the security expires. * @param mixed issue The security's issue date. * @param int rate The security's interest rate at date of issue. * @param int yield The security's annual yield. * @param int basis The type of day count to use. * 0 or omitted US (NASD) 30/360 public static function PRICEMAT($settlement, $maturity, $issue, $rate, $yield, $basis= 0) { if (($rate <= 0) || ($yield <= 0)) { if (!is_numeric($daysBetweenIssueAndSettlement)) { return $daysBetweenIssueAndSettlement; $daysBetweenIssueAndSettlement *= $daysPerYear; return $daysBetweenIssueAndMaturity; $daysBetweenIssueAndMaturity *= $daysPerYear; if (!is_numeric($daysBetweenSettlementAndMaturity)) { return $daysBetweenSettlementAndMaturity; $daysBetweenSettlementAndMaturity *= $daysPerYear; return ((100 + (($daysBetweenIssueAndMaturity / $daysPerYear) * $rate * 100)) / (1 + (($daysBetweenSettlementAndMaturity / $daysPerYear) * $yield)) - (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate * 100)); * Returns the Present 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 $fv Future Value * @param int $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period public static function PV($rate = 0, $nper = 0, $pmt = 0, $fv = 0, $type = 0) { if ($type != 0 && $type != 1) { if (!is_null($rate) && $rate != 0) { return (- $pmt * (1 + $rate * $type) * ((pow(1 + $rate, $nper) - 1) / $rate) - $fv) / pow(1 + $rate, $nper); return - $fv - $pmt * $nper; public static function RATE($nper, $pmt, $pv, $fv = 0.0, $type = 0, $guess = 0.1) { $y = $pv * (1 + $nper * $rate) + $pmt * (1 + $rate * $type) * $nper + $fv; $f = exp($nper * log(1 + $rate)); $y = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv; $y0 = $pv + $pmt * $nper + $fv; $y1 = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv; // find root by secant method $rate = ($y1 * $x0 - $y0 * $x1) / ($y1 - $y0); $y = $pv * (1 + $nper * $rate) + $pmt * (1 + $rate * $type) * $nper + $fv; $f = exp($nper * log(1 + $rate)); $y = $pv * $f + $pmt * (1 / $rate + $type) * ($f - 1) + $fv; * Returns the price per $100 face value of a discounted security. * @param mixed settlement The security's settlement date. * The security settlement date is the date after the issue date when the security is traded to the buyer. * @param mixed maturity The security's maturity date. * The maturity date is the date when the security expires. * @param int investment The amount invested in the security. * @param int discount The security's discount rate. * @param int basis The type of day count to use. * 0 or omitted US (NASD) 30/360 public static function RECEIVED($settlement, $maturity, $investment, $discount, $basis= 0) { if (($investment <= 0) || ($discount <= 0)) { if (!is_numeric($daysBetweenSettlementAndMaturity)) { return $daysBetweenSettlementAndMaturity; return $investment / ( 1 - ($discount * $daysBetweenSettlementAndMaturity)); * Returns the straight-line depreciation of an asset for one period * @param cost Initial cost of the asset * @param salvage Value at the end of the depreciation * @param life Number of periods over which the asset is depreciated public static function SLN($cost, $salvage, $life) { return ($cost - $salvage) / $life; * Returns the sum-of-years' digits depreciation of an asset for a specified period. * @param cost Initial cost of the asset * @param salvage Value at the end of the depreciation * @param life Number of periods over which the asset is depreciated public static function SYD($cost, $salvage, $life, $period) { if (($life < 1) || ($period > $life)) { return (($cost - $salvage) * ($life - $period + 1) * 2) / ($life * ($life + 1)); * Returns the bond-equivalent yield for a Treasury bill. * @param mixed settlement The Treasury bill's settlement date. * The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer. * @param mixed maturity The Treasury bill's maturity date. * The maturity date is the date when the Treasury bill expires. * @param int discount The Treasury bill's discount rate. public static function TBILLEQ($settlement, $maturity, $discount) { // Use TBILLPRICE for validation $testValue = self::TBILLPRICE($settlement, $maturity, $discount); return (365 * $discount) / (360 - $discount * $daysBetweenSettlementAndMaturity); * Returns the yield for a Treasury bill. * @param mixed settlement The Treasury bill's settlement date. * The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer. * @param mixed maturity The Treasury bill's maturity date. * The maturity date is the date when the Treasury bill expires. * @param int discount The Treasury bill's discount rate. public static function TBILLPRICE($settlement, $maturity, $discount) { if (!is_numeric($daysBetweenSettlementAndMaturity)) { return $daysBetweenSettlementAndMaturity; if ($daysBetweenSettlementAndMaturity > 360) { $price = 100 * (1 - (($discount * $daysBetweenSettlementAndMaturity) / 360)); } // function TBILLPRICE() * Returns the yield for a Treasury bill. * @param mixed settlement The Treasury bill's settlement date. * The Treasury bill's settlement date is the date after the issue date when the Treasury bill is traded to the buyer. * @param mixed maturity The Treasury bill's maturity date. * The maturity date is the date when the Treasury bill expires. * @param int price The Treasury bill's price per $100 face value. public static function TBILLYIELD($settlement, $maturity, $price) { if (!is_numeric($daysBetweenSettlementAndMaturity)) { return $daysBetweenSettlementAndMaturity; if ($daysBetweenSettlementAndMaturity > 360) { return ((100 - $price) / $price) * (360 / $daysBetweenSettlementAndMaturity); } // function TBILLYIELD() public static function XIRR($values, $dates, $guess = 0.1) { // create an initial range, with a root somewhere between 0 and guess $f1 = self::XNPV($x1, $values, $dates); $f2 = self::XNPV($x2, $values, $dates); if (($f1 * $f2) < 0.0) break; $f1 = self::XNPV($x1 += 1.6 * ($x1 - $x2), $values, $dates); $f2 = self::XNPV($x2 += 1.6 * ($x2 - $x1), $values, $dates); $f = self::XNPV($x1, $values, $dates); $f_mid = self::XNPV($x_mid, $values, $dates); if ($f_mid <= 0.0) $rtb = $x_mid; * Returns the net present value for a schedule of cash flows that is not necessarily periodic. * To calculate the net present value for a series of cash flows that is periodic, use the NPV function. * =XNPV(rate,values,dates) * @param float $rate The discount rate to apply to the cash flows. * @param array of float $values A series of cash flows that corresponds to a schedule of payments in dates. The first payment is optional and corresponds to a cost or payment that occurs at the beginning of the investment. If the first value is a cost or payment, it must be a negative value. All succeeding payments are discounted based on a 365-day year. The series of values must contain at least one positive value and one negative value. * @param array of mixed $dates A schedule of payment dates that corresponds to the cash flow payments. The first payment date indicates the beginning of the schedule of payments. All other dates must be later than this date, but they may occur in any order. public static function XNPV($rate, $values, $dates) { $valCount = count($values); for ($i = 0; $i < $valCount; ++ $i) { * Returns the annual yield of a security that pays interest at maturity. * @param mixed settlement The security's settlement date. * The security's settlement date is the date after the issue date when the security is traded to the buyer. * @param mixed maturity The security's maturity date. * The maturity date is the date when the security expires. * @param int price The security's price per $100 face value. * @param int redemption The security's redemption value per $100 face value. * @param int basis The type of day count to use. * 0 or omitted US (NASD) 30/360 public static function YIELDDISC($settlement, $maturity, $price, $redemption, $basis= 0) { if (($price <= 0) || ($redemption <= 0)) { if (!is_numeric($daysBetweenSettlementAndMaturity)) { return $daysBetweenSettlementAndMaturity; $daysBetweenSettlementAndMaturity *= $daysPerYear; return (($redemption - $price) / $price) * ($daysPerYear / $daysBetweenSettlementAndMaturity); } // function YIELDDISC() * Returns the annual yield of a security that pays interest at maturity. * @param mixed settlement The security's settlement date. * The security's settlement date is the date after the issue date when the security is traded to the buyer. * @param mixed maturity The security's maturity date. * The maturity date is the date when the security expires. * @param mixed issue The security's issue date. * @param int rate The security's interest rate at date of issue. * @param int price The security's price per $100 face value. * @param int basis The type of day count to use. * 0 or omitted US (NASD) 30/360 public static function YIELDMAT($settlement, $maturity, $issue, $rate, $price, $basis= 0) { if (($rate <= 0) || ($price <= 0)) { if (!is_numeric($daysBetweenIssueAndSettlement)) { return $daysBetweenIssueAndSettlement; $daysBetweenIssueAndSettlement *= $daysPerYear; return $daysBetweenIssueAndMaturity; $daysBetweenIssueAndMaturity *= $daysPerYear; if (!is_numeric($daysBetweenSettlementAndMaturity)) { return $daysBetweenSettlementAndMaturity; $daysBetweenSettlementAndMaturity *= $daysPerYear; return ((1 + (($daysBetweenIssueAndMaturity / $daysPerYear) * $rate) - (($price / 100) + (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate))) / (($price / 100) + (($daysBetweenIssueAndSettlement / $daysPerYear) * $rate))) * ($daysPerYear / $daysBetweenSettlementAndMaturity); } // class PHPExcel_Calculation_Financial
|
