如何使用excel制作贷款偿还进度分析图

吃一口奶黄包

已于 2025-01-24 13:30:50 修改

阅读量479

点赞数 7

文章标签： excel

于 2025-01-16 17:12:14 首次发布

本文链接：https://blog.csdn.net/weixin_73261958/article/details/145184560

版权

一、贷款偿还进度分析图是什么

贷款偿还阶段分析图：以时间为横轴，还款金额等为纵轴，展示还款计划与实际还款对比，反映还款是否按时足额及进度是否符合预期。具体表现形式分类如下：

按表现形式分类
甘特图：横纵轴分别表示时间和贷款流程阶段，以横线展示各阶段起止及持续时间。
折线图：以时间为横轴、贷款进度量化指标为纵轴，展示贷款业务进展趋势。
柱状图：用于比较不同贷款项目、客户群体或时间段的贷款进度情况。
流程图：以图形符号和箭头展示贷款业务流程、顺序及各环节逻辑关系。
饼图：主要用于展示贷款业务中各部分占总体的比例关系。

二、为什么要学习贷款偿还进度分析图

对个人：可直观了解还款情况，合理安排财务收支，避免逾期；能预测未来现金流，提前规划资金使用，实现财务目标；还能评估自身债务风险，及时调整财务策略，避免陷入债务困境。
对金融机构：有助于实时监控贷款质量，发现潜在违约风险；可据此量化评估风险，调整风险准备金，优化风险管理；能为信贷决策提供参考，优化信贷资源配置；也有利于了解客户状况，提供个性化服务，维护客户关系，提升满意度和忠诚度。

三、如何制作贷款进度分析图

这里举例的为以饼图显示的贷款偿还阶段分析图，反映还款计划与实际还款占总体的比例关系。具体步骤如下图：

（1）制表录入必要条件

首先我们需要做两张表：

第一张表：其内容为已知的贷款的年利率、期数、贷款总额和需要计算的每期还款额。

①在B2:B5分别输入“年利率”、“期数”、“贷款总额”和“每期还款额”（每期还款额是为辅助第二张表的计划还款所设）。

②在C2:C4输入已知的对应数据。

C5单元格数据即每期还款额不是直接给出的，所以需要通过公式计算，输入的公式为“=PMT(C2,C3,C4)”。【公式解释后文会补充】

第二张表：其内容为对应年份的计划还款和实际还款。

①在B8：D8分别输入“年份”、“计划还款”和“实际还款”。

②在B9:B13输入还款的对应年份2015至2019。

③在C9:C13输入计划还款额，计划还款额就是每期还款额，都为“=$C$5”，对第一张表计算出的每期还款额进行绝对引用，（绝对引用下拉不会改变引用数据，这里不使用绝对引用会出错）。实际还款则按照实际金额登记。

得到结果如下图所示：

函数补充介绍

这里用到PMT函数，PMT 函数是 Excel 中用于计算贷款偿还额的财务函数，它的语法如下：

PMT(rate,nper,pv,[fv],[type])

各参数含义

rate：贷款利率。若每年还款为年利率，若每月还款为年利率除以每年还款期数。
nper：还款总期数。若每年还款，则期数为年数；若每月还款，则期数为年数*12。
pv：贷款本金，即现值，是指未来一系列还款额在当前的价值。
[fv]：可选参数，未来值或最后一次付款后希望得到的现金余额，默认为 0。比如贷款还清后希望账户无余额，该值为 0；若有特定的终值要求则设置相应数值。
[type]：可选参数，指定各期的付款时间是在期初还是期末，0 或省略代表期末付款，1 代表期初付款。

这里给出的“=PMT(C2,C3,C4)”，就是对应rate贷款利率为年利率，nper期数为还款总年数，pv还款额现在价值为贷款总额，后两个参数因没有明确规定所以可以省略。

（2）创建进度辅助表

在F8:H8分别输入“辅助1”、“占比”和“辅助2”。

占比列G9:G13单元格输入的公式应为对应实际还款额/-对应的计划付款额，得出在对应年份中，实际还款相对于计划还款的占比情况。所以在G9单元格输入“=D9/-C9”，然后选中G9单元格，下拉至G13，引用数据会自动对应变化，不用逐个输入。

两个辅助列公式相同，计算未完成计划还款的比例除以2，F9和H9都为“=(1-$G9)/2”，这里“$G9”表示列绝对引用（G）、行相对引用（9）。这样在下拉公式时，始终引用的是同一列（占比列）对应行的数值。然后分别下拉至F13和H13。

得到结果如下图所示：

（3）创建分析图

1.创建饼图中的圆环图：选中F8：H9，插入-所有图表-饼图-圆环图，设置自己想要的颜色和添加想要显示出的数据标签。得到结果如下图所示：

2.创建可以下拉选择年份的控件：

若excel还没有手动添加过开发工具，则点击文件-选项-自定义-勾选开放工具；

开发工具-插入-点击第一行中的第二个，然后在圆环图旁边创建；右键该控件，设置控件格式，在数据源区域输入$B$9:$B$13（年份数据），单元格链接输入$G$2（G2单元格是随便选的一个单元格，但后面操作中需要用到），下拉显示项数选择5(因为期数为5年)-确定。如下图所示：

3.创建公式：公式-定义名称-输入“=OFFSET(Sheet1!$F$8:$H$8,Sheet1!$F$2,0)”，OFFSET函数是Excel的一个查找与引用函数，用于以指定的引用为参照系，通过给定偏移量得到新的引用。语法为：
=OFFSET(起始单元格，行偏移[行数],列偏移[列数])
这里的输入的第一参数Sheet1!$F$8:$H$8是辅助1，辅助2和占比这三个单元格作为起始单元格；第二参数Sheet1!$F$2是上一步骤单元格链接到的单元格，来表示行偏移量；由于列不偏移，所以第三参数为0。

定义这个公式就可以会获得当年数下拉至第几年时，对应的占比和辅助项也向下获取第几行的数据，就能获得未完成计划还款的比例。

具体如下图所示：