![使用 Excel 中的一系列日期输入来计算平均利率](https://linux22.com/image/1488146/%E4%BD%BF%E7%94%A8%20Excel%20%E4%B8%AD%E7%9A%84%E4%B8%80%E7%B3%BB%E5%88%97%E6%97%A5%E6%9C%9F%E8%BE%93%E5%85%A5%E6%9D%A5%E8%AE%A1%E7%AE%97%E5%B9%B3%E5%9D%87%E5%88%A9%E7%8E%87.png)
我需要根据不同日期变化的汇率表来计算日历年的平均利率。
该表仅显示了设定利率的开始日期和结束日期,为期十年。利率可能在一个日历年内变动四次,而有些年份则根本不变动。十年期间有 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
,但我遇到了在未来时期产生“零”结果的问题,因为预计没有结果。
我确信有一个更清晰、更准确的答案,我非常有兴趣了解,但这可能会对某些人有所帮助。