设为首页收藏本站

中国会计视野论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

微信登录

微信扫一扫,快速登录

查看: 1391|回复: 1

【Excel财务应用】怎么用年限总和法设置固定资产月折旧、当年折旧和累计折旧的函数...

[复制链接]
发表于 2018-5-29 20:27:03 | 显示全部楼层 |阅读模式
  美国注册管理会计师认证(CMA®)
CMA考试7折报名,让您无惧未来!
CMA获得政府及各大企业集团一致推荐
CMA是您挖掘职业潜能的通关秘钥
 

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

 

欢迎订阅会计视野微信公众号
第一时间了解最新财会知识
碎片化学习新方法
200万读者追随的真爱选择。

本帖最后由 彭怀文 于 2018-5-30 18:41 编辑

前面讲述了平均年限法计算固定资产折旧的函数公式,本次讲述用年限总和法来设置固定资产折旧的函数公式及其过程。
一、年限总和法简介
年数总和法,是指用固定资产原值减去预计残值后的净额,乘以一个逐年递减的分数(称为折旧率),计算折旧额的一种加速折旧的方法。
年数总和法又称总和年限法、折旧年限积数法、年数比率法、级数递减法或年限合计法,是固定资产加速折旧法的一种。
逐年递减分数的分子代表固定资产尚可使用的年数;分母代表使用年数的逐年数字之总和,假定使用年限为n年,分母即为1+2+3+……+n=n(n+1)÷2,相关计算公式如下:
年折旧率=尚可使用年数/年数总和×100%
年折旧额=(固定资产原值-预计残值)×年折旧率
月折旧率=年折旧率/12
月折旧额=(固定资产原值-预计净残值)×月折旧率
因此,年限总和法属于加速折旧方法,每期折旧金额是不相等的(所以不是直线法);且会计处理要求是先按年度进行折旧,然后再除以12来计算月折旧。因此,月折旧不能简单地将折旧期限由年乘以12变换为月,同时当年折旧和累计折旧的函数公式都不能简单地利用“间隔月份”去乘以“月折旧”。
二、年限总和法折旧方法函数简介
年限总和法方法函数-的是——SYD函数,其使用方法:
SYD(固定资产原值, 预计净残值, 使用期限, 折旧所属期间)
按照中国会计规则,函数公式中的“使用期限”只能使用以“年”为单位的期限,不能转换为“月”;“折旧所属期间”也只能是以“年”为单位的期间。
尤其是在计算月折旧金额时,不能简单地把函数公式中的“使用期限”和“折旧所属期间”都换算为“月”,因为其计算的结果与中国会计规则要求的按年折旧再除以12的金额是不相等的,不信的话可以试试看!
三、月折旧函数公式的设置过程详解
有了平均年限法设置函数公式的经验,我们此处也关注两个问题就可以顺利设置函数公式了:
1.折旧条件。即什么时候该折旧,什么不该折旧。
2.函数公式内部各参数如何取得。函数公式中的“固定资产原值”、“预计净残值”、“使用期限”都是常量,而“折旧所属期间”则是一个变量。
(一)折旧条件
可以采用平均年限法一样的方法,将其条件罗列如下:图E3-1
E3-1.png
      因此,将图E3-1转换为IF函数公式:
=IF(EOMONTH($E$2,0)-C4<=0,0,IF(DATEDIF(EOMONTH(C4,-1)+1,EOMONTH($E$2,0),"M")=0,0,IF(DATEDIF(EOMONTH(C4,-1)+1,EOMONTH($E$2,0),"M")<=F4*12,1,0)))       (公式A)
(二)折旧所属期间问题的解决
年限总和法折旧每一年金额不一样,是递减的,所以折旧月份对应的折旧“年”是很重要的,第1个月至第12个月对应的是第1年,以此类推。
有前面设置的“公式A”把不需要折旧的期间全部排出后,剩下的就是需要折旧的期间,因此我们采用一些数学方法来解决这个问题。
E3-2.png
因此,折旧的函数公式就可以表述为:
SYD(D4,D4*E4,F4,ROUNDUP(DATEDIF(EOMONTH(C4,-1)+1,EOMONTH($E$2,0),"M")/12,0))  (公式B)
现在只需要将“公式B”代入“公式A”中将其结果“1”替换后除以12,这样就得到需要的月折旧函数公式:
=IF(EOMONTH($E$2,0)-C4<=0,0,IF(DATEDIF(EOMONTH(C4,-1)+1,EOMONTH($E$2,0),"M")=0,0,IF(DATEDIF(EOMONTH(C4,-1)+1,EOMONTH($E$2,0),"M")<=F4*12,SYD(D4,D4*E4,F4,ROUNDUP(DATEDIF(EOMONTH(C4,-1)+1,EOMONTH($E$2,0),"M")/12,0))/12,0)))   (月折旧函数公式)
四、当年折旧函数公式设置
(一)当年折旧的条件限制
采用年限总和法进行折旧时,每一年的折旧金额都不一样,所以不能简单用月折旧金额乘以折旧月份数。同时,很多时候折旧年度会跨越2个自然年度,比如2017年6月入账的固定资产,采用年限总和法折旧,那么折旧年度第1年中有6个月在2017年,有6个月在2018年。当然,也有刚好在一个完整自然年度的,入账时间在12月份就是。
所以,要求当年折旧额就必须解决:
1.当年跨越了属于固定资产折旧的那两个年度;
2.固定资产折旧年度在当年各自有几个月。
因此将上述限制条件用图表列示:如图E3-3         
E3-3.png
(二)各个分段期间的折旧公式
SYD函数中的参数“固定资产原值”、“预计净残值”、“使用期限”都是常量,而“折旧所属期间”则是个变量。“折旧所属期间”须是数值或计算结果是数值,并且要满足大于0且小于等于“使用期限”,为了达到这个要求,设置函数公式时只能分段计算,把不满足条件的排除。
1.间隔月份<12
意味着就是第1个折旧年度,但是可能折旧月份不足12个月,公式可以表示为:
=SYD(D4,D4*E4,F4,1)* (12-MONTH(C4)/12  (公式C)
2.间隔年度<F4(使用年限)
意味着当年跨越了属于固定资产折旧的那两个年度,年折旧必须分段计算然后相加。
用DATEDIF函数求两个时间间隔的年份数时,是按整年计算的,不足的1年的不计算,计算结果也是整数,比如1年零2个月计算的结果只是1。因此,此处可以利用这一点来计算折旧年限的前半段部分。
前半部分月份的计算:这时需要进行一下观察,比如2017年1月入账的,2017年折旧11个月,2018年度中属于第一个折旧年度就还剩1个月,以此类推,就会得出前半段部分月份数恰恰等于入账的月份数。这样是不是就很简单了,直接取入账时间的月份数就OK了。
前半部分年折旧公式:
SYD(D4,D4*E4,F4,DATEDIF(EOMONTH(C4,-1)+1,DATE(YEAR($E$2),12,31),"Y"))*MONTH(C4)/12(公式D)
同样道理,可以得出后半部分年折旧公式:
SYD(D4,D4*E4,F4,DATEDIF(EOMONTH(C4,-1)+1,DATE(YEAR($E$2),12,31),"Y")+1)*(12-MONTH(C4))/12(公式E)
将公式D和公式E相加,就得出该年度的折旧公式:
=SYD(D4,D4*E4,F4,DATEDIF(EOMONTH(C4,-1)+1,DATE(YEAR($E$2),12,31),"Y"))*MONTH(C4)/12+SYD(D4,D4*E4,F4,DATEDIF(EOMONTH(C4,-1)+1,DATE(YEAR($E$2),12,31),"Y")+1)*(12-MONTH(C4))/12   (公式F)
3.间隔年度=F4(使用年限)
当间隔年度等于固定资产预计使用年限时,固定资产将在该年度停止折旧。当年折旧月份数与前述相同,不再赘述。因此,最后一年的年折旧公式:
=SYD(D4,D4*E4,F4,F4)* MONTH(C4)/12  (公式G)
(三)当年折旧公式的合成
将“条件”和各个分段期间的折旧公式合并在一起就可以得到当年折旧公式:
=IF(DATE(YEAR($E$2),12,31)-C4<=30,0,IF(DATEDIF(EOMONTH(C4,-1)+1,DATE(YEAR($E$2),12,31),"M")<12,SYD(D4,D4*E4,F4,1)*(12-MONTH(C4)),IF(DATEDIF(EOMONTH(C4,-1)+1,DATE(YEAR($E$2),12,31),"M")<F4*12,SYD(D4,D4*E4,F4,DATEDIF(EOMONTH(C4,-1)+1,DATE(YEAR($E$2),12,31),"Y"))*MONTH(C4)+SYD(D4,D4*E4,F4,DATEDIF(EOMONTH(C4,-1)+1,DATE(YEAR($E$2),12,31),"Y")+1)*(12-MONTH(C4)),IF(DATEDIF(EOMONTH(C4,-1)+1,DATE(YEAR($E$2),12,31),"Y")=F4,SYD(D4,D4*E4,F4,F4)*MONTH(C4),0))))/12   (公式H)
五、累计折旧函数公式的设置过程
(一)累计折旧率的探索
采用年限总和法计算折旧,每年折旧金额都不一样,且Excel中没有计算累计折旧的函数,因此只能逐年相加。当Excel函数不能解决的时候,这时应回到会计规则上来考虑。在会计处理时,每年折旧是用原值减去预计净残值后乘以折旧率,每年折旧率虽然不同,但是每年折旧率的分母是相同的。当我们需要计算折旧年度累计折旧时可以用(原值-预计净残值)*折旧率之和。
以固定资产使用年限5年为例,下面来研究一下折旧率的规律:如图E3-4
E3-4.png
当我们把固定资产使用年限换为n年时:
累计折旧率分母=(n+n-1+n-2+…+2+1)=n(n+1)÷2
累计折旧率分子= n(n+1)÷2-(n-N)(n-N+1) ÷2  (备注:N代表折旧所属年限)
所以,累计折旧率(年)可以表示为:
(n(n+1)÷2-(n-N)(n-N+1) ÷2)/(n(n+1)÷2)=(1-(n-N)(n-N+1)/(n(n+1))  (公式I)
(二)累计折旧可能存在的时间段考虑
我们需要计算的累计折旧不一定刚好是整年计算,所以需要考虑的分段计算。分段计算的另一个原因就是Excel函数对相关参数的限制性。因此,我们考虑把累计折旧的时间段分为如下:图E3-5
E3-5.png
(三)累计折旧函数公式
将公式I中的n用F4替换,将N用函数公式替换,然后再代入分时间段的条件函数中,就可以得到我们需要的函数公式:
=IF(EOMONTH($E$2,0)<C4,0,IF(AND(DATEDIF(C4,EOMONTH($E$2,0),"M")>=0,DATEDIF(C4,EOMONTH($E$2,0),"M")<F4*12),D4*(1-E4)*((1+F4)*F4-(1+F4-INT(DATEDIF(C4,EOMONTH($E$2,0),"M")/12))*(F4-INT(DATEDIF(C4,EOMONTH($E$2,0),"M")/12)))/((1+F4)*F4)+SYD(D4,D4*E4,F4,DATEDIF(C4,EOMONTH($E$2,0),"Y")+1)*DATEDIF(EOMONTH(C4,-1)+1,EOMONTH($E$2,0),"YM")/12,D4*(1-E4)))   (公式J)
以上就是我用年限总和法设置固定资产折旧函数公式的过程,希望能抛砖引玉。建议下载附件测试。
(明日发布用双倍余额递减法设置函数公式的详细过程)

更多Excel财务应用,请参阅:

E3-4.png

利用Excel计算固定资产折旧和累计折旧-2 - 副本.xlsx

36.13 KB, 下载次数: 84

评分

1

查看全部评分

本帖被以下淘专辑推荐:

发表于 2018-5-29 22:51:14 | 显示全部楼层
这本书有电子版吗?
您需要登录后才可以回帖 登录 | 注册 新浪微博登陆

本版积分规则

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

GMT+8, 2018-10-23 11:54

Powered by Discuz! X3.3

© 2001-2017 Comsenz Inc.

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