Excel数据管理应用教程第六章 财务分析函数及应用第六章 财务分析函数及应用 6.1投资计算函数 6.2折旧计算函数 6.3偿还率计算函数
6.4债券分析函数6.1投资计算函数投资计算函数常见参数说明贷款定额还款期数计算函数NPER 贷款分期偿还金额计算函数PMT贷款
利息、本金金额计算函数IPMT、PPMT投资未来值计算函数FV投资偿还额现值计算函数 PV投资定期现金流净现值计算函数投资不定期现
金流净现值计算函数XNPV投资计算函数常见参数说明Rate：表示各期利率，是一个固定值Pmt：表示各期所应收取（或支付）的金额，其
数值在整个年金期间（或投资期内）保持不变，通常pmt包括本金和利息，但不包括其他费用和税款Pv：表示从该项投资或贷款开始计算时已经
入帐的款项，或一系列未来付款当前值的累积和，也称为本金Fv：表示未来值，或在最后一次付款后可以获得的现金余额Type：用以指定各期
的付款时间是在期初还是期末(0为期末，1为期初)，如果省略type，则假设其值为0Nper：总投资（或贷款）期，即该项投资（或贷款
）的付款期总数Per：用于计算其本金数额的期数(介于1到nper之间)上一页一、贷款定额还款期数计算函数NPER １.实例１ 超市
贷款还款期数预测 【实例内容】：由于拓展业务需要，欲向银行贷款1,000,000元。根据新风超市目前的经济与经营状况进行判断，再综
合其他各种因素考虑，新风超市每月有能力且最适合的还款额为12,000元，若以7.50%的平均年利率作为参考，选择期（月）末付款方式
，至少需要多久能还清贷款？ 一、贷款定额还款期数计算函数NPER 【实例操作步骤】： 步骤1：构造数据模型 。如图6-1所示。 步
骤2：选定B4单元格，在辑栏中输入“=NPER(B2/12,-B3,B1)”。步骤3：单击“输入”按钮，此时可以这笔贷款方案需要偿
还的时间（月）。 图6-1 例6.1数据模型一、贷款定额还款期数计算函数NPER ２. NPER函数知识介绍1）NPER函数功能
：基于固定利率及等额分期付款方式，返回一项投资或贷款的期数。 2）语法形式：NPER（rate，pmt，pv[，fv，type]）
3）参数说明 4）注意点：第一所有期数的单位要统一 第二所有的入帐用正数表示，支付额用
负数表示 二、贷款分期偿还金额计算函数PMT １.实例2 超市贷款分期偿还金额计算 【实例内容】：君华银行向新风超市提供
了四种基于固定利率及等额分期期末付款的贷款方案，还款期数及年利率如表6-2所示，新风超市应该选择哪一种贷款方案比较合适？ 表6-2
君华银行贷款方案表二、贷款分期偿还金额计算函数PMT【实例操作步骤】： 步骤1：构造数据模型 。如图6-3所示。 步骤2：选定
D8单元格，在编辑栏中输入“=PMT(C8/12,B812,$B$1)”。 步骤3：单击“输入”按钮，此时可以看到第一种贷款方案
的每月还款额。 图6-3例6.2数据模型 二、贷款分期偿还金额计算函数PMT２. PMT函数知识介绍1）PMT函数功能：基于固定利
率及等额分期付款方式，返回投资或贷款的每期付款额。 2）语法形式：PMT(rate，nper，pv[，fv，type]) 3）
参数说明 三、贷款利息、本金金额计算函数IPMT、PPMT １.实例3 超市贷款分期偿还利息与本金金额计算 【实例内容】：在实
例2中新风超市确定贷款方案后，第一年每月偿还的利息和本金分别是多少？ 三、贷款利息、本金金额计算函数IPMT、PPMT【实例操作步
骤】： 步骤1：构造数据模型 。如图6-5所示。 步骤2：选定B14单元格，在编辑栏中输入“=IPMT($C$9/12,A14,$
B$912,$B$1)”。 步骤3：选定C14单元格，在编辑栏中输入“=PPMT($C$9/12,A14,$B$912,$B
$1)”。 步骤4：用填充柄填充B和C两列，可以看到结果。图6-5 例6.3数据模型 三、贷款利息、本金金额计算函数IPMT、PP
MT２. IPMT函数知识介绍1）IPMT函数功能：基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期次内的利息偿还额。
2）语法形式：IPMT(rate，per，nper，pv[，fv，type]) 3）参数说明 三、贷款利息、本金金额计算函
数IPMT、PPMT3. PPMT函数知识介绍1）PPMT函数功能：基于固定利率及等额分期付款方式，返回投资或贷款在某一给定期次内
的本金偿还额。 2）语法形式：PPMT(rate，per，nper，pv[，fv，type]) 3）参数说明 四、投资未来
值计算函数FV １.实例4 超市存款投资未来值计算 【实例内容】：新风超市计划在5年后创办一所新风希望小学，需要资金200,000
元。从本年开始筹备资金，打算在每月月初存入5,000元，在整个投资期间内，平均年投资回报率为7.6%，3年后这笔存款是否足够创办新
风希望小学？ 四、投资未来值计算函数FV【实例操作步骤】： 步骤1：构造数据模型 。如图6-7所示。 步骤2：选定B30单元格，在
编辑栏中输入“=FV(B27/12,B2812,-B29,,1)”。 步骤3：单击“输入”按钮，此时可以看到这笔存款3年后的价值
。 图6-7 例6.4数据模型 四、投资未来值计算函数FV２. FV函数知识介绍 1）FV函数功能：基于固定利率及等额分期付款方式
，返回某项投资的未来值。 2）语法形式：FV(rate，nper，pmt[，pv，type]) 3）参数说明 五、投资偿还
额现值计算函数 PV １.实例5 超市保险投资决策 【实例内容】：新风超市计划为高级员工提高福利，购买一笔医疗保险，购买的成本为2
8,000元，该保险可以在今后30年中每月末回报150元，投资年回报率为5.6%。新风超市购买这份保险是否划算？ 五、投资偿还额现
值计算函数 PV【实例操作步骤】： 步骤1：构造数据模型 。如图6-9所示。 步骤2：选定B35单元格，在编辑栏中输入“=PV(B
32/12,B3312,B34)”。 步骤3：单击“输入”按钮，此时可以看到这份保险的现值。 图6-9 例6.5数据模型 五、投
资偿还额现值计算函数 PV２. PV函数知识介绍1）PV函数功能：返回投资的现值。现值为一系列未来付款当前值的累积和，可以作为一种
权衡长期投资的方法。 2）语法形式：PV(rate，nper，pmt[，fv，type]) 3）参数说明 六、投资定期现金
流净现值计算函数NPV １.实例6 超市收购投资决策 【实例内容】：新风超市计划收购一小超市，收购成本为500,000元，必须预先
付清，经过市场调查，预计今后6年的营业收入可达到110,000元，130,000元，160,000元，180,000元，190,0
00元和210,000元，每年的贴现率为8.7%。这项收购计划是否可行？ 六、投资定期现金流净现值计算函数NPV【实例操作步骤】：
步骤1：构造数据模型 。如图6-11所示。 步骤2：选定B45单元格，在编辑栏中输入“=NPV(B38,B39:B44)-B37
”。 步骤3：单击“输入”按钮，此时可以看到收购小超市投资计划的当前纯利润。 图6-11 例6.6数据模型 六、投资定期现金流净现
值计算函数NPV２. NPV函数知识介绍1）NPV函数功能：计算一组定期现金流的净现值。通过使用贴现率以及一系列未来支出（负值）和
收入（正值），返回一项投资的净现值（当前纯利润）。 2）语法形式：NPV(rate，value1，value2， ...) 3
）参数说明 ：Rate是应用于现金流的贴现率，为一固定值。value1，value2，...代表1到29笔支出及收入的参数值，va
lue1，value2，...所属各期间的长度必须相等，而且支付及收入的时间都发生在期末。如果支付的时间发生在期初，不能作为val
ue参数，而是将收入转换成净现值后再扣除支付金额。 七、投资不定期现金流净现值计算函数XNPV １.实例7 超市项目投资决策
【实例内容】：新风超市计划在2008年5月3日拿出500,000元资金用于某项投资，预计可于如下时间内分4次获取返回资金：2008
年12月3日返回120,000元，2009年2月3日返回160,000元，2009年5月3日返回160,000元，2009年12月
3日返回90,000元，资金流转折扣为8.2%，这项投资是否划算？ 七、投资不定期现金流净现值计算函数XNPV【实例操作步骤】：
步骤1：构造数据模型 。如图6-13所示。 图6-13 例6.7数据模型 七、投资不定期现金流净现值计算函数XNPV步骤2：将A5
0：A54单元格区域格式设置为“日期”。结果如图6-14所示。 步骤3：选定B55单元格，在编辑栏中输入“=XNPV(B47,B5
0:B54,A50:A54)”。 步骤4：单击“输入”按钮，此时可以看到这项投资计划的当前纯利润。 图6-14 转换日期序列 七、
投资不定期现金流净现值计算函数XNPV２. XNPV函数知识介绍1）XNPV函数功能：返回一组现金流的净现值，这些现金流不一定定期
发生。 2）语法形式：XNPV(rate，values，dates) 3）参数说明 ： rate是应用于现金流的贴现率，
为一固定值。 dates是与现金流支付相对应的支付日期表。 values是与dates中的支付时间相对应的一系列现金
流转。 6.2 折旧计算函数折旧计算函数常见参数说明直线折旧函数SLN固定余额递减折旧函数DB双倍余额递减折旧函数DDB年限总
和折旧函数SYD可变余额递减折旧函数VDB 折旧计算函数常见参数说明Cost：固定资产原值Salvage：固定资产使用年限终了时的
估计残值Life：固定资产进行折旧计算的周期总数Period：进行折旧计算的期次，必须与life使用的单位相同上一页一、五种折旧计
算函数１.实例8 计算超市货架的折旧金额 【实例内容】：新风超市计划将现有一批已使用3年的货架转手卖出，3年前的购买价值为50,0
00元，使用年限为8年，预计报废价值为15,00元，使用直线折旧，固定余额递减折旧，双倍余额递减折旧，年限总和折旧和可变余额递减折
旧5种不同的折旧方法计算出这批货架这3年的折旧金额。 一、五种折旧计算函数【实例操作步骤】： 步骤1：构造数据模型 。如图6-16
所示。 图6-16 例6.8数据模型 一、五种折旧计算函数步骤2：选定B7单元格，在编辑栏中输入“=SLN($B$1,$B$2
,$B$3)”。 步骤3：选定C7单元格，在编辑栏中输入“=DB($B$1,$B$2,$B$3,A7)”。 步骤4：选定D7单元格
，在编辑栏中输入“=DDB($B$1,$B$2,$B$3,A7)”。 步骤5：选定E7单元格，在编辑栏中输入“=SYD($B$1,
$B$2,$B$3,A7)”。 步骤6：选定F7单元格，在编辑栏中输入“=VDB($B$1,$B$2,$B$3,A7-1,A7,1
.5)”。 步骤7：用填充柄工具填充B、C、D、E、F列，可以看到结果。 一、五种折旧计算函数２. SLN函数知识介绍1）SLN函
数功能：用直线折旧的计算方法，返回一项资产每期的直线折旧费。 2）语法形式：SLN(cost，salvage，life) 3）
参数说明 一、五种折旧计算函数3. DB函数知识介绍1）DB函数功能：用固定余额递减的计算方法，返回一项资产在给定期间内的折旧
值。用于计算固定利率下的资产折旧金额。 2）语法形式：DB(cost，salvage，life，period[，month])
3）参数说明 ：month为第一年的月份数，如省略，则假设为12。 一、五种折旧计算函数4. DDB函数知识介绍1）DDB函数
功能：用双倍余额递减法或其他指定计算方法，返回一笔资产在给定期间内的折旧值。 2）语法形式：DDB(cost，salvage，l
ife，period[，factor]) 3）参数说明 ：factor参数为余额递减速率，也称为折旧因子。如果factor被省略
，则假设为2，即双倍余额递减法。这5个参数都必须为正数。 一、五种折旧计算函数5. SYD函数知识介绍1）SYD函数功能：用年
限总和折旧计算方法，返回一笔资产在给定期间内的折旧值。 2）语法形式：SYD(cost，salvage，life，per) 3
）参数说明 ：per参数为期间，单位与life相同。 一、五种折旧计算函数6. VDB函数知识介绍1）VDB函数功能：用可变余
额递减计算方法，返回一笔资产在给定期间内的折旧值。 2）语法形式： VDB(cost，salvage，life，star_per
iod，end_period，factor，no_switch) 3）参数说明 ：star_period参数为进行折旧计算的起始
期间，单位与life相同。End_period参数为进行折旧计算的截止期间，单位与life相同。factor参数为余额递减速率，也
称为折旧因子。如果factor被省略，则假设为2，即双倍余额递减法。No_switch参数指定当折旧值大于余额递减计算值时，是否转
用直线折旧法，为一逻辑值。 6.3偿还率计算函数 投资偿还率计算函数RATE投资定期现金流内部收益率计算函数IRR投资修正收益
率计算函数MIRR一、投资偿还率计算函数RATE １.实例9 银行的贷款偿还率计算 【实例内容】：新风超市于5年前创建，创建初期向
银行贷款200,000元，期限5年，每个月偿还4,000元，这笔贷款银行的年收益率是多少？ 一、投资偿还率计算函数RATE 【实例
操作步骤】： 步骤1：构造数据模型 。如图6-18所示。 步骤2：选定B4单元格，在编辑栏中输入“=RATE(B212,-B3,
B1)”,单击“输入”按钮 。步骤3：选定B5单元格，在编辑栏中输入“=B412”，单击“输入”按钮，此时可以看到银行的年收益率
。 图6-18例6.9数据模型 一、投资偿还率计算函数RATE ２.RATE函数知识介绍 1）RATE函数功能：返回年金的各期利率
。 2）语法形式：RATE(nper，pmt，pv，fv，type，guess) 3）参数说明 :guess参数为预期利率。如
果省略，则假设该值为10%。 二、投资定期现金流内部收益率计算函数IRR １.实例10 超市投资定期现金流内部收益率计算 【实例
内容】：5年来新风超市每年的收入分别为50,000元，70,000元，100,000元，110,000元，150,000元和180
,000元，期初投资的200,000元现在内部收益率是多少？ 二、投资定期现金流内部收益率计算函数IRR【实例操作步骤】： 步骤1
：构造数据模型 。如图6-21所示。 步骤2：选定B13单元格，在编辑栏中输入“=IRR(B7:B12)”。 步骤3：单击“输入”
按钮，此时可以看到新风超市近5年的内部收益率。 图6-21 例6.10数据模型 二、投资定期现金流内部收益率计算函数IRR２.IR
R函数知识介绍 1）IRR函数功能：返回由数值代表的一组现金流的内部收益率。 2）语法形式：IRR(values，guess)
3）参数说明 : Values参数为数组或单元格的引用，包含用来计算返回的内部收益率的数字。 Guess参数为对函数IRR
计算结果的估计值。 三、投资修正收益率计算函数MIRR １.实例11 超市投资修正收益率计算 【实例内容】：5年来，新风超市将
每年所获得的收益用于重新投资，每年的收益率为10%，那开业5年后的修正收益率是多少？ 三、投资修正收益率计算函数MIRR【实例操作
步骤】： 步骤1：构造数据模型 。如图6-23所示。 步骤2：选定B16单元格，在编辑栏中输入“=MIRR(B7:B12,B5,B
15)”。 步骤3：单击“输入”按钮，此时可以看到新风超市近5年的内部修正收益率。 图6-23 例6.11数据模型 三、投资修正收
益率计算函数MIRR２.MIRR函数知识介绍 1）MIRR函数功能：返回某一连续期间内现金流的修正内部收益率。函数MIRR同时考虑
了投资的成本和现金再投资的收益率。 2）语法形式：MIRR(values，finance_rate，reinvest_rate)
3）参数说明 : Values参数为一个数组或对包含数字的单元格的引用。 Finance_rate参数为现金流中使用的资
金支付的利率。 Reinvest_rate参数为将现金流再投资的收益率。 6.4债券分析函数 债券分析函数常见参数说明有价证券
应计利息计算函数ACCRINT有价证券价格计算函数PRICE一次性付息证券利率计算函数INTRATE有价证券贴现率计算函数DISC
有价证券收益率计算函数YIELD债券分析函数常见参数说明Issue:有价证券的发行日first_interest:证券的起息日Se
ttlement:证券的成交日Rate:有价证券的年息票利率Par:有价证券的票面价值，如果省略 par，视为 $1000Freq
uency:年付息次数，如果按年支付，frequency = 1；按半年期支付，frequency = 2；按季支付，freque
ncy = 4。Basis:日计数基准类型:0或省略-US(NASD) 30/360；1-实际天数/实际天数；2-实际天数/360
；3-实际天数/365；4-欧洲 30/360Maturity:债券到期日期Yld:有价证券的年收益率Redemption:有价证
券到期时的清偿价值Discount:有价证券的贴现率Investment”有价证券的投资额pr:有价证券的价格上一页一、有价证券应
计利息计算函数ACCRINT １.实例12 有价证券应计利息计算 【实例内容】：新风超市员工小王于2007年5月16日购买了价值1
0,000元的债券，该债券的发行日期是2007年3月1日，起息日期为2007年7月31日，息票利率为15.0%，按半年期支付，日计
数基准30/360，计算该债券的应计利息。 一、有价证券应计利息计算函数ACCRINT【实例操作步骤】： 步骤1：构造数据模型 。
如图6-25所示。 步骤2：选定B8单元格，在编辑栏中输入“=ACCRINT(B1,B2,B3,B4,B5,B6,B7)”。 步骤
3：单击“输入”按钮，此时可以看到小王所购买的债券的应计利息。 图6-25 例6.12数据模型 一、有价证券应计利息计算函数ACC
RINT２. ACCRINT函数知识介绍1）ACCRINT函数功能：返回定期付息有价证券的应计利息。 2）语法形式：ACCRIN
T (issue，first_interest，settlement，rate，par，frequency，basis) 3）参
数说明 4）注意点：若要计算到期一次性付息有价证券的应计利息可用ACCRINTM函数，语法形式为ACCRINTM（issue，
maturity，rate，par，basis） 二、有价证券价格计算函数PRICE １.实例13 有价证券价格计算 【实例内容】
：新风超市员工小林于2003年5月14日购买了面值为100元的债券，该债券的到期日期是2008年5月31日，债券息票利率为13.2
%，按半年期支付，收益率为15.3%，日计数基准为实际天数/365，计算该债券的发行价格。 二、有价证券价格计算函数PRICE【实
例操作步骤】： 步骤1：构造数据模型 。如图6-27所示。 步骤2：选定B17单元格，在编辑栏中输入“=PRICE(B10,B11
,B13,B14,B12,B15,B16)”。 步骤3：单击“输入”按钮，此时可以看到小林所购买的债券的发行价格。 图6-27
例6.13数据模型 二、有价证券价格计算函数PRICE２. PRICE函数知识介绍1）PRICE函数功能：返回定期付息的面值 $1
00 的有价证券的价格。 2）语法形式：PRICE(settlement，maturity，rate，yld，redemption
，frequency，basis) 3）参数说明 4）注意点：若要计算折价发行的面值 $100 的有价证券的价格可用PRIC
EDISC函数，语法形式为PRICEDISC（settlement，maturity，discount，redemption，ba
sis）。若要计算到期付息的面值 $100 的有价证券的价格可用PRICEMAT函数，语法形式为PRICMAT（settlemen
t，maturity，issue，rate，yld，basis）。 三、一次性付息证券利率计算函数INTRATE １.实例14 一
次性付息证券利率计算 【实例内容】：新风超市员工小李于2002年3月19日购买了价值10,000元的债券，该债券的到期日期是200
7年9月20日，到期时的返还金额为17,278元，日计数基准30/360，计算该债券的利率。 三、一次性付息证券利率计算函数INT
RATE【实例操作步骤】： 步骤1：构造数据模型 。如图6-29所示。 步骤2：选定B17单元格，在编辑栏中输入“=INTRATE
(B19,B20,B21,B22,B23)”。步骤3：单击“输入”按钮，此时可以看到小李所购买的债券的利率。 图6-29 例6.1
4数据模型 三、一次性付息证券利率计算函数INTRATE２. INTRATE函数知识介绍1）INTRATE函数功能：返回一次性付息
证券的利率。 2）语法形式：INTRATE(settlement，maturity，investment，redemption，
basis) 3）参数说明 四、有价证券贴现率计算函数DISC １.实例15 有价证券贴现率计算 【实例内容】：新风超市员工
小曹于2005年11月19日购买债券，该债券的到期日期是2009年9月15日，价格为70.32元，清偿价格为100元，日计数基准实
际天数/360，计算该债券的贴现率。 四、有价证券贴现率计算函数DISC【实例操作步骤】： 步骤1：构造数据模型 。如图6-31所
示。 步骤2：选定B31单元格在编辑栏中输入“DISC(B26,B27,B28,B29,B30)”。 步骤3：单击“输入”按钮，此
时可以看到小曹所购买的债券的贴现率。 图6-31 例6.15数据模型 四、有价证券贴现率计算函数DISC２. DISC函数知识介绍
1）DISC函数功能：返回有价证券的贴现率。 2）语法形式：DISC(settlement，maturity，pr，redemp
tion，basis) 3）参数说明 五、有价证券收益率计算函数YIELD １.实例16 有价证券收益率计算 【实例内容】：
新风超市员工小郑于2001年11月21日购买了价格为76元的票面价值100元的债券，该债券的到期日期是2007年9月15日，息票利
率为7.98%，按半年期支付，日计数基准实际天数/365，计算该债券的收益率。 五、有价证券收益率计算函数YIELD【实例操作步骤】： 步骤1：构造数据模型 。如图6-33所示。 步骤2：选定B40单元格，在编辑栏中输入“YIELD(B33,B34,B35,B36,B37,B38,B39)”。 步骤3：单击“输入”按钮，此时可以看到小曹所购买的债券的收益率。 图6-33例6.16数据模型 五、有价证券收益率计算函数YIELD２. YIELD函数知识介绍1）YIELD函数功能：用于计算债券收益率，返回定期付息有价证券的收益率。 2）语法形式：YIELD(settlement，maturity，rate，pr，redemption，frequency，basis) 3）参数说明 小结在日常生活使用Excel过程中，普遍要涉及到财务方面的计算，如贷款、投资回报、折旧等，其特点是运算量大，耗时多，数据复杂，如果欠缺财务专业知识更加无法完成。Excel中财务函数使用时不必理解具体的计算方法，只要知道函数的功能按照要求填写变量值就可以了。财务函数就是为了解决一般的财务计算问题，比如投资未来值和现值计算，资产折旧额计算，债券价值计算等。财务函数中的投资计算函数、折旧计算函数、偿还率计算函数、债券及其他金融函数都为财务分析提供了极大的便利。本章将Excel中的常用财务函数，应用于辅助新风超市解决贷款、投资和资产折旧等实际问题，涉及到常用财务函数的基本格式，参数和使用注意事项。Excel能辅助解决现实生活中诸多财务方面的问题，读者可参考本章，建立适合问题的数据模型，再使用合适的财务函数加以计算解决。 |
|
