导航:首页 > 贷款平台 > excel公司贷款表

excel公司贷款表

发布时间:2022-09-15 09:40:52

1. excel表格中怎么设计一个分期偿还贷款表

按住ctrl键,用鼠标拖动sheet1工作表标签,或者直接右击工作表标签,复制工作表。

2. 货款利息计算表用Excel怎么做

一、等额本息贷款方式的计算

1、计算公式

利息=-IPMT(各期利率,第几期,总期数,本金)

本金=-PPMT(各期利率,第几期,总期数,本金)

每月因还金额=应还利息+应还本金

2、编制excel表格

如下图,编制表格。即可很清晰的计算出每月应当还的利息和本金。(当前执行利息指的是月贷款利息)

3. excel表格中怎么设计一个分期偿还贷款表

B6输入公式

=PMT(B3/12,B2,B1)

改变B1的金额,B2的期数,B3的年利率,B6就是每期应还金额。

4. 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(G7>EOMONTH(本月结息日,0),K7, ROUND(D7*H7*(本月结息日-T7+1)/360,2))

将上述各公式合并。

L7单元格的完整公式:

=IF(OR(K7=0,G7<本月结息日,AND(I7="季度",MOD(MONTH(本月结息日),3)< >0)),0,IF(G7>EOMONTH(本月结息日,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 授信台账的主要信息

5. 急求!小额贷款公司excel表格

关键是你想解决什么问题。是流水记录,还是客户资料,还是还款信用跟踪…………

6. 求一份小额贷款公司用的财务报表EXCEL模板,带公式,带自动求和的那种,感谢--

以下是全套财务报表带公式(相当不错):
查找必搜网,点常用软件(文字或图片)-管理经营-EXCEL财务表带公式
因网络拒绝发地址,只能委屈你自己去点出来了。
包含全套标准财务报表:
资产负债表、利润表(损益表)、现金流量表等等

邮件我也发了。
以下是全套财务报表带公式(相当不错):
查找必搜网,点常用软件(文字或图片)-管理经营-EXCEL财务表带公式
因网络拒绝发地址,只能委屈你自己去点出来了。
包含全套标准财务报表:
资产负债表、利润表(损益表)、现金流量表等等

邮件我也发了。
现在都去读AIA国际会计师公会了啊,有小伙伴们去免费领他们的词汇手册了吗?2013/12/19 10:03:47

7. Excel中通过这两个函数设计贷款还款时间表——PMT和EDATE!

在上一期,我们介绍了三个财务应用类的Excel函数——FV、PV和PMT,所涉及的案例是投资的计算。本期我们继续介绍PMT函数应用于贷款的还款额计算。

如下图所示,我们设计了一个关于贷款还款额的计算模型:

Loan Amount(C7单元格):贷款数额。

Annual Interest Rate(C8单元格):年利率。

Term in Years(C9单元格):还款年限。

Payment Frequency(C10单元格):还款频率。

First Repayment Date(C11单元格):首个还款日。

Periods Per Year(C12单元格):每年还款的期数。

Repayment Periods(C13单元格):还款总期数。

Rate per Period(G7单元格):每期利率。

Projected Interest(G8单元格):预计利息。

Total Payments(G9单元格):总还款额。

Total Interest(G10单元格):总利息。

Est. Interest Savings:预估节省的利息。

我们需要根据所提供的信息,计算出C15单元格中的“Monthly Payment(月还款额)”。

还款频率可以是按照每月还款,也可以按照每年还款。

我们先要计算出“Periods Per Year”:在C12单元格中输入函数IF,如果还款频率为“每月”,则得到每年还款期数为12,否则为1(即按照每年还款频率)。

在得到每年的还款期数的基础上,我们就可以计算出总的还款期数,在C13单元格中输入公式如下:每年的还款期数乘以总的还款年限。

同理,我们也可以计算出每期的还款利率,在G7单元格中输入公式如下:年利率除以每期的还款期数。

我们现在知道了还款额、每期还款利率、还款总期数,可以在C15单元格中通过PMT函数,计算出每月应还款的数额。

此类PMT函数中的两个可选参数,都不会用到,所以在此忽略即可,按Enter键后,即可返回结果。

因此处为还款额,所以符号用了“-”,如果我们不想看到此符号,在PMT函数前加上一个“-”(减号)即可。

在“Monthly Payment”的基础上,我们可以计算出“Projected Interest”,在G8单元格中输入公式如下:月还款额乘以总还款期数,再减去本金。

在下面的数据表格中,我们设计了一个还款的时间表。最开始的还款额为C7单元格中的贷款数额,首个还款日为C11单元格中的日期。

在首日还款日的基础上,我们计算出每期还款的时间,所用到的函数是EDATE,需要注意的是此例中EDATE函数的第二个参数需要根据C10单元格中的还款频率来确定,故我们通过IF函数来判定,如果是“Monthly”,则时间往后添加一个月,如果是“Annual”,则往后加12个月。

要确定最后一个还款日,我们需要根据“Balance”来判断,即当还款余额为0时,就无需再添加还款日了。因此在EDATE函数前再使用IF函数:当G20(上一还款日的余额小于等于0,当前还款日为空,否则继续执行EDATE函数)。

按Enter键后,B21单元格中返回为空,因G20单元格数据为0,但我们仍通过快速填充复制此公式。

在C20单元格中,我们来计算到期应付款项,所用的函数是IF,如果还款余额(Balance)加上利息,小于每期应还款额,则只需支付还款余额加上利息,否则应还款为“Monthly Payment”。

在E20单元格中,计算出每期应还的利息:本金乘以利率。

在F20单元格中,计算出到期应还本金:到期应还款额减去利息,再加上任何额外已付款项。

在G20单元格中,计算出剩余应还款额:上一余额减去每期应还本金。

选中F20和G20单元格,使用快速填充功能完成数据填充。

如果我们在第一个月有额外还款,所有的相关数据均会进行重算。

最后,我们也可以计算出G11单元格中的“预估节省的利息”:预估利息减去实际的总利息。

通过以上的案例,我们可以在Excel中利用财务类的函数以及其他的一些方法来设计一个贷款还款的计算模型以及时间表。其中重要的是,理解计算的过程所涉及到的相关参数,找到其间的互相联系与逻辑,以便我们在进行数据的运算或处理时更加得心应手。

8. 急求小额贷款公司财务报表格式EXCEL(自带公式)

发送小额贷款公司财务报表格6份,请查收。

9. 怎样用excel表格计算贷款还款明细

使用IPMT函数
说明

语法IPMT(rate, per, nper, pv, [fv], [type])
IPMT 函数语法具有以下参数 (参数:为操作、事件、方法、属性、函数或过程提供信息的值。):
Rate 必需。各期利率。
Per 必需。用于计算其利息数额的期数,必须在 1 到 nper 之间。
Nper 必需。总投资期,即该项投资的付款期总数。
Pv 必需。现值,或一系列未来付款的当前值的累积和。
Fv 可选。未来值,或在最后一次付款后希望得到的现金余额。如果省略
fv,则假设其值为零(例如,一笔贷款的未来值即为零)。
Type 可选。数字 0 或 1,用以指定各期的付款时间是在期初还是期末。如果省略
type,则假设其值为零。
Type支付时间0期末1期初

10. 银行贷款计算excel

用rate()函数算月利率,年利率=月利率×12
参数简单点如下

rate(期数,-月供,贷款额),注意:月供值为负,贷款额为正,或月供值为正,贷款额为负都行
比如贷款10000元10年共120期,月供100元,可在excel表格任意一个内输入
=rate(120,-100,10000)
可得到数字 0.008770092,即为月利率 0.8770092%
把它×12得到贷款利率为 10.5241109%
或一次性输入=rate(120,-100,10000)*12直接得到年利率10.5241109%

阅读全文

与excel公司贷款表相关的资料

热点内容
兴业消费金融发放的贷款 浏览:896
上海房贷贷款条件收入 浏览:731
做好公积金提取贷款工作的总结 浏览:617
股东互相担保贷款 浏览:762
农行住房贷款还贷日期 浏览:369
商业银行发放普惠型消费贷款包括 浏览:783
用小额贷款付首付合适吗 浏览:218
134万贷款月供是多少 浏览:795
二套房商业银行贷款利率 浏览:436
无工作怎么在平安贷款 浏览:808
贷款小额能办信用卡吗 浏览:541
父母老赖对子女买房贷款 浏览:810
北京博金信诚投资担保有限公司贷款服务 浏览:770
上海57岁贷款买房 浏览:298
银行贷款会查你的流水吗 浏览:259
打印贷款余额表需要带什么资料 浏览:896
房产贷款工作怎么样 浏览:484
国家贷款利率是涨还是跌 浏览:130
贷款消费和租赁消费的不同 浏览:128
江阴贷款二套房首付比例 浏览:188