Swift中EXCEL的RATE函数提供不同的结果 [英] RATE Function from EXCEL in Swift providing different results
问题描述
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，类型:0，猜测:0.1)//-1.347153369879729-错误(正确为0.010833331)
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，类型:0，猜测:0.1)//0.008324438477500274 ---正确
RATE(nper: 24, pmt: -46.14, pv: 1000, fv: 0, type: 0, guess: 0.1) //0.008324438477500274 --- CORRECT
在上面的第一个值中给出的结果比我在Excel中得到的结果错误，但在第二个值中给出了正确的结果.
In above the first values gave wrong result than what I get in Excel but on 2nd values it gave correct result.
推荐答案
此处是LibreOffice中用于RATE函数的C ++代码的Swift端口.原始源代码可以在 https:中找到: //cgit.freedesktop.org/libreoffice/core/tree/sc/source/core/tool/interpr2.cxx
Here is a Swift port of the C++ code used for the RATE function in LibreOffice. The original source code can be found at https://cgit.freedesktop.org/libreoffice/core/tree/sc/source/core/tool/interpr2.cxx
如果尝试计算速率有任何错误，这些设置将返回nil.
These are setup to return nil if there is any error trying to calculate the rate.
func rateIteration(nper: Double, pmt: Double, pval: Double, fval: Double, type: Bool, guess: Double) -> Double? {
// See also #i15090#
// Newton-Raphson method: x(i+1) = x(i) - f(x(i)) / f'(x(i))
// This solution handles integer and non-integer values of Nper different.
// If ODFF will constraint Nper to integer, the distinction of cases can be
// removed; only the integer-part is needed then.
var valid = true
var found = false
var x = 0.0
var xNew = 0.0
var term = 0.0
var termDerivation = 0.0
var geoSeries = 0.0
var geoSeriesDerivation = 0.0
let iterationsMax = 150
var count = 0
let epsilonSmall = 1.0E-14
let SCdEpsilon = 1.0E-7
var pv = pval
var fv = fval
if type {
// payment at beginning of each period
fv = fv - pmt
pv = pv + pmt
}
if nper == nper.rounded() {
// Integer nper
x = guess
while !found && count < iterationsMax {
let powNminues1 = pow(1 + x, nper - 1)
let powN = powNminues1 * (1 + x)
if x == 0.0 {
geoSeries = nper
geoSeriesDerivation = nper * (nper - 1) / 2
} else {
geoSeries = (powN - 1) / x
geoSeriesDerivation = nper * powNminues1 / x - geoSeries / x
}
term = fv + pv * powN + pmt * geoSeries
termDerivation = pv * nper * powNminues1 + pmt * geoSeriesDerivation
if abs(term) < epsilonSmall {
found = true // will catch root which is at an extreme
} else {
if termDerivation == 0.0 {
xNew = x + 1.1 * SCdEpsilon // move away from zero slope
} else {
xNew = x - term / termDerivation
}
count += 1
// more accuracy not possible in oscillating cases
found = abs(xNew - x) < SCdEpsilon
x = xNew
}
}
valid = x > -1.0
} else {
// nper is not an integer value
x = (guess < -1.0) ? -1.0 : guess
while valid && !found && count < iterationsMax {
if x == 0.0 {
geoSeries = nper
geoSeriesDerivation = nper * (nper - 1) / 2
} else {
geoSeries = (pow(1 + x, nper) - 1) / x
geoSeriesDerivation = nper * pow(1 + x, nper - 1) / x - geoSeries / x
}
term = fv + pv * pow(1 + x, nper) + pmt * geoSeries
termDerivation = pv * nper * pow(1 + x, nper - 1) + pmt * geoSeriesDerivation
if abs(term) < epsilonSmall {
found = true // will catch root which is at an extreme
} else {
if termDerivation == 0.0 {
xNew = x + 1.1 * SCdEpsilon
} else {
xNew = x - term / termDerivation
}
count += 1
// more accuracy not possible in oscillating cases
found = abs(xNew - x) < SCdEpsilon
x = xNew
valid = x >= -1.0 // otherwise pow(1 + x, nper) will fail
}
}
}
if valid && found {
return x
} else {
return nil
}
}
func RATE(nper: Double, pmt: Double, pv: Double, fv: Double, type: Double = 0, guess: Double = 0.1) -> Double? {
let payType = type != 0.0
if nper <= 0.0 { // constraint from ODFF spec
return nil
}
if let res = rateIteration(nper: nper, pmt: pmt, pval: pv, fval: fv, type: payType, guess: guess) {
return res
} else {
if guess == 0.1 {
/* TODO: this is rather ugly, instead of looping over different
* guess values and doing a Newton goal seek for each we could
* first insert the values into the RATE equation to obtain a set
* of y values and then do a bisecting goal seek, possibly using
* different algorithms. */
var x = guess
for step in 2...10 {
var g = x * Double(step)
if let res = rateIteration(nper: nper, pmt: pmt, pval: pv, fval: fv, type: payType, guess: g) {
return res
} else {
g = x / Double(step)
if let res = rateIteration(nper: nper, pmt: pmt, pval: pv, fval: fv, type: payType, guess: g) {
return res
}
}
}
}
}
return nil
}
这是您的两个测试用例:
Here are your two test cases:
RATE(nper: 252, pmt: -29002.85, pv: 2500000, fv: 0)
RATE(nper: 24, pmt: -46.14, pv: 1000, fv: 0)
结果是:
0.010833331202068584
0.0083244384774994358
0.010833331202068584
0.0083244384774994358
这篇关于Swift中EXCEL的RATE函数提供不同的结果的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
