EXCEL在理财规划上的运用课件

第三章EXCEL在理财规划上的运用第三章EXCEL在理财规划上的运用1EXCEL在理财规划上的运用EXCEL电子表格的财务函数运用达成理财目标的计算方法案例分析与电子表格运用EXCEL在理财规划上的运用EXCEL电子表格的财务函数运2EXCEL财务函数的运用步骤1.打开EXCEL电子表格步骤2菜单中选择【插入】的功能步骤3.下拉菜单选择【函数】步骤4.出现插入函数对话框，在选取类别选择【财务】步骤5.在财务函数中选择需要用的终值、现值或年金函数EXCEL财务函数的运用步骤1.打开EXCEL电子表格3EXCEL在理财规划上的运用课件4EXCEL在理财规划上的运用课件5终值FV函数EXCEL设定练习我现在有20万，如果投资到基金，能够每年赚10%,请问5年后，会变成多少钱？FV(10%,5,0,-20,0)=32.2如果现在不投入，但每年年底投入5万元储蓄，一样假设赚10%，5年后会有多少钱?FV(10%,5,-5,0,0)=30.5如果整笔与每年年底投入同时进行，可累积多少钱?FV(10%,5,-5,-20,0)=62.7终值FV函数EXCEL设定练习我现在有20万，如果投资到基金6EXCEL常用财务函数说明

-PMT与PPMTEXCEL常用财务函数说明

-PMT与PPMT7EXCEL在理财规划上的运用课件8PV函数与PMT函数EXCEL练习我希望在5年后，有一笔钱能让我累积30万买车子，如果我能每年维持10%的报酬率。请问：我现在应单笔存多少钱？或者每年底或每月底存多少钱？单笔:PV(10%,5,0,30,0)=-18.63每年:PMT(10%,5,0,30,0)=-4.91每月:PMT(10%/12,5*12,0,30,0)=-0.3874有一张还有5年到期的债券，债息率为5%，半年配息一次，票面100万，当前市场利率为4%，请问这支债券的市价为何？PV(4%/2,5*2,100*5%/2,100,0)=104.49PV函数与PMT函数EXCEL练习我希望在5年后，有一笔钱能9EXCEL常用财务函数说明

-IPMT与ISPMTEXCEL常用财务函数说明

-IPMT与10贷款本利计算练习贷款30万,利率6%,20年还清,本利平均摊还月供额多少?第100期本金与利息各多少?如果采用本金平均摊还,第100期本金与利息各多少?i=6%/12=0.5%,n=12*20=240PMT(0.5%,240,300000)=-2419.3月供本息PPMT(0.5%,100,240,300000)=1063.9月供本IPMT(0.5%,100,240,300000)=1085.4月供息ISPMT(0.5%,100,240,300000)=875本摊利息300000/240=1250本金摊还法每期本金。贷款本利计算练习贷款30万,利率6%,20年还清,本利平11EXCEL财务函数说明

-NPER与RATEEXCEL财务函数说明

-NPER12NPER与RATE函数EXCEL练习几年后可以累计100万资产退休?资产20万,年投资1万,报酬率8%NPER(8%,-1,-20,100,0)=16.13达成理财目标应有的报酬率，目标10年累积100万,资产20万,年投资6万RATE(10,-6,-20,100,0)=3.73%NPER与RATE函数EXCEL练习几年后可以累计1013EXCEL在理财规划上的运用课件14期间现金流量NPV(8%)0-5000-50001500500210001000315001500420002000530003000IRR13.76%NPV=947.09IRR与NPV函数EXCEL练习期间现金流量NPV(8%)0-5000-500015005015理财目标方程序

-检验理财目标合理当前的可投资额x复利终值系数+未来的年储蓄能力x年金终值系数=理财目标理财目标:5年后累积100万创业资金当前可投资额30万,投资报酬率6%每年储蓄10万FV(6%，5，-10，-30，0)=96.5输入endi=6%，n=5，PV=-30，PMT=-10，CPTFV=96.596.5<100，理财目标无法达成，差一点理财目标方程序

-检验理财目标合理16投资价值方程合理的投资价格=每年可领取的固定现金流量*年金现值系数+到期回收的本金*复利现值系数(N=到期年数R=折现率)投资房地产每年可收租金2万5年出售可回收50万在5%的折现率下的合理房价=PV(5%,5,2,50,1)=-48.27投资价值方程合理的投资价格=每年可领取的固定现17Excel运用练习汇总Excel运用练习汇总18目标基准点法目标并进法目标顺序法目标现值法达成理财目标的计算方法目标基准点法达成理财目标的计算方法19目标基准点法目标基准点法20EXCEL在理财规划上的运用课件21案例分析-退休章帆现年40岁，希望60岁退休，退休后余命20年，每年需要6万元的生活费。目前张先生年收入10万元，假设实质投资报酬率为5%，往后除了提拔1.2万元到养老金账户之外，还有5万元已经配置到个人养老账户中，要实现理财目标，张先生每年还有自己储蓄多少来准备退休金。案例分析-退休章帆现年40岁，希望22案例分析-退休计算退休时需要的退休金:PV（5%，20，-6，0，1）=78.51万元计算每年提拔的养老金可以累积的金额:FV（5%，20，-1.2，0，0）=39.68万元计算退休时需要自己准备的金额：78.51-39.68=38.83万元计算每年储蓄的金额：PMT（5%，20，-5，38.83，0）=-0.77案例分析-退休计算退休时需要的退休金:23案例分析-购房李红目前资产20万，每年可储蓄3万元，打算5年后购房，投资报酬率6%,贷款20年，利率4%，试分析其购房能力，假设房价水平和收入不变。案例分析-购房李红目前资产20万，每年24首付款部分=FV（6%，5，-3，-10，0）=30.29万元贷款能力部分=PV（4%，20，-3，0，0）=40.77万元可购房总价=30.29+40.77=71.06万元案例分析-购房首付款部分案例分析-购房25案例分析-子女教育赵先生的女儿10年读大学。目前大学每年学费1万元，学费成长率为3%，投资报酬率为5%，赵先生每月需要进行的专项教育金储蓄至少为多少？案例分析-子女教育赵先生的女儿10年读大学。目前2610年后每年大学费用FV（3%，10，0，-1，0）=1.34万元大学学费总额PV=（5%，4，-1.34，0，1)=4.99万元赵先生每月储蓄教育金PMT（5%/12，120，0，4.99，0）=0.0321万元赵先生每月至少储存321元。案例分析-子女教育10年后每年大学费用案例分析-子女教育27目标并进法目标并进法28

案例分析5年后购房目标终值50万元，20年后子女教育金目标终值20万元，30后年退休金目标终值100万元，投资报酬率8%，房贷利率6%，贷款20年，贷款七成，以目标并进法，在往后的人生阶段，各需要多少净现金流量投入额，才能完成所有目标?案例分析5年后购房目标终值50万元，229

案例分析购房首付款=50*30%=15万元前5年储蓄=PMT（8%，5，0，15）=2.5568购房贷款=50-15=35万元住房按揭款=PMT（6%，20，35）=-3.0515前20年子女教育金储蓄=PMT（8%，20，0，20）=-0.437退休期30年退休金准备额=PMT（8%，30，0，100）=-0.8827案例分析购房首付款=50*30%=130案例分析案例分析31目标顺序法目标顺序法325年购房50万，20年子女教育20万，30年退休100万，报酬率8%，房贷利率4%，贷款20年，现有净资产7万，年储蓄3万，以目标顺序法可否完成所有目标?案例分析5年购房50万，20年子女教育20万，30年退休100万，报33第一步计算购房目标是否实现：PV=-7,PMT=-3i=8%,N=5FV(8%,5,-3,-7)=27.9PV(4%,20,-3)=40.840.8+27.9-50=18.7购房目标可完成，购房后剩下18.7万，可用在其它目标上

案例分析第一步计算购房目标是否实现：案例分析34第二部计算子女教育目标是否实现：FV(8%,15,0,18.7)=59.359.3-20=39.3可达成子女教育目标，还剩下39.3万

案例分析第二部计算子女教育目标是否实现：案例分35第三步，计算退休目标FV(8%,10,0,-39.3)=84.8FV(8%,5,-3)=17.684.8+17.6=102.4102.4>100可达成退休目标，还剩下2.4万

案例分析第三步，计算退休目标案例分析36目标现值法目标现值法37目标现值法目标现值法38目标现值法051015202530购房50万子女20万养老100万贷款期限20年资源：现值7万，以及以后每年3万目标现值法0539第三章EXCEL在理财规划上的运用第三章EXCEL在理财规划上的运用40EXCEL在理财规划上的运用EXCEL电子表格的财务函数运用达成理财目标的计算方法案例分析与电子表格运用EXCEL在理财规划上的运用EXCEL电子表格的财务函数运41EXCEL财务函数的运用步骤1.打开EXCEL电子表格步骤2菜单中选择【插入】的功能步骤3.下拉菜单选择【函数】步骤4.出现插入函数对话框，在选取类别选择【财务】步骤5.在财务函数中选择需要用的终值、现值或年金函数EXCEL财务函数的运用步骤1.打开EXCEL电子表格42EXCEL在理财规划上的运用课件43EXCEL在理财规划上的运用课件44终值FV函数EXCEL设定练习我现在有20万，如果投资到基金，能够每年赚10%,请问5年后，会变成多少钱？FV(10%,5,0,-20,0)=32.2如果现在不投入，但每年年底投入5万元储蓄，一样假设赚10%，5年后会有多少钱?FV(10%,5,-5,0,0)=30.5如果整笔与每年年底投入同时进行，可累积多少钱?FV(10%,5,-5,-20,0)=62.7终值FV函数EXCEL设定练习我现在有20万，如果投资到基金45EXCEL常用财务函数说明

-PMT与PPMTEXCEL常用财务函数说明

-PMT与PPMT46EXCEL在理财规划上的运用课件47PV函数与PMT函数EXCEL练习我希望在5年后，有一笔钱能让我累积30万买车子，如果我能每年维持10%的报酬率。请问：我现在应单笔存多少钱？或者每年底或每月底存多少钱？单笔:PV(10%,5,0,30,0)=-18.63每年:PMT(10%,5,0,30,0)=-4.91每月:PMT(10%/12,5*12,0,30,0)=-0.3874有一张还有5年到期的债券，债息率为5%，半年配息一次，票面100万，当前市场利率为4%，请问这支债券的市价为何？PV(4%/2,5*2,100*5%/2,100,0)=104.49PV函数与PMT函数EXCEL练习我希望在5年后，有一笔钱能48EXCEL常用财务函数说明

-IPMT与ISPMTEXCEL常用财务函数说明

-IPMT与49贷款本利计算练习贷款30万,利率6%,20年还清,本利平均摊还月供额多少?第100期本金与利息各多少?如果采用本金平均摊还,第100期本金与利息各多少?i=6%/12=0.5%,n=12*20=240PMT(0.5%,240,300000)=-2419.3月供本息PPMT(0.5%,100,240,300000)=1063.9月供本IPMT(0.5%,100,240,300000)=1085.4月供息ISPMT(0.5%,100,240,300000)=875本摊利息300000/240=1250本金摊还法每期本金。贷款本利计算练习贷款30万,利率6%,20年还清,本利平50EXCEL财务函数说明

-NPER与RATEEXCEL财务函数说明

-NPER51NPER与RATE函数EXCEL练习几年后可以累计100万资产退休?资产20万,年投资1万,报酬率8%NPER(8%,-1,-20,100,0)=16.13达成理财目标应有的报酬率，目标10年累积100万,资产20万,年投资6万RATE(10,-6,-20,100,0)=3.73%NPER与RATE函数EXCEL练习几年后可以累计1052EXCEL在理财规划上的运用课件53期间现金流量NPV(8%)0-5000-50001500500210001000315001500420002000530003000IRR13.76%NPV=947.09IRR与NPV函数EXCEL练习期间现金流量NPV(8%)0-5000-500015005054理财目标方程序

-检验理财目标合理当前的可投资额x复利终值系数+未来的年储蓄能力x年金终值系数=理财目标理财目标:5年后累积100万创业资金当前可投资额30万,投资报酬率6%每年储蓄10万FV(6%，5，-10，-30，0)=96.5输入endi=6%，n=5，PV=-30，PMT=-10，CPTFV=96.596.5<100，理财目标无法达成，差一点理财目标方程序

-检验理财目标合理55投资价值方程合理的投资价格=每年可领取的固定现金流量*年金现值系数+到期回收的本金*复利现值系数(N=到期年数R=折现率)投资房地产每年可收租金2万5年出售可回收50万在5%的折现率下的合理房价=PV(5%,5,2,50,1)=-48.27投资价值方程合理的投资价格=每年可领取的固定现56Excel运用练习汇总Excel运用练习汇总57目标基准点法目标并进法目标顺序法目标现值法达成理财目标的计算方法目标基准点法达成理财目标的计算方法58目标基准点法目标基准点法59EXCEL在理财规划上的运用课件60案例分析-退休章帆现年40岁，希望60岁退休，退休后余命20年，每年需要6万元的生活费。目前张先生年收入10万元，假设实质投资报酬率为5%，往后除了提拔1.2万元到养老金账户之外，还有5万元已经配置到个人养老账户中，要实现理财目标，张先生每年还有自己储蓄多少来准备退休金。案例分析-退休章帆现年40岁，希望61案例分析-退休计算退休时需要的退休金:PV（5%，20，-6，0，1）=78.51万元计算每年提拔的养老金可以累积的金额:FV（5%，20，-1.2，0，0）=39.68万元计算退休时需要自己准备的金额：78.51-39.68=38.83万元计算每年储蓄的金额：PMT（5%，20，-5，38.83，0）=-0.77案例分析-退休计算退休时需要的退休金:62案例分析-购房李红目前资产20万，每年可储蓄3万元，打算5年后购房，投资报酬率6%,贷款20年，利率4%，试分析其购房能力，假设房价水平和收入不变。案例分析-购房李红目前资产20万，每年63首付款部分=FV（6%，5，-3，-10，0）=30.29万元贷款能力部分=PV（4%，20，-3，0，0）=40.77万元可购房总价=30.29+40.77=71.06万元案例分析-购房首付款部分案例分析-购房64案例分析-子女教育赵先生的女儿10年读大学。目前大学每年学费1万元，学费成长率为3%，投资报酬率为5%，赵先生每月需要进行的专项教育金储蓄至少为多少？案例分析-子女教育赵先生的女儿10年读大学。目前6510年后每年大学费用FV（3%，10，0，-1，0）=1.34万元大学学费总额PV=（5%，4，-1.34，0，1)=4.99万元赵先生每月储蓄教育金PMT（5%/12，120，0，4.99，0）=0.0321万元赵先生每月至少储存321元。案例分析-子女教育10年后每年大学费用案例分析-子女教育66目标并进法目标并进法67

案例分析5年后购房目标终值50万元，20年后子女教育金目标终值20万元，30后年退休金目标终值100万元，投资报酬率8%，房贷利率6%，贷款20年，贷款七成，以目标并进法，在往后的人生阶段，各需要多少净现金流量投入额，才能完成所有目标?案例分析5年后购房目标终值50万元，268

案例分析购房首付款=50*30%=15万元前5年储蓄=PMT（8%，5，0，15）=2.5568购房贷款=50-15=35万元住房按揭款=PMT（6%，20，35）=-3.0515前20年子女教育金储蓄=