住房贷款例题？

项目2 Thera Bank银行贷款案例

一、案例背景

Thera Bank是一家拥有不断增长客户群的银行。这银行中大多数客户的存款规模都是不一样的。由于贷款业务的客户数量很少，所以银行希望有效地将存款用户转化为贷款用户以此扩大贷款业务量的基础，以带来更多的贷款业务，并在此过程中，通过贷款利息赚取更多。

因此，该银行去年为存款用户但未办理个人贷款业务的客户开展了一项推广活动来 促使其办理个人贷款业务 ，有部分客户通过此活动已经办理了相关服务。这时的零售营销部门希望 制定更好的策略去定位营销 ，以最小的预算提高成功率。该部门希望识别出更有可能购买贷款的潜在客户，提高转化的成功率，降低营销的费用。

二、数据理解

本数据集总计5000行，14列数据，对应字段理解如下：

总体来说，该数据集主要由Personal Loan贷款结果以及一系列用户的身份资产信息等构成

三、数据观察及清洗

本文数据清洗流程上仍然分为初步观察和清洗两步，数据观察主要通过以下几步：

针对异常数据类型及数据问题进行修正，修正后重新进行描述性统计，观察数据总体特征

整体思路：

1 此次活动效果如何？促成了多少人进行贷款？

2 什么样的人更倾向于进行贷款

活动效果即使 是否达成该目标 ，本案例中的目标是进行贷款，因此只需要统计Personal Loan 中贷款和没有贷款的人数即可

通过此次活动，5000名客户中有 480名客户 开通了贷款业务，占比约10%左右，且该活动仅针对未办理贷款的用户开展，总体来说，业务提升了10%以上，效果已经相当不错

要分析贷款用户的潜在影响因素，就是要将是否贷款Personal Loan这一字段与其他字段求出 相关性 （corr（）函数），找出相关系数最大的值并展示

在此基础上进一步划分画板，将各列与personal loan（是否贷款）相关系数进行可视化展示，得到如下结果

从图中可以发现：

1.影响贷款的强相关变量有：收入、每月信用卡消费额、是否有存款账户

2.影响贷款的弱相关变量有：教育程度、房屋抵押价值、家庭人数

3.其余因素如邮编、是否有网银、信用卡、证券账户均对贷款无太大影响

4.年龄和工作年限尽管也无显著影响，但由于其为连续性变量，不排除某一区间内人群对贷款需求较大，后续需进一步分析

在上一步的基础上将影响贷款的变量进一步分析，根据变量性质分开处理

将本案例中的变量分为定性与定量，分别探究其与是否贷款的影响，本案例中的定向变量主要有是否存在银行存款账户、教育程度、家庭人数等

由结果知，开通了存款账户的用户申请贷款的可能性相较于未开通存款账户的用户整整差了六倍，因此想办法让客户开通存款账户未尝不是提高贷款率的手段之一

随着学历升高，申请贷款的比例增加，说明 高学历用户更倾向于成为贷款用户 ，侧面反映出学历越高，越能接受超前消费的消费观，越容易成为贷款的潜在用户

家庭人数3-4的客户相对于1-2的客户申请贷款可能性更高，侧面反映出随着家庭成员增加，经济压力逐渐加大，贷款的潜在需求增加，更倾向于使用该业务

本案例中主要对定量变量中的年领、收入、信用卡还款额和房屋抵押贷款与是否开通贷款的关系进行探究，由于是定量变量，区间为连续区间，相对于定性变量，为便于了解数据全貌，此类变量应针对不同区间分别统计

综合来看，贷款用户和非贷款用户年龄差距不大,从具体年龄层来看，32.0-39.0这一年龄段客户更倾向于贷款

其余定量变量如收入、房屋抵押价值、每月信用卡消费额的分析过程与年龄这一变量基本相同

总体来看，高收入群体较低收入群体更倾向于贷款，当收入超过82，贷款人数将达到之前的5倍，超过98，贷款意愿达到17%以上，超过170，贷款意愿达到一半以上，因此收入越高，贷款业务意向越强烈

当房屋抵押价值超过109.5后，贷款意愿明显提高，总体来看，房屋抵押价值越高，客户越倾向于贷款

大部分贷款用户信用卡消费额平均值接近4，而未贷款用户则接近2，足足是两倍之多，而从用户分层来看，信用卡消费额超过2.8千美金之后，贷款概率提升了4倍，超过6千美金，会回落到0.3左右，较2.8千美金之前有显著提升，因此要重点把握每月信用卡消费额大于2.8千美金以上的客户

总价72万的房子,首付30万,贷25年,月供是多少钱？

若是通过招商银行贷款，具体月供、利息还需要看您的贷款执行利率和还款方式等，可通过招行官网或者手机银行贷款月供试算，试算结果仅供参考。

手机银行：登录手机银行，点击我的-全部-助手-理财计算器-贷款计算器；

招行主页：主页中部金融工具-个人贷款计算器。

输入贷款金额、贷款年利率、贷款期限、选择还款方式等信息，尝试计算贷款月供金额和利息等。

（应答时间：2021年10月14日，最新业务变动请以招行官网公布为准。）

温馨提示：如后续有任何疑问，欢迎随时联系招行APP@您的贴心客服哦~

买房子贷款怎么计算

①按照还贷能力计算的贷款额度

职工本人贷款额度的计算公式为：

（借款人月工资总额+借款人所在单位住房公积金月缴存额）×还贷能力系数-借款人现有贷款月应还款总额]×贷款期限（月）。

夫妻双方贷款额度的计算公式为：

（夫妻双方月工资总额+夫妻双方所在单位住房公积金月缴存额）×还贷能力系数-夫妻双方现有贷款月应还款总额]×贷款期限（月）。

其中还贷能力系数为40%。

月工资总额＝公积金月缴额÷（单位缴存比例＋个人缴存比例）。

②按照房屋价格计算的贷款额度

计算公式为：贷款额度＝房屋价格×贷款成数

a.购买商品住房、限价商品住房、定向安置经济适用住房、定向销售经济适用房或私产住房。

职工家庭（包括职工、配偶及未成年子女，下同）贷款购买首套住房（包括商品住房、限价商品住房、定向安置经济适用住房、定向销售经济适用住房或私产住房），贷款额度不超过所购房屋价格的80%。其中私产住房的房屋价格为购房全部价款与房屋评估价格的较低值。

职工家庭贷款购买第二套及其他符合我市购房条件的自有住房的，贷款额度不超过所购房屋价格的70%。

购买定向安置经济适用住房的，贷款额度还应不高于所购住房全部价款与房屋补偿金的差价。

b.购买公有现住房的，贷款额度不超过所购房屋价格的70%；在农村集体土地上建造、翻建、大修自有住房的，贷款额度不超过其所需费用的70%。

③按照住房公积金账户余额计算的贷款额度

a.购买限价商品住房或经济适用住房，贷款额度不得高于职工申请公积金贷款时住房公积金账户余额（同时使用配偶住房公积金申请公积金贷款，为职工及配偶住房公积金账户余额之和，下同）的20倍，住房公积金账户余额不足2万元的按2万元计算。

b.贷款购买首套自有住房的，贷款额度不得高于职工申请公积金贷款时住房公积金账户余额的20倍，住房公积金账户余额不足2万元的按2万元计算。

c.以下情况贷款额度不得高于职工申请贷款时住房公积金账户余额的10倍，住房公积金账户余额不足2万的按2万计算：贷款购买第二套住房的；购买公有现住房的；在农村集体土地上建造、翻建、大修自有住房的。

④按照贷款最高限额计算的贷款额度

使用本人住房公积金申请住房公积金贷款的，贷款最高限额60万元；同时使用配偶住房公积金申请住房公积金贷款的，贷款最高限额80万元。

申请贷款时职工或其配偶正常缴存按月住房补贴的，参照正常缴存补充住房公积金的规定执行。

计算出的贷款额度数值保留到千位，千位以下不为零的千位加一。

扩展资料：

还款方式

按还款方式又分为等额本息还款方式和等额本金还款方式两种。

等额本息还款方式是在还款期内，每月偿还同等数额的贷款(包括本金和利息)，这样由于每月的还款额固定，可以有计划地控制家庭收入的支出，也便于每个家庭根据自己的收入情况，确定还贷能力。

等额本金还款方式是将本金每月等额偿还，然后根据剩余本金计算利息，所以初期由于本金较多，从而使还款额在初期较多，而在随后的时间每月递减，这种方式的好处是，由于在初期偿还较大款项而减少利息的支出，比较适合还款能力较强的家庭。

等额本息还款法也叫月均法，本金归还速度较慢，还款压力较轻，代价是支付的总利息较多，与等额本金还款方式对比，总利息差额在中短期（1-5年）的期限不会十分明显，在长期限内（20-30年），总利息差额才明显。

无论等额本金还款是等额本息还款，每期的利息计算方法是一致的，等于剩余本金乘以月利率。

选择什么还款方式，要看个人还款能力，不可贪图支付总利息少就选等本金还款法，实际操作中，不少人还是选择等额本息还款法。

参考资料：住房贷款-百度百科

住房贷款勿入四大雷区

许多人都知道，办理贷款的第一步便是通过银行的审核。限购限贷的大背景下，银行对于贷款的审批也是愈加严格，老百姓们稍不注意，便会踩到“雷区”，被银行“拒之门外”。年中过后，新一轮的贷款热度席卷而来，老百姓们越来越关心自己的贷款资格问题，小编总结了四种贷款“雷区”，与广大借款人们分享。

雷区一：不良信用记录

案例：客户张先生是一名公司高管，收入与各方面条件均不错，年中他看中了一套房子，打算申请商业贷款，然而在银行征信系统里被查出有信用卡逾期记录，因此张先生的置业梦想只能就此“搁置”。

专家解析：“伟嘉安捷”指出，借款人在申请使用贷款时，第一步便是通过银行征信系统的审核，而对于信用卡逾期或未还的情况，是有可能遭到银行“拒贷”的，按照规定，信用卡连续3次或累计6次逾期还款的情况，将有可能吃到银行贷款“闭门羹”。虽然张先生其他方面的资质很好，但是信用卡的不良记录，使他依旧无法获得银行贷款。眼下，办理信用卡的人越来越多，而银行对于个人征信的审核也是愈加严酷，因此征信问题是最容易造成银行“拒贷”的原因。另外，“伟嘉安捷”提醒广大借款人，除去信用卡逾期问题，借款人贷款购房的月供也要及时还款，月供若出现连续3次或以上逾期，也会被记为不良的信用记录，因此个人的征信问题，是借款人申请贷款的“重要门槛。”

雷区二：外地来京者纳税未满5年

案例：小王是山东人，来北京打工3年，但是由于单位不够正规，因此一直没有给小王办理社保，小王打算利用3年的积蓄买一套小户型房屋，但是却被告知尚无购房资格，更没有办法申请贷款，为此小王一筹莫展。

专家解析：“伟嘉安捷”认为，小王作为外地在京人士，目前的资质是显然无法获得银行贷款的，按照规定，外地购房人士只有在提供北京市工作居住证、满五年不断档的社保证明或者满五年的纳税证明后，才可以拥有购房资格，申请使用银行贷款。所以对于外地在京人士不能提供以上材料的，贷款购房必然踩到“雷区”。

雷区三：未还清的贷款

案例：刘小姐目前在三元桥拥有一套贷款购得的两居室，正处在还贷期，年中她打算“卖小买大”，使用贷款购买一套大三居，然而在贷款申请时同样被银行“拒之门外。”

专家解析：“伟嘉安捷”告诫广大借款人，按照现行规定，如果打算再次使用贷款，必须先偿还目前现有贷款，对于置业升级人群来说，若第一套房屋是使用贷款所购，那么必须在保证贷款全部偿还的情况下，才可以再次使用贷款购房，否则便会遭遇银行的“拒贷”。

雷区四：第三套房屋停贷

案例：孙先生是一家民企的老板，名下拥有2套房产，由于生意周转，孙先生曾经将自己名下的两套房屋全部用作抵押贷款，今年他卖掉了其中一套房屋，也结清了另一套房屋的贷款，然而，他再次打算贷款购买新房时，他的贷款计划却被银行按下了“急刹车”。

专家解析：“伟嘉安捷”认为，今年“国五条”新政出台后，众所周知第三套房贷款被叫停，然而很多人不清楚究竟何种情况算作“第三套房停贷”，案例中孙先生认为自己目前只有一套住房，而且已经还清了贷款，便可以再次贷款购房，然而“伟嘉安捷”指出，目前贷款严格执行“认房又认贷”，所谓“第三套房停贷”，是指只要借款人名下拥有过2套住房或2次购房贷款记录，那么无论借款人目前名下有几套房产，再次贷款均会被算作“第三套住房，”贷款购房将面临全部“叫停”，因此借款人应严格注意自己的住房套数，以免触碰到贷款“雷区”。

（以上回答发布于2015-11-12，当前相关购房政策请以实际为准）

买新房，就上搜狐焦点网

案例：三类刚需购房者贷款购房的省钱攻略

购房贷款可以想办法省钱，但是怎么省钱却也是因人而异。对于刚需购房者，省钱更是特别需要考虑的问题，那么究竟该如何省钱呢？给三个案例具体分析下。

专家分析认为，有三类刚需的购房人可以考虑这些省钱的贷款“高招”：

首次购房人群

代表人物：小沈 外地人 某大型公司高薪白领

典型案例：小沈是山东人，毕业后一直留京工作，现就职于一家大型软件公司工作，月薪15000元，收入稳定。小沈目前一直租房居住，月租金2000元，小沈打算以后定居北京，他觉得一直租房居住不是回事，所以准备在城区内淘一套小户型房屋，但是小沈现在手头积蓄加上父母的资助也只有100万元，难以实现全款购房，只能使用银行贷款，他自己有着中长期的提前还款计划，小沈想知道在贷款方式中有没有省钱妙招。

贷款方案：商业贷款 银行及还款方式寻突破

专家指导：首先，对于小沈这样的外地购房人，根据新政目前银行一般要求其提供在京一年以上的纳税或社保缴纳证明，而小沈所在的公司可以提供这些证明。其次，对于首次购房人群，目前商业贷款和公积金贷款在首付方面无差别，只是利率有略微的差别，但是商业贷款可以贷款的额度比较高，因此专家建议小沈选择商业贷款，目前部分银行对首套房的借款人政策相对实惠，仍可申请到下浮30%的优惠利率。

在贷款方式上可以选择等额本金还款，虽然前期还款压力相对等额本息大一些，但是从长远来看，等额本金还是比较节省利息的。再加上小沈有提前还款的打算，等额本金更适合小沈使用。

在购房方面，专家建议小沈可以选择地铁沿线及商圈附近的小户型房屋，便于其上下班出行。在贷款方面，可以选择贷款额在80万元左右，采用等额本金还款的话，第一个月还款6093.78 元，还款额在小沈月收入的50%以内，而等额本金月还款额以后逐月递减，后期还款压力会越来越少，比较适用于他。

置业升级人群

代表人物：小刘 小赵 北京人 公司高层管理人员

首套房全款购买

典型案例：小刘是北京人，现任某公司高层管理人员，收入颇丰。小刘和爱人结婚多年，两人名下都没有过贷款购房记录，小刘现在居住的房子为当初父母以小刘的名义全款购买的，该房产位于通州区，房子较旧且面积较小。由于小刘和爱人准备要小孩，所以再换套房子，小刘看上了一套总价为200万元左右的大两居。但小刘现在手头资金只有40万元，父母资助50万元，名下的这套房产现在市场价约为120万元。

贷款方案：卖旧买新 一步到位实现置业升级

专家指导：像小刘这种名下已有住房的置业升级人群，根据目前“认房又认贷”的二套房政策，如果再次购房有可能将被认定为第二套住房。专家建议小刘最好还是采取“卖旧买新”全款买房的置业升级计划。在购房方面，因为小刘购房的目的是为了日后生孩子的需求，不如一步到位选择学区周边或者离重点小学较近的区域购房，做好长远打算。

首套房贷款购买

典型案例：小赵是北京人，现任某外企公司人力资源部经理，其名下有一套婚前贷款购买的住房，贷款已于前年结清，现市场价约为150万元。眼看着小赵的儿子已经到了快上小学的年龄，小赵想接父母过来接送孩子，可是现在居住的小两居明显不够三代人共同居住使用，所以小赵想贷款再买套大点的房子。小赵夫妻现有存款 60万元，小沈不知道如何贷款购房能满足自己的需求。

贷款方案：“1+1”式置业 公积金二套房更省钱

专家指导：像小赵这种有过贷款购房记录的置业升级人群，从目前的政策来看，即使将这套房产出售，央行征信系统仍会保留其贷款记录，所以只要小赵再贷款购房，都会被认定为二套房，首付、利率均上调。所以建议小赵选择时下年轻人较为流行的“1+1”式置业升级模式，保留这套房子，再在附近买套小房子，这样一来可以减轻首付及还款压力。同时，还可以保持两代人生活上的独立性。

专家建议小赵可以在同小区选择一套房总价100万元的一居室，这样可以方便父母接送孩子。在贷款方面，小赵的公司有为其缴纳公积金，建议选择在二套房贷款上优势较大的公积金贷款，因为在商业贷款和公积金贷款首付相差无几的情况下，公积金贷款利率优惠确实能够让借款人省钱不少，小赵的情况大约能申请到60万元的公积金贷款。

（以上回答发布于2015-12-11，当前相关购房政策请以实际为准）

点击查看更全面，更及时，更准确的新房信息

Excel读书笔记29——贷款管理表——贷款利息计提与查询的全自动管理示例

在具有一定规模的企业中，除了会计核算和财务管理以外，融资管理也是一项严谨而重要的工作。特别是融资批量较大的时候，不同授信期限、不同融资额度、不同还款到期日以及到期应付金额等信息的整理与统计，往往弄得资金管理人员疲于奔命。

本节中，我们以逸凡公司的授信及贷款情况为例，讨论如何用Excel来设计一套信息量强大的“贷款管理统计表”（参见示例文件“表5-4 贷款管理统计表”）。

一、基本框架与功能展示

“贷款管理统计表”由授信台账（见图5-71）和贷款台账（见图5-72）构成。

图5-71 授信台账

图5-72 贷款台账

贷款台账将担负起每笔贷款信息记录和未来应付总额查询预警的重任。

我们的目标是：只需要在授信台账中手工录入授信合同号、金融机构、授信总额度以及授信期限，在贷款台账手工录入授信合同号、贷款合同号、贷款本金、贷款起始日、期限、年利率以及结息周期等基础信息，就可以实现以下功能。

1.主要信息功能

（1）自动显示每笔授信当前已用额度、可用额度及所有授信总体可用额度。

（2）自动显示当前贷款总额、每笔（及全部）贷款本月应付利息和应付本息总额。

2.辅助信息功能

用户录入自定义期间后（不大于30天），自动提示未来该天数内将要到期应付的本金、利息及本息总额，并对具体明细记录予以标识。

二、基本前提及假设

1.授信、融资期限以月为单位

一般情况下，金融机构为企业提供的授信期限及融资期限均以整月（或可折算为整月）为单位进行计算。例如半年（6个月）、一年（12个月）等。但是也有个别以天（无法折算为整月）计算的情况。例如100天等。本节内容仅考虑相关期限可以以月为单位进行计算的情况。

2.贷款本金在到期日一次性偿还

在贷款本金的归还问题上，对于大多数企业和金融机构来说，都还是习惯采用到期日一次性偿还的方式。故本节内容就不考虑提前偿还、分期偿还、展期偿还、无力偿还甚至耍赖不还等非主流情况了。

3.利息结算周期仅仅考虑月度和季度两类，且贷款期限大于结息周期

月度结息和季度结息是目前最广泛采用的结息方式。为避免讨论的事项过于繁杂，本节内容只考虑上述两类结息方式。且当贷款期限小于3个月时，只能采用月度结息方式。

4.贷款到期日与结息规则

需要声明的是，以下结息规则为我国金融机构现行通用规则，不属于假设。如果政策有变动，再将它们降级为假设吧。

（1）贷款（授信）到期日。

贷款（授信）到期日和定期存款到期日不同，存款到期日一般为对年（月），而贷款到期日一般为对年（月）的前一天。例如，同样以2013年1月1日为起始日，且期限为一年，定期存款的到期日为2014年1月1日，而贷款的到期日一般为2013年12月31日。

（2）结息付息日。

1）月度结息付息：每月20日为结息日，次日付息。

即上月21日～本月20日的贷款利息，在本月21日支付，以此类推。当然，前提是贷款的起始日早于上月21日且贷款的到期日晚于本月的20日。

2）季度结息付息，每季度末月20日为结息日，次日付息。

即上季度末月21日～本季度末月20日的贷款利息，在本季度末月21日支付。当然，前提是贷款的起始日早于上季度末月21日且贷款的到期日晚于本季度末月的20日。

3）贷款到期当日，结算并支付尚未支付的利息。

（3）计算日利息时，全年按360天计算。

有资金管理经验的读者朋友应该发现过一个现象：一笔贷款实际支付的利息总额，总会略大于按贷款利率计算的利息总额。这是因为在贷款期限内每次结息采用的日利息，是以一年360天计算的，但是全年有365（或366）天。这样，实际支付的年利息就等于贷款本金×年利率×（365÷360）了。

（4）计息起止日。

从贷款到期日的规则可以看出，贷款计息按是资金实际占用天数计息的（而不是定期存款按算头不算尾模式计息），故计息起止日计算的原则有两条：计息起始日为贷款起始日与上期结息日次日的较晚者；计息截止日为本期结息日与贷款到期日的较早者。

具体来说，就是：贷款起始日早于上期结息日次日时，如果贷款到期日晚于本期结息日，则本期计息起止日为上期结息日次日至本期结息日；如果贷款到期日早于本期结息日，则本期计息起止日为上期结息日次日至贷款到期日。

例如：当前为2014年7月11日，某笔贷款于2014年1月15日发放，一年期，月度结息。则其贷款起始日（2014年1月15日）早于上期结息日次日（2014年6月21日）且贷款到期日（2015年1月14日）晚于本期结息日（2014年7月20日），故本期计息起止日为2014年6月21日～2014年7月20日。

如果上述借款为半年期，则其贷款到期日（2014年7月14日）早于本期计息日（2014年7月20日），故本期计息起止日为2014年6月21日～2014年7月14日。

贷款起始日晚于上期结息日次日时，如果贷款到期日晚于本期结息日，则本期计息起止日为贷款起始日至本期结息日；如果贷款到期日早于本期结息日，则本期计息起止日为贷款起始日至贷款到期日。后面这种情况只可能在贷款期限小于一个月时才会出现，所以属于小概率事件。

例如：当前为2014年7月11日，某笔贷款于2014年6月28日入账，一年期，月度结息。则其贷款起始日（2014年6月28日）晚于上期结息日的次日（2014年6月21日）且贷款到期日（2015年6月27日）晚于本期结息日（2014年7月20日），故本期计息起止日为2014年6月28日～2014年7月20日。

三、注意事项

为了便于后续拓展及查询工作，在手工录入相关参数时应注意保持名称的统一（例如“金融机构”中涉及某银行名称时，应统一名称规格，避免同一个金融机构出现多种名称）。

在本章第一节中，我们曾经提到过用数据有效性中的序列设置来杜绝“同物不同名”情况的发生。比如贷款台账中的结息周期就可以用这一招限定只能录入“月度”和“季度”。但是对于金融机构而言，这一招就不太好使了，因为金融机构这个参数填写的范围不具有较强的限定性，你不可能预知是否会有新的机构出现，这也就无法设计备选选项了。所以这里我们只能强调填写信息时的规范了。

四、知识点装备

在阅读本节下面的内容前，请各位读者朋友首先确认大脑中是否已经基本装备了图5-73中的相关知识点。

在开始讲解本节案例前，我们插播一个即将用到的知识点：定义和使用名称。

通俗地说，定义名称类似于数学中常用的“设X=某信息”，这里的“某信息”可以是常量、单元格区域或者公式等。设置完成后，我们即可用“X”来实现其被赋予的“某信息”的功能。使用名称可简化复杂的公式，使其更加容易理解和维护。下面，我们举例简单说明。

图5-73 相关知识点

在图5-74的订货单中，我们将总金额的公式使用定义名称将其设为“总金额”。操作步骤为：【公式】选项卡→“定义的名称”组→定义名称（见图5-74）。

图5-74 定义名称的使用

此时，我们就可以用定义的名称来设置总金额公式（见图5-75）。

D3:D5单元格区域的公式均为：=总金额

图5-75 用定义的名称代替传统公式

使用定义名称和设置辅助参数类似，可以将冗长的复合公式分步简化，从而增强复合公式的可理解性。后文将有具体应用。

五、主要信息的公式设计方法

在对“贷款管理统计表”的框架、功能和相关注意事项有了大致的认识后，我们就通过逸凡公司贷款的案例来讨论“贷款管理统计表”的设计了。

【案例5-4】逸凡公司截至2014年7月11日获得金融机构的授信情况如图5-76所示，获得贷款情况如图5-77所示，单位均为元。

图5-76 逸凡公司授信信息表

图5-77 逸凡公司贷款信息表

结合“贷款管理统计表”结构，相关设计方法如下。

1.授信台账公式设计方法（上）

（1）即时日期显示（D2单元格）。

要让表格始终显示“今天”的日期，在保证计算机系统日期无误的情况下，我们只需要用到一个简单的日期公式即可（见图5-78）。

D2单元格的公式为：=TODAY( )

图5-78 设置永远是“今天”的公式

（2）授信到期日（H4:H8单元格区域）。

这个参数的公式也比较简单，我们在本章第三节中就已经见过类似的情况了。只不过本表中需要将到期日精确到具体的日期，而不仅仅是月份。需要注意的是，由于贷款到期日不是按对年（月）方式计算的，所以需要在日参数中减去1。

H4单元格的公式为：

=IF(OR(F4="",G4=""),"",DATE(YEAR(G4),MONTH(G4)+F4,DAY(G4)-1))

执行列填充后，即可完成授信到期日公式的设置（见图5-79）。

现在，授信台账中还有已用额度（D4:D8单元格区域）、可用额度（E4:E8单元格区域）以及当前可用授信额度（G2单元格）三个参数尚未设计公式。由于这三个数据在发生贷款业务后才能体现其作用，所以我们稍后再回来探讨。

2.贷款台账公式设计方法

（1）即时日期显示（A3单元格）。

这个没什么可说的了。A3单元格的公式为：=TODAY( )

图5-79 授信到期日公式

（2）金融机构（C7:C16单元格区域）。

由于金融机构是授信台账中的已有数据，所以没有必要重复录入，用VLOOKUP函数来查找引用。同时，我们还需要考虑不小心输入了不存在的授信合同时（此时无匹配的金融机构），公式应给予及时提示。

C7单元格的公式为：

=IF(A7="","",IFERROR(VLOOKUP(A7,授信台账!$A$4:$B$8,2,0),"授信合同号不存在"))

执行列填充后，即可完成金融机构信息的匹配（见图5-80）。

图5-80 VLOOKUP函数自动匹配关联信息

如果用户还需要在贷款台账中看到对应授信合同下的授信到期日、授信总额度等信息，也应采用同样的方式，在此就不再赘述。

（3）贷款到期日（G7:G16单元格区域）。

思路和授信台账中到期日的公式是一样的。

G7单元格的公式为：

=IF(OR(E7="",F7=""),"",DATE(YEAR(F7),MONTH(F7)+E7,DAY(F7)-1))

执行列填充后，即可完成贷款到期日的计算（见图5-81）。

图5-81 贷款到期日的公式

（4）本月计息起始日（T7:T16单元格区域）。

下面我们将进入本月应付本息和本月应付利息等数据的公式设计。我们知道，计算利息的一个重要前提是要确定计息的天数，由于本期计息的截止日是明确的（本月结息日或贷款到期日），所以，我们需要来看看如何计算本月计息的起始日。

本月计息起始日可以理解为本月之前最后一次结息日（称为“上次结息日”）的次日。由于它只是一个辅助性参数，为了避免辅助性参数影响到重要信息的清晰读取，我们将其放置在主表之外的区域（T列）。

从计息规则我们知道，本月计息起始日将根据贷款起止日及结息周期的不同而不同，具体情形如图5-82所示。

图5-82其实已经给我们提供了公式设计的思路和逻辑。为避免最终的公式过于冗长，在设计公式前，我们可以对几个即将频繁使用的指标的公式进行定义名称设置（见图5-83）。

图5-82 本月计息起始日的各种情况

图5-83 本节定义名称的参数（一）

第一步：净化环境。

从图5-81可知，当期限、贷款起始日以及结息周期三个参数中，只要缺席一个，就无法推算本月计息起始日。所以我们得出：

T7单元格的第一步公式为：

=IF(OR(E7="",F7="",I7=""),"",进入第二步)

第二步：确定贷款已在本月前归还的情况。

由于我们假设贷款均在到期日一次性还清，所以第二步的关键是判断贷款在本月之前是否已经到期偿还。

如果贷款到期日不晚于上月最后一天（G7=EOMONTH($F$2,-1)），那么说明该笔贷款已经在本月之前偿还，不需再考虑本期计息。假设我们要求在此种情况下，本期计息起始日显示为“已结清”。于是我们可以得出：

T7单元格的第二步公式为：

=IF(G7=EOMONTH(本月结息日,-1),"已结清",进入第三步)

注意，此处涉及位于第3行的单元格，需要通过设置绝对引用以确保后续列填充的正确。后面的公式设计也应遵循该原理。

第三步：确定月度结息模式下结息。

由于月度结息模式和季度结息模式对应的上次结息日次日是有差异的。月度结息模式下，上次结息日次日就是上个月21日，而季度结息模式下，上次结息日次日则是上一个季度末月的21日。所以进入第三步后，我们只能以结息周期来分步讨论了。

T7单元格的第三步公式：

=IF(I7="月度",IF(F7=上月结息日,上月结息日次日,F7),进入第四步)

第四步：确定季度结息模式下结息。

从前面的步骤可知，进入第四步则意味着是季度结息模式下尚未归还的贷款了。

季度结息与月度结息一样，仍然是要判断贷款起始日与上次结息日的先后关系。只是，季度结息模式下的上次结息日稍微麻烦点，它必须是最近一个季度末月的结息日。

那么，我们怎么来识别季度末月？找逻辑规律——季度末月的月份数，一定是3的整数倍。

于是，我们可以看到这样的规律：

季度末月的月份数除以3，余数为0。

季度末月后的第一个月份数除以3，余数为1。

季度末月后的第二个月份数除以3，余数为2。

我们再将这个规律反其道而行之：

季度末月后的第一个月份数减去1，为上季度末月月份数。

季度末月后的第二个月份数减去2，为上季度末月月份数。

季度末月的月份数减去3，为上季度末月月份数。

用当前月份减去当前月份数除以3的余数，就可以算出上个季度末月。但是有个例外，当余数为0时，我们需要减去3。于是，我们就需要用到专职计算余数的MOD函数了。

T7单元格的第四步公式为：

=IF(F7=DATE(YEAR(本月结息日),MONTH(本月结息日)-IF(MOD(MONTH(本月结息日),3)=0,3,MOD(MONTH(本月结息日),3)),DAY(本月结息日)), DATE(YEAR(本月结息日),MONTH(本月结息日)-IF(MOD(MONTH(本月结息日),3)=0,3,MOD(MONTH(本月结息日),3)),21),F7)

将上述各步骤公式合并。

T7单元格的完整公式为：

=IF(OR(E7="",F7="",I7=""),"",IF(G7=EOMONTH(本月结息日,-1),"已结清", IF(I7="月度",IF(F7=上月结息日,上月结息日次日,F7),IF(F7=DATE(YEAR(本月结息日),MONTH(本月结息日)-IF(MOD(MONTH(本月结息日),3)=0,3, MOD(MONTH(本月结息日),3)),DAY(本月结息日)),DATE(YEAR(本月结息日), MONTH(本月结息日)-IF(MOD(MONTH(本月结息日),3)=0,3,MOD(MONTH(本月结息日),3)),21),F7))))

执行列填充后，即可得各项贷款的本月计息起始日（见图5-84，图5-84a为6月示例，图5-84b为7月示例）。

图5-84 本月计息起始日的公式

（5）本月应付利息（K7:K16单元格区域）。

本月应付利息是指将在本月实际支付的利息，其计算公式为：

本月应付利息=（贷款本金×年利率÷360）×计息期间天数=日利息×计息期间天数

结合本节基本假设及前提板块中的信息，我们可知计息期间将根据贷款起止日情形的不同而不同，具体情形如图5-85所示。

图5-85 本月计息期间的各种情况

此外，我们还应该考虑到：

1）本月应付利息包含本月结息日付息以及本月到期日付息。

本月结息日付息是指在本月结息日结算并支付的利息，本月到期日付息是指本月因贷款到期而在贷款到期日结算并支付的利息。当贷款到期日恰好是当月结息日时，我们认定所有利息均为结息日付息。

2）对于季度结息模式下的贷款，只有在季度末月时才会在结息日付息。

据此，我们设计的步骤如下。

第一步：净化环境。

从图5-85的相关信息中可知，当贷款到期日、结息周期为空白，以及贷款到期日在本月前（此时本月计息起始日显示为“已结清”）时。本月应付利息为0。

K7单元格的第一步公式为：

=IF(OR(G7="",I7="",T7="已结清"),0,进入第二步)

第二步：贷款在本月到期情况下的本月应付利息。

判断贷款到期日是否在本月，又是一个比较贷款到期日和本月结息日两个日期是否同年同月的问题。如果同月，则本月应付利息的计息期间为本月计息起始日的次日至贷款到期日。

需要提示大家注意的是，由于计息期间的起始日和截止日都应计息，故计算计息天数时，应该在两个日期相减后，再加上1。

例如：计息起始日为2014年1月1日，计息截至日为2014年1月3日，共3天，但是两个日期之差为2，故需要加上1。

K7单元格的第二步公式为：

=IF(12*YEAR(G7)+MONTH(G7)=12*YEAR(本月结息日)+MONTH(本月结息日),ROUND(D7*H7*(G7-T7+1)/360,2),进入第三步)

第三步：贷款在本月后到期情况下的本月应付利息。

如果贷款在本月后到期，则本月应付利息需要根据结息周期模式区分，当贷款为季度结息模式且本月为非季度末月时（AND(I7="季度",MOD(MONTH(A3),3)0)），则不付息。否则本次结息期间为本月计息起始日至本月结息日。

K7单元格的第三步公式为：

=IF(AND(I7="季度",MOD(MONTH(本月结息日),3) 0),0,ROUND(D7* H7*(本月结息日-T7+1)/360,2))))

将上述各步骤公式合并。

K7单元格的完整公式为：

=IF(OR(G7="",I7="",T7="已结清"),0,IF(12*YEAR(G7)+MONTH(G7)=12*YEAR(本月结息日)+MONTH(本月结息日),ROUND(D7*H7*(G7-T7+1)/360,2), IF(AND(I7="季度",MOD(MONTH(本月结息日),3) 0),0,ROUND(D7*H7*(本月结息日-T7+1)/360,2))))

执行列填充后，即可得各项贷款的本月应付利息（见图5-86）。

单从长度看，本月应付利息的公式似乎并不繁琐。但是请注意，如果没有本月计息起始日（T7单元格）甘为人梯的奉献，那么，本月应付利息的公式中所有涉及T列的单元格参数，都必须替换为本月计息起始日的完整公式。那样，规模就不是一般得庞大了。

就像攀登珠峰需要在途中建立若干大本营一样，当发现设置某个公式的逻辑层次和分支较多时，应立即启用若干中间信息（比如计算本月应付利息时，本月计息起始日就属于一个中间信息）作为大本营，起到公式设计过程中步步为营化繁为简的作用。以避免攻克公式高峰的过程中大脑死机。当然，定义名称也可以看作一种建立大本营的方式。

图5-86 本月应付利息的公式

（6）本月结息日付息（L7:L16单元格区域）与本月到期日付息（M7:M16单元格区域）。

之所以要把本月应付利息区按其支付时间予以区分，是考虑到一般情况下，当月的结息日会集中支付大量的利息。所以，我们有必要高度关注结息日付息的情况。而且我们在实现查询未来若干天内应付本息功能时，也需要对付息的时间进行区分。

根据上述关系我们可以知道：

本月结息日付息+本月到期日付息=本月应付利息

所以，在已经算出本月应付利息的情况下，结息日付息和到期日付息这两个参数只需要知道其中的一个，另外一个就可以用倒算法解决了。在本案例中，我们将对结息日付息的公式进行分析讨论。

从计息规则我们可以知道，结息日付息的相关影响因素如图5-87所示。

图5-87 到期日与到期日结息期间的关系

虽然图5-87中出现了六种情况，但实际上本月结息日只有三种可能：不付息，付息金额等于本月应付利息，付息金额等于上期结息日次日至本月结息日的应付利息。根据上述逻辑，我们的设计步骤如下：

第一步：本月结息日不付息的情况。

当出现下列情况之一时，本月结息日不付息：

1）本月应付利息为0。

2）贷款到期日在本月结息日之前。

3）季度结息模式下，本月非季度末月。

L7单元格的第一步公式为：

=IF(OR(K7=0,G7本月结息日,AND(I7="季度",MOD(MONTH(本月结息日),3) 0)),0,进入第二步)

第二步：本月结息日付息等于本月应付利息的情况。

如果贷款到期日在本月以后，则说明本月不会发生到期日付息。于是，本月结息日付息等于本月应付利息。否则，就说明贷款到期日在本月结息日后且次月之前。此时本月结息日付息金额就等于上期结息日次日至本月结息日的应付利息。

L7单元格的第二步公式为：

=IF(G7EOMONTH(本月结息日,0),K7, ROUND(D7*H7*(本月结息日-T7+1)/360,2))

将上述各公式合并。

L7单元格的完整公式：

=IF(OR(K7=0,G7本月结息日,AND(I7="季度",MOD(MONTH(本月结息日),3) 0)),0,IF(G7EOMONTH(本月结息日,0),K7,ROUND(D7*H7*(本月结息日-T7+1)/360,2)))

此时，我们再倒算设置到期日付息公式。

M7单元格的公式为：=K7-L7

执行列填充后，即可得出结息日付息及到期日付息（见图5-88）。

图5-88 结息日付息与到期日付息的公式

（7）本月应付本息（J7:J16单元格区域）。

本月应付本息也就是本月应付的贷款本金和利息之和。其中，本月应付利息已经在K列得出，所以，问题的关键是本月应付本金。

如果本月需要支付贷款本金，则说明贷款将在本月到期，这又用到了贷款到期日所在年月与当前年月是否相等的判断。如果相等，则本月应付本息就等于贷款本金加上本月应付利息，否则，就只考虑本月应付利息。

N7单元格的公式为：

=IF(G7="",0,IF(12*YEAR(G7)+MONTH(G7)=12*YEAR(本月结息日)+MONTH(本月结息日),D7+K7,K7))

执行列填充后，即可得本月应付本息（见图5-89）。

图5-89 本月应付本息的公式

（8）当前贷款总额（B3单元格）。

当前贷款总额是尚未到期的贷款本金之和，计算逻辑自然是判断贷款到期日是否晚于“今天”。这当然得靠SUMIF函数来实现了（见图5-90）。

B3单元格的公式为：=SUMIF(G7:G16,""A3,D7:D16)

图5-90 当前贷款总额的公式

（9）本月应付利息（C3单元格）及本月应付本息（D3单元格）。这个就直接上SUM函数了。

C3单元格的公式为：=SUM(K7:K16)

D3单元格的公式为：=SUM(J7:J16)

当然，还可以设置本月结息日应付利息等公式以满足管理需要，不再赘述。

至此，贷款台账的主要信息公式设置完毕。

接下来，我们再回头去完成授信台账中尚未竣工的工程。

3.授信台账公式设计方法（下）

（1）已用额度（D4:D8单元格区域）。

已用额度即相关授信合同中，已经取得贷款且尚未偿还的金额。这个定义包含了两层含义，首先要认准是自家合同下的贷款，不能张冠李戴把其他合同的贷款算到自家名下；其次要界定相关的贷款是不是还在占用中，因为已经到期归还的贷款将不再占用授信额度。

这样我们就知道，已用额度是一个多条件求和的问题了。

D4单元格的公式：

=SUMIFS(贷款台账!$D$7:$D$16,贷款台账!$A$7:$A$16,A4,贷款台账!$G$7:$G$16,""$D$2)

执行列填充后，即可得各授信合同的当前已用额度（见图5-91）。

图5-91 授信已用额度的公式

（2）可用额度（E4:E8单元格区域）。

可用额度给我们的第一感觉，自然是授信总额度减去已用额度了。但是，这只能是第一感觉，我们的第二感觉还应该立马想到，这个逻辑成立的前提是授信合同还未到期。这样，我们的公式才符合逻辑。

E4单元格的公式为：=IF(H4$D$2,C4-D4,0)

执行列填充后，即可得可用额度（见图5-92）。

图5-92 授信可用额度的公式

（3）当前可用授信额度（G2单元格）。

这个参数主要就是汇总可用额度了。

G2单元格的公式为：=SUM(E4:E8)

至此，授信台账的主要信息公式也彻底完工了（见图5-93）。

图5-93 授信台账的主要信息

关于房屋贷款案例表格和住房贷款例题的介绍本篇到此就结束了，不知道你从中找到你需要的信息了吗 ？如果你还想了解更多这方面的信息，记得收藏关注本站。