Java中Excel的irr函数计算（附源码）
废话不多说 直接上源码
java实现excel中irr函数的计算
public static double irr(double[] income) {
return irr(income, 0.1D);
}
public static double irr(double[] values, double guess) {
int maxIterationCount = 20;
double absoluteAccuracy = 1.0E-007D;
double x0 = guess;
int i = 0;
while (i < maxIterationCount) {
double fValue = 0.0D;
double fDerivative = 0.0D;
for (int k = 0; k < values.length; k++) {
fValue += values[k] / Math.pow(1.0D + x0, k);
fDerivative += -k * values[k] / Math.pow(1.0D + x0, k + 1);
}
double x1 = x0 - fValue / fDerivative;
if (Math.abs(x1 - x0) <= absoluteAccuracy) {
return x1;
}
x0 = x1;
i++;
}
return (0.0D / 0.0D);
}
/**
* 默认猜测值
*/
private static final double irrX0 = 0.00001d;
public static String monthIrr(double[] income) {
double ret = irr(income,irrX0);
BigDecimal b = new BigDecimal(100);
double irr = new BigDecimal(ret).setScale(4, BigDecimal.ROUND_HALF_UP).multiply(b).doubleValue();
return irr+"%";
}
public static String yearIrr(double[] income) {
double ret = irr(income,irrX0) ;
BigDecimal bigDecimal = new BigDecimal(ret);
// =(1+Z2)^12-1
BigDecimal a = new BigDecimal(1);
BigDecimal b = new BigDecimal(100);
double year = (bigDecimal.add(a)).
multiply(bigDecimal.add(a)).
multiply(bigDecimal.add(a)).
multiply(bigDecimal.add(a)).
multiply(bigDecimal.add(a)).
multiply(bigDecimal.add(a)).
multiply(bigDecimal.add(a)).
multiply(bigDecimal.add(a)).
multiply(bigDecimal.add(a)).
multiply(bigDecimal.add(a)).
multiply(bigDecimal.add(a)).
multiply(bigDecimal.add(a)).subtract(a).setScale(4, BigDecimal.ROUND_HALF_UP).multiply(b).doubleValue();
return year+"%";
}
public static void main(String[] args) {
// 月
double[] income = {-150,0,0,0,0,0,0,0,0,0,0,0,20};
System.out.println(yearIrr(income));
System.out.println(monthIrr(income));
}
优化入参之后的版本
public static double listIrr(List<Transaction> values, double guess) {
int maxIterationCount = 20;
double absoluteAccuracy = 1.0E-007D;
double x0 = guess;
int i = 0;
while (i < maxIterationCount) {
double fValue = 0.0D;
double fDerivative = 0.0D;
for (int k = 0; k < values.size(); k++) {
fValue += values.get(k).getAmount() / Math.pow(1.0D + x0, k);
fDerivative += -k * values.get(k).getAmount() / Math.pow(1.0D + x0, k + 1);
}
double x1 = x0 - fValue / fDerivative;
if (Math.abs(x1 - x0) <= absoluteAccuracy) {
return x1;
}
x0 = x1;
i++;
}
return (0.0D / 0.0D);
}
/**
* 默认猜测值
*/
private static final double irrX0 = 0.00001d;
public static String monthIrr(List<Transaction> income) {
try {
double ret = listIrr(income,irrX0);
BigDecimal b = new BigDecimal(100);
double irr = new BigDecimal(ret).setScale(4, RoundingMode.HALF_UP).multiply(b).doubleValue();
return irr+"%";
}catch (Exception e){
return "NaN";
}
}
public static String yearIrr(List<Transaction> income) {
try {
double ret = listIrr(income,irrX0) ;
BigDecimal bigDecimal = new BigDecimal(ret);
// =(1+Z2)^12-1
BigDecimal a = new BigDecimal(1);
BigDecimal b = new BigDecimal(100);
double year = (bigDecimal.add(a)).
multiply(bigDecimal.add(a)).
multiply(bigDecimal.add(a)).
multiply(bigDecimal.add(a)).
multiply(bigDecimal.add(a)).
multiply(bigDecimal.add(a)).
multiply(bigDecimal.add(a)).
multiply(bigDecimal.add(a)).
multiply(bigDecimal.add(a)).
multiply(bigDecimal.add(a)).
multiply(bigDecimal.add(a)).
multiply(bigDecimal.add(a)).subtract(a).setScale(4, RoundingMode.HALF_UP).multiply(b).doubleValue();
return year+"%";
}catch (Exception e){
return "NaN";
}
}
/**
* 获取两个日期之间的所有月份 (年月)
*
* @param startTime
* @param endTime
* @return：YYYY-MM
*/
public static List<String> getMonthBetweenDate(String startTime, String endTime){
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM");
// 声明保存日期集合
List<String> list = new ArrayList<String>();
try {
// 转化成日期类型
Date startDate = sdf.parse(startTime);
Date endDate = sdf.parse(endTime);
//用Calendar 进行日期比较判断
Calendar calendar = Calendar.getInstance();
while (startDate.getTime()<=endDate.getTime()){
// 把日期添加到集合
list.add(sdf.format(startDate));
// 设置日期
calendar.setTime(startDate);
//把日期增加一天
calendar.add(Calendar.MONTH, 1);
// 获取增加后的日期
startDate=calendar.getTime();
}
} catch (ParseException e) {
e.printStackTrace();
}
return list;
}
/**
* 要先根据入参的时间 算出时间之间间隔的月份
* double[] income = {-1500000,0,0,0,0,0,0,0,0,0,0,0,200000};
* @param income
* @return
*/
public static List<Transaction> toIrr(List<Transaction> income) {
// 获取最大时间和最小时间
List<Transaction> toIrrList = new ArrayList<>();
income = income.stream().sorted(Comparator.comparing(Transaction::getWhen)).collect(Collectors.toList());
if (income.size() > 0){
String startDate = income.get(0).getWhen().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
String endDate = income.get(income.size() - 1).getWhen().format(DateTimeFormatter.ofPattern("yyyy-MM-dd"));
List<String> monthBetweenDate = getMonthBetweenDate(startDate, endDate);
Map<String,Transaction> map = new HashMap<>();
for (Transaction transaction : income) {
String format = transaction.getWhen().format(DateTimeFormatter.ofPattern("yyyy-MM"));
map.put(format,transaction);
}
monthBetweenDate.forEach(p->{
if (map.get(p) != null){
toIrrList.add(new Transaction(map.get(p).getAmount(),p + "-01"));
}else {
toIrrList.add(new Transaction(0,p + "-01"));
}
});
}
toIrrList.forEach(System.out::println);
return toIrrList;
}
public static void main(String[] args) {
// 月
// double[] income = {-1500000,0,0,0,0,0,0,0,0,0,0,0,200000};
List<Transaction> income = new ArrayList<>();
Transaction toIr1 = new Transaction(-1500000,"2021-09-01");
Transaction toIr4 = new Transaction(1200000,"2022-10-01");
income.add(toIr4);
income.add(toIr1);
List<Transaction> toIrrList = toIrr(income);
System.out.println("月："+yearIrr(toIrrList));
System.out.println("年："+monthIrr(toIrrList));
}
实体类
import java.time.LocalDate;
import java.time.ZoneId;
import java.util.Date;
public class Transaction {
final double amount;
final LocalDate when;
public Transaction(double amount, LocalDate when) {
this.amount = amount;
this.when = when;
}
public Transaction(double amount, Date when) {
this.amount = amount;
this.when = LocalDate.from(when.toInstant().atZone(ZoneId.systemDefault()));
}
public Transaction(double amount, String when) {
this.amount = amount;
this.when = LocalDate.parse(when);
}
public Transaction(double amount) {
this.amount = amount;
this.when = null;
}
public double getAmount() {
return amount;
}
public LocalDate getWhen() {
return when;
}
@Override
public String toString() {
return "Transaction{" +
"amount=" + amount +
", when=" + when +
'}';
}
}
