PhpOffice\PhpSpreadsheet\Calculation\Financial\CashFlow\Constant\Periodic\Interest

Complexity

Total Complexity

20

Size/Duplication

Total Lines	201
Duplicated Lines	0 %

Test Coverage

Coverage

100%

Importance

Changes

0

4 Methods

Rating	Name	Duplication	Size	Complexity
A	rateNextGuess()	0	16	3
B	schedulePayment()	0	36	6
A	payment()	0	42	5
B	rate()	0	42	6

<?php

namespace PhpOffice\PhpSpreadsheet\Calculation\Financial\CashFlow\Constant\Periodic;

use PhpOffice\PhpSpreadsheet\Calculation\Exception;
use PhpOffice\PhpSpreadsheet\Calculation\Financial\CashFlow\CashFlowValidations;
use PhpOffice\PhpSpreadsheet\Calculation\Financial\Constants as FinancialConstants;
use PhpOffice\PhpSpreadsheet\Calculation\Functions;
use PhpOffice\PhpSpreadsheet\Calculation\Information\ExcelError;

class Interest
{
    private const FINANCIAL_MAX_ITERATIONS = 128;

    private const FINANCIAL_PRECISION = 1.0e-08;

    /**
     * IPMT.
     *
     * Returns the interest payment for a given period for an investment based on periodic, constant payments
     *         and a constant interest rate.
     *
     * Excel Function:
     *        IPMT(rate,per,nper,pv[,fv][,type])
     *
     * @param mixed $interestRate Interest rate per period
     * @param mixed $period Period for which we want to find the interest
     * @param mixed $numberOfPeriods Number of periods
     * @param mixed $presentValue Present Value
     * @param mixed $futureValue Future Value
     * @param mixed $type Payment type: 0 = at the end of each period, 1 = at the beginning of each period
     */
    public static function payment(
        mixed $interestRate,
        mixed $period,
        mixed $numberOfPeriods,
        mixed $presentValue,
        mixed $futureValue = 0,
        mixed $type = FinancialConstants::PAYMENT_END_OF_PERIOD
    ): string|float {
        $interestRate = Functions::flattenSingleValue($interestRate);
        $period = Functions::flattenSingleValue($period);
        $numberOfPeriods = Functions::flattenSingleValue($numberOfPeriods);
        $presentValue = Functions::flattenSingleValue($presentValue);
        $futureValue = ($futureValue === null) ? 0.0 : Functions::flattenSingleValue($futureValue);
        $type = Functions::flattenSingleValue($type) ?? FinancialConstants::PAYMENT_END_OF_PERIOD;

        try {
            $interestRate = CashFlowValidations::validateRate($interestRate);
            $period = CashFlowValidations::validateInt($period);
            $numberOfPeriods = CashFlowValidations::validateInt($numberOfPeriods);
            $presentValue = CashFlowValidations::validatePresentValue($presentValue);
            $futureValue = CashFlowValidations::validateFutureValue($futureValue);
            $type = CashFlowValidations::validatePeriodType($type);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        // Validate parameters
        if ($period <= 0 || $period > $numberOfPeriods) {
            return ExcelError::NAN();
        }

        // Calculate
        $interestAndPrincipal = new InterestAndPrincipal(
            $interestRate,
            $period,
            $numberOfPeriods,
            $presentValue,
            $futureValue,
            $type
        );

        return $interestAndPrincipal->interest();
    }

    /**
     * ISPMT.
     *
     * Returns the interest payment for an investment based on an interest rate and a constant payment schedule.
     *
     * Excel Function:
     *     =ISPMT(interest_rate, period, number_payments, pv)
     *
     * @param mixed $interestRate is the interest rate for the investment
     * @param mixed $period is the period to calculate the interest rate.  It must be betweeen 1 and number_payments.
     * @param mixed $numberOfPeriods is the number of payments for the annuity
     * @param mixed $principleRemaining is the loan amount or present value of the payments
     */
    public static function schedulePayment(mixed $interestRate, mixed $period, mixed $numberOfPeriods, mixed $principleRemaining): string|float
    {
        $interestRate = Functions::flattenSingleValue($interestRate);
        $period = Functions::flattenSingleValue($period);
        $numberOfPeriods = Functions::flattenSingleValue($numberOfPeriods);
        $principleRemaining = Functions::flattenSingleValue($principleRemaining);

        try {
            $interestRate = CashFlowValidations::validateRate($interestRate);
            $period = CashFlowValidations::validateInt($period);
            $numberOfPeriods = CashFlowValidations::validateInt($numberOfPeriods);
            $principleRemaining = CashFlowValidations::validateFloat($principleRemaining);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        // Validate parameters
        if ($period <= 0 || $period > $numberOfPeriods) {
            return ExcelError::NAN();
        }

        // Return value
        $returnValue = 0;

        // Calculate
        $principlePayment = ($principleRemaining * 1.0) / ($numberOfPeriods * 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 == $numberOfPeriods) {
                $returnValue = 0.0;
            }
        }

        return $returnValue;
    }

    /**
     * RATE.
     *
     * Returns the interest rate per period of an annuity.
     * RATE is calculated by iteration and can have zero or more solutions.
     * If the successive results of RATE do not converge to within 0.0000001 after 20 iterations,
     * RATE returns the #NUM! error value.
     *
     * Excel Function:
     *        RATE(nper,pmt,pv[,fv[,type[,guess]]])
     *
     * @param mixed $numberOfPeriods The total number of payment periods in an annuity
     * @param mixed $payment The payment made each period and cannot change over the life of the annuity.
     *                           Typically, pmt includes principal and interest but no other fees or taxes.
     * @param mixed $presentValue The present value - the total amount that a series of future payments is worth now
     * @param mixed $futureValue The future value, or a cash balance you want to attain after the last payment is made.
     *                               If fv is omitted, it is assumed to be 0 (the future value of a loan,
     *                               for example, is 0).
     * @param mixed $type A number 0 or 1 and indicates when payments are due:
     *                      0 or omitted    At the end of the period.
     *                      1               At the beginning of the period.
     * @param mixed $guess Your guess for what the rate will be.
     *                          If you omit guess, it is assumed to be 10 percent.
     */
    public static function rate(
        mixed $numberOfPeriods,
        mixed $payment,
        mixed $presentValue,
        mixed $futureValue = 0.0,
        mixed $type = FinancialConstants::PAYMENT_END_OF_PERIOD,
        mixed $guess = 0.1
    ): string|float {
        $numberOfPeriods = Functions::flattenSingleValue($numberOfPeriods);
        $payment = Functions::flattenSingleValue($payment);
        $presentValue = Functions::flattenSingleValue($presentValue);
        $futureValue = Functions::flattenSingleValue($futureValue) ?? 0.0;
        $type = Functions::flattenSingleValue($type) ?? FinancialConstants::PAYMENT_END_OF_PERIOD;
        $guess = Functions::flattenSingleValue($guess) ?? 0.1;

        try {
            $numberOfPeriods = CashFlowValidations::validateFloat($numberOfPeriods);
            $payment = CashFlowValidations::validateFloat($payment);
            $presentValue = CashFlowValidations::validatePresentValue($presentValue);
            $futureValue = CashFlowValidations::validateFutureValue($futureValue);
            $type = CashFlowValidations::validatePeriodType($type);
            $guess = CashFlowValidations::validateFloat($guess);
        } catch (Exception $e) {
            return $e->getMessage();
        }

        $rate = $guess;
        // rest of code adapted from python/numpy
        $close = false;
        $iter = 0;
        while (!$close && $iter < self::FINANCIAL_MAX_ITERATIONS) {
            $nextdiff = self::rateNextGuess($rate, $numberOfPeriods, $payment, $presentValue, $futureValue, $type);
            if (!is_numeric($nextdiff)) {
                break;
            }
            $rate1 = $rate - $nextdiff;
            $close = abs($rate1 - $rate) < self::FINANCIAL_PRECISION;
            ++$iter;
            $rate = $rate1;
        }

        return $close ? $rate : ExcelError::NAN();
    }

    private static function rateNextGuess(float $rate, float $numberOfPeriods, float $payment, float $presentValue, float $futureValue, int $type): string|float
    {
        if ($rate == 0.0) {
            return ExcelError::NAN();
        }
        $tt1 = ($rate + 1) ** $numberOfPeriods;
        $tt2 = ($rate + 1) ** ($numberOfPeriods - 1);
        $numerator = $futureValue + $tt1 * $presentValue + $payment * ($tt1 - 1) * ($rate * $type + 1) / $rate;
        $denominator = $numberOfPeriods * $tt2 * $presentValue - $payment * ($tt1 - 1)
            * ($rate * $type + 1) / ($rate * $rate) + $numberOfPeriods
            * $payment * $tt2 * ($rate * $type + 1) / $rate + $payment * ($tt1 - 1) * $type / $rate;
        if ($denominator == 0) {
            return ExcelError::NAN();
        }

        return $numerator / $denominator;
    }
}