Java实现Excel RATE()函数：牛顿法迭代结果异常求助

Let's break down why your Newton-Raphson implementation is spiraling out of control—there are critical issues with your objective function and derivative calculation that are causing those wild, non-converging iterations.

1. Your Objective Function f(r) Is Misdefined #

First, let's align with the core loan payment equation you referenced:
For a loan present value PV, paid back in n periods at rate r per period, the periodic payment P is:
$$P = \frac{r \times PV}{1 - (1+r)^{-n}}$$

To solve for r, we need to rearrange this into a function where f(r) = 0:
$$f(r) = P \times (1 - (1+r)^{-n}) - r \times PV = 0$$

Your original f function has no connection to this equation—it's solving an unrelated problem entirely, which is why the iterations start moving in the wrong direction immediately.

2. The Derivative Calculation Is Incorrect #

Since your objective function was wrong, the derivative (which depends entirely on f(r) was also invalid. The complex fractions in your derivative code were prone to numerical blowups (like dividing by terms that approach zero), leading to the extreme values you saw.

For the corrected f(r) above, the derivative simplifies nicely:
$$f'(r) = P \times n \times (1+r)^{-(n+1)} - PV$$

3. Iteration Stability & Initial Guess #

Your initial guess of 0.00001 (0.001% monthly) is far too low for a 360-period loan (typical 30-year mortgage). A more realistic starting point (like 0.005, or 0.5% monthly / 6% annual) helps Newton-Raphson converge quickly. We also need convergence checks to stop once we hit a stable result.

Corrected Code Implementation #

Here's the fixed version with explanations:

public static void main(String[] args) {
    double pv = 99000.0;       // Amount received from the loan (present value)
    double pmt = 599.55;       // Monthly payment amount (adjust this to match your actual payment)
    int numPeriods = 360;      // Total number of payments
    double initialGuess = 0.005; // 0.5% monthly rate (reasonable starting point)
    double epsilon = 1e-8;     // Tolerance for convergence (stop when changes are smaller than this)
    int maxIterations = 50;    // Prevent infinite loops

    double currentRate = initialGuess;
    for (int i = 0; i < maxIterations; i++) {
        double fValue = calculateObjective(pv, numPeriods, currentRate, pmt);
        double fPrimeValue = calculateDerivative(pv, numPeriods, currentRate, pmt);

        // Avoid division by a near-zero derivative
        if (Math.abs(fPrimeValue) < 1e-10) {
            System.out.println("Derivative too small—stopping to avoid division errors.");
            break;
        }

        double nextRate = currentRate - fValue / fPrimeValue;

        // Check if we've converged to a stable rate
        if (Math.abs(nextRate - currentRate) < epsilon) {
            System.out.printf("Converged to monthly rate: %.8f\n", nextRate);
            System.out.printf("Annual interest rate: %.4f%%\n", nextRate * 12 * 100);
            break;
        }

        currentRate = nextRate;
        System.out.printf("Iteration %d: Current rate = %.8f\n", i + 1, currentRate);
    }
}

// Corrected objective function: f(r) = P*(1-(1+r)^-n) - r*PV = 0
private static double calculateObjective(double pv, int n, double r, double pmt) {
    // Using a rearranged form to avoid division by zero when r is near 0
    return pmt * (1 - Math.pow(1 + r, -n)) - r * pv;
}

// Derivative of the objective function
private static double calculateDerivative(double pv, int n, double r, double pmt) {
    return pmt * n * Math.pow(1 + r, -(n + 1)) - pv;
}

Key Fixes Explained #

• Aligned with Loan Math: The objective function now properly checks if the present value of your payments matches the loan amount received.
• Simplified Derivative: No messy fractions to cause numerical overflow—just a straightforward calculation from the corrected function.
• Convergence Guards: We stop iterating once the rate stabilizes to avoid infinite loops or extreme values.
• Realistic Initial Guess: Starts with a rate that's typical for long-term loans, helping the algorithm converge in just a few iterations.

内容的提问来源于stack exchange，提问作者Morkus