Excel租期租金计算公式转JS代码结果不符排查求助

我尝试参考下方给出的Excel公式计算租期内月数、租期对应租金、月租金以及剩余总债务，已经编写了对应的JS代码，但运行后无法得到预期的正确结果，麻烦帮忙确认代码转换逻辑是否正确，或指出存在的问题，非常感谢。

effectiveDate = new Date('11/1/2021')

[
    {
        "description": "Current Term",
        "startDate": "6/1/2021",
        "endDate": "04/30/2025",
        "annualRent": 359659.19999999995
    },
    {
        "noticeDate": "04/30/2028",
        "description": "Current Term - Rent Adjustment",
        "startDate": "05/01/2025",
        "endDate": "04/30/2029",
        "annualRent": 377642.16000000003
    }
]

• 公式1预期结果：41.97
  公式内容：=IF(AND(H4="Current",I4<$B$6,J4>$B$6),DAYS360($B$6,J4)/360*12,IF(AND(H4="Current",I4>$B$6,J4>$B$6),DAYS360(I4,J4)/360*12,0))
  注：公式中$B$6对应输入的effectiveDate值，即2021/11/1
• 公式2预期结果：47.97
  公式内容：=IF(AND(H5="Current",I5<$B$6,J5>$B$6),DAYS360($B$6,J5)/360*12,IF(AND(H5="Current",I5>$B$6,J5>$B$6),DAYS360(I5,J5)/360*12,0))

Compute(scheduleData: any):any{
    let startDate = typeof scheduleData.startDate === 'string' ? new Date(scheduleData.startDate):scheduleData.startDate;
    const endDate = typeof scheduleData.endDate === 'string' ? new Date(scheduleData.endDate):scheduleData.endDate;
    if(!startDate || startDate.toString() === 'Invalid Date'){
    }
    let monthlyRent = scheduleData.annualRent / 12;
    let monthsInPeriod = (this.Days360(startDate, endDate) / 360) * 12 
    let rentInPeriod = monthsInPeriod * monthlyRent;
    return { 
      description: scheduleData.description,
      monthlyRent: monthlyRent,
      monthsInPeriod: monthsInPeriod,
      rentInPeriod: rentInPeriod
    }
  }

  Days360(startDate: Date, endDate: Date, decimalPlace: number = 2){
    if(!startDate || !endDate){
      return undefined;
    }
    let startDay = startDate.getUTCDate();
    let endDay = endDate.getUTCDate();

    if(isLastDayOfMonth(startDate)){
      startDay = 30;
    }

    if(isLastDayOfMonth(startDate) && isLastDayOfMonth(endDate)){
      endDay = 30;
    }
    const computedDays = (((endDate.getUTCFullYear() - startDate.getUTCFullYear()) * 360) + ((endDate.getUTCMonth() - startDate.getUTCMonth()) * 30) + (endDay - startDay));
    return parseFloat(parseFloat(computedDays.toString()).toFixed(decimalPlace));
  }

存在的问题 #

1. 核心计算逻辑缺失：原Excel公式的计算起始点不是固定取租期的startDate，而是要和effectiveDate比较取最大值：如果租期开始早于effectiveDate，就从effectiveDate开始计算；如果租期开始晚于effectiveDate，才从租期startDate开始计算。现有代码完全没有用到effectiveDate参数，直接用租期startDate作为计算起点，结果自然不符合预期。
2. Days360实现不符合Excel标准：现有代码仅处理了起始日是月末、且起止日均为月末的情况，标准Excel DAYS360（US规则）还需要处理：起始日为31号改为30、结束日为31号且起始日≥30时结束日改为30、结束日为月末且起始日非月末时结束日改为30的逻辑，你当前的实现漏了这部分规则。
3. 异常处理为空：你判断了startDate无效的分支，但没有做任何处理，后续代码会继续执行，容易出现非预期错误。

修正后代码 #

// 补全月末判断方法
isLastDayOfMonth(date: Date): boolean {
  const testDate = new Date(date.getTime());
  testDate.setUTCDate(testDate.getUTCDate() + 1);
  return testDate.getUTCDate() === 1;
}

// 修正后的Compute方法，新增effectiveDate入参
Compute(scheduleData: any, effectiveDate: Date): any {
  let termStart = typeof scheduleData.startDate === 'string' ? new Date(scheduleData.startDate) : scheduleData.startDate;
  const termEnd = typeof scheduleData.endDate === 'string' ? new Date(scheduleData.endDate) : scheduleData.endDate;
  
  // 无效日期直接返回空
  if (!termStart || termStart.toString() === 'Invalid Date' || !termEnd || termEnd.toString() === 'Invalid Date' || !effectiveDate || effectiveDate.toString() === 'Invalid Date') {
    return null;
  }

  // 按Excel公式逻辑取计算起始点：租期起始和effectiveDate的最大值
  const calcStart = termStart < effectiveDate ? effectiveDate : termStart;
  
  // 计算起始晚于等于租期结束时，月数、租金均为0
  if (calcStart >= termEnd) {
    return {
      description: scheduleData.description,
      monthlyRent: scheduleData.annualRent / 12,
      monthsInPeriod: 0,
      rentInPeriod: 0
    }
  }

  const monthlyRent = scheduleData.annualRent / 12;
  const monthsInPeriod = (this.Days360(calcStart, termEnd) / 360) * 12;
  const rentInPeriod = monthsInPeriod * monthlyRent;

  return {
    description: scheduleData.description,
    monthlyRent: monthlyRent,
    monthsInPeriod: parseFloat(monthsInPeriod.toFixed(2)),
    rentInPeriod: rentInPeriod
  }
}

// 修正后的Days360，符合Excel US标准
Days360(startDate: Date, endDate: Date, decimalPlace: number = 2): number | undefined {
  if (!startDate || !endDate) {
    return undefined;
  }

  let startYear = startDate.getUTCFullYear();
  let startMonth = startDate.getUTCMonth();
  let startDay = startDate.getUTCDate();
  let endYear = endDate.getUTCFullYear();
  let endMonth = endDate.getUTCMonth();
  let endDay = endDate.getUTCDate();

  const startIsLast = this.isLastDayOfMonth(startDate);
  const endIsLast = this.isLastDayOfMonth(endDate);

  // 起始日是当月最后一天或31号，统一改为30
  if (startIsLast || startDay === 31) {
    startDay = 30;
  }

  // 结束日是当月最后一天且起始日非31号/月末的情况，结束日改为30
  if (endIsLast && !(startIsLast || startDay === 31)) {
    endDay = 30;
  }
  // 结束日是31号且起始日≥30，结束日改为30
  if (endDay === 31 && startDay >= 30) {
    endDay = 30;
  }

  const computedDays = (endYear - startYear) * 360 + (endMonth - startMonth) * 30 + (endDay - startDay);
  return parseFloat(computedDays.toFixed(decimalPlace));
}

验证结果 #

用你提供的样本数据测试：

• 第一条租期计算起始为2021/11/1，到2025/4/30的DAYS360结果为1259，换算后月数为1259/360*12≈41.97，符合预期
• 第二条租期计算起始为2025/5/1，到2029/4/30的DAYS360结果为1439，换算后月数为1439/360*12≈47.97，符合预期

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