设为首页收藏本站

中国会计视野论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

微信登录

微信扫一扫,快速登录

查看: 2465|回复: 10

[财务管理] 【EXCEL玩管理】运用规划求解工具测算组合贷款的贷款利率设计

[复制链接]
发表于 2018-6-1 09:32:58 | 显示全部楼层 |阅读模式
  美国注册管理会计师认证(CMA®)
现在就加入吧!
CMA获得政府及各大企业集团一致推荐
CMA是您挖掘职业潜能的通关秘钥
 

视野思享会年费会员
一年内免费直播参与
历届活动视频回放
与爱学习群体共同成长

 

《跟卢子一起学Excel》
Excel知名公众号作者
随书扫码看视频学Excel
20万读者追随的真爱选择。


公司CFO易总和银行在商量组合贷款,期限统一为5年,工商银行在现有利率的基础上可以再下浮5个百分点,农村商业银行在现有利率的基础上可以再下浮15个百分点,中国银行在现有利率的基础上可以再下浮10个百分点。换句话,它们的利率最低可以达到4.1325%、4.437%和3.5235%。
现有固定贷款金额和利率不变,项目的建设周期是确定的,银行如果都按照项目周期来放款,我们怎么测算综合成本最低,还款计划最优呢?
目前集团与几家金融机构初洽的第二种组合贷款情况如表4-5如示:
表4-5 多金融机构贷款组合条件表二
  
项目
  
工商银行
农村商业银行
中国银行
贷款额(万元)
9 000.00
10 000.00
6 000.00
年利率浮动范围
4.1325%-4.3500%
4.437%-5.22%
3.5235%-3.9150%
贷款期间
5年
这个问题我们利用EXCEL的规划求解工具来解决:
Ø 设计空白表格。首先将“组合贷款设计_期限”复制一个副本并改名为“组合贷款设计_利率”,并将其中的表格“组合贷款设计(年份可变)”改名为“组合贷款设计(利率可变)”,将第9行的三个单元格全部修改为5,如图4-49所示。
图4-49.jpg
图4-49 组合贷款设计利率表
Ø 调用“规划求解”工具。点中单元格F16,点击EXCEL“数据”选项卡上的“分析”组的“规划求解”,调出“规划求解参数”对话框(图4-41)。
l “通过更改可变单元格”:由于我们要求的是贷款利率的变动引发的结果,故而这里填入三家银行的贷款利率所在的单元格“$D$8:$F$8”。
l 点击“全部重置”按钮,将原先设置的限制条件全部清除,点击“添加按钮”增加新的限制条件,这些约束条件及其说明如表4-6。全部约束添加完成后,“遵守约束”内将显示全部限制条件,如图4-50所示。
表4-6 约束条件表(利率)
  
序号
  
约束条件
含义
1
$D$8 <= .0435
工商银行贷款利率上限4.35%
2
$D$8 >= .041325
工商银行贷款利率下限4.1325%
3
$E$8 <= .0522
农村商业银行贷款利率上限5.22%
4
$E$8 >= .04437
农村商业银行贷款利率下限4.437%
5
$F$8 >= .03915
中国银行贷款利率下限3.9150%
6
$F$8 <= .035235
中国银行贷款利率上限3.5235%
7
$F$15 <= $F$5
月还款金额之和不能超过借款人月还款能力
图4-50.jpg
图4-50 “规划求解参数”之“遵守约束”参数(利率可变)
点击“装入/保存”按钮,调出“装入/保存模型”对话框(图4-44之①),提示要求我们准备11个纵向连续的空白单元格用以存放约束条件,我们将存放区域的第一个单元格设置为“$I$4”,点击“保存”按钮,EXCEL自动在单元格区域“I4:I14”存放约束条件,具体内容我们列示于单元格区域“J4:J14”,如图4-51所示。从单元格区域“I4:I14”可以一目了然地看出表格“组合贷款设计(利率可变)”中数据对已设置的约束条件的遵守情况,已经满足的条件,用“TRUE”表示,不满足的条件,用“FALSE”表示。
图4-51.jpg
图4-51 “规划求解参数”之“约束”保存值(利率可变)
Ø 运行“规划求解”工具。检查以上设置的规划求解参数,确保无误后,点击“规划求解参数”对话框底部的“求解”按钮,弹出“规划求解结果”对话框(图4-46),该对话框中的设置与“4.5.2”相同。点选中“运算结果报告”后生成的规划求解报告如图4-52所示。
图4-52.jpg
图4-52 不勾选“制作报告大纲”的利率可变时的规划求解报告
Ø “规划求解”结果的分析解读。点击图4-46“确定”按钮,返回工作表“组合贷款设计_利率”。此时,规划求解工具已经将计算结果填入表格“组合贷款设计(利率可变)”之中,如图4-53所示。单元格D8、E8和F8分别为4.1325%、4.4370%和3.5235%,单元格F15为461.64,单元格F16为25851.15。
这表明:在贷款金额和贷款期限已经确定的前提下,按照集团要求的每月最大还款不得超过500万元,贴现率为2.75%的情况下,在三家银行的利率的年限分别4.1325%、4.4370%和3.5235%,每月最大偿还金额461.64万元。不同期限的组合贷款折现汇总金额为25 851.15万元,此时贷款综合成本最低,还款计划最优。
图4-53.jpg
图4-53 组合贷款利率设计结果表

以上就是运用规划求解工具测算组合贷款的贷款利率设计方案,更多方案设计请参看:
QQ图片20180601092607.png
本文使用的EXCEL文件:
规划求解在组合贷款中的设计.xlsx (795.82 KB, 下载次数: 174)

评分

2

查看全部评分

 楼主| 发表于 2018-6-1 14:42:02 | 显示全部楼层
有网友私信,找不到EXCEL“数据”选项卡上的“分析”组的“规划求解”,在第一次使用时,请注意:

点击EXCEL“文件”选项卡上的“选项”,调出“EXCEL选项”对话框,点击对话框左侧的“加载项”,在对话框下部的“管理”选择“EXCEL加载项”,点击其右侧的“转到”按钮,调出“加载宏”对话框,勾选其中的“规划求解加载项”后,点击“确定”按钮关闭“加载宏”对话框。这一步的操作仅需要进行一次。
发表于 2018-6-1 17:29:49 | 显示全部楼层

欢迎更多分享!
发表于 2018-6-2 09:44:21 | 显示全部楼层
发表于 2018-6-4 09:35:00 | 显示全部楼层
感谢分享
发表于 2018-6-5 11:49:30 | 显示全部楼层
运用规划求解工具测算组合贷款的贷款利率设计
发表于 2018-6-6 09:41:42 | 显示全部楼层
厉害了,改天买书来看看。
发表于 2018-6-6 09:53:16 | 显示全部楼层
棒棒哒
 楼主| 发表于 2018-6-8 11:30:59 | 显示全部楼层
真心感谢各位的支持~
QQ图片20180608112619.png

发表于 3 天前 | 显示全部楼层
感谢分享
发表于 昨天 08:14 | 显示全部楼层
谢谢分享!学习规划求解。
您需要登录后才可以回帖 登录 | 注册 新浪微博登陆

本版积分规则

关闭

站长推荐上一条 /1 下一条

手机版|中国会计视野论坛 ( 沪ICP备05013522号-2  

GMT+8, 2018-6-18 10:16

Powered by Discuz! X3.3

© 2001-2017 Comsenz Inc.

快速回复 返回顶部 返回列表