如何在 Microsoft Excel 中创建可变利率摊销表

首先设置贷款金额、期限、利率等基础参数，再生成利率序列并计算各利率下月供，接着创建逐月摊销明细表，最后通过下拉列表实现利率选择与摊销表联动更新。

如果您需要在 Microsoft Excel 中计算贷款在不同利率下的还款情况，可以通过创建可变利率摊销表来实现。这种表格允许您动态调整利率并实时查看每期还款金额、本金与利息的变化。

本文运行环境：Dell XPS 13，Windows 11

一、设置基础参数区域

在开始构建摊销表之前，先定义贷款的基本参数，这些参数将作为公式引用的输入源，便于后续修改和计算。

1、在单元格 A1 输入“贷款金额”，在 B1 输入具体数值，例如 500000。

2、在 A2 输入“贷款期限（年）”，在 B2 输入年限，如 30。

3、在 A3 输入“基准年利率（%）”，在 B3 输入初始利率，如 4.5。

4、在 A4 输入“利率变化步长（%）”，在 B4 输入调整幅度，如 0.25，用于后续模拟不同利率场景。

5、在 A5 输入“每年复利次数”，在 B5 输入 12，表示按月计息。

二、生成利率变量序列

为了实现“可变”利率功能，需要建立一个利率变化列表，以便为每个利率生成对应的摊销计划或进行对比分析。

1、在 D1 输入“利率（%）”作为标题。

2、从 D2 开始，输入起始利率值，例如 =B3-0.5，以包含低于基准的情况。

3、在 D3 输入公式 =D2+$B$4，然后向下拖动填充至 D10，生成一系列递增的利率。

4、选中 D2:D10 区域，命名为“Rate_Series”以便后续公式调用。

三、构建动态月供计算模块

使用 Excel 的 PMT 函数结合变动利率，计算每个利率下的每月还款额，确保结果随利率变化自动更新。

1、在 E1 输入“月供金额”。

2、在 E2 输入公式 =-PMT(D2/12/$B$5, $B$2*$B$5, $B$1)，该公式根据当前行利率计算等额本息月供。

3、将 E2 公式向下复制到 E10，得到各利率对应的月还款金额。

阿里妈妈·创意中心

阿里妈妈营销创意中心

0

查看详情

4、为提高可读性，可对 E 列应用货币格式，右键单元格 → 设置单元格格式 → 货币。

四、创建单利率摊销明细表

选择某一特定利率，构建详细的逐月摊销表，展示每期利息、本金及剩余余额的变化过程。

1、在 G1 输入“期数”，H1 输入“期初余额”，I1 输入“月供”，J1 输入“当期利息”，K1 输入“偿还本金”，L1 输入“期末余额”。

2、在 G2 输入 1，G3 输入 2，选中后拖动填充柄至 G361（对应 30 年共 360 期）。

3、在 H2 输入初始贷款金额，即 =$B$1。

4、在 I2 输入月供公式 =$E$2（假设基于 D2 对应的利率），并向下填充。

5、在 J2 计算当期利息：=H2*D2/12，注意 D2 应为绝对引用或使用固定利率单元格。

6、在 K2 计算偿还本金：=I2-J2，然后向下填充。

7、在 L2 计算期末余额：=H2-K2，并将其设为下一期初余额，即 H3==L2，再整体向下复制所有列。

五、实现利率联动控制

通过数据验证和 INDIRECT 等函数，使用户可以选择不同利率并自动刷新摊销表内容。

1、在 N1 输入“选择利率（%）”。

2、在 N2 创建下拉列表：选择“数据”选项卡 → 数据验证 → 允许“序列”，来源输入 =Rate_Series。

3、在 B6 输入公式 =N2，作为当前活动利率。

4、修改摊销表中的利率引用，将原 D2 替换为 $B$6，使整个摊销表基于所选利率重新计算。

5、此时更改 N2 下拉框中的利率值，摊销表将自动更新对应数据。

以上就是如何在 Microsoft Excel 中创建可变利率摊销表的详细内容，更多请关注php中文网其它相关文章！