Excel投资计算函数.doc

Excel投资计算函数使用一、FV、PMT、PV、RATE、NPER投资计算函数（一） 求某项投资的未来值FV在日常工作与生活中，我们经常会遇到要计算某项投资的未来值的情况，此时利用Excel函数FV进行计算后，可以帮助我们进行一些有计划、有目的、有效益的投资。FV函数基于固定利率及等额分期付款方式，返回某项投资的未来值。语法形式为FV(rate,nper,pmt,pv,type)。其中rate为各期利率，是一固定值，nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数，pmt为各期所应付给（或得到）的金额，其数值在整个年金期间（或投资期内）保持不变，通常Pv包括本金和利息，但不包括其它费用及税款，pv为现值，或一系列未来付款当前值的累积和，也称为本金，如果省略pv，则假设其值为零，type为数字0或1，用以指定各期的付款时间是在期初还是期末，如果省略type，则假设其值为零。例如：假如某人两年后需要一笔比较大的学习费用支出，计划从现在起每月初存入2000元，如果按年利2.25%，按月计息（月利为2.25%/12），那么两年以后该账户的存款额会是多少呢？公式写为：FV(2.25%/12, 24,-2000,0,1)图1（二） 求某项投资的现值PVPV函数用来计算某项投资的现值。年金现值就是未来各期年金现在的价值的总和。如果投资回收的当前价值大于投资的价值，则这项投资是有收益的。其语法形式为：PV(rate,nper,pmt,fv,type) 其中Rate为各期利率。Nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数。Pmt为各期所应支付的金额，其数值在整个年金期间保持不变。通常 pmt 包括本金和利息，但不包括其他费用及税款。Fv 为未来值，或在最后一次支付后希望得到的现金余额，如果省略 fv，则假设其值为零（一笔贷款的未来值即为零）。Type用以指定各期的付款时间是在期初还是期末。例如，假设要购买一项保险年金，该保险可以在今后二十年内于每月末回报￥600。此项年金的购买成本为80,000，假定投资回报率为8%。那么该项年金的现值为：PV(0.08/12, 12*20,600,0) 计算结果为：￥-71,732.58。负值表示这是一笔付款，也就是支出现金流。年金（￥-71,732.58）的现值小于实际支付的（￥80,000）。因此，这不是一项合算的投资。图2（三） 求贷款分期偿还额PMTPMT函数基于固定利率及等额分期付款方式，返回投资或贷款的每期付款额。PMT函数可以计算为偿还一笔贷款，要求在一定周期内支付完时，每次需要支付的偿还额，也就是我们平时所说的分期付款。比如借购房贷款或其它贷款时，可以计算每期的偿还额。其语法形式为：PMT(rate,nper,pv,fv,type) 其中，rate为各期利率，是一固定值，nper为总投资（或贷款）期，即该项投资（或贷款）的付款期总数，pv为现值，或一系列未来付款当前值的累积和，也称为本金，fv为未来值，或在最后一次付款后希望得到的现金余额，如果省略fv，则假设其值为零（例如，一笔贷款的未来值即为零），type为0或1，用以指定各期的付款时间是在期初还是期末。如果省略type，则假设其值为零。例如，需要10个月付清的年利率为8%的￥10,000贷款的月支额为：PMT（8%/12,10,10000） 计算结果为：-￥1,037.03。（四） RATE与NPERRATE函数用于求出投资的利率或回报率，NPER用于求投资或贷款的期数。语法形式为：Rate(nper,mpt,pv fv,type)Nper(rate,pmt,pv,fv,type)二、LOOKUP查找函数格式：LOOKUP（lookup_value，lookup_vector，result_vector）Lookup_value 是要在指定一行或一列区域中查找的值，其值可以为数字、文本和逻辑值或包含其值的名称或引用。Lookup_vector为一行或一列的区域，其区域中的数值可以为文本、数字或逻辑值。注意：Lookup_vector 的数值必须按升序排序：.、-2、-1、0、1、2、.、A-Z、FALSE、TRUE；否则，函数LOOKUP 不能返回正确的结果。文本不区分大小写。Result_vector 只包含一行或一列的区域，其大小必须与 lookup_vector 相同。说明：如果函数 LOOKUP 找不到 lookup_value，则查找 lookup_vector 中小于或等于 lookup_value 的最大数值；如果 lookup_value 小于 lookup_vector 中的最小值，函数 LOOKUP 返回错误值 #N/A。 例如，现有一电话号码簿，从A1至D51，即A列是人名，B、C、和D列是相应的办公电话、家庭电话和手机电话号码，如果要在电话簿中查找某人的手机电话号码，应该将姓名作为查找值，但实际上需要的是手机电话号码。则应对此电话簿先按姓名进行升序排序，然后在E1单元格输入=LOOKUP（“王敏力”，A2：A51，D2：D51），就可在E1单元格中将找到的“王敏力”的手机号码显示出来。再如下图中的表格数据，其中A9单元格中输入的是要查找的姓名，B9中输入Lookup函数：=LOOKUP(A9,B1:B7,D1:D7)，结果显示的该人所在的科室。需要注意的是，表格数据需要按照查找列，也就是姓名进行升序排序。三、LEN、LENB、FIND、FINDB、RIGHT 字符函数LEN和LENB（用于双字节字符）返回文本字符串中的字符数。LEN(text)，LENB(text) ；其中text是是要查找其长度的文本。空格将作为字符进行计数。例：A1=“北京第二外国语学院计算机教研室” LEN（A1）结果为：15，LENB（A1）结果为：30查找字符函数FIND和FINDB(用于双字节字符)用于查找字符串函数。FIND(find_text,within_text,start_num) ,FINDB(find_text,within_text,start_num)；其中Find_text要查找的文本，Within_text包含要查找文本的文本Start_num指定开始进行查找的字符，within_text 中的首字符是编号为 1 的字符。如果忽略 start_num，则假设其为 1。例：A1=“北京第二外国语学院计算机教研室”，Find（“计算机教研室”，A1）结果为：10，Findb（A1）结果为：19。提取字符函数LEFT和LEFTB（用于双字节字符）根据所指定的字符数返回文本字符串中的第一个或前几个字符。RIGHT和RIGHTB（用于双字节字符）根据所指定的字符数返回文本字符串中最后一个或多个字符。LEFT(text,num_chars)，LEFTB(text,num_bytes) ；RIGHT(text,num_chars)，RIGHTB(text,num_bytes)；其中Text是包含要提取字符的文本字符串，Num_chars 指定要由 LEFT 所提取的字符数，Num_chars 必须大于或等于 0，如果 num_chars 大于文本长度，则 LEFT 返回所有文本，如果省略 num_chars，则假定其为 1。例：A1=“北京第二外国语学院计算机教研室”，Left（A1，6）结果为：北京第二外国，Leftb（A1，6）结果为：北京第；Right（A1，6）结果为：计算机教研室，Rightb（A1，6）结果为：教研室。四、DATE日期转换函数，TEXT格式转换函数DATE：返回代表特定日期的序列号。如果在输入函数前，单元格的格式为“常规”，则结果将设为日期格式。语法：DATE(year,month,day)Year 参数 year 可以为一到四位数字。Microsoft Excel 将根据所使用的日期系统来解释 year 参数。默认情况下，Microsoft Excel for Windows 将使用 1900 日期系统，而 Microsoft Excel for Macintosh 将使用 1904 日期系统。Month 代表一年中从 1 月到 12 月（一月到十二月）各月的正整数或负整数。Day 代表一月中从 1 日到 31 日各天的正整数或负整数。TEXT：众所周知，EXCEL中的TEXT 函数参数众多，使用后公式简洁，很是方便，但是要记住这些参数就好比翻字典，反而令人望而生畏，列位看官可以先看看下面的内容，以后需要使用时记住来本贴查一查就OK了。TEXT()，将数值转换为按指定数字格式表示的文本。 语法：TEXT(value,format_text) Value 为数值、计算结果为数字值的公式，或对包含数字值的单元格的引用。Format_text 为“单元格格式”对话框中“数字”选项卡上“分类”框中的文本形式的数字格式。 说明 Format_text 不能包含星号 (*)。 通过“格式”菜单调用“单元格”命令，然后在“数字”选项卡上设置单元格的格式，只会更改单元格的格式而不会影响其中的数值。使用函数 TEXT 可以将数值转换为带格式的文本，而其结果将不再作为数字参与计算。 公式 说明 =A2& sold &TEXT(B2, $0.00)& worth of units. 将上面内容合并为一个短语 (Buchanan sold $2800.00 worth of units.) =A3& sold &TEXT(B3,0%)& of the total sales. 将上面内容合并为一个短语 (Dodsworth sold 40% of the total sales.)TEXT生成的三种日期格式：e:=text(now(),e) 与 =text(now(),YYYY) 是一样的b=text(now(),b) 佛历2位年份bbbb=text(now(),bbbb) 佛历4位年份 与公元纪年相差543年也就是公元纪年543就是了。dbnum1=TEXT(NOW(),dbnum1yyyy年m月d日) 中文小写日期dbnum2=TEXT(NOW(),dbnum2yyyy年m月d日) 中文大写日期佛历为部分佛教国家计算纪元的方式，该计算方式以释迦摩尼去世当年度为计算基准。西元1950年，锡兰首都可伦坡举办首届“世界佛教徒友谊会”，会中议决：佛陀诞生于西元前623年，成道于西元前588年，去世于西元元前543年。三年后的1954年，该年会于缅甸仰光举行，会中再决议佛教国家以“佛历”纪元，并以释迦牟尼涅盘日推算，西元1954年为佛历2498年。日期转换把20080808修改成20080808的格式可以用以下公式：=-text(20080808,#-00-00) 俩减号是将文本型数字转换成可以计算的数字。你可以试试将单元格设置成文本模式，输入数字后如果想计算，得先转换。把英文月份化成数字月份：=MONTH(-(Aug&1)=MONTH(-(August&1)返回一个月的最后一天，就是用下一个月的第一天1就行。得到上一个月的最后一天：=today()-day(now()确定季度：=text(roundup(month(now()/3,0),dbnum1第0季度)星期：=weekday(now()=mod(today()-2,7)+1=text(today(),aaaa)返回最近星期天的日期=today()-weekday(today(),2)=today()-mod(today()-1,7)=today()+8-weekday(today()=today()+6-mod(today()-2,7)两个日期相隔的星期天数：=int(weekday(date1,2)+date2-date1)/7)关于函数TEXT原值公式结果公式1一=TEXT(A3,DBNUM1)10一十=TEXT(A4,DBNUM1)11一十一=TEXT(A5,DBNUM1)1一=TEXT(A6,DBNUM10)10一=TEXT(A7,DBNUM10)11一一=TEXT(A8,DBNUM10)1壹=TEXT(A9,DBNUM2)10壹拾=TEXT(A10,DBNUM2)11壹拾壹=TEXT(A11,DBNUM2)1壹=TEXT(A12,DBNUM20)10壹零=TEXT(A13,DBNUM20)11壹壹=TEXT(A14,DBNUM20)11=TEXT(A15,DBNUM3)101十=TEXT(A16,DBNUM3)111十1=TEXT(A17,DBNUM3)11=TEXT(A18,DBNUM30)1010=TEXT(A19,DBNUM30)1111=TEXT(A20,DBNUM30)12.312.3=TEXT(A21,#.#)12.312.30=TEXT(A22,#.#0)12.3512.35=TEXT(A23,#.#0)0.12312.30%=TEXT(A24,#.#0)0.12323/187=