The formula is from the excel is what I wanna implement using JSON object below.

I have the computations but their is error and result not exact, maybe someone has an idea about it. Thanks.

I just wanna know if I made mistake on the convertion. Thanks. this should be the correct result based on the json

#FORMULA

=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))

#sample data input (the scheduleData object and effectiveDate input)

effectiveDate = new Date('11/1/2021')
    
    
scheduleData=    [
        {
            "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
        }
    ]


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));
  }

CodePudding user response：

The only code I did not see to check was the isLastDayOfMonth function. I tested your code with the following implementation of isLastDayOfMonth and it works:

function isLastDayOfMonth(inDate: Date): boolean {
  const thirties = [3, 5, 8, 10];//april,june,sept,nov
  const thOnes = [0, 2, 4, 6, 7, 9, 11];//jan,mar,may,jul,aug,oct,dec
  if (thirties.includes(inDate.getMonth())) { return inDate.getDate() === 30; }
  if (thOnes.includes(inDate.getMonth())) { return inDate.getDate() === 31; }
  if (inDate.getFullYear() % 4 === 0) { return inDate.getDate() === 29; }
  return inDate.getDate() === 28;
}

Edit:

I did not see where you took Effective Date into consideration in your original code which was also where I was running into issues. The spreadsheet showed 41.97 for the first line and I was getting 46.97.

With the following code (in TS Playground), I was able to get the results accurately:

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

function 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') {
    return scheduleData;
  }
  let monthlyRent = scheduleData.annualRent / 12;
  let range360: any = (startDate < effectiveDate && endDate > effectiveDate) ?
    Days360(effectiveDate, endDate) :
    (startDate > effectiveDate && endDate > effectiveDate) ?
      Days360(startDate, endDate) :
      0;
  let monthsInPeriod = (range360 / 360) * 12;
  let rentInPeriod = monthsInPeriod * monthlyRent;
  return {
    description: scheduleData.description,
    monthlyRent: monthlyRent,
    monthsInPeriod: monthsInPeriod,
    rentInPeriod: rentInPeriod
  };
}

function Days360(startDate: Date, endDate: Date, decimalPlace: number = 2): number | undefined {
  if (!startDate || !endDate) {
    return undefined;
  }
  let startMonth = startDate.getMonth();
  let startDayOfMonth = startDate.getDate();
  let startYear = startDate.getFullYear();
  let endMonth = endDate.getMonth();
  let endDayOfMonth = endDate.getDate();
  let endYear = endDate.getFullYear();

  let monthsDifference = endMonth - startMonth;
  let dayOfMonthDifference = endDayOfMonth - startDayOfMonth;
  let yearDifference = endYear - startYear;

  let monthsDifferenceInDate360 = 30 * monthsDifference;
  let yearDifferenceInDate360 = 360 * yearDifference;

  let differenceSum = yearDifferenceInDate360   monthsDifferenceInDate360   dayOfMonthDifference;
  return parseFloat(parseFloat(differenceSum.toString()).toFixed(decimalPlace));
}
var scheduleData = [{
  "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
}
];

scheduleData.forEach(function (sd): void {
  console.log(Compute(sd));
});