使用 Excel 中的一系列日期输入来计算平均利率

使用 Excel 中的一系列日期输入来计算平均利率

我需要根据不同日期变化的汇率表来计算日历年的平均利率。

该表仅显示了设定利率的开始日期和结束日期,为期十年。利率可能在一个日历年内变动四次,而有些年份则根本不变动。十年期间有 22 个条目。我需要计算该期间十个日历年中每年的平均利率。

汇率示例表:

汇率

因此,在上表 (DD/MM/YYYY格式 ) 中,为了得到 2008 年的平均利率,我进行了以下计算:

  • 2008 年 1 月 1 日至 2008 年 7 月 7 日 - 基本利率:6.00% - 期间利​​率 3.10%
  • 2008 年 7 月 8 日至 2008 年 11 月 3 日 - 基本利率:6.25% - 期间利​​率 2.04%
  • 2008 年 11 月 4 日至 2008 年 12 月 31 日 - 基本利率:7.00% - 期间利​​率 1.13%

全年平均利率为 6.27%。

我可以使用 Excel 中的公式来计算这个数字吗?

到目前为止,我面临的最大挑战是运用任何公式来有效地“拖拽”以产生每个日历年的正确结果。

答案1

我给出了一个答案,它基本上起到了作用。

我想将其发布在这里以防其他人遇到这种情况。

示例解决方案表简短

我为每一年添加了一个新列,并在每一列上方输入了一年的开始日期和结束日期(在本例中从 8 月 1 日开始到次年 7 月 31 日结束)。

从那里我在每个单元格中使用了以下公式的变体:

D5 =MAX(MIN($B5,D$2)-MAX($A5,D$1),0)/(D$2-D$1+1)*$C5

具体可以分解为:

  • 结束日期(但不迟于年底)

减:

  • 开始日期(但不早于年初)
  • 上限不小于零(因为当公式复制到其他时期时会产生负结果)

除以:

  • 该期间的天数(得出一年的百分比)

乘以:

  • 本期间利率

虽然这需要更大的表格,但它为每个期间生成了相当准确的平均利率,如总数所示。它确实感觉有点笨重,但由于实际表格要大得多,公式可以轻松地拖到大量期间和条目中。

使用该公式可能会更准确YEARFRAC,但我遇到了在未来时期产生“零”结果的问题,因为预计没有结果。

我确信有一个更清晰、更准确的答案,我非常有兴趣了解,但这可能会对某些人有所帮助。

相关内容