用EXCEL函数解决会计准则中实际利率法应用问题

技能分享——巧用EXCEL函数，算清各种收益利率(二)五、RATE函数——计算投资或贷款的实际利率，适用于贷款利率利息这里举得例子是，假设房贷为50W，20年还清，采取的还款方式是等额本息方法。

1）公积金贷款，利率2018年1月1日基准利率为3.75%，经过房贷计算器计算，每月还款数额为2835.98Nper——期数，240期。

Pmt——每月还款额Pv——贷款总额500000Fv——未来值，贷款还清就没有了，所以为0。

Type——期初或是期末，这里还款其实是每月末之前就好，所以输入0。

经过计算的结果为0.271%，注意，这里计算出来的即为实际月利率，用它乘以12，就是年利率3.25%！六、XIRR——计算现金流内部回报率，适用于各种不定期不定额投资收益的计算这里以一组基金定投为例，比如格格每月设定定投1000元某基金，但是没有设置固定日期扣款，所以金额是固定的，但是日期不是每月固定的日期。

基金定投为负值，所以有很多1000，恰好在2018年1月时，该支基金分红220现金，所以此项为正值。

在2018年4月初，恰好大盘大跌了一下，所以格格有点闲钱，又一次性买入了10000。

而4月6号的市值为28000。

在EXCEL表格中输入以上的交易记录，选择插入函数X IR R，依然是财务下面的一个公式哈。

Values——选择金额一列Dates——选择日期一列，这里特别注意的是，日期一定要用年月日的规范格式，刚刚格格皮了一下，用了类似20170105这样的格式，结果是计算出错啦。

Guess——提示是可以输入一下近似收益值，事实上是填不填都一样的，不会影响计算结果。

点击确认，最后的计算结果是0.43，这也就是说在如上整个定投过程中，这支基金的年化收益率为43%。

七、IRR——计算一系列现金流内部回报率，适用于投资及分红的收益计算我们不管是投资一个房产或者是投资一个项目，这个公式就是非常好的预测回本时间及收益率的工具。

一个用excel算真实利率的简单方法元旦刚刚过去，转眼又到了春节的消费旺季。

工作了一年的你是否有大量的资金需要年底使用呢？如果资金较为紧张，银行的分期付款功能可在此时解决一时之需。

面对五花八门的分期还款方式，真实的利率究竟怎么计算呢？其实，用 EXCEL中的IRR公式就可以解决。

一、IRR，内部回报率（Internal rate of return），又叫内部收益率，是指项目投资实际可望达到的收益率。

简单的理解，就是用来计算借款的实际成本。

例如，办一个10万元的信用卡现金分期，年利率按6%计算，分12个月还清，以后每个月要还给银行8833.33元。

打开excel，在excel的第一列输入借款月份，从1-12，代表12个月。

然后第二列分别输入银行借的10万元，和每一期的还款额度8833.33元。

因为8833.33元是我每月要还给银行的金额，我手上的现金流变少了，所以用负数-8833.33来表示。

然后在最后一行的空白单元格里，输入公式：=IRR（B3：B15）*12就得到IRR，实际贷款利率啦。

最后得出的结论是，我这笔信用卡现金分期的实际利率是10.9%。

不仅是信用卡分期，IRR还可以用来计算房贷、车贷、网贷借款、基金、P2P等很多产品的真实利率。

二、IRR也可用于计算其它还款方式。

比如，有的贷款是先收手续费，然后每个月还本金，那利息怎么计算？比如，借款10万元，年利率仍按6%计算，先还手续费6000，本金再分12个月还清，每个月还8333.33元。

那么，我一开始借到手的现金流，不是10万，而是100000-6000=94000元。

IRR就是：如果是按月还本，最后一个月再付息呢；或者是每个月先付利息，最后一个月还本呢？大同小异哦~在使用信用卡分期消费或贷款时，用IRR这么一算，就一目了然了。

最后提醒大家，虽然银行的分期还款业务非常方便，但对于持卡人来说，“适度消费、量入为出”仍是最佳的用卡策略。

本文为头条号作者发布，不代表今日头条立场。

《经济计量学》上机实验3 Excel操作excel现值、终值、分期付款、利率及还款期一、现值计算PV、FV、PMT、RATE、NPER在Excel中，计算现值的函数是PV，其语法格式为：PV（rate，nper，pmt，[fv]，[type]）。

其中：参数rate为各期利率，参数nper为投资期（或付款期）数，参数pmt为各期支付的金额。

省略pmt参数就不能省略fv参数；fv参数为未来值，省略fv参数即假设其值为0，也就是一笔贷款的未来值为零，此时不能省略pmt参数。

type参数值为1或0，用以指定付款时间是在期初还是在期末，如果省略type则假设值为0，即默认付款时间在期末。

注：例题的练习方式是在Excel中描述问题，引用单元格求解。

如例1案例1：计算复利现值。

某企业计划在5年后获得一笔资金1000000元，假设年投资报酬率为10%，问现在应该一次性地投入多少资金？在Excel工作表的单元格中录入：=PV（10%，5，0，-1000000），回车确认，结果自动显示为620921.32元。

案例2：计算普通年金现值。

购买一项基金，购买成本为80000元，该基金可以在以后20年内于每月月末回报600元。

若要求的最低年回报率为8%，问投资该项基金是否合算？在Excel工作表的单元格中录入：=PV（8%/12，12*20，-600），回车确认，结果自动显示为71732.58元。

71732.58元为应该投资金额，如果实际购买成本要80000元，那么投资该项基金是不合算的。

案例3：计算预付年金现值。

有一笔5年期分期付款购买设备的业务，每年年初付500000元，银行实际年利率为6%.问该项业务分期付款总额相当于现在一次性支付多少价款？在Excel工作表的单元格中录入：=PV（6%，5，-500000，0，1），回车确认，结果自动显示为2232552.81元。

即该项业务分期付款总额相当于现在一次性支付2232552.81元。

例 二 ：２０ 年 １ １日，甲公 司与 乙公 ０５ 月 司 签订 一份 购货 合 同 ， 甲公 司从 乙公司 砸人 勾 台需要安装的特大型设备。合同约定 ，甲 公司采用分期付款方式支付价款。 该设备价 款共 计 ９０ 元 ， ２０ 年 至 ２０ 年 的 ５ ０万 在 ０５ ０９ 年 内每半年支付９ 万元 ， ０ 每年的付款 日期分别 为当年 ６ ３ 月 ０日和 ｌ 月 ３ 日。假定甲公司 ２ １ 郭长 城 河北金融 学院 河北 保定 ０ ０ ０ ７ ０ １ 的折现率 为 １％。经过计算得出的付款额现 ０ 值为 ５ ３ １０ ４ 元 ，确认 的融资费用合计 ５ ０ ．０ １ 率的条件下是不能确定各期问的利息收益和 ３６ ８９ ６ 元 （ 两 个数据 是用 下面 介绍 的 ４９ ８ ． ０ 这 【 文章 摘 要 】 本 金偿还 额 的 ，因为 它们 不是 唯一 的 ，这就 Ｅ Ｅ ＸＣ Ｌ表格 计 算 出来 的 ，比原 来 的结果要 实 际利 率 法 使 用 了贷 币时 间价 值 的 如同我们知道两个数可以计算它们的和， 但 准 确 ） 。 原，但 是 Ａａ ０ ０ ０ ９ ０ ０ ０元 Ｂ＝ ５ ０ １ ． ０元 ５３ ｌ０４ 其 计算 量 大 ，不 容 易计 算 准确 ，在 辨 析 是如 果知道 和 却不能 唯一地 分解 成两 个数一 本 ＝３ ６ ８ ９ ６ 其 原理 基 础 上 利 用Ｅ Ｃ Ｌ 助 进 行 计 算 ， 样 。在 已知现值 （ 金 ）和 利率 的条件 下各 ｂ ４ ９ ８ ．０元 ＸＥ辅 费用 ）和 本金偿 还额 都是 可 以提 高计 算 结 果 的 准确 性 ，减 少计 算 期 间的 利息 收益 （ 由发行 （ 出租和 出借 ）方事 先约 定 的 ，比如 二 、利用 Ｅ Ｃ Ｌ Ｘ Ｅ 辅助实际利率法计算 工作 量 。 债券 可 以到期 一 次还本付 息 ， 也可 以分 期付 对 于 给 出 本金 需 要 计算 内部 报 酬率 的 息到期还本，还可以每期还本付息等等。但 实 际利率 摊销 法 ，我 们可 以在 Ｅ Ｅ 【 关键词】 ＸＣ Ｌ中设 如果 没有 计如 下 图一这 样 的表格 。 面 图一 中的数据 实 际利 率 法 ；货 币时 间价 值 ； 企业 会 计 是进 行货 币时 间价值 分 析 的时候 ， 下 规定 ， 一般认 为付 息 方 式是和银 行 借款 一样 就是 例 一 的数据 。 准 则 ；Ｅ Ｃ Ｌ 本利 和 ＸＥ ； 分期 付息 ，到 期还 本 。 Ｄ ，Ｃ 一 Ｃ ５ 元格 中的 数据 是 已知 ３ ６ ～ １单 实际利率 法作 为￣ｆ 公允价值 计量 属 Ｉ －ｌ － 生 实际利率法就是按分期付息 、到期还 的 ，可 【 艮 据给 定 的数 据进 行 修改 。 的一种方 法 ，在 企 业会计 准则 ２０ ） 中得 本方式来计算利息的， ０６ 也就是每期的利息收 Ｃ 单 元填 入 “ Ｄ ” ５ ３ 到 最 为广 泛 的应用 。金 融资产 、金融 负债核 人 （ 费用 ）就 等于 它 的现值 （ 金 ）和 实 际 本 Ｆ 单元 填入 “ Ｄ３ ５ ＝ ” 算， 分期付款购入固定资产、无形资产核算， 利率 的乘 积。但 是按 现值 （ 本金 ）乘 以实际 Ｆ ３单元填人 “ Ｉ Ｒ（ ５ｃａ） Ｒ Ｃ ： ” ５ 融 资租入 核算 和其他 资产 的 延期付款 核算 等 利率计 算 出的 当期应 计利 息 和实 际收 到 （ 或 Ｄ 单元 填人 “ Ｆ ｝Ｆ ３ ， Ｄ ｆＤ１ ６ ＝ ５￥ ￥ ” 从 ６ Ｉ ５ Ｊ 都 规定 使用实 际 利率法 。但 是在 企业 会计 支付 ）的费用 可 能存在 差额 ，在考 虑货 币时 用填 充柄 填 充公 式 ， 准则 ２０）和 企业 会计 准则—— 应用 指南》 间价 值 的前提 下 ， 个差 额也是 要 按实 际利 ０６ 这 Ｅ 单元填人 “ Ｃ Ｄ ” ６ ６ ６ ，从 Ｅ 到Ｅ ５ ６ ｌ 中并没有 解释 过实 际利率 法 的原理 ，到 目前 率产生 利 息的 。换 句话 说如 果差 额为 正 ，认 用填 充柄 填 充公 式 。 出版 的讲 授 《 业会计 准则 ２０ ） 企 ０６ ）的教 材也 为是应收而未收的本金， 就要将这个差额加 Ｆ 单元 填入 “ Ｆ ６ ，从 Ｆ 到 Ｉ ５ ６ ５Ｅ ” ６ ７ １ 没有 做过 实际 利率法 的原 理分 析 ，都 只是在 到本 金上 合并计 算 下期 的利 息；如 果差 额为 用填 充柄 填 充公 式 。 设计 列 的具体 准则 中给与 举例说 明，造 成许 负， 认为是已经偿付 的本金 ， 就要从本金上 Ｄ１ 单 元填 人 “ Ｓ ６ ＝ ＵＭ（ ６ Ｄｌ） Ｄ ： ５” 多 会计人 员使用 实 际利率法 时 只会机 械地 套 减去这 个差 额后 计算 下期 利息 。这样 计算 出 Ｃ１ 单 元填 入 “ Ｓ ７ ＝ ＵＭ（ ６ Ｃ１） Ｃ ： ５” 用表格， 计算工作量大， 计算结果不准确。 笔 的利息就称为实际利息， 在分期付款的情况 Ｃ１ 单 元填 入 “ Ｄ ６ Ｄ ” ８ １＋ ３ 者从方便会计人员理解的角度，对实际利率 下也 叫做 “ 认 的 融 资费 用” 因为 实 际利 确 。 所 有 数字 单 元 格都 设 置 成 数值 型 ，保 法 的原理进 行 了解析 ， 且用 Ｅ Ｅ 设 计 了 息 是根 据 已知 的金融 资产 （ 并 ＸＣ Ｌ 负债 ）根据货 币 留两 位小 数 两 个关于 实际利 率法 的算法 表格 。 会计 人员 时间价值 的原理调整的， 也就是本金和利息 设计 完 成 这 个表 格 ，我 们 只需 在 Ｄ３ ， 在理 解原理 的基 础上 ，只 需填入 一组 金额 数 都考虑了进来， 所以这样计算的本利和等于 Ｃ 一 Ｃ１ 单 元格 中填入 已知 的数据 ，就可 ６ ５ 据，就可以自动准确地计算出摊余成本及各 实际金融资产 （ 负债）的金额合计。可以用 以计算出各期 的利息 、摊销成本和摊余成 期 的利息 收入 或利息 费用 。 下面 的公 式表 示这 个 原理 。 本。 Ａ＋ａ Ｂ＋ｂ 例三 ：假 定例 ～中 的债 券是 ８ 期 的 ， 年 实际利率法的原理 Ａ 已知金 融 资产 （ 债 ）本 金 负 公 司 支付 的价款 是 １０ 元 ，其他 条件 不变 ， ５０ 实 际利率法 是考虑 了货 币时间价值 因素 ａ已知金融资产 （ 负债 ）利息和计 我们在 Ｄ ，Ｃ Ｃ ５ ３ ６ １ 单元格中填入已知的 的计 量方 法。在 货 币时问 价值 的计算 中 ，有 Ｂ现 值 数据，就会得到如下图二的结果，立刻可以 以下 几个相 互关 联 的概 念 ：现值 （ 本金 ） 、本 ｂ实 际 利息 合计 》 转 ２ ４页 ２ 利和 、利率 、利息 、终 值和 期间 ，就单 个概 例一 ： ０８ １ １ ＸＹ ２０ 年 月 日， Ｚ公 司支付 念来说 它们 是很容 易理 解 的 ，但是 如果 考虑 价款 １０ 元 （ ００ 含交易费用）从活跃市场购入 骚 嗣 ｌ ５ ｉｊ ｌ 《 黼 熬曩ｉ 黎潮 曩 蒸 商 ｉ ｉ 蠡；ｌｋ 靠 ●ｌ秘 镰 ｉ 旺 酗 盏 蠡 了它 们之间 的相互 关 系理 解 起来就 有一 定的 某公司 ５ 年期债券， 面值 １５ 元，票面利率 ２０ 难度 ， 计算过程也很繁琐。实际利率法就运 ４７％， ．２ 按年支付利息 （ 即每年 ５ 元 ） 本金 ９ ， 要鼯利率睡 衰一 用了现值 （ 本金） 本利和、 、 利率和利息之间 最后一息支付 ，计算出的实际利息合计 ５ ５ ４ 秣 蹋 ＿ 正Ｊ （． 搬蚺 一 ｓ∞Ｌｊ ‘矧 １ ． ９ Ｉ 。 １ ￣ １ 的相 互关 系 。 元 （ 这个数据是用下面介绍的 Ｅ Ｅ ＸＣ Ｌ表格 ３ ５ｇｏ ｌ ｏ 螭 （ ５） ５ ｛ Ｅ 一 如果存在一组和期间相对应的金融资产 计算出来的，不用考虑计算过程中的尾差） 。 ４ ５ ，０ ９０ １ ３ ５ １．０ （４ ５ ） ｓ ０ ５ ｉ ｏ ０ ３ ９０ Ｉ ８ １ （ 负债） ，则在已知利率 的前提下可以计算出 Ａ＝１ ５ ２ ０元 ａ ９ （ ９ ×５）元 ＝２ ５ ５ 瑚 这组金融资产 （ 负债）的现值 （ 本金） ，当然 Ｂ ０ ０ ＝１０ 元 ｂ ５ ５ 。 ４元 在已知现值 （ 本金）的前提下也可以计算出 剩患 ． 摩 ∞ 在没有实际本金收 （ 付）的情况下 ，比 曛 融 １１． １４ 艘 ５５ ∞ ５５ 利率 ，这就是货币时间价值 中现值和内部报 如分期付款 采购或销售 ，公式也可以写成 ： 酬率的计算。但是在 已知现值 （ 本金）和利 Ａａ Ｂ＋ｈ