常用EXCEL函数详解及应用实例(函数分类汇总版)

注Hale Waihona Puke Baidu事项：
利息相应部分计算表 利率（rate) 期间(年)(nper) 借款额（现值pv） #VALUE! 末期月（end-period) 付款时间类型（type)
2013-5-24 16:58 1 200000 1 5 0
利息相应部分 公式显示 ########## =CUMIPMT(D20/12,D21*12,D22,D23,D24,D2
常用Excel函数详解及应用实例
A 1 B C D E F G
2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 例1 20 21 22 23 24 25
八、财务函数 返回 1.CUMIPMT 计算贷款偿还额的利息相应部分的累计
函数定义： 使用格式： 格式简义： 参数定义： 返回一笔贷款在给定的start-period到end-period期间累计偿还的利息数额. CUMIPMT(rate,nper,pv,start_period,end_period,type) CUMIPMT(利率,总付款期数,现值,首期,末期,0或1) Rate 为利率. Nper 为总付款期数. Pv 为现值. Start_period 为计算中的首期,付款期数从1开始计数. End_period 为计算中的末期. Type 为付款时间类型.为0时，期末付款，为1时，期初付款。 1.如果该函数不可用,并返回错误#NAME?,请安装并加载"分析工具库"加载宏. 2.应确认所指定的rate和nper单位的一致性.例如,同样是四年期年利率为10%的贷款,如果 按月支付,rate应为10%/12,nper应为4*12；如果按年支付,rate应为10%,nper为4. 3.Nper、start_period、end_period和type将被截尾取整. 4.如果rate≤0、nper≤0或pv≤0,函数CUMIPMT返回错误值#NUM!. 5.如果start_period<1、end_per
常用Excel函数详解及应用实例
A 54 55 56 57 例3 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 B C D E F G
的折旧属于特例。对于第一个周期，函数 DB 的计算公式为：cost * rate * month / 12 对于 最后一个周期，函数 DB 计算公式为：((cost - 前期折旧总值) * rate * (12 - month)) / 12
-799768.29 =CUMIPMT(D20/12,D21*12,D22,1,D24,1)(期
26 返回 2.CUMPRINC 计算贷款偿还额的本金相应部分的累计 27 函数定义： 返回一笔贷款在给定的start-period到end-period期间累计偿还的本金数额. 28 使用格式： CUMPRINC(rate,nper,pv,start_period,end_period,type) 29 格式简义： CUMPRINC(利率,总付款期数,现值,首期,末期,付款时间类型) 30 参数定义： Rate 为利率. 31 Nper 为总付款期数. 32 31 Pv 为现值. 33 12 =DAY(EOMONTH(TODAY(),0))(计算本月总天数) 34 #NAME? End_period 为计算中的末期. 35 #NAME? Type 为付款时间类型.为1时，期初付款；为0时，期末付款。 36 37 例2 本金相应部分的计算 38 利率(rate) 9% 结果 公式显示 39 总付款期数(nper)(年) 30 -68.28 =CUMPRINC(D38/12,D39*12,D40,1,D42, 40 借款额（现值pv) 125000 41 首期（start-period) 1 42 末期（end-period) 1 -998.29 =CUMPRINC(D38/12,D39*12,D40,1,D42, 43 付款时间类型(type) 0 44 返回 3.DB 计算折旧值(固定资产折旧法) 45 函数定义： 使用固定余额递减法,计算一笔资产在给定期间内的折旧值. 46 使用格式： DB(cost,salvage,life,period,month) 47 格式简义： DB(资产原值,折旧期末的价值,折旧期限,折旧值的期间,第一年的月份数) 48 参数定义： 342626195810260017 为资产原值. 49 1958-10-26 Salvage 为资产在折旧期末的价值(也称为资产残值). 50 男 Life 为折旧期限(有时也称作资产的使用寿命). 51 2018-10-26 Period 为需要计算折旧值的期间.Period必须使用与life相同的单位. 52 2018-10-26 Month 为第一年的月份数,如省略,则假设为12. rate = 1 - ((salvage / cost) ^ (1 / life))，保留 3 位小数 第一个周期和最后一个周期 53 注意事项： 第 1 页，共 16 页
(nomial-rate) 复利计算
期数(npery) 实际年利率 公式显示 108 0.02 2 0.0201 =EFFECT(B108,C108) 第 2 页，共 16 页
常用Excel函数详解及应用实例
A B C D E F G
109 返回 6.FV 计算将来的价格 110 函数定义： 基于固定利率及等额分期付款方式,返回某项投资的未来值 111 使用格式： FV(rate,nper,pmt,pv,type) 112 格式简义： FV(利率,付款期总数,各期所应支付的金额,现值,指定各期的付款时间是在期初还是期末) 113 参数定义： Rate 为各期利率. 114 Nper 为总投资期,即该项投资的付款期总数. 115 Pmt 为各期所应支付的金额,其数值在整个年金期间保持不变.通常pmt包括本金 116 和利息,但不包括其他费用及税款.如果忽略pmt,则必须包括pv参数. 为现值,即从该项投资开始计算时已经入帐的款项,或一系列未来付款的当前值的 117 Pv 118 累积和,也称为本金.如果省略PV,则假设其值为零,并且必须包括pmt参数. 119 Type 数字0或1,用以指定各期的付款时间是在期初还是期末.如果省略type,则假 120 设其值为零. 121 要点： 有关函数FV中各参数以及年金函数的详细信息,请参阅函数PV. #VALUE! 1.应确认所指定的rate和nper单位的一致性.例如,同样是四年期年利率为12%的贷款,如果按月支付, 122 注意事项： #VALUE! rate应为12%/12,nper应为4*12；如果按年支付,rate应为12%,nper为4. 123 0 2.在所有参数中,支出的款项,如银行存款,表示为负数；收入的款项,如股息收入,表示为正数. 124 0 125 126 例6 计算储蓄或投资的到期额 127 利率 期间(年) 每月存入额 付款类型 到期额 公式显示 128 0.02 5 -15000 1 947286.52 =FV(B128/12,C128*12,D12 129 130 例7 现在每年年初存入1000元,10年后到期能收回多少钱(银行利率为5%) 131 Rate Nper Pmt 结果 ￥13,206.79 =FV(B132,C132,-D132,,1) 132 0.05 10 1000 133 返回 7.FVSCHEDULE 计算利率变动存款的将来价格 基于一系列复利返回本金的未来值.函数FVSCHDULE用于计算某项投资在变动或可调利率下的未来值 134 函数定义： 135 使用格式： FVSCHEDULE(principal,schedule) 136 格式简义： FVSCHEDULE(现值,利率范围) 137 参数定义： Principal 为现值. 138 Schedule 为利率数组. 139 要点： Schedule中的值可以是数字或空白单元格；其他任何值都将在函数FVSCHEDULE的运算中产 140 生错误值#VALUE!.空白单元格被认为是0(没有利息). 141 注意事项： 如果该函数不可用,并返回错误值#NAME?,请安装并加载"分析工具库"加载宏. 142 143 例8 利率发生变动时的到期领取额 144 本金 利率 到期额 公式显示 145 1000000 4.50% 1209690.694 =FVSCHEDULE(B145,C145:C148) 146 5.00% 147 5.50% 148 4.50% 149 返回 8.IPMT 150 函数定义： 151 使用格式： 152 格式简义： 153 154 参数定义： 155 156 157 158 159 160 161 162
每年应提取的折旧额（固定资产净值为4800万元，使用年限为5年，残值为200万元，4种方法） 年数总和法 折旧方法： 固定折旧率法 平均年限法 双倍余额递减法 固定资产原值： 5000 5000 5000 5000 净残值： 200 200 200 200 使用年限： 5 5 5 5 各 年次 折旧额 折旧额 折旧额 折旧额 ￥2,375.00 ￥960.00 2,000.00 1,600.00 年 1 ￥1,246.88 ￥960.00 1,200.00 1,280.00 提 2 ￥654.61 ￥960.00 720.00 960.00 取 3 ￥343.67 ￥960.00 440.00 640.00 折 4 旧 ￥4,620.15 ￥3,840.00 4,360.00 4,480.00 合计 =DB($D$59,$D$60,$D$61,C63)(固定资产折旧法) =($E$59-$E$60)/$E$61(平均年限法) =DDB($F$59,$F$60,$F$61,C63)(双倍余额递减法) 双倍余额递减法最后两年：第4年=(F59-F60-SUM(F63:F65))/2;第5年=F66 =SYD($G$59,$G$60,$G$61,C63)(年数总和法)
74 返回 4.DDB 计算折旧值(双倍余额递减法) 75 函数定义： 用双倍余额递减法计算一笔资产在给定期间内的折旧值. 使用格式： 76 77 格式简义： DDB(资产原值,折旧期末的价值,折旧期限,计算折旧值的期间,余额递减速率) 78 参数定义： Cost 为资产原值. 79 Salvage 为资产在折旧期末的价值(也称为资产残值). 80 Life 为折旧期限(有时也称作资产的使用寿命). 81 Period 为需要计算折旧值的期间.Period必须使用与life相同的单位. 82 Factor 为余额递减速率.如果factor被省略,则假设为2(双倍余额递减法). 83 要点： 这五个参数都必须为正数. 84 注意事项： 双倍余额递减法以加速的比率计算折旧.折旧在第一阶段是最高的,在后继阶段中会减少. 85 DDB使用下面的公式计算一个阶段的折旧值： 86 1.((资产原值-资产残值)-前面阶段的折旧总值)*(余额递减速率/生命周期). 87 2.如果不想使用双倍余额递减法,更改余额递减速率. 88 3.当折旧大于余额递减计算值时,如果希望转换到直线余额递减法,请使用VDB函数. 89 90 例4 数据说明 数据 计算结果 公式 资产原值 91 2400 ￥1.32 =DDB(C91,C92,C93*365,C94,C95)(第1天折旧) 资产残值 92 300 ￥40.00 =DDB(C91,C92,C93*12,C94,C95)(第1个月折旧) 使用寿命 93 10 ￥480.00 =DDB(C91,C92,C93,C94,C95)(第1年折旧) 折旧期间 94 1 ￥306.00 =DDB(C91,C92,C93,2,1.5)(第2年折旧1.5递减率) 余额递减率 95 2 ￥22.12 =DDB(C91,C92,C93,10,C95)(第10年折旧) 96 返回 5.EFFECT 计算贷款或分期储蓄的利率 97 函数定义： 利用给定的名义年利率和每年的复利期数,计算有效的年利率 98 使用格式： EFFECT(nominal_rate,npery) 99 格式简义： EFFECT(名义利率,每年的复利期数) 100 参数定义： Nominal_rate Nominal_rate为名义利率. 101 Npery 为每年的复利期数. 1.如果该函数不可用,并返回错误值#NAME?,请安装并加载"分析工具库"加载宏. 102 注意事项： 2.Npery将被截尾取整. 103 3.如果任一参数为非数值型,函数EFFECT返回错误值#VALUE!.如果nominal_rate≤0或npery<1, 104 函数EFFECT返回错误值#NUM!. 105 106 107 例5 名目利率