《专题：EXCEL在投资学中的应用》由会员分享，可在线阅读，更多相关《专题：EXCEL在投资学中的应用（40页珍藏版）》请在金锄头文库上搜索。
1、补充专题：Excel在投资学里的应用EXCEL 函数nExcel中所提的函数其实是一些预定义的公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算。用户可以直接用它们对某个区域内的数值进行一系列运算，如分析和处理日期值和时间值、确定贷款的支付额、确定单元格中的数据类型、计算平均值、排序显示和运算文本数据等等。 n函数的结构以函数名称开始，后面是左圆括号、以逗号分隔的参数和右圆括号。如果函数以公式的形式出现，请在函数名称前面键入等号（=）。在创建包含函数的公式时，公式选项板将提供相关的帮助。 nExcel函数一共有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、
2、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。数学和三角函数 n求和函数SUMnSUBTOTAL、SUM、SUMIF、SUMPRODUCT、SUMSQ、SUMX2MY2、SUMX2PY2、SUMXMY2 n例（见附表“小新月支出统计表”）： 求解小新支出的总金额。n更进一步 求解小新1月份支出的用于吃饭的总金额。 =SUMIF($e$4:$e$98,“日常餐费“,$D$4:$D$98)其中”$e$4:$e$98 ”为提供逻辑判断依据的单元格区域，“日常餐费”为判断条件即只统计$e$4:$e$98区域中部门为“日常餐费的单元格， $D$4:$D$98为实际求和的
3、单元格区域。 n四舍五入ROUND（例见表“随机、四舍五入及IF函数”） ROUND(number,num_digits) 这个函数有两个参数，分别是number和num_digits。其中number就是将要进行四舍五入的数字；num_digits则是希望得到的数字的小数点后的位数。 逻辑函数n用来判断真假值，或者进行复合检验的Excel函数，我们称为逻辑函数。在Excel中提供了六种逻辑函数。即AND、OR、NOT、FALSE、IF、TRUE函数。n（一）AND函数所有参数的逻辑值为真时返回 TRUE；只要一个参数的逻辑值为假即返回 FALSE。简言之，就是当AND的参数全部满足某一条件时
4、，返回结果为TRUE，否则为FALSE。语法为AND(logical1,logical2, .)，其中Logical1, logical2, . 表示待检测的 1 到 30 个条件值，各条件值可能为TRUE，可能为 FALSE。 参数必须是逻辑值，或者包含逻辑值的数组或引用。 n（二）IF函数nIF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，因此If函数也称之为条件函数。它的应用很广泛，可以使用函数 IF 对数值和公式进行条件检测。它的语法为IF(logical_test,value_if_true,value_if_false)。其中Logical_test表示计算结果为
5、TRUE 或 FALSE 的任意值或表达式。本参数可使用任何比较运算符。n例：某班级的成绩如附表所示，为了做出最终的综合评定，我们设定按照平均分判断该学生成绩是否合格的规则。如果各科平均分超过60分则认为是合格的，否则记作不合格。 如何解决？ nValue_if_true显示在logical_test 为 TRUE 时返回的值，Value_if_true 也可以是其他公式。Value_if_false logical_test 为 FALSE 时返回的值。Value_if_false 也可以是其他公式。nIF多层嵌套 在上述的例子中，我们只是将成绩简单区分为合格与不合格，在实际应用中，成绩通常
6、是有多个等级的，比如优、良、中、及格、不及格等。有办法一次性区分吗？可以使用多层嵌套的办法来实现。 n首先区分： 90分或以上为优秀； 6090为及格； 60分以下为不及格n编写函数如下： =IF(B9=90,优秀,IF(B9=60,及格,不及格)n其它函数举例 COUNTIF 语法形式为COUNTIF(range,criteria)。其中Range为需要计算其中满足条件的单元格数目的单元格区域。Criteria确定哪些单元格将被计算在内的条件，其形式可以为数字、表达式或文本。例如，条件可以表示为 32、“32”、“32”、“apples”。 例：统计每人有多少门课优秀。n练习 试一下加嵌套，
7、将分数段区分如下： 60以下不及格； 6080中； 8090良好； 90100优秀。 另外，如果分数大于100或小于0，出现提示 “出错了”。 统计函数nExcel的统计工作表函数用于对数据区域进行统计分析。例如，统计工作表函数可以用来统计样本的方差、数据区间的频率分布等。 n求数据集的内部平均值TRIMMEAN函数TRIMMEAN先从数据集的头部和尾部除去一定百分比的数据点，然后再求平均值。当希望在分析中剔除一部分数据的计算时，可以使用此函数。比如，我们在计算选手平均分数中常用去掉一个最高分，去掉一个最低分，XX号选手的最后得分，就可以使用该函数来计算。n语法形式为TRIMMEAN(arra
8、y,percent)其中Array为需要进行筛选并求平均值的数组或数据区域。Percent为计算时所要除去的数据点的比例， n例：小潘参加演讲大赛，8位评委打出分数，求解小潘获得的有效平均分。 n求数据集的最大值MAX与最小值MIN这两个函数MAX、MIN就是用来求解数据集的极值（即最大值、最小值）。函数的用法非常简单。语法形式为 函数（number1,number2,.），其中Number1,number2,. 为需要找出最大数值的 1 到 30 个数值。 n例： 同样是小潘参赛，能否使用上面的函数算出有效平均分？n其它统计函数 求数据集中的中位数MEDIAN； 方差VAR； 财务函数n财务
9、函数可以进行一般的财务计算，如确定贷款的支付额、投资的未来值或净现值，以及债券或息票的价值。这些财务函数大体上可分为四类：投资计算函数、折旧计算函数、偿还率计算函数、债券及其他金融函数。它们为财务分析提供了极大的便利。 n求某项投资的未来值FV在日常工作与生活中，我们经常会遇到要计算某项投资的未来值的情况，此时利用Excel函数FV进行计算后，可以帮助我们进行一些有计划、有目的、有效益的投资。FV函数基于固定利率及等额分期付款方式，返回某项投资的未来值。n语法形式为FV(rate,nper,pmt,pv,type)。其中rate为各期利率，是一固定值，nper为总投资（或贷款）期，即该项投资（
10、或贷款）的付款期总数，pmt为各期所应付给（或得到）的金额，其数值在整个年金期间（或投资期内）保持不变，pv为现值，或一系列未来付款当前值的累积和，也称为本金，如果省略pv，则假设其值为零，type为数字0或1，用以指定各期的付款时间是在期初还是期末，如果省略t，则假设其值为零。n例： 假如某人两年后需要一笔比较大的学习费用支出，计划从现在起每月初存入2000元，如果按年利2.25%，按月计息（月利为2.25%/12），那么两年以后该账户的存款额会是多少呢？n求投资的净现值NPVNPV函数基于一系列现金流和固定的各期贴现率，返回一项投资的净现值。投资的净现值是指未来各期支出（负值）和收入（正值
11、）的当前值的总和。n语法形式为：NPV(rate,value1,value2, .) 其中，rate为各期贴现率，是一固定值；value1,value2,.代表1到29笔支出及收入的参数值，value1,value2,.所属各期间的长度必须相等，而且支付及收入的时间都发生在期末。需要注意的是：NPV按次序使用value1,value2，来注释现金流的次序。所以一定要保证支出和收入的数额按正确的顺序输入。 n例：（见附表“长期投资决策的常用函数”） 投资某产品生产线，投资成本为$100,000，预计前五年的营业收入如下：$12,000，$15,200，$20,000，$32,000 和 $44,
12、500。每年的贴现率为 10%。计算该投资的净现值。n求贷款分期偿还额PMTPMT函数基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。PMT函数可以计算为偿还一笔贷款，要求在一定周期内支付完时，每次需要支付的偿还额，也就是我们平时所说的分期付款。比如借购房贷款或其它贷款时，可以计算每期的偿还额。 n语法形式为：PMT(rate,nper,pv,fv,type) 其中，rate为各期利率，是一固定值，nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数，pv为现值，或一系列未来付款当前值的累积和，也称为本金，fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，
13、则假设其值为零（例如，一笔贷款的未来值即为零），type为0或1，用以指定各期的付款时间是在期初还是期末。如果省略type，则假设其值为零。 n例： （见附表“长期投资决策的常用函数”） 某人欲购买一套价值60万元的住房，向银行申请消费贷款40万元，贷款年限10年，年利率为6%，试制作按月还款表。n内部收益率的函数-IRRIRR函数返回由数值代表的一组现金流的内部收益率。这些现金流不一定必须为均衡的，但作为年金，它们必须按固定的间隔发生，如按月或按年。内部收益率为投资的回收利率，其中包含定期支付（负值）和收入（正值）。n语法形式为IRR(values,guess) 其中values为数组或单元
14、格的引用，包含用来计算内部收益率的数字，values必须包含至少一个正值和一个负值，以计算内部收益率；guess为对函数IRR计算结果的估计值，excel使用迭代法计算函数IRR。从guess开始，函数IRR不断修正收益率，直至结果的精度达到0.00001%，如果函数IRR经过20次迭代，仍未找到结果，则返回错误值#NUM！，在大多数情况下，并不需要为函数IRR的计算提供guess值，如果省略guess，假设它为0.1（10%）。如果函数IRR返回错误值#NUM！，或结果没有靠近期望值，可以给guess换一个值再试一下。n例：（见附表“长期投资决策的常用函数”） 仍然是刚才的投资项目：某产品生产线，投资成本为$100,000，预计前五年的营业收入如下：$12,000，$15,200，$20,000，$32,000 和 $44,500。每年的贴现率为 10%。分别求出投资两年、四年以及五年后的内部收益率。 n思考： 设计你自己的买卖提醒： 一只股票若连续5天上涨，在第6天出现小实体的K线，KDJ指标出现死亡交叉，则卖出。
