Excel制作贷款还款清单的详细教程

Excel制作贷款还款清单的详细教程

在Excel中制作贷款还款清单，可以通过设置贷款的基本信息、计算每期还款额、跟踪还款进度等步骤来实现。首先，需要输入贷款金额、利率、贷款期限等信息，然后使用公式计算每期还款额，并创建清单表格记录每期的还款信息。下面详细介绍如何在Excel中制作一个详细的贷款还款清单。

一、设置基本贷款信息

在开始制作贷款还款清单之前，首先需要输入基本的贷款信息。这些信息包括贷款金额、年利率、贷款期限（以月为单位）等。

1. 输入贷款基本信息

在Excel表格中，可以使用以下步骤来输入贷款的基本信息：

• 在A1单元格输入“贷款金额”。
• 在B1单元格输入贷款的具体金额，例如“100000”。
• 在A2单元格输入“年利率”。
• 在B2单元格输入贷款的年利率，例如“5%”。
• 在A3单元格输入“贷款期限（月）”。
• 在B3单元格输入贷款的期限，例如“360”表示30年。

2. 计算月利率

根据年利率计算月利率，公式为：月利率 = 年利率 / 12。

• 在A4单元格输入“月利率”。
• 在B4单元格输入公式
  =B2/12
  ，并按回车键。

二、计算每期还款额

在输入完贷款基本信息后，下一步是计算每期的还款额。常用的贷款还款方式有等额本息和等额本金两种，以下分别介绍这两种方式的计算方法。

1. 等额本息还款法

等额本息还款法每月还款金额相同，其中利息部分逐月递减，本金部分逐月递增。计算公式为：

• 在A5单元格输入“每月还款额（等额本息）”。
• 在B5单元格输入公式
  =B1*B4*(1+B4)^B3/((1+B4)^B3-1)
  ，并按回车键。

2. 等额本金还款法

等额本金还款法每月偿还的本金相同，利息部分逐月递减。计算公式为：

• 在A6单元格输入“每月本金偿还额（等额本金）”。
• 在B6单元格输入公式
  =B1/B3
  ，并按回车键。

每月利息计算公式为：

• 在A7单元格输入“每月利息（等额本金）”。
• 在B7单元格输入公式
  =B1*B4
  ，并按回车键。

三、创建还款清单表格

在计算完每期还款额后，接下来需要创建一个还款清单表格来跟踪每期的还款信息。

1. 设置还款清单表头

• 在D1单元格输入“期数”。
• 在E1单元格输入“还款日期”。
• 在F1单元格输入“每期还款额”。
• 在G1单元格输入“本金”。
• 在H1单元格输入“利息”。
• 在I1单元格输入“剩余本金”。

2. 填充还款清单内容（等额本息）

假设还款周期为每月1次，以下是填充还款清单内容的步骤：

• 在D2单元格输入“1”，然后在D3单元格输入“2”，以此类推直到贷款期限结束。
• 在E2单元格输入首次还款日期，例如“2023-11-01”，然后拖动填充以生成每月的还款日期。
• 在F2单元格输入公式
  =$B$5
  （每月还款额），然后拖动填充至贷款期限结束。
• 在G2单元格输入公式
  =PPMT($B$4, D2, $B$3, -$B$1)
  （每期本金），然后拖动填充至贷款期限结束。
• 在H2单元格输入公式
  =IPMT($B$4, D2, $B$3, -$B$1)
  （每期利息），然后拖动填充至贷款期限结束。
• 在I2单元格输入公式
  =$B$1-SUM(G$2:G2)
  （剩余本金），然后拖动填充至贷款期限结束。

3. 填充还款清单内容（等额本金）

假设还款周期为每月1次，以下是填充还款清单内容的步骤：

• 在D2单元格输入“1”，然后在D3单元格输入“2”，以此类推直到贷款期限结束。
• 在E2单元格输入首次还款日期，例如“2023-11-01”，然后拖动填充以生成每月的还款日期。
• 在G2单元格输入公式
  =$B$6
  （每月本金偿还额），然后拖动填充至贷款期限结束。
• 在H2单元格输入公式
  =B$1*(1-(D2-1)/$B$3)*$B$4
  （每期利息），然后拖动填充至贷款期限结束。
• 在F2单元格输入公式
  =G2+H2
  （每期还款额），然后拖动填充至贷款期限结束。
• 在I2单元格输入公式
  =$B$1-SUM(G$2:G2)
  （剩余本金），然后拖动填充至贷款期限结束。

四、格式化还款清单

为了使还款清单更加清晰易读，可以对表格进行一些格式化设置。

1. 设置单元格格式

• 选中D列至I列的所有单元格。
• 右键选择“设置单元格格式”。
• 在“数字”选项卡中，选择“货币”。
• 设置小数位数为2，点击“确定”。

2. 添加边框

• 选中D1至I贷款期限末尾的所有单元格。
• 右键选择“设置单元格格式”。
• 在“边框”选项卡中，选择“外边框”和“内部边框”，点击“确定”。

3. 调整列宽

• 选中D列至I列的列标题。
• 双击列标题右侧的边框，自动调整列宽。

五、添加图表

为了更直观地展示还款进度，可以在Excel中添加一个图表。

1. 插入折线图

• 选中D列至I列的所有单元格。
• 点击“插入”选项卡。
• 选择“折线图”中的“二维折线图”。

2. 设置图表格式

• 右键点击图表区域，选择“选择数据”。
• 在“选择数据源”窗口中，点击“添加”。
• 在“系列值”中，选择G列的所有单元格，点击“确定”。
• 重复上述步骤，添加H列和I列的数据。
• 右键点击图表区域，选择“设置图表区域格式”，调整图表样式和颜色。

通过以上步骤，就可以在Excel中创建一个详细的贷款还款清单，并通过图表更直观地展示还款进度。希望这篇文章能帮助你更好地管理贷款还款信息。

本文原文来自PingCode