设为首页收藏本站

中国会计视野论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

微信登录

微信扫一扫,快速登录

查看: 842|回复: 11

【Excel财务应用】怎么用平均年限法设置固定资产折旧公式

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

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

 

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

    前几日,因为要统计企业固定资产享受加速折旧的税会差异,需要通过Excel来计算固定资产的累计折旧和当年折旧额。结果,当表格制作好后就遇到了“拦路虎”:Excel函数只能计算每期的折旧金额,而不能计算累计折旧和当年折旧额。虽然通过网络搜索,看了很多高人的帖子,仍然不能满足我的需求。
于是,开始我的艰难探索!
一、确定了目标,然后从简单处开始步步深入!——月折旧公式的设置过程详解
当一件事比较复杂时,就需要让其变得简单;当一件事不能一步完成时,就需要分步来完成。
固定资产折旧计算方法中,最简单的当然是平均年限法!
——所以,我首先就从平均年限法开始来设置公式。
平均年限法折旧在Excel中有现成的函数公式,公式也很简单:
SLN(固定资产原值,预计净残值,使用期限)。
此处的使用期限可以是“月”,也可以是“年”,计算的结果当然是对应着“月折旧额”和“年折旧额”。
因此,在图E2-1中“月折旧”单元格G4的函数公式就是这样的:
=SLN(D4,D4*E4,F4*12)      (公式①)
但是,实际需要却比较复杂,必须要遵守会计规则啊,比如入账当月是不可以折旧的。
我希望得到的结果是这样的:
E2-1.png
——当我在“查询月度”处输入不同时间,就会得到不同的结果。此时“月折旧”的函数公式应满足的会计规则有:
1.当“查询月度”<“入账日期”,折旧额为0;
2.入账当月不能折旧;
3.达到使用年限后不能折旧,但是到期当月要计提折旧。
将上述条件用图形象展示出来就是这样的:图E2-2
E2-2.png
如果要使用条件函数IF来进行判断,则可以将上述条件转化为对“查询月度-入账时间”的结果进行判断。如下表:图E2-3
E2-3.png
说明:我之所以将“结果”用1和0来表示,是希望这个计算的结果与折旧函数公式计算结果相乘,这样就得到了我最终希望的月折旧金额了。
有了图E2-3,再使用IF函数来写函数公式就简单多了,将其变为函数公式如下:
=IF(E2-C4<0,0,IF(AND(DATEDIF(EOMONTH(C4,-1)+1,EOMONTH(E2,0),"M")>0,DATEDIF(EOMONTH(C4,-1)+1,EOMONTH(E2,0),"M")<=F4*12),1,0))        (公式②)
函数公式含义解释:
EOMONTH(C4,-1)就是把“入账时间”C4变为入账当月的第1日,EOMONTH(E2,0)则是把查询时间变为查询月度的最后一天。
接下来的步骤就是把折旧函数公式①和公式②组合在一起,办法有两个:一个办法就是用乘号“*”将两个公式连接在一起;另一个办法就是将公式②中判断结果用公式①直接替换。两个办法结果一样,随你喜欢。
我是直接替换进去的,公式就是:
=IF($E$2-C4<0,0,IF(AND(DATEDIF(EOMONTH(C4,-1)+1,EOMONTH($E$2,0),"M")>0,DATEDIF(EOMONTH(C4,-1)+1,EOMONTH($E$2,0),"M")<=F4*12),SLN(D4,D4*E4,F4*12),0))
(备注:为了向下批量复制,特别将E2调整为绝对引用)
如果用思维导图将上述过程进行一下总结,就是这样的:图E2-4
E2-4.png
二、当年折旧公式设置过程详解
假设在图E2-1D的基础上还要计算“当年折旧”,也就是要计算“查询月度”所属年度的折旧金额。
E2-5.png
该问题看似简单,以为用“月折旧”乘以12就可以,其实不然,存在以下问题:
1.可能年度折旧月份不足12个月,比如开始和结束折旧的年度;
2.查询时间属于固定资产已经达到使用期限而停止折旧;
3.查询月度恰好是入账时间的月份,当月折旧为0,但是当年折旧额可能不应为0.
将这些问题(其实也是条件)归纳整理:如图E2-6
E2-6.png
现在新的问题又出现了,结果为0和12很好办,但是所谓“按月”就需要计算当年应折旧的月份数。
问题1:开始折旧的年度,如果是1月份入账则折旧11个月,2月份入账则折旧10个月,以此类推可以得出结论:折旧的个月=12-入账的月份数,比如12-1,12-2等等,12月份入账当年不折旧,结果等于0,也没有毛病,况且这种可能已经在上一个条件中给包含了。
所以,此处结果可以表示为:12-MONTH(C4)。
问题2:结束折旧的年度,如果是1月份入账的则1月份结束折旧,2月份入账的则2月份结束折旧,是不是与入账时间的月份数相等呢?当然,这个结论的前提固定资产使用年限是整数哦。
所以,此处结果可以表示为MONTH(C4)(备注:前提是固定资产使用年限为整数)
因此,将图E2-6用条件函数IF写成函数公式:
=IF(DATE(YEAR($E$2),12,31)-C4<=0,0,IF(DATEDIF(C4,DATE(YEAR($E$2),12,31),"M")<12,12-MONTH(C4),IF(DATEDIF(C4,DATE(YEAR($E$2),12,31),"M")<F4*12,12,IF(DATEDIF(C4,DATE(YEAR($E$2),12,31),"M")<(F4+1)*12,MONTH(C4),0))))
有了这函数公式,再要计算查询年度的折旧金额就简单,只需要在这个公式后面乘以月折旧金额公式即可,因此“当年折旧”的函数公式:
=IF(DATE(YEAR($E$2),12,31)-C4<=0,0,IF(DATEDIF(C4,DATE(YEAR($E$2),12,31),"M")<12,12-MONTH(C4),IF(DATEDIF(C4,DATE(YEAR($E$2),12,31),"M")<F4*12,12,IF(DATEDIF(C4,DATE(YEAR($E$2),12,31),"M")<(F4+1)*12,MONTH(C4),0))))*SLN(D4,D4*E4,F4*12)
此处也可以利用思维导图总结一下,但是与前面的差不多,就省略了吧。
三、累计折旧公式设置过程详解
有了前面设置“月折旧”和“当年折旧”公式的经验,在此时就可以拿出来使用了:累计折旧可以用月折旧金额乘以累计折旧月份数就可以,当然要排除不折旧和已经折旧完毕的情况。所以,条件归纳整理为:如图E2-7
E2-7.png
将图E2-7用函数IF转化为公式:
=IF(EOMONTH(E2,0)-C4<=0,0,IF(DATEDIF(EOMONTH(C4,-1)+1,EOMONTH(E2,0),"M")<=F4*12,DATEDIF(EOMONTH(C4,-1)+1,EOMONTH(E2,0),"M"),F4*12))
然后再乘以月折旧函数公式“SLN(D4,D4*E4,F4*12)”就可以得到“累计折旧”的函数公式:
=IF(EOMONTH(E2,0)-C4<=0,0,IF(DATEDIF(EOMONTH(C4,-1)+1,EOMONTH(E2,0),"M")<=F4*12,DATEDIF(EOMONTH(C4,-1)+1,EOMONTH(E2,0),"M"),F4*12))*SLN(D4,D4*E4,F4*12)
(备注:注意在详细批量复制时注意把E2调整为绝对引用)
以上就是我设置采用平均年限法进行折旧时函数公式详细过程,希望能抛砖引玉!
请大家下载附件测试,并提出宝贵意见。
(明日发布用年限总和法设置函数公式的详细过程)

更多函数知识,请参阅:
(当当网、京东商城、亚马逊、淘宝等有售)

利用Excel计算固定资产折旧和累计折旧-直线法和年限总和法.xlsx

24.03 KB, 下载次数: 74

评分

1

查看全部评分

 楼主| 发表于 2018-5-28 18:26:43 | 显示全部楼层
发表于 2018-5-29 22:34:37 | 显示全部楼层
谢谢彭老师,这里EOMONTH这个函数用的很妙,-1再加1算是神来之笔,把DATEDIF的会计月份的漏洞彻底解决。
发表于 2018-5-30 07:06:20 | 显示全部楼层
发表于 2018-5-30 15:01:13 | 显示全部楼层
我有更简便的公式
发表于 2018-5-30 15:02:13 | 显示全部楼层
固定资产折旧测试表(使用年限平均法)                                                       
客户名称:        A有限公司                                               
会计截止日:        2016-7-31                                               
启用日期          折旧方法          原值          残值率(%)          使用寿命(年)          使用寿命(月)          本期应折旧月数          本期应折旧金额
2010-11-1         年限平均法          89,000.00         5        5        60        0         -   
2011-11-12         年限平均法          107,500.00         5        5        60        7         11,914.56
2016-5-11         年限平均法          30,000.00         5        5        60        2         950.00
2016-7-12         年限平均法          51,282.05         5        5        60        0         -   
注:上表中,假定原值自雇用日起无调整。                                                       
G5中公式为:=MIN(MONTH($B$3),DATEDIF(A5,$B$3,"m"),MAX(0,F5-DATEDIF(A5,$B$3,"m")+MONTH($B$3)))                                                       
发表于 2018-5-30 15:03:06 | 显示全部楼层
注:上表中,假定原值自使用日起无调整。
发表于 2018-5-30 15:03:41 | 显示全部楼层
启用日期为A列,依此类推
发表于 2018-5-30 15:05:14 | 显示全部楼层
11,914.56 和 950.00 分别为应计折旧金额
发表于 2018-5-30 15:08:44 | 显示全部楼层
用MIN、MAX、MOTH和DATEDIF函数的组合,公式字符数更少
 楼主| 发表于 2018-5-30 18:21:27 | 显示全部楼层
ssyygyitx 发表于 2018-5-30 15:02
固定资产折旧测试表(使用年限平均法)                                                       
客户名称:        A有限公司                                               
会计截止日:        2016-7-31                                         ...

感谢交流!
我本身就是抛砖引玉,欢迎上传Excel文档附件,以便更多的网友下载学习和使用。
 楼主| 发表于 2018-5-30 18:23:53 | 显示全部楼层
jackjiang 发表于 2018-5-29 22:34
谢谢彭老师,这里EOMONTH这个函数用的很妙,-1再加1算是神来之笔,把DATEDIF的会计月份的漏洞彻底解决。{:b ...

欢迎多交流。
您需要登录后才可以回帖 登录 | 注册 新浪微博登陆

本版积分规则

关闭

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

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

GMT+8, 2018-6-21 14:57

Powered by Discuz! X3.3

© 2001-2017 Comsenz Inc.

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