建模实操｜用Excel算折旧之直线法

在财务管理和会计工作有一个非常高频的话题：如何计算固定资产的折旧额。

主流的折旧方法分为直线法和余额递减法。

直线法简单易懂、适用范围广；余额递减法，顾名思义，前期折旧多、后期折旧慢慢减少，适合技术更新快的设备。

手动计算两种方法的折旧额并不是一件难事，但只要修改一个参数就要重新计算一遍，会浪费大量时间。

事实上，如果掌握了Excel函数，不管使用直线法还是余额递减法，都能实现输入基础数据、自动输出结果，效率直接翻倍。

所以本文以直线法折旧为主要内容，一步步指导大家_如何用Excel执行具体操作_，就算是建模新手，跟着步骤也能一次学会。

一、什么是折旧？

为什么要计算折旧？

折旧是固定资产在使用过程中，因为磨损、老化、技术更新等原因，价值逐渐减少。

比如买了一辆新车，开了一年，它的价值肯定不如刚买的时候，这个价值减少的过程就是折旧。

很多人刚接触折旧的概念会产生疑惑：买固定资产花的钱，为什么不直接算成当年的费用，而非要拆成几年算折旧呢？

其实折旧的核心是匹配原则：

如果固定资产能用3年，就应该_把它的成本分摊到3年里_，这样才能让每年的利润更真实，而不是只在购买当年承担所有成本。

比如花6万元现金买一台能用3年的机器，如果直接算成当年的成本费用，会导致当年利润骤降6万，后续2年利润虚高；

而按照折旧分摊后，每年扣除2万元的折旧额，固定资产的账面价值随着时间的流逝稳定减少，并且每一年的利润更贴合实际经营情况。

在财务建模中，折旧将影响：

• **利润表：**折旧计入管理费用或生产成本，直接拉低净利润；

• **现金流量表：**折旧是非付现费用，计算经营活动现金流时要加回，能增加现金流净额；

• **资产负债表：**累计折旧会减少固定资产的账面价值，反映资产的消耗情况。

所以，折旧计算是建模的基础模块，一旦计算错误会导致三张报表数据全部出错，后续估值、盈利预测也会跟着跑偏。

而用Excel函数自动计算，既能保证正确率，又能在参数调整时快速更新结果，是建模必备技能。

二、直线法计算折旧的核心和适用场景

直线法是最基础的折旧方法，核心逻辑是把固定资产的成本均匀分摊到每一年，每年折旧额都一样。

核心公式：

年折旧额=（固定资产原值-预计净残值）÷预计使用年限

例如：某公司在年初购买一台机器设备，支付现金60000元，预计使用8年，预计净残值20000元。

• 固定资产原值：购买机器设备的总花费60000元；

• 预计净残值：机器设备报废后预计能卖20000元；

• 预计使用年限：机器设备能正常使用的年数8年。

所以该机器设备的**年折旧额=（60000-20000）÷8= 5000元**，

第1年折旧：5000元；

第2年折旧：5000元；

…

第8年折旧：5000元；

即未来8年每年折旧5000元，8年后机器设备的账面价值降至20000元。

适用场景：

适合_使用强度均匀、无技术更新_的固定资产，比如办公桌椅、普通生产机器、厂房等。

另外，建模中如果没有特殊要求，**默认用直线法**即可，因为它计算简单、数据稳定，不易引发争议。

三、Excel 实操：用函数自动计算折旧

首先，Excel内置了专门计算折旧的函数，比如：

• SLN()：计算直线折旧； 

• DB()：计算余额递减法折旧； 

• DDB()：计算双倍余额递减法折旧； 

• SYD()：计算年数总和法折旧。

但在财务建模中，几乎不会使用这些函数。

一个原因是内置折旧函数默认资产在年初购入，并以此为前提计算首年折旧。

例如 SLN () 会直接按完整年度分摊折旧额，DB () 首年折旧额按全年的占比来计算。

但实际建模中，资产购入的时间往往灵活多样，可能是年中购入、季度末购入，甚至是分批购入，此时内置函数的年初假设会直接导致折旧计算偏差。

第二个原因，财务建模最关键的是让人能看懂，任何一个数据结果都应当能够通过公式反向拆解，让使用者清晰看到数字从哪里来。

但SLN ()、DB () 等函数属于 “黑箱操作”，计算过程被内置函数掩盖，无法直观的看到计算逻辑。

于是，在财务建模中，我们还是会手动编写计算过程，用公式表达出折旧的全部逻辑。 

接下来按难易级别，分简化版、升级版和专业版三个等级分别介绍直线法计算折旧额的函数。

1. 简化版折旧额计算（全年度折旧）： 

假设：HL Corporation年初购买一台60000元的设备，预计使用8年，残值20000元。我们要计算年折旧额。

可以创建以下工作簿，将重要参数以蓝色字体输入表格中，

包括_购买价格60000元，残值20000元，预计使用年限8年_，并将折旧期数（Period）和每一期要计算的项目。

**包括：**期初账面价值（Opening Balance）、折旧费用（Depreciation Expense）、期末账面价值（Closing Balance），搭建一个单独的区域。

其中，设备在第一年的Opening Balance=E8，即购买价格60000元，

第一年的_Depreciation Expense=($E$8-$E$9)/$E$10_，即_(60000-20000)/8=5000_。其中的$是锁定的意思，即：

• $E$8**：**同时锁定列和行，把公式横向或纵向复制时，始终锁定E8对应的数字。

• **E$8：**只锁定行，把公式横向复制时，此项会从E8变为F8、G8，但公式纵向复制时，8不变，所以始终对应E8。

• **$E8：**只锁定列，把公式横向复制时，E不变，所以始终对应E8，但公式纵向复制时，此项会从E8变为E9、E10。

• **E8：**完全不锁定。

当你把光标放在E8上时，连续按F4键，就可以轮流切换上述四种锁定方式。

因为设备在8年均匀分摊折旧，所以Depreciation Expense每年都一样，公式中的所有项目均锁定不变。

固定资产在第一年的Closing Balance=E13-F13=60000-5000=55000。

固定资产在第二年的Opening Balance=第一年的Closing Balance=G13，即第二年初资产账面价值为55000元。

接下来的计算公式非常类似，所以有一个非常实用的快捷键：先选中整列，再按 Ctrl +D。

这个组合键的作用是将选中列的第一个单元格内容，自动复制到整列，D是Down的首字母，即往下复制单元格内容，非常方便快捷。

把_Opening Balance，Depreciation Expense，Closing Balance_的内容依次往下复制，得到的结果如下图所示。

这是最基础的直线法折旧模型。

如果新购置的固定资产购买价格、残值、或者预计使用年限不同，只需要修改Excel表格中的蓝色字体参数，就可以实现自动计算。

2. 升级版折旧额计算： 

（新增资产的第一年折旧遵循“半年度规则”）

假设： HL Corporation不是在年初1月1日把所有资产买齐的，而是在整个年度中平均分布陆续购入的。

新购入的设备就不能按年初购买全额计算折旧，而是按照“年中购买”来处理，只在半年的时间计提折旧。即：

在第一年花60000元买了设备，全年只使用了一半时间，所以_第一年的折旧额只记一半；_

从第二年起，才开始按全额折旧。直到最后一年（第8+1年），折旧额也记一半，折旧计提完后，留下设备的残值20000元。

根据上述假设，设备在_第一年的Opening Balance、Closing Balance以及第二年的Opening Balance_公式不变。

只需要修改Depreciation Expense的计算公式，让它判断：现在是购买设备的第一年，还是后面某一年。用到的是：IF函数。

• 如果当前是第1年（If Period=1），Depreciation Expense=年折旧额÷2，即**($E$8-$E$9)/$E$10/2**，

• 如果当前不是第1年（If Period≠1），Depreciation Expense=年折旧额，即**($E$8-$E$9)/$E$10**。

所以Depreciation Expense的单元格中，计算公式是：IF(B13=1,($E$8-$E$9)/$E$10/2, ($E$8-$E$9)/$E$10)。

选中整列，再按 Ctrl +D，往下复制单元格内容。新的折旧额计算结果如下：

到第8年为止，新的计算公式都可以准确计算每一年的折旧额。

但是，如果继续向下复制单元格内容，第9年的Depreciation Expense将继续按5000元计算，导致Closing Balance=17500，也就是最后一期折旧结束。

设备的账面价值小于预计残值20000元。

说明目前的IF函数还不够完善，一旦超出预计使用年限（Period>8），会出现折旧过头的问题。

由此，我们引出进一步的解决方法，来看下面的专业版折旧额计算，是在IF函数的基础上，额外嵌套一个新的函数，可以帮我们解决**“模型内折旧过头”**的问题。

3. 专业版折旧额计算： 

（“半年度规则”+避免模型内折旧过头）

具体的逻辑是：

• 如果当前是第1年，Depreciation Expense=年折旧额÷2， 

• 如果当前不是第1年，Depreciation Expense=年折旧额，

• 但如果该设备剩余可计提折旧额<年折旧额，以剩余可计提折旧额作为Depreciation Expense。

以数字来举例：

第9年的Depreciation Expense是多少，以两个数字比大小来决定。

第一个数字是年折旧额=5000元，第二个数字是该设备剩余可计提折旧额，还剩多少折旧额可以扣除=设备的期初账面价值-残值=22500-20000=2500元。

2500<5000，最多只能计提2500元的折旧，没法再按年折旧额5000元扣除了，所以第9年的Depreciation Expense=2500元，即两者取孰低。

两者取孰低用MIN函数来表示。

所以，再上一步IF函数的基础上再做修改，把最后一项($E$8-$E$9)/$E$10改为MIN(($E$8-$E$9)/$E$10,E13-$E$9)，以此增加“停止折旧”的约束。

所以新的折旧额计算公式为：

=IF(B13=1($E$8-$E$9)/$E$10/2,MIN(($E$8-$E$9)/$E$10,E13-$E$9))。

向下复制单元格内容，第9年的Depreciation Expense将按2500元计算，这是折旧的最后一年，所以Closing Balance=20000。

还可以再向下复制单元格内容，第10年的Depreciation Expense=0，折旧已计提完成，没有折旧了，所以_Closing Balance=20000_，保持在残值不变。

这就是直线法计算折旧额的建模思路。以一个IF函数+一个MIN函数，让固定资产在第一年遵循“半年度规则”折旧，剩余年份实现精准折旧并在模型周期内避免过度折旧。

总的来说，折旧计算看似简单，却是_财务建模的基础_，算错一步会影响整个模型的准确性。