《Excel与财务分析》PPT课件.ppt

第6章Excel与财务分析 Excel在经济管理中的应用 认识 Excel提供了许多有关财务 投资 偿还 利息及折旧方面的函数 在工作表中运用这些函数可以较松地完成相关的财务运算 或者对其他财务管理软件的运算输出数据进行验证 本章学习目标 1 掌握常用折旧函数的用法2 了解常用投资函数的使用方法3 理解常用债券分析函数的使用方法4 了解常用计算偿还率函的用法5 能够用上述几类函数解决实际问题 6 1计算折旧的函数 1 折旧函数Excel折旧函数有DB DDB SLN YDB及VDB 用这5个折旧函数可以确定指定时期内资产的折旧值 这5个折旧函数有4个参数是共有的 如表所示 6 1计算折旧的函数 DB函数用法DB cost salvage lIFe period month 其中 lIFe和period须使用相同的时间单位 month为第一年的月份数 如果省略 则默认为12 功能DB函数使用固定余额递减法 计算资产在给定期限的折旧值例如某学校买了一批计算机 价值为500000元使用期限为3年 报废价值为100000元 每年的折旧公式及结果值如下所示 DB 500000 100000 3 1 6 103750 00DB 500000 100000 3 2 6 164443 75DB 500000 100000 3 3 6 96199 59 6 1计算折旧的函数 DDB函数用法 DDB cost salvage lIFe period factor 其中 factor为余额递减速率 如factor省略 则默认为2 双倍余额递减法 5个参数都必须为正数功能DDB函数使用双倍余额递减法或其他指定方法 计算一笔资产在给定期限内的折旧值例如 学校花10万元购买了一台新设备 使用期限为10年 报废价值为1万 下面的例子给出几个期限内的折旧值 结果保留两位小数 DDB 100000 10000 3650 1 54 79 第一天的折旧值 factor被Excel默认设置设为2 DDB 100000 10000 120 1 1666 67 第一个月折旧值 DDB 100000 10000 10 1 20000 00 第一年的折旧值 DDB 100000 10000 10 3 1 5 10837 50 第三年的折旧 这里没有使用双倍余额递减法 factor 1 5 6 1计算折旧的函数 SYD SLN VDB函数用法SYD cost salvage life per SLN cost salvage life VDB cost salvage lIFe start period end period factor no switch 功能SYD函数计算某项资产按年限总和折旧法计算的某期限内的折旧值SLN函数计算一项资产每期的直线折旧值VDB函数代表可变余额递减法 可使用双倍递减余额法或其他指定的方法 计算指定期间内或某一时间段内的资产折旧额 6 2投资函数 1 投资函数的参数Excel投资分析方面的函数 这些函数使用的参数大致相同 意义相近 如下表所示 6 2投资函数 2 PMT函数用法PMT rate nper pv fv type 说明 PMT返回的支付款项包括本金和利息 但不包括税款 rate和nper单位要一致 功能PMT基于固定利率及等额分期付款方式 计算投资或贷款的每期付款额 例如 某人买房了 贷款200000元 利率为7 分10个月付清 则他的月支付额为 PMT 7 12 10 200000 20647 26 6 2投资函数 3 PV函数用法PV rate nper PMT fv type 功能PV函数可以计算投资的现值 现值是一系列未来付款当前值的累积和 如借入方的借入款即为贷出方贷款的现值 例如 某人想买一笔养老保险 该保险可以在今后25年内于每月末回报500元 该保险的购买成本为75000 假定投资回报率为6 7 该保险是否合算 现在可以通过函数PV计算一下这笔投资是否值得 该项投资的年金现值为 PV 0 067 12 12 25 500 0 72700 05 6 2投资函数 4 FV函数用法FV rate nper PMT pv type 功能FV函数计算投资在将来某个日期的价值 它可以计算出投资的一次性偿还金额 也可以计算出一系列数额相等的分期偿还金额 例如某人为保证退休后的生活 打算每年年初存入3000元 在整个投资期间 平均投资回报率为7 此人今年30岁 到他60岁时 有多少存款 计算方法如下 FV 7 30 2000 1 202146 08 6 2投资函数 5 XNPV函数用法XNPV rate values dates 功能XNPV函数计算一组现金流的净现值 这些现金流不一定定期发生例如假定某项投资需要在2002年3月2日支付现金30000元 并于下述时间获取以下金额的返回资金 2002年4月21日返回8750元 2002年6月10日返回7250元 2002年7月30日返回16250元 2002年9月18日返回9750元 假设资金流转折扣为7 则净现值为 XNPV 0 07 30000 8750 7250 16250 9750 37317 37667 38017 38367 38717 5465 45 6 2投资函数 6 其它投资函数NPV rate value1 value2 IPMT rate per nper pv fv type PPMT rate per nper pv fv type NPER rate PMT pv fv type 6 3计算偿还率的函数 1 RATE函数用法RATE nper PMT pv fv type guess 功能RATE函数用于计算投资的各期利率 可以计算连续分期等额投资的偿还率 也可以计算一次性偿还的投资利率例如某人修房贷款100000元的6年期贷款 月支付额为2200元 该笔贷款的利率为 RATE 12 6 2200 100000 1 38 6 3计算偿还率的函数 2 IRR函数用法IRR values guess 功能IRR函数计算由数值代表的一组现金流的内部收益率 内部收益率是指投资偿还的固有率 它是引起投资的净现值等于零的比率例如某人投资70000元开设一家录像带租借店 并预期今后5年的净收益为 12000元 15000元 18000元 21000元和26000元 试计算该项投资的内部收益率 在Excel工作表的B1 B6中分别输入下面的数值 70000 12000 15000 18000 21000和26000 该投资4年后的内部收益率为 IRR B1 B5 2 12 6 4债券分析函数 1 债券分析函数的参数Excel提供了许多计算与分析债券的函数 运用这些函数可以较方便地进行各种类型的债券分析 债券分析函数与数据分析工具有关 它们由 分析工具库 加载宏工具提供 如果在Excel的工作表中引用债券分析函数时发现函数不存在 可以通过工具中的 加载宏 命令 把 分析工具库 加载到Excel系统中 只有这样才能使用债券分析函数 多数债券分析所使用的参数都基本相同 如下表所示 6 4债券分析函数 6 4债券分析函数 2 ACCRINT ACCRINTM函数功能ACCRINT函数计算定期付息有价证券的应计利息 ACCRINTM函数用于计算到期一次性付息有价证券的应计利息 格式 ACCRINT issue first interest settlement rate par frequency basis ACCRINTM issue maturity rate par basis 6 4债券分析函数 案例 例 某国库券2000年1月8日发行 2000年10月1日为起息日 成交日期是2000年6月1日 息票利率10 0 票面价值2000元 按半年期付息 日计数基准30 360 则应计利息为 ACCRINT 2000 1 8 2000 10 1 2000 6 1 0 1 2000 2 0 79 4444表明2000年6月1日的利息为79 4444元 例 一短期债券的交易情况如下 2001年4月1日发行 2001年6月15日到期 息票利息10 0 票面价值是1000元 日计数基准 实际天数 365 则应计利息 ACCRINTM 2001 4 1 2001 6 15 0 1 1000 3 20 54795 6 4债券分析函数 3 INTRATE RECEIVED函数功能INTRATE函数计算一次性付息证券的利率 RECEIVED函数计算一次性付息的有价证券到期收回的金额 格式 INTRATE settlement maturity investment redemption basis RECEIVED settlement maturity investment discount basis 6 4债券分析函数 案例 例7 某债券于2001年2月15日成交 2001年5月15日到期 总投资额为1000000万元 清偿价值为1014420元 日计数基准为 实际天数 360 则该债券贴现率为 INTRATE 2001 2 15 2001 5 15 1000000 1014420 2 0 058328 例7 某债券于2001年2月15日发行 2001年5月15日到期 总投资额为 1000000 贴现率为5 75 日计数基准 实际天数 360 则该证券到期日可回收的总金额为 RECEIVED 2001 2 15 2001 5 15 1000000 0 0575 2 1014420 266 6 4债券分析函数 4 PRICE PRICEDISC PRICEMAT功能PRICE函数计算定期付息的面值100元的有价证券的价格 PRICEDISC函数计算折价发行的面值100元的有价证券的价格 PRICEMAT函数计算到期付息的面值100元的有价证券的价格格式 PRICE settlement maturity rate yld redemption frequency basis PRICEDISC settlement maturity discount redemption basis PRICEMAT settlement maturity issue rate yld basis 6 4债券分析函数 案例 例 某债券于2000年11月11日发行 2001年2月15日成交 2002年4月13日到期 半年息票利率为6 1 收益率为6 1 日计数基准为30 360 则该债券的价格为 PRICEMAT 2001 2 15 2002 4 13 2000 11 11 0 061 0 061 0 99 89465 6 4债券分析函数 5 DISC函数功能DISC函数计算有价证券的贴现率 格式 DISC settlement maturity pr redemption basis 案例某债券于2001年2月15日成交 2001年6月10日到期 价格为97 975元 清偿价格为100元 日计数基准 实际天数 360 则该债券的贴现率为 DISC 2001 2 15 2001 6 10 97 975 100 2 0 063391 或6 3391 6 4债券分析函数 6 YIELD YIELDDISC YIELDMAT功能YIELD函数计算定期付息有价证券的收益率 YIELD函数用于计算债券收益率 YIELDDISC函数计算折价发行的有价证券的年收益率 YIELDMAT函数到期付息的有价证券的年收益率格式 YIELD settlement maturity rate pr redemption frequency basis YIELDDISC settlement maturity pr redemption basis YIELDMAT settlement maturity issue rate pr basis 6 4债券分析函数 7 TBILLEQ TBILLYIELD TBILLPRICE功能COUPDAYBS函数计算当前付息期内截止到成交日的天数 COUPDAYSNC函数计算从成交日到下一付息日之间的天数 COUPDAYS函数计算成交日所在的付息期的天数 COUPNUM函数计算成交日和到期日之间的利息应付次数 向上取整到最近的整数 COUPPCD函数计算成交日之前的上一付息日的日期 格式COUPDAYBS settlement maturity frequency basis COUPDAYSNC settlement maturity frequency basis COUPDAYS settlement maturity frequency basis COUPNUM settlement maturity frequency basis COUPPCD settlement maturity frequency basis 6 4债券分析函数 8 DURATION函数功能DURATION函数计算面值100元的定期付息有价证券的修正期限 期限定义为一系列现金流现值的加权平均值 用于计量债券价格对于收益率变化的敏感程度 格式 DURATION settlement maturity coupon yld frequency basis 6 5Excel财务函数应用 案例1 内含报酬率计算某方案投资及营业现金流量如表7 5所示 求内含报酬率 设所求内含报酬率为i 令 则方程的解为所求 6 5Excel财务函数应用 案例2固定资产折旧计算 直线法某项固定资产原值为30000元 预计净残值为1800元 使用年限为4年 选定Excel的一张工作表 输入SLN函数及其他内容 如表所示 6 5Excel财务函数应用 固定资产折旧 余额递减法 例如 某项固定资产原值为4000元 预计净残值为400元 使用期限为8年 选定Excel的一张工作表 输入DB函数 可利用Excel的自动填充功能快捷输入 及其他内容 如表所示 6 5Excel财务函数应用 固定资产折旧 双倍余额递减法例如 某项固定资产原值为16000元 预计净残值为500元 使用期限为5年 选定Excel的一张工作表 输入DDB函数及其他内容 如表所示 6 5Excel财务函数应用 年限总和法利用SYD函数 例如 某项固定资产原值为10000元 预计净残值为1000元 使用年限为5年 选定Excel的一张工作表 输入SYD函数及其他内容 如表所示 TheEnd