Excel会计与财务管理应用大全 – 第23页

Excel 计算可变利率下投资的未来值函数FVSCHEDULE

上节中介绍的FV函数在计算投资的未来值时，其利率是固定的。如果某项投资的利率是可变的，要计算该项投资的未来值时就需要使用FVSCHEDULE函数。

FVSCHEDULE函数的功能是计算某项投资在可变利率下的未来值。它的语法格式为：

FVSCHEDULE（principal，schedule）

它只有两个参数，principal表示现值，即本金；schedule输入为数组，表示利率数组。

例如，某人在2009年1月存入12万元，协定按月计算利息。在该年中，存款年利率一共变化了3次，1~3月为3.57%，4~6月为3.48%，7~11月为3.85%，12月份再次上调为3.88%，现需要计算一年后的存款总额。

步骤01：打开实例文件“FVSCHEDULE函数计算模型.xlsx”工作簿。在单元格C4中输入公式“=B4/12”，按下Enter键后，向下复制公式至单元格C15，计算结果如图4-31所示。

图4-31　计算月利率

步骤02：在单元格B17中输入计算存款额公式“=FVSCHEDULE（B1,C4:C15）”，按下Enter键后，计算结果如图4-32所示。

图4-32　输入公式计算一年后存款总额

小技巧：schedule参数必须为数组

在使用FVSCHEDULE函数时需要注意，schedule参数必须为数组。但在本例中，需要注意，不能直接使用年利率数组，即不能直接将单元格区域B4:B15作为schedule参数。因为是按月份计算利息，而且每个月的年利率是不一致的，因此应先计算出每月的月利率。

Excel 计算一笔投资的未来值函数FV

FV函数的功能是基于固定的利率及等额分期付款方式，返回某项投资的未来值。它的格式为：

FV（rate,nper,pmt,pv,type）

一共有5个参数，rate为各期利率；nper为总投资期；pmt为各其应支付的金额；pv表示从该项投资开始计算时已经入账的款项，即本金；type用以指定各期的付款时间是在期初还是期末。

例如，现需要启动某项投资，已经存入指定专用账户先期存款10万元，年利率为4.5%，并在今后的36个月每个月存入2000元到该账户。现需要计算两年后该账户的存款金额。具体操作步骤如下所示。

步骤01：创建FV函数模型。启动Excel 2016新建一个工作簿，在工作表Sheet1中根据上面的描述文字输入已知条件，并设置好单元格格式，如图4-29所示。

步骤02：设置公式计算未来值。在单元格B5中输入公式“=FV（B2/12,D2,-C2,-A2,0）”，按下Enter键后，计算结果为191356.96，如图4-30所示。在该公式中，将存款额都输入为负数，因为现在是将它们看成投资，应为支出。

图4-29　创建函数计算模型

图4-30　输入公式计算未来值

Excel 计算阶段本金和利息函数CUMPRINC和CUMIPMT

在实际工作和生活中，有时也许需要计算某一个时间段内某项贷款所需要支付的本金和利息，在Excel中，可以使用阶段本金和利息函数CUMIPMT和CUMPRINC来完成此项计算。

步骤01：打开实例文件“计算阶段本金和利息.xlsx”工作簿。在单元格B5中输入公式“=CUMPRINC（C2/12,B2*12,A2,18,36,0）”，计算某些月期间内应付本金，按下Enter键后，计算结果如图4-24所示。

图4-24　计算某些月期间内应付本金

步骤02：在单元格B6中输入公式“=CUMPRINC（C2,B2,A2,2,4,0）”，计算某些年期间内应付本金，按下Enter键后，计算结果如图4-25所示。

图4-25　计算某些年期间内应付本金

步骤03：在单元格C5中输入公式“=CUMIPMT（C2/12,B2*12,A2,18,36,0）”，计算某些月期间内应付利息，按下Enter键后，计算结果如图4-26所示。

步骤04：在单元格C6中输入公式“=CUMIPMT（C2,B2,A2,2,4,0）”，算某些年期间内应付利息，按下Enter键后，计算结果如图4-27所示。

图4-26　计算某些月期间内应付利息

图4-27　计算某些年期间内应付利息

小技巧：本金和利息函数使用需注意

在使用本节中所介绍的本金和利息计算函数时需要注意一点，即时间单位要保持一致性。如果要计算的是以“月”为单位的期间，则所有与时间相关的参数都必须统一为以“月”为单位，利率需转换为月利率，还款期限也必须转换为以“月”为单位的总期数。

高手支招：在单元格中显示公式代码

在默认的情况下，单元格中只显示公式运算的结果，而非公式代码。在某些特殊的情况下，希望包含公式的单元格中能够显示公式的代码，在Excel中只需要简单一步操作即可实现。

在“公式”选项卡中的“公式审核”组中单击“显示公式”按钮，即可显示当前工作区中包含的所有公式，如图4-28所示。再次单击该按钮，可隐藏公式，显示公式运算的结果。

图4-28　在单元格中显示公式代码

Excel 计算付款中的本金和利息函数PPMT和IPMT

PPMT函数的功能是基于固定利率及等额分期付款方式下，返回投资在某一给定期间内的本金偿还额。它的语法格式为：

PPMT（rate,per,nper,pv,fv,type）

该函数一共有6个参数，rate为贷款利率；per用于计算本金数额的期数；nper表示该项贷款的付款总期数；pv表示本金；fv表示在最后一次付款后希望得到的现金余额；type用于指定各期的付款时间是在期初还是期末。

IPMT函数的功能是基于固定利率及等额分期付款方式下，返回给定期数内对投资的利息偿还额。它的语法格式为：

IPMT（rate,per,nper,pv,fv,type）

其参数个数和含义等同于PPMT函数。

已知某人的贷款金额、期限及利率，现分别计算出前8个月每个月还款的本金和利息。

步骤01：打开实例文件“计算本金和利息.xlsx”工作簿。在单元格B5中输入公式“=PPMT（$C$2/12,A5,$B$2*12,$A$2）”，按下Enter键后向下复制公式，计算出前8个月每个月支付的本金，如图4-22所示。从结果可以得知，本金在逐渐增加。

图4-22　输入公式计算本金

步骤02：在单元格C5中输入公式“=IPMT（$C$2/12,A5,$B$2*12,$A$2）”，计算出前8个月每个月支付的利息额，计算结果如图4-23所示。从结果可以得知，每个月支付的利息在逐渐减少。

图4-23　输入公式计算利息

Excel 分期付款函数PMT

PMT函数的功能是基于固定的利率及等额分期付款方式，返回贷款的每期付款额。它的语法格式为：

PMT（rate,nper,pv,fv,type）

它一共有5个参数，rate为贷款利率；nper表示贷款周期；pv表示本金；fv表示在最后一次付款后希望得到的现金余额，如省略，则默认值为0；type为数字0或1，用以指定各期的付款时间是在期末还是在期初。

下面通过一个具体实例介绍PMT函数及应用。

例如，某人向银行贷款30万，贷款期限为10年，贷款利率为8.78%，现要求计算每年偿还金额和每月偿还金额。

步骤01：打开实例文件“PMT函数计算模型.xlsx”工作簿。在单元格B4中输入公式“=PMT（C2,B2,A2）”，按下Enter键后，计算结果为-46294.34，如图4-20所示。

图4-20　计算每年还款额

步骤02：在单元格B5中输入公式“=PMT（C2/12,B2*12,A2）”，按下Enter键后，计算得到的每月偿还金额为-3764.65，如图4-21所示。

图4-21　计算每月还款额

Excel 年数总和折旧法与SYD函数

年数总和折旧法又称折旧年限积数法、年数比率法、级数递减法或年限合计法，是将固定资产的原值减去预计净残值后的净额乘以一个逐年递减的分数计算每年的折旧额，这个分数的分子代表固定资产尚可使用的年数，分母代表使用年限的逐年数字总和。年限总和折旧法也是一种加速折旧法，它以固定资产的原始价值减去预计净残值后的余额乘以一个逐年递减的分数，作为该期的折旧额。计算公式为：

年折旧额=（固定资产原值-预计残值）×（尚可使用年数/年次数字的总和）

其中，年次数字的总和＝life+（life-1）+（life-2）+…+1=（life×（life+1））/2

在Excel中，年限总和折旧法对应的函数为SYD，它的语法格式为：

SYD（cose,salvage,life,per）

前面3个参数的含义与前面介绍的折旧函数类似，参数per为期间，其单位与life相同。

例如，已知某企业购进一台设备，资产原值为15万，残值为2元，使用年限为8年，现假设要按年限总和法计算每一年的折旧。

步骤01：打开实例文件“年限总和折旧计计算模型.xlsx”工作簿。在单元格D4中输入公式“=SYD（$A$2,$B$2,$C$2,C4）”，按下Enter键后，向下复制公式至单元格D11，计算出各年的折旧额，如图4-16所示。

图4-16　设置公式计算各年折旧

步骤02：从计算结果可以看出，每年的折旧额呈递减趋势。在单元格D13中输入公式“=SUM（D4:D11）”，计算出累计折旧额，如图4-17所示。

高手支招：设置在屏幕上显示函数参数提示

图4-17　计算累计折旧额

在单元格中输入公式时，在屏幕上显示函数参数提示，可以帮助用户快速准确地完成公式的输入。如果用户在输入函数名称和左括号时，屏幕上并未显示相关函数的提示，则可以如下设置。

步骤01：单击“文件”菜单，在展开的菜单项中单击“选项”，如图4-18所示。

图4-18　选择“选项”

步骤02：在“Excel选项”对话框中单击“公式”标签，在“使用公式”区域勾选“公式记忆式键入”复选框，然后单击“确定”按钮，如图4-19所示。

开启了“公式记忆式键入”功能，除了可以在屏幕上显示函数参数外，在输入某个具体参数时，还会显示工作表中已定义的名称。

图4-19　勾选“公式记忆式键入”复选框

Excel 可变余额递减折旧法与VDB函数

可变余额递减法是指以不同倍率的余额递减法计算一个时期内折旧额的方法。在Excel中，对应的函数为VDB，该函数的语法表达式为：

VDB（cost,salvage,life,start_period,end_period,factor,no_switch）

该函数共有7个参数，Cost为资产原值；salvage为资产残值；life为使用年限；start_period为进行折旧计算的起始期间，必须与life的单位相同；end_period为进行折旧计算的截止期间，其单位也必须与life的单位相同；factor为余额递减速率，也可称为折旧因子，如果省略该参数，则默认为2；no_switch为一逻辑值，指定当折旧值大于余额递减计算值时，是否转用直线折旧法，该值如果为TRUE，表示即使折旧值大于余额递减计算值，也不转用直线折旧法，如果为FALSE或省略，且折旧值大于余额递减计算值时，Excel将转用直线折旧法。

现通过一个具体的实例来进一步理解可变余额折旧法。

步骤01：打开实例文件“可变余额递减法计算折旧.xlsx”工作簿。在单元格B4中输入公式“=VDB（A2,B2,C2*365,0,1）”，计算第一天的折旧额，计算结果如图4-10所示。

图4-10　计算第一天的折旧

步骤02：在单元格B5中输入公式“=VDB（A2,B2,C2,0,1）”，计算第一年的折旧额，计算结果如图4-11所示。

图4-11　计算第一年的折旧

步骤03：在单元格B6中输入公式“=VDB（A2,B3,C2,2,3）”，计算第3年的折旧，计算结果如图4-12所示。

图4-12　计算第3年的折旧

步骤04：在单元格B7中输入公式“=VDB（A2,B2,C2*365,181,452）”，计算某些天内的折旧，计算结果如图4-13所示。

步骤05：在单元格B8中输入公式“=VDB（A2,B2,C2*12,6,12）”，计算某些月内的折旧，计算结果如图4-14所示。

步骤06：在单元格B9中输入公式“=VDB（A2,B2,C2,4,6）”，计算某些年内的折旧，计算结果如图4-15所示。

小技巧：使用VDB函数注意时间单位须一致

图4-13　以天为单位计算某段时间内的折旧

图4-14　以月为单位计算某段时间内的折旧

图4-15　以年为单位计算某段时间内的折旧

在使用VDB函数计算折旧时，需要注意参数life与参数start_period和end_period的单位必须一致。如果以“天”为单位，则都应以“天”为单位；如果以“月”为单位，则都应以“月”为单位；如果以“年”为单位，则都应为“年”为单位。

Excel 双倍余额递减折旧法与DDB函数

双倍余额递减折旧法是在不考虑固定资产净残值的情况下，根据每年年初固定资产净值和双倍的直线法折旧率计算固定资产折旧额的一种方法。采用这种方法，固定资产的账面余额会随着折旧的计提减少，而折旧率不变。因此，各期计提的折旧额必然逐年减少。折旧在第一阶段是最高的，在后续阶段中会减少。年折旧额的计算公式为：

年折旧额=（固定资产原值-累计折旧额）×（余额递减速率/预计使用年限）

在Excel中，使用函数DDB来计算按双倍余额递减法计算折旧，该函数的语法格式为：

DDB（cost,salvage,life,period,factor）

它一共有5个参数，cost为资产原值；salvage为资产残值；life为使用年限；period为需要计算折旧值的期间；factor为余额递减速率，如果该函数被省略，则默认为2，此时会采用双倍余额递减法。

我们仍以上节中的实例以例，已知某企业在2005年3月购进一部价值15万元的大型设备，使用年限为8年，预计净残值为2万元。不同的是现要求按余额递减速率分别为2倍和3倍计算该设备每年的折旧费以及累计折旧额。

按双倍余额计算折旧

打开实例文件“双倍余额递减折旧法计算折旧.xlsx”工作簿。在单元格D4中输入公式“=DDB（$A$2,$B$2,$C$2,C4）”，按下Enter键后，复制公式至单元格D11，得到如图4-8所示的计算结果。此处公式中省略了factor参数，默认值为2。

图4-8　按双倍余额递减计算折旧

按3倍余额计算折旧

在单元格E4中输入公式“=DDB（$A$2,$B$2,$C$2,C4,$E$2）”，按下Enter键后，复制公式至单元格E11，得到如图4-9所示的计算结果。从结果可以看出，3倍余额进一步加快了固定资产折旧，在第5年末就提前完成了该项资产的折旧计提。

小技巧：DDB函数与DB函数的区别

两种折旧方法都属于加速折旧法，与DB函数不同的在于，DDB函数中不需要指出第一年使用的月份数。

图4-9　按3倍余额递减计算折旧

Excel 固定余额递减折旧法与DB函数

固定余额递减折旧法是一种加速折旧法，即在预计使用年限内，将后期折旧的一部分移到前期，使前期折旧额大于后期折旧额。固定余额递减折旧法的计算公式为：

年折旧额=（资产原值-前期折旧总值）×固定的年折旧率

其中固定的年折旧率的计算公式为：

年折旧率=1-（（残值/资产原值）^（1/折旧期限））

这个比率是固定不变的，然后乘以逐年递减的固定资产现有价值。

在Excel中，使用固定余额递减法计算折旧的函数为DB函数，它的语法格式为：

DB（cost,salvage,life,period,month）

该函数共有5个参数，cost指资产原值；salvage指资产残值；life指使用年限；period为需要计算折旧值的期间；month为每一年的月份数，如省略该参数，则默认值为12。

该函数对于第一个周期和最后一个周期的折旧属于特例。对于第一个周期，函数DB的计算公式为：

资产原值×年折旧率×第一年的月份数/12

对于最后一个周期，函数DB的计算公式为：

（（资产原值-前期折旧总值）×年折旧率×（12-第一年的月份数））/12

例如，已知某企业在2005年3月购进一部价值15万元的大型设备，使用年限为8年，预计净残值为2万元。现要求按固定余额递减折旧法计算该设备每年的折旧费以及累计折旧额。

步骤01：打开实例文件“固定余额递减折旧法计算模型.xlsx”工作簿。输入公式计算第一年折旧额。在单元格D4中输入公式“=DB（A2,B2,C2,C4,D2）”，按下Enter键后，计算结果如图4-4所示。

图4-4　输入公式

步骤02：更改参数的引用方式。由于公式中对于资产原值、资产残值等参数的引用都是固定的单元格，为了在后面能拖动复制公式，先将公式中引用固定单元格的方式更改为绝对引用，即将单元格D4中的公式更改为“=DB（$A$2,$B$2,$C$2,C4,$D$2）”，如图4-5所示。

步骤03：拖动单元格D4右下角的填充柄，向下复制公式至单元格D11，计算出其余年份的折旧额，如图4-6所示。

步骤04：在单元格D13中输入公式“=SUM（D4:D11）”，计算出累计折旧额，如图4-7所示。从结果可以看出，累计折旧额加上资产残值约等于资产原值。

小技巧：快速更改公式中单元格的引用方式

在更改公式中单元格的引用方式时，除了可以手动输入绝对引用符$外，还可以通过按功能键F4来转换引用方式。

图4-5　更改参数的引用方式

图4-6　复制公式

图4-7　计算累计折旧额

Excel 直线折旧法与SLN函数

直线折旧法即平均年限法，它假定折旧是由于时间的推移而不是使用的关系造成的，认为服务潜力降低的决定因素是随时间推移所造成的陈旧和破坏，而不是使用所造成的有形磨损。因而假定资产的服务潜力在各个会计期间所使用的服务总成本是相同的，而不管其实际使用程度如何。它是按固定资产的使用年限平均计提折旧的一种方法，是最简单、最普遍的折旧方法。平均年限法适用于各个时期使用情况大致相同的固定资产折旧。

直线折旧法的计算公式如下：

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

预计净残值=固定资产报废时的预计的残余价值-预计的报废清理费用

年折旧率=（固定资产的年折旧额/固定资产的原始价值）×100%

在Excel中，用户无须再按上面的公式进行数学运算，只需要调用对应的直线折旧函数SLN，正确设置函数参数，即可由函数自动计算出结果。

SLN函数的格式为：SLN（cost,salvage,life），它一共有3个参数，cost为资产原值，salvage为资产残值，life为使用年限。

例如，已知某企业购进一部价值1258000元的大型设备，使用年限为15年，预计净残值为15000元。现要求按直线折旧法，计算该设备每年的折旧费以及年折旧率。

步骤01：打开实例文件“直线折旧法计算模型.xlsx”工作簿。在单元格B5中输入“=SLN（”，此时，屏幕上会自动显示出该函数的语法提示，用户可以根据屏幕提示依次设置函数参数，如图4-1所示。

图4-1　输入函数名

步骤02：在单元格B5中输入完整的公式“=SLN（B2,B3,B4）”，按下Enter键后，得到如图4-2所示的计算结果。

图4-2　输入公式

步骤03：在单元格B6中输入公式“=B5/B2”，按下Enter键后，将该单元格格式设置为“百分比”，得到如图4-3所示的年折旧率。

图4-3　计算年折旧率