swift - Swift 中 EXCEL 中的 RATE 函数提供不同的结果

Question

func RATE(nper: Double, pmt: Double, pv: Double, fv: Double, type: Double, guess: Double) -> Double{
    var rate = guess
    var y: Double = 0
    var f: Double = 0
    var FINANCIAL_MAX_ITERATIONS: Double = 128
    var FINANCIAL_PRECISION = 1.0e-08
    if (abs(rate) < FINANCIAL_PRECISION) {
        y = pv * (1 + nper * rate) + pmt * (1 + rate * type) * nper + fv
    } else {
        f = exp(nper * log(1 + rate))
        y = pv * f + pmt * (1 / rate + type) * (f - 1) + fv
    }
    var y0 = pv + pmt * nper + fv
    var y1 = pv * f + pmt * (1 / rate + type) * (f - 1) + fv

    // find root by secant method
    var i: Double  = 0
    var x0: Double = 0
    var x1 = rate
    while ((abs(y0 - y1) > FINANCIAL_PRECISION) && (i < FINANCIAL_MAX_ITERATIONS)) {
        rate = (y1 * x0 - y0 * x1) / (y1 - y0)
        x0 = x1
        x1 = rate

        if (abs(rate) < FINANCIAL_PRECISION) {
            y = pv * (1 + nper * rate) + pmt * (1 + rate * type) * nper + fv
        } else {
            f = exp(nper * log(1 + rate))
            y = pv * f + pmt * (1 / rate + type) * (f - 1) + fv
        }

        y0 = y1
        y1 = y
        i += 1
    }

    return rate
}

RATE(nper: 252, pmt: -29002.85, pv: 2500000, fv: 0, type: 0, guess: 0.1) // -1.347153369879729 -- WRONG (Correct is 0.010833331)

RATE(nper: 24, pmt: -46.14, pv: 1000, fv: 0, type: 0, guess: 0.1) //0.008324438477500274 --- CORRECT

在上面第一个值给出的结果比我在 Excel 中得到的结果错误，但在第二个值上它给出了正确的结果。

Answer 1

我认为您的方法对于不起作用的示例没有收敛。

似乎没有人确定 Excel 使用的是什么方法，但有人猜测它使用的是 Newton Raphson 方法，而不是正割法。

我在下面使用 N-R 完成了一个似乎可行的实现。

func pvCalc(nper: Double, pmt: Double, fv: Double, type: Int, rate: Double) -> Double {
    let pvPayments = -pmt / rate * (1 - 1 / pow(1 + rate, nper))
    let pvFV = -fv / pow(1 + rate, nper)
    return type == 0 ? pvPayments + pvFV : pvPayments * (1 + rate) + pvFV
}

func pvDeriv(nper: Double, pmt: Double, fv: Double, type: Int, rate: Double) -> Double {
    let derivPayments = pmt / pow(rate, 2) * (1 - 1 / pow(1 + rate, nper)) - pmt * nper / rate / pow(1 + rate, nper + 1)
    let derivFV = fv * nper / pow(1 + rate, nper + 1)
    if type == 0 {
        return derivPayments + derivFV
    } else {
        return (1 + rate) * derivPayments - pmt / rate * (1 - 1 / pow(1 + rate, nper)) + derivFV
    }
}

func RATE(nper: Double, pmt: Double, pv: Double, fv: Double, type: Int, guess: Double) -> Double{
    let FINANCIAL_MAX_ITERATIONS = 512
    let FINANCIAL_PRECISION = 1.0e-08
    var rate = guess
    for _ in 1...FINANCIAL_MAX_ITERATIONS {
        let current_pv = pvCalc(nper: nper, pmt: pmt, fv: fv, type: type, rate: rate)
        if abs(current_pv - pv) < FINANCIAL_PRECISION {
            return rate
        }
        let current_pvDeriv = pvDeriv(nper: nper, pmt: pmt, fv: fv, type: type, rate: rate)
        rate = rate - (current_pv - pv) / current_pvDeriv
    }
    return rate
}

RATE(nper: 252, pmt: -29002.85, pv: 2_500_000, fv: 0, type: 0, guess: 0.1) // 0.01083333120206857
RATE(nper: 24, pmt: -46.14, pv: 1000, fv: 0, type: 0, guess: 0.1) // 0.008324438477472666