excel的IRR函数
2024-09-06 09:04:07
office官网找到IRR的介绍
https://support.office.com/zh-cn/article/irr-%E5%87%BD%E6%95%B0-64925eaa-9988-495b-b290-3ad0c163c1bc
https://docs.microsoft.com/zh-cn/office/vba/language/reference/user-interface-help/irr-function?f1url=https%3A%2F%2Fmsdn.microsoft.com%2Fquery%2Fdev11.query%3FappId%3DDev11IDEF1%26l%3Dzh-CN%26k%3Dk(vblr6.chm1009282)%3Bk(TargetFrameworkMoniker-Office.Version%3Dv15)%26rd%3Dtrue
1.找js的实现
function IRR(cashFlows, estimatedResult) {
var result = "isNAN";
if (cashFlows != null && cashFlows.length > 0) {
// check if business startup costs is not zero:
if (cashFlows[0] != 0) {
var noOfCashFlows = cashFlows.length;
var sumCashFlows = 0;
// check if at least 1 positive and 1 negative cash flow exists:
var noOfNegativeCashFlows = 0;
var noOfPositiveCashFlows = 0;
for (var i = 0; i < noOfCashFlows; i++) {
sumCashFlows += cashFlows[i];
if (cashFlows[i] > 0) {
noOfPositiveCashFlows++;
} else {
if (cashFlows[i] < 0) {
noOfNegativeCashFlows++;
}
}
} // at least 1 negative and 1 positive cash flow available?
if (noOfNegativeCashFlows > 0 && noOfPositiveCashFlows > 0) {
// set estimated result:
var irrGuess = 0.1; // default: 10%
if (!isNaN(estimatedResult)) {
irrGuess = estimatedResult;
if (irrGuess <= 0) {
irrGuess = 0.5;
}
} // initialize first IRR with estimated result:
var irr = 0;
if (sumCashFlows < 0) { // sum of cash flows negative?
irr = -irrGuess;
} else { // sum of cash flows not negative
irr = irrGuess;
} // iteration:
// the smaller the distance, the smaller the interpolation
// error
var minDistance = 1e-15; // business startup costs
var cashFlowStart = cashFlows[0];
var maxIteration = 100;
var wasHi = false;
var cashValue = 0;
for (var i = 0; i <= maxIteration; i++) {
// calculate cash value with current irr:
cashValue = cashFlowStart; // init with startup costs // for each cash flow
for (var j = 1; j < noOfCashFlows; j++) {
cashValue += cashFlows[j] / Math.pow(1 + irr, j);
} // cash value is nearly zero
if (Math.abs(cashValue) < 0.01) {
result = irr;
break;
} // adjust irr for next iteration:
// cash value > 0 => next irr > current irr
if (cashValue > 0) {
if (wasHi) {
irrGuess /= 2;
}
irr += irrGuess;
if (wasHi) {
irrGuess -= minDistance;
wasHi = false;
}
} else {// cash value < 0 => next irr < current irr
irrGuess /= 2;
irr -= irrGuess;
wasHi = true;
} // estimated result too small to continue => end
// calculation
if (irrGuess <= minDistance) {
result = irr;
break;
}
}
}
}
}
return result;
}
2. var一个 [] 来验证找到的函数 是否可用
3.调用IRR,estimatedResult设置为0.1,保持与excel默认值一致
最新文章
- webpack那些事儿
- elk
- 6.原型模式(Prototype Pattern)
- nexus启动不了
- synchronized内置锁
- java.util.AbstractStringBuilder源码分析
- Java 之文件目录操作
- [Cycle.js] Fine-grained control over the DOM Source
- Linux段管理,BSS段,data段,.rodata段,text段
- Intellij Idea web项目的部署配置[转]
- 一个web应用的诞生--数据存储
- 懒人小技巧, Toad 常用偷懒方法
- System.nanoTime
- 2019-03-23 shell练习,日志统计
- JDBC缺点分析
- 【POJ1741】Tree
- [Hinton] Neural Networks for Machine Learning - Bayesian
- hadoop day 3
- C#保留小数
- 模版方法模式(Template Method)