☰

计算每日复利 - 闰年问题

tag-icon • tag-icon microsoft-excel
计算每日复利 - 闰年问题

所以我有一个有趣的问题——我需要计算某个金额的复利。很简单。需要每天计算。很简单。需要适当考虑闰年。不容易。

我已经读完了Excel 公式将年利率转换为复利日利率和周利率，这是关于利息和计算利息的很好的入门书。我有当前的公式：

=InitialAmount*(1+0.1/365)^(TODAY()-EOMONTH(StartDate,0))-F8

请注意 - 开始日期是月内的某个日期，但利息仅在月底开始计算。

区别同样大。但是，当我遇到闰年时，它会按便士细分。我不喜欢按便士细分 - 我怎样才能让每日复利正确地考虑到闰年？

谢谢

编辑：为了完全清楚，我需要能够计算出跨越闰年和非闰年的利息金额 - 例如，如果我从 2015 年 7 月 21 日开始计算利息，则 2015 年剩余时间是正常利息，2016 年是闰年利息，2017 年则恢复正常利息。

还要注意的是，所列利率是按年计算的，与一年中的天数无关。因此，闰年的日利率会有所不同。

由于仍然存在一些混淆：

例子：

第1天为2015年12月31日，初始值为100,000.00，年利率20%，100,000.15 = 100,000*(1+.2/365)^(1/365)。

第二天是2016年1月1日，初始值现在是100000.15，利率还是年利率20%，100000.30 = 100000.15*(1+.2/366)^(1/366)。

第三天是 1 月 2 日。100000.30 = 100,000.15*(1+.2/366)^(1/366)。

~368 天是 1 月 1 日。乘数现在回到 (1+.2/365)^(1/365)。

我正在寻找一个能够完美地跳转到闰年并正确计算复利的公式。

(100000*(1+.2/365)^(1/365))*(1+.2/366)^(1/366) - 是第一天 + 第二天的总和。

编辑 2：问题的当前状态：解决方案如下所示：

=PV*(1+rate/365)^DaysNotInALeapYear*(1+rate/366)^DaysInALeapYear

其中 rate 为年利率。

答案1

好吧，这花了不少时间，但解决方案却非常棘手。感谢@Floris 帮助我解决了这个问题。

问题要求一切都归结为一个单一的公式，但我将把一切分解成各个部分，然后将它们重新组合在一起。

我将略过复利的一些基础知识。有关入门知识，请参阅https://www.investopedia.com/terms/c/compoundinterest.asp对于概念，以及Excel 公式将年利率转换为复利日利率和周利率有关 Excel 中背后数学原理的简单示例。

我们从标准利息公式开始：

FV = PV(1+r/n)^(t*n)

，其中 t 以年为单位。这意味着（对于正常年份）每日复利为

FV = PV*(1+r/365)^DaysNotLeap

闰年的复利是

FV = PV*(1+R/366)^DaysInLeapYears

从数学上来说，我们可以将这两者结合起来，如下所示：

FV= PV*(1+r/365)^DaysNotLeap*(1+R/366)^DaysInLeapYears

现在的诀窍是找出闰年和两个日期之间平日的天数。这相当复杂，但一旦我们找到一个，我们就能找到另一个。

我将它们命名为 StartDate 和 EndDate。

两者之间间隔多少天？

DaysDifference=EndDate-StartDate+1
DaysDifference=Days(EndDate,StartDate)+1

这两个公式是同一个东西，只是写法略有不同。一个使用 Excel 内置的 days 函数，另一个是简单的减法。

为什么是 +1？如果我从 1 月 2 日开始，到 1 月 3 日结束，那么我有 2 号和 3 号。但如果我减去日期，我只能得到 1。需要再加一个来补偿。

这很简单。这两个年份之间有多少个闰年，包括我们是从闰年开始、以闰年结束还是完全跳过闰年？我们可以通过计算这两个年份之间预计有多少天以及这两个年份之间实际有多少天来做到这一点。

DaysInYearsBetween=DAYS(DATE(YEAR(enddate)+1,1,1),DATE(YEAR(startdate),1,1))

我们实际上拥有的日子

我们预期的天数：首先，我们计算两天之间有多少年：

YearsBetween=YEAR(enddate)-YEAR(startdate)+1

然后我们乘以 365

ExpectedDaysBetween=YearsBetween*365

所有公式的全面扩展将在最后一步发生。

那么有多少个闰年呢？让我们减去这两个数字：

LeapYearsInvolved = DaysInYearsBetween - ExpectedDaysBetween

这是可行的，因为每个闰年都会增加一天，所以额外天数就是闰年的数量。太棒了！

我们需要知道最终年份或结束年份是否是闰年。这可以通过一对测试来完成，测试方式类似：

IsStartDateLeap=IF(DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366,TRUE,FALSE)

这非常明确，并显示了所需的一切。如果你对 Excel 不太熟悉，我建议使用上述方法。你可以将其缩短为：

IsStartDateLeap=DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366

还有这对：

IsEndDateLeap=DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366

如果我们的起始年份或结束年份是闰年，这将为我们提供一个相当简单的真/假值，我们可以使用它来帮助计算这些日子属于哪里。

接下来，我们需要计算一年中未在开始日期和结束日期使用的天数。例如，如果我们从 1 月 27 日开始，那么那一年有 26 天我们没有使用。

UnusedStartDays=startdate - DATE(YEAR(startdate)-1,12,31)

UnusedEndDays=DATE(YEAR(enddate)+1,1,1)-enddate

现在我们已经掌握了所有信息，让我们将它们整合在一起。闰日 - 详细版本：

DaysInLeapYear=LeapYearsInvolved*366-if(IsStartDateLeap,UnusedStartDays,0)-if(IsEndDateLeap,UnusedEndDays)

闰日 - 简短版本

DaysInLeapYear=LeapYearsInvolved*366-IsStartDateLeap*UnusedStartDays-IsEndDateLeap*UnusedEndDays

请注意，在 Excel 2010 及更早版本中，您可能需要将其转换为 (--IsStartDateLeap) 格式，以强制将布尔值转换为数字。

DaysNotLeap=DaysDifference-DaysInLeapYear

正如前面提到的，如果我们知道一个，我们就知道两个。

我们已经掌握了拼图的所有部分。是时候通过简单的替换将它们拼凑在一起了。我不会详细介绍替换的所有细节，只会分阶段发布。

FV= PV*(1+r/365)^(DaysDifference-DaysInLeapYear)*(1+R/366)^DaysInLeapYears

。

FV= PV*(1+r/365)^(DaysDifference-(LeapYearsInvolved*366-if(IsStartDateLeap,UnusedStartDays,0)-if(IsEndDateLeap,UnusedEndDays)))*(1+R/366)^(LeapYearsInvolved*366-if(IsStartDateLeap,UnusedStartDays,0)-if(IsEndDateLeap,UnusedEndDays))

。

FV= PV*(1+r/365)^(DaysDifference-(LeapYearsInvolved*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate))))*(1+R/366)^(LeapYearsInvolved*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate)))

。

FV= PV*(1+r/365)^((Days(EndDate,StartDate)+1)-((DaysInYearsBetween - ExpectedDaysBetween)*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate))))*(1+R/366)^((DaysInYearsBetween - ExpectedDaysBetween)*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate)))

。

FV= PV*(1+r/365)^((Days(EndDate,StartDate)+1)-((DAYS(DATE(YEAR(enddate)+1,1,1),DATE(YEAR(startdate),1,1)) - YearsBetween*365)*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate))))*(1+R/366)^((DAYS(DATE(YEAR(enddate)+1,1,1),DATE(YEAR(startdate),1,1)) - YearsBetween*365)*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate)))

我们最终得到：

FV= PV*(1+r/365)^((Days(EndDate,StartDate)+1)-((DAYS(DATE(YEAR(enddate)+1,1,1),DATE(YEAR(startdate),1,1)) - (YEAR(enddate)-YEAR(startdate)+1)*365)*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate))))*(1+R/366)^((DAYS(DATE(YEAR(enddate)+1,1,1),DATE(YEAR(startdate),1,1)) - (YEAR(enddate)-YEAR(startdate)+1)*365)*366-if((DAYS(DATE(YEAR(startdate),12,31), DATE(YEAR(startdate)-1, 12, 31))=366),(startdate - DATE(YEAR(startdate)-1,12,31)),0)-if((DAYS(DATE(YEAR(Enddate),12,31), DATE(YEAR(Enddate)-1, 12, 31))=366),(DATE(YEAR(enddate)+1,1,1)-enddate)))

就这样。如何计算两个日期之间的每日复利（并根据闰年进行调整）。

对于“这是否非常接近”这个问题 - 是的。以 10,000 的初始值为基础，从 2008 年 4 月 24 日到 2016 年 2 月 2 日的 8 年时间里，“正确”复利可获得 14,753.70 美元的价值，而“不正确”复利可获得 14,757.28 美元的价值。非常接近 - 但银行和政府非常挑剔，政府尤其想要所有的钱。

答案2

我相信下面的电子表格截图会告诉你如何做到这一点。B 列给出公式，C 列给出值。这里有几个步骤：

  1. 确定从开始日期到结束日期有多少天。
  2. 确定从起始年份开始到终止年份结束有多少年
  3. 确定这些年份有多少天
  4. 减去 365*年数：差值就是闰年数
  5. 确定起始年份和终止年份有多少天：这决定了它们是否是闰年
  6. 确定从开始日期到结束日期的总期间内有多少个闰日。这些闰日以 rate/366 累积
  7. 总天数减去闰日数，以比率/365 累加
  8. 现在你已经计算出了你想要的数字

我的计算结果两端可能都存在 1 天的错误 - Excel 公式=DAYS(stopDate, startDate)对连续天数返回 1；我不确定周一开立、周二还款的贷款是否应该产生 1 天或 2 天的利息。但这可能是你能弄清楚的事情。

请让我知道这是否有意义！

在此处输入图片描述

附注1：

您可能可以将其作为一个单一的怪物方程来执行，但它会非常非常混乱。如果您不单独计算 numNormalDays（B17），而是将其计算为，则可能会进行一些简化

=(1+rate/365)^totalDays*(1-rate/366)^leapDays

因为leapDays<<totalDays，该近似值应该有效（然后您不必使用 B16 的结果两次，如果您尝试在一行中执行此操作，这将使您的等式的大小加倍。

如您所见，在给出的示例中，两种方法之间的差异在于第 8 位有效数字。不确定这是否足以引起人们的恐慌……

PS2：

这将是很多最好将所有这些工作隐藏在 VBA 函数中 - 只需正确验证一次，然后您的电子表格就可以在任何地方使用它。实际上，在使用过程中出错的可能性要小得多（因为复制粘贴一个庞大的方程式可能会出错，而且会出错……而且很难排除故障）。

VBA 函数可能看起来像这样（非常接近电子表格中使用的方法，在实现上有一些差异）：

Option Explicit

Function compoundInterestLeap(startDate, endDate, rate)
' compute the interest that accrues from the end of the month that includes startDate, to endDate
' taking account of the fact that interest accrues more slowly in leap years (1/366 th per day).
' needs error checking?

Dim startYear, endYear, totalYears, leapYears, totalDays, leapDays, normalDays
Dim missingDaysFirst, missingDaysLast, totalYearDays
Dim yearOneIsLeap, yearNisLeap As Boolean
Dim eom As Date

eom = Application.WorksheetFunction.EoMonth(startDate, 0)

startYear = year(startDate)
endYear = year(endDate)
totalYears = endYear - startYear + 1
With Application.WorksheetFunction
    totalDays = .Days(endDate, eom)
    totalYearDays = .Days(DateSerial(endYear + 1, 1, 1), DateSerial(startYear, 1, 1))
    missingDaysFirst = .Days(eom, DateSerial(startYear, 1, 1))
    missingDaysLast = .Days(endDate, DateSerial(endYear, 12, 31))
End With

leapYears = totalYearDays - totalYears * 365

leapDays = leapYears * 366
If isLeapYear(startYear) Then
    leapDays = leapDays - missingDaysFirst
End If

If isLeapYear(endYear) Then
    leapDays = leapDays - missingDaysLast
End If

normalDays = totalDays - leapDays

compoundInterestLeap = (1 + rate / 365) ^ normalDays * (1 + rate / 366) ^ leapDays

End Function

Function isLeapYear(year)
' return True if the year passed as an argument is a leap year
' no error checking...
If Application.WorksheetFunction.Days(DateSerial(year, 12, 31), DateSerial(year - 1, 12, 31)) = 366 Then
    isLeapYear = True
Else
    isLeapYear = False
End If

End Function

答案3

最后，我想向大家推荐一下金融机构使用的方法。这种方法首先计算非闰年的天数和闰年天数。然后计算利息。可以使用单个公式或两个单独的公式来计算。

我的解决方案有两个公式。

在此处输入图片描述

正常天数（2016 年 1 月 31 日 - 2015 年 11 月 12 日）= 80 天(手机 B589)。

闰年天数 = 29 天(手机 B590)。

正常年份公式 =(((1+0.055)^(B589/365)-1)*B584)

闰年公式 =(((1+0.055)^(B590/366)-1)*B584)

然后得到总金额为 160.55。

我确实希望您发现这是一个有用的解决方案。

答案4

考虑到您的闰年问题，我想向您展示 Excel 在计算复利时如何考虑它。

检查屏幕截图。

在此处输入图片描述

为了更好地理解，我采集了两个样本，第一个是常规样本，第二个是闰年样本，并且我使用了两个不同的公式。

检查贷款日期，2016 年是闰年，复合于2017 年。

旁边是 2015 年的贷款日期，2016 年复利年份（闰年）。

在下一行你可以找到 2 个相似的值（10,550.00) 第一个表示平年，第二个表示闰年。

我使用的公式是，

=E542*(1+0.055)^ROUNDDOWN((E545-E544)/365,0)

=E542*(1+0.055)^ROUNDDOWN((E545-E544)/366,0)

注意：单元格 E542 为本金金额。E545 为计算日期，E544 为贷款发放日期。

在最后一行，你又会发现两个相似的数额，我使用的公式是，

=E542*(1+5.5%/365)^365

=E542*(1+5.5%/366)^366

现在主要的问题是如何涉及闰年。为此，我使用公式来确定闰年，

=IF(MOD(YEAR(E545), 4) = 0, "飞跃", "常规")

所以结论是，先测试年份是否是闰年，然后应用公式计算复利。公式是，，

=IF(MOD(YEAR(F545), 4) = 0, E542*(1+0.055)^ROUNDDOWN((F545-F544)/366,0), E542*(1+0.055)^ROUNDDOWN((F545-F544)/365,0))

这是我发现的考虑到闰年计算 CI 的最佳解决方案。

希望你觉得它有用，如果再次出现不同，请写下来。

相关内容