如何在Excel中按照给定规则计算每日利息（利率1%）

需求概述 #

日利率为1%，计息规则如下：

• 余额为负或0时，当日不计息；若后续无交易且前序余额为负，期间也不计息。
• 余额为正时，当日计息；若后续日期余额变为0/负，需以该正余额为基数，计算中间间隔天数的利息并填入余额变化当日的单元格。
• 若前序余额为0，中间日期不计息。

具体规则示例 #

• 1月1日余额-100：当日及后续1月2、3日不计息。
• 1月5日余额100：当日计息；1月8日余额0，需计算1月6、7日两天利息填入1月8日单元格。
• 1月9日余额100：当日计息；1月12日余额-100，需计算1月10、11日两天利息填入1月12日单元格。
• 1月14日余额0：当日不计息；1月15日因前序余额为0不计息；1月16日余额400：当日计息。

实现方案（Excel公式） #

假设：

• A列：日期（A2=1月1日，A3=1月2日，依此类推）
• B列：期末余额（B2对应1月1日余额，以此类推）
• C列：利息计算结果

方法1：单数组公式（适合Excel 365/2021及以上） #

直接在C2单元格输入以下公式，下拉填充：

=IF(OR(B2<=0,AND(B2=0,MAX(IF($B$2:B2>0,ROW($B$2:B2),0))=0)),0,
IF(B2>0,B2*1%,
LET(
    last_pos_date,XLOOKUP(TRUE,$B$2:INDEX($B:$B,ROW(B2)-1)>0,$A$2:INDEX($A:$A,ROW(B2)-1),,-1,1),
    days_gap,A2-last_pos_date-1,
    INDEX($B:$B,MATCH(last_pos_date,$A:$A,0))*1%*days_gap
)))

方法2：分步辅助列（易理解，适合全版本Excel） #

1. 辅助列D（标记计息起始点）：D2输入=IF(B2>0,1,0)，下拉填充。
2. 辅助列E（最近计息起始日期）：E2输入=IF(D2=1,A2,E1)，下拉填充。
3. 辅助列F（最近计息起始余额）：F2输入=IF(D2=1,B2,F1)，下拉填充。
4. 利息列C：C2输入=IF(B2>0,B2*1%,IF(OR(B2<=0,E1=""),0,(A2-E1-1)*F1*1%))，下拉填充。

公式逻辑说明 #

• 优先判断当前余额≤0且无有效前置正余额时，返回0。
• 余额为正，直接计算当日利息：余额×日利率。
• 余额变为0/负时，找到最近一次正余额的日期，计算间隔天数（当前日期与该日期的差值减1），再用该正余额×日利率×间隔天数得到应计利息。

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