Excel在资金时间价值

1、Excel在资金时间价值 第2章 Excel在资金时间价值计算中 的应用 n1 终值与现值的计算 n2 年金的终值与现值 n3 名义利率和有效利率 n4 实际应用举例 n5 其他几个资金时间价值的计算函数及其 应用 Excel在资金时间价值 1 终值与现值的计算 n1.1 单利终值与现值的计算单利终值与现值的计算 1.1.1 单利终值的计算单利终值的计算 nFVFVn n=PV+I=PV+PV=PV+I=PV+PVi in=PVn=PV(1+i(1+in)n) n例例2-1：假如：假如1000元，存入银行元，存入银行3年，年， 年利率年利率10%，则三年共可获得多少本，则三年共可获得多少本 息

2、？息？ Excel在资金时间价值 n1.1.2 单利现值的计算单利现值的计算 PV=FVPV=FVn n/(1+i/(1+in)n) 例例2-2：某人打算：某人打算5年后从银行取出年后从银行取出1000 元，单利年利率元，单利年利率5%，则现在须存入银行，则现在须存入银行 的金额为？的金额为？ Excel在资金时间价值 1.2 复利终值与现值复利终值与现值 n1.2.1 复利终值复利终值 通过公式直接利用通过公式直接利用Excel处理处理 n公式：公式：FVnPV(1+i)n n PVFVIF(i,n) n(1+(1+i i) )n n复利终值系数或复利终值系数或1 1元的复利终值，元的复利终

3、值， 用符号用符号FVIF(i,n)来来表示。表示。 FVIF(i,n) (1+(1+i i) )n n Excel在资金时间价值 n例例2-32-3：利用：利用ExcelExcel制作复利终值系数表，制作复利终值系数表， 利率利率1%1%到到10%10%，计息年数从，计息年数从1 1年到年到1010年。年。 n利用相应的引用制作利用相应的引用制作 n利用数组公式制作利用数组公式制作 Excel在资金时间价值 n 利用利用FV函数计算函数计算 nFV(rate,nper,pmt,pv,type) Rate: 每期利率每期利率 nper: 年金处理中的总期数年金处理中的总期数 pmt: 年金年金

4、 pv: 现值现值,初始值初始值,省略时其值为零省略时其值为零 type: 年金类型年金类型, 0(普通年金普通年金);1(先付年先付年 金金) Excel在资金时间价值 n例例2-4：某人存入银行：某人存入银行100元，年利率元，年利率5%， 复利计息，则复利计息，则5年后的复利终值为多少？年后的复利终值为多少？ = FV(5%,5,0,-100) Excel在资金时间价值 1 终值与现值的计算 n1.2.2 复利现值复利现值 通过公式直接利用通过公式直接利用Excel处理处理 n公式公式: : PV=FVn PVIF(i,n) n = FV/(1+i)n n1/(1+i)n复利现值系数，或

5、复利现值系数，或1元的复利元的复利 现值现值 , 用符号用符号PVIF(i,n)来表示。来表示。 PVIF(i,n) = 1/(1+i)n Excel在资金时间价值 n例例2-52-5：利用：利用ExcelExcel制作复利现值系数表，制作复利现值系数表， 利率利率1%1%到到10%10%，计息年数从，计息年数从1 1年到年到1010年。年。 n利用相应的引用制作利用相应的引用制作 n利用数组公式制作利用数组公式制作 Excel在资金时间价值 n 利用利用PV函数计算函数计算 nPV(rate,nper,pmt,fv,type) Rate: 每期利率每期利率 nper: 年金处理中的总期数年金

6、处理中的总期数 pmt: 年金年金 fv: 终值终值,省略时其值为零省略时其值为零 type: 年金类型年金类型, 0(普通年金普通年金);1(先付年先付年 金金) Excel在资金时间价值 n例例2-6：某人：某人5年后要从银行取出年后要从银行取出500元，年元，年 利率利率5%，复利计息，则现在应存入银行的，复利计息，则现在应存入银行的 金额为多少？金额为多少？ = PV(5%,5,0,-500) Excel在资金时间价值 2 年金的终值与现值年金的终值与现值 n普通年金（后付）一定时期内每期期末等普通年金（后付）一定时期内每期期末等 额收付的系列款项；额收付的系列款项； n预付年金（先付

7、）一定时期内每期期初等预付年金（先付）一定时期内每期期初等 额收付的系列款项；额收付的系列款项； n递延年金（延期）前面若干期没有收付业递延年金（延期）前面若干期没有收付业 务，后面若干期有等额的收付业务；务，后面若干期有等额的收付业务； n永续年金永续年金 无限期等额发生的系列收付款。无限期等额发生的系列收付款。 Excel在资金时间价值 n2.1.12.1.1普通年金终值系数普通年金终值系数 FVAn=A(1+i)n-1/i= =AFVIFA(i,n) (1+i)n-1/i称之为年金终值系数或年金复称之为年金终值系数或年金复 利系数，通常用符号利系数，通常用符号FVIFA(i,n)来表示。

8、来表示。 FVIFA(i,n)= (1+i)n-1/I 2.1 普通年金的终值与现值普通年金的终值与现值 Excel在资金时间价值 nFV函数函数 n计算年金终值通常用计算年金终值通常用FV函数，函数， FV函数的功函数的功 能是基于固定利率及等额分期付款方式，能是基于固定利率及等额分期付款方式， 返回某项投资的未来值。返回某项投资的未来值。 Excel在资金时间价值 nFV(rate,nper,pmt,pv,type) Rate: 每期利率每期利率 nper: 年金处理中的总期数年金处理中的总期数 pmt: 年金年金 pv: 现值现值,初始值初始值,省略时其值为零省略时其值为零 type:

9、年金类型年金类型, 0(普通年金普通年金);1(先付年金先付年金) 当当pmt=0（或忽略）时，（或忽略）时，FV函数相当于已知现函数相当于已知现 值计算终值；值计算终值；pv=0（或忽略）时，（或忽略）时， FV函数相函数相 当于已知年金计算终值。当于已知年金计算终值。 Excel在资金时间价值 n例例2-7：若某人在：若某人在10年的期限内每年年末等年的期限内每年年末等 额地向银行存入额地向银行存入1000元，银行按元，银行按5%的年利的年利 率复利计息，那么此人可一次性从银行取率复利计息，那么此人可一次性从银行取 出本息多少钱？出本息多少钱？ n例例2-8：利用：利用Excel制作年金终

10、值系数制作年金终值系数 表。表。 思路：年金现值系数的含义并结合思路：年金现值系数的含义并结合 FV函数。函数。 Excel在资金时间价值 nPVAn=A1-(1+i)-n/i=APVIFA(i,n) n1-(1+i)-n/i称之为年金现值系数或年金贴现称之为年金现值系数或年金贴现 系数，通常用符号系数，通常用符号PVIFA(i,n)来表示。来表示。 nPVIFA(i,n)= 1-(1+i)-n/I nPV函数函数 n计算年金现值通常用计算年金现值通常用PV函数，函数， PV函数的函数的 功能是返回未来若干期资金的现值。功能是返回未来若干期资金的现值。 2.1.2 普通年金现值系数普通年金现值

11、系数 Excel在资金时间价值 nPV(rate,nper,pmt,fv,type) Rate: 每期利率每期利率 nper: 年金处理中的总期数年金处理中的总期数 pmt: 年金年金 fv: 未来值，终值未来值，终值 type: 年金类型年金类型, 0(普通年金普通年金);1(先付年金先付年金) n如果忽略如果忽略fv，则必须包含，则必须包含pmt参数。参数。 n当当pmt=0（或忽略）时，（或忽略）时，PV函数相当于已知终值函数相当于已知终值 计算现值；计算现值；fv=0（或忽略）时，（或忽略）时， PV函数相当于函数相当于 已知年金计算现值。已知年金计算现值。 Excel在资金时间价值

12、n例例2-8：某人打算在今后的：某人打算在今后的4年中每年等额年中每年等额 从银行取出从银行取出2000元，在银行按元，在银行按10%的年利的年利 率复利计息的情况下，此人现在应一次性率复利计息的情况下，此人现在应一次性 存入银行多少钱？存入银行多少钱？ n例例2-9：利用：利用Excel制作年金现值系数表。制作年金现值系数表。 Excel在资金时间价值 n2.2.1 先付年金的终值先付年金的终值 FVAFVAn nA AF FVIFA(i,n) (1+i)1+i) A AFFVIFA(i,n+1) 11 n2.2.2 先付年金的现值先付年金的现值 PVAnAPVIFA(i,n) (1+i)

13、APVIFA(i,n-1) +1 2.2 先付（预付）年金的终值与现值先付（预付）年金的终值与现值 Excel在资金时间价值 n例例2-10：某人准备在今后的：某人准备在今后的5年中每年中每 年年初等额存入银行年年初等额存入银行8000元钱，如元钱，如 果银行按果银行按4%的年利率复利计息，那的年利率复利计息，那 么么5年末此人可一次性从银行取出多年末此人可一次性从银行取出多 少钱？少钱？ nFV(4%,5,-8000,1)= Excel在资金时间价值 n例例2-11：某企业准备在今后：某企业准备在今后3年期限内年期限内 租用一台设备，按租赁合同的约定每租用一台设备，按租赁合同的约定每 年年初

14、需支付租金年年初需支付租金6000元，若贴现率元，若贴现率 为为10%，那么全部租金的现值为多少？，那么全部租金的现值为多少？ nPV(10%,3,-6000,1)= Excel在资金时间价值 n2.3.1 递延年金的终值递延年金的终值 和普通年金的终值计算完全一样。和普通年金的终值计算完全一样。 n2.3.2 递延年金的现值递延年金的现值 递延年金现值：假设递延期为递延年金现值：假设递延期为m m，从第，从第 m+1m+1期期末开始连续期期末开始连续n n期等额收付款项的期等额收付款项的 现值就是递延年金现值。现值就是递延年金现值。 2.3 递延年金的终值与现值递延年金的终值与现值 Exce

15、l在资金时间价值 n两种推导方法：两种推导方法： n PVAPVAn nA A PVIFA(i,n)PVIFA(i,n)PVIF(iPVIF(i, ,m)m) n PVAPVAn nA APVIFA(i,m+n)PVIFA(i,m+n)A APVIFA(i,m)PVIFA(i,m) n = A = APVIFA(i,m+n)PVIFA(i,m+n)PVIFA(i,m)PVIFA(i,m) Excel在资金时间价值 n例2-12：某人准备现在存入银行一笔钱， 希望能够在第6年至第10年末每年等额从银 行取出1000元钱，如果银行存款的年利率 为8%，且复利计息，那么此人现在应当一 次性存入银行多

16、少钱？ n按照第一种计算方法： nPV(8%,5,0,-PV(8%,5,-1000)= n按照第二种计算方法： nPV(8%,10,-1000)- PV(8%,5,-1000) = Excel在资金时间价值 n复利的计息期不总是复利的计息期不总是1 1年，可能是季度，月等，当年，可能是季度，月等，当 利息在利息在1 1年内要复利几次时，给出的年利率是名义年内要复利几次时，给出的年利率是名义 利率。利率。 n名义利率与实际利率的换算：名义利率与实际利率的换算： n 假设：假设：i i 实际利率实际利率 n r r 名义利率名义利率 n mm每年复利的次数每年复利的次数 n则有：则有：FVFV1

17、1=PV=PV（1+1+i i） nFVFVm m= PV 1+= PV 1+（r/r/m m） m m nFVFV1 1= FV= FVm m n则：则： 1+1+i i= 1+= 1+（r/r/m m） m m ni i = 1+ = 1+（r/r/m m） m m 1 1 3 名义利率与有效（实际）利率名义利率与有效（实际）利率 Excel在资金时间价值 n给定名义利率和一年内的计息次数，可以给定名义利率和一年内的计息次数，可以 利用利用EFFECT函数计算有效年利率。函数计算有效年利率。 nEFFECT函数的功能是基于给定的名义年函数的功能是基于给定的名义年 利率和年复利次数，计算有效

18、年利率。利率和年复利次数，计算有效年利率。 nEFFECT(nominal_rate,npery) nnominal_rate名义利率名义利率 nnpery每年的复利期数（次数）每年的复利期数（次数） n例例2-13 ：假定贷款的年利率为：假定贷款的年利率为6%，按日计，按日计 息，则其有效年利率为多少？息，则其有效年利率为多少？ nEFFECT(6%,365)= 3.1 有效年利率的计算有效年利率的计算 Excel在资金时间价值 n给定有效年利率和一年内的计息次数，可以利用给定有效年利率和一年内的计息次数，可以利用 NOMINAL函数计算名义年利率。函数计算名义年利率。 nNOMINAL函数

19、的功能是基于给定的有效年利率函数的功能是基于给定的有效年利率 和年复利次数，计算名义年利率。和年复利次数，计算名义年利率。 nNOMINAL(effect_rate,npery) neffect_rate有效年利率有效年利率 nnpery每年的复利期数（次数）每年的复利期数（次数） n例例2-14：假定有效年利率为：假定有效年利率为12%，按月计息，则，按月计息，则 名义年利率为多少？名义年利率为多少？ nNOMINAL(12%,12)= 3.2 名义年利率的计算名义年利率的计算 Excel在资金时间价值 4 实际应用举例实际应用举例 n4.1 贷款年利率的计算贷款年利率的计算 n例例2-15

20、：某人向银行贷款：某人向银行贷款10万元购买房子，在万元购买房子，在 今后的今后的5年中，每年年末要向银行交还年中，每年年末要向银行交还2.34万万 元，问银行贷款的年利率是多少？元，问银行贷款的年利率是多少？ nRATE函数，功能是返回未来款项的各期利率。函数，功能是返回未来款项的各期利率。 RATE函数通过迭代法计算得出，并且可能函数通过迭代法计算得出，并且可能 无解或有多个解。无解或有多个解。 在在20次迭代计算后，如果相邻两次结果没次迭代计算后，如果相邻两次结果没 有收敛于有收敛于0.0000001，则返回错误值，则返回错误值#NUM。 Excel在资金时间价值 nRATE(nper,

21、pmt,pv,fv,type,guess) nGuess为预期利率（估计值），如果省略为预期利率（估计值），如果省略 预期利率，则假设该值为预期利率，则假设该值为10%，如果，如果RATE 函数不收敛，则需要改变函数不收敛，则需要改变guess的值。通的值。通 常情况下当常情况下当guess的值位于的值位于0和和1之间时，之间时， RATE函数收敛。函数收敛。 n利用利用RATE函数进行求解函数进行求解 Excel在资金时间价值 n例例2-16：某公司目前拟对原有一台设备进：某公司目前拟对原有一台设备进 行更新改造，预计现在一次支付行更新改造，预计现在一次支付10万元，万元， 可使公司每年成本

22、节约可使公司每年成本节约2.5万元。若现在银万元。若现在银 行复利年利率为行复利年利率为6%，那么这项更新设备至，那么这项更新设备至 少使用几年才合算？少使用几年才合算？ nNPER函数，基于固定利率及等额分期付款函数，基于固定利率及等额分期付款 方式，返回某项投资的总期数。方式，返回某项投资的总期数。 NPER(rate, pmt, pv, fv, type) n利用函数求解利用函数求解 4.2 贷款偿还期的计算贷款偿还期的计算 Excel在资金时间价值 4.3 等额分期付款方式下贷款年偿等额分期付款方式下贷款年偿 还额的计算还额的计算 n4.3.1 等额分期付款方式下贷款年偿还总额的计算等

23、额分期付款方式下贷款年偿还总额的计算 例例2-17：某企业有一笔贷款，年利率：某企业有一笔贷款，年利率12%，5 年后到期，若到期一次还本付息，需偿还年后到期，若到期一次还本付息，需偿还100 万元，那么：万元，那么： 如果每年末等额偿债，每期应如果每年末等额偿债，每期应 偿还多少？偿还多少？ 如果每年初等额偿债，每期应偿如果每年初等额偿债，每期应偿 还多少？还多少？ PMT函数，基于固定利率及等额分期付款方式，函数，基于固定利率及等额分期付款方式， 返回贷款的每期付款额。返回贷款的每期付款额。 PMT(rate,nper,pv,fv,type) 利用函数求解利用函数求解 Excel在资金时间

24、价值 n例例2-18：某企业向银行取得借款：某企业向银行取得借款400万万 元，元， 期限期限4年，年利率年，年利率8%，与银行约定，与银行约定 每年等额偿还借款：每年等额偿还借款： 如果每年末等额如果每年末等额 偿债，每期应偿还多少？偿债，每期应偿还多少？ 如果每年初如果每年初 等额偿债，每期应偿还多少？等额偿债，每期应偿还多少？ nPMT(rate,nper,pv,fv,type) Excel在资金时间价值 4.3.2 等额分期付款方式下贷款年本等额分期付款方式下贷款年本 金偿还额的计算金偿还额的计算 nPPMT函数，基于固定利率及等额分期付款函数，基于固定利率及等额分期付款 方式，返回投

25、资在某一给定期间内的本金方式，返回投资在某一给定期间内的本金 偿还额。偿还额。 nPPMT(rate,per,nper,pv,fv,type) nPer用于计算其本金数额的期数，必须用于计算其本金数额的期数，必须 介于介于 1 到到 nper 之间。之间。 n应注意所指定的应注意所指定的 rate 和和 nper 单位的一致单位的一致 性。性。 Excel在资金时间价值 n例例2-19：某企业向银行取得借款：某企业向银行取得借款6000万元，万元， 期限期限5年，年利率年，年利率10%，与银行约定以等额分，与银行约定以等额分 期付款方式每期末偿还借款，则第期付款方式每期末偿还借款，则第1至第至第5年每年每 年的本金偿还额为多少？年的本金偿还额为多少？ n利用函数求解，利用函数求解， PPMT(rate,per,nper,pv,fv,type) n以例以例2-19为例，利用为例，利用PPMT函数制作个年本金函数制作个年本金 偿还计划表。偿还计划表。 Excel在资金时间价值 4.3.3 等额分期付款方式下贷款年利等额分期付款方式下贷款年利 息的计算息的计算 nIPMT 函数，基于固定利率及等额分期付款方函数，基于固定利率及等额分期付款方 式，返回给定期数内对投资的利息偿还额。式，返回给定期数内对投资的利息偿还额。 nIPMT(rate,pe