8-03综合理财规划

授课大纲
金融理财师
综合理财规划
综合理财规划流程 用EXCEL表计算财务函数 EXCEL表的综合运用
金融理财师
资格认证培训
1
2
一.综合理财规划流程
建立和界定与客户的关系 收集客户信息，了解客户的目标和期望 分析和评估客户当前的财务状况 制定并向客户提交个人理财规划方案 执行个人理财规划方案 监控个人理财规划方案执行
金融理财师
综合理财规划流程图
客户想得到的服务 客户 沟通 我们能提供的服务
金融理财师
需求面谈
资料 提供
个人财务资料-资产负债与现金流量 个人问卷资料-理财目标与风险偏好 理财相关统计数据库 复利年金各项公式设置 当前问题及未来改善建议
软件运用 个别调整
资料 输入
深入商谈
理财规划书 协助运行及绩效追踪
3
4
综合理财规划的流程
金融理财师
1.1 第一步：建立和界定与客户的关系金 融 理 财 师
搜集潜在客户优先族群名单 以信件或电话介绍顾问服务内容 取得客户同意安排需求面谈 说明自己的资历、认证资格与经验 议定提供理财规划服务的方式 议定所提供的规划服务如何收费
第一步：建立和界定与客户的关系,与客户进行初步面 谈，了解客户需求； 第二步：根据需求面谈和后续沟通所了解的情况，确定客 户理财目标； 第三步：全面考虑需求面谈和后续沟通所了解的情况，分 析客户的财务状况和行为特征，并根据客户财务 状况、行为特征与理财目标进行仿真分析； 第四步：根据前面各个步骤了解的情况和仿真分析的结 果，拟定理财规划报告书，并帮助客户分析和理 解理财规划报告书中拟定的各种理财规划建议； 第五步：客户同意理财报告书中的理财规划建议之后，可 以在客户认同的条件下，协助客户执行理财方案； 第六步：监督并控制理财方案的执行。
5 6
1

1.1.1 开拓客户
谁是客户 有能力 有需求 可接近
金融理财师
理财规划服务执行流程
电话接近 监控修正 准客户 名单 要求推 荐介绍 成交 解说财 务报告 说明金 融理财
金融理财师
客户在哪里 现有数据中找寻 客户介绍客户
第一次 面谈
发现事实 与感觉 第二次 面谈
7
8
1.1.2 了解客户的现状与需求
目的： 引起客户注意 发掘潜在或目前的问 题 强化客户解决问题的 需求
金融理财师
1.2 第二步：收集客户信息， 了解客户的目标和期望
金融理财师
步骤： 了解客户的现况与期 望 了解客户对现况的感 受 了解客户想要改变的 意愿
客户的目标和期望 应收集的客户信息 家庭成员年龄、关系、 理财目标-何时达成多 少金额 职业、健康状况 理财价值观-各目标优 家庭资产负债信息 先顺序 家庭收入支出的现金 风险偏好-可接受最大 流量信息 损失 保险及税负状况 理财个性-纪律性、依 投资组合明细 赖性、私密性
10
9
1.2.1全方位理财规划参考问卷（一） 融 理 财 师 金
1.基本数据 客户姓名: 配偶姓名; 通讯地址 2. 家庭资产负债 存款及国内债券基金 储蓄险现金价值 人民币理财产品 外币或QDII理财产品 国内股票或股票基金 3. 家庭现金流量 本人年工作收入 配偶年工作收入 家庭投资收入 预期年工作收入成长率
11
1.2.1全方位理财规划参考问卷（二） 融 理 财 师 金
4.子女教育金规划 目前未成年子女年龄 年高教费现 值 岁 岁 □学士 □学士 万元 资助子女高等教育年数
编号: 年龄 年龄 联络电话: 手机: E-Mail : 家庭人口数
子女一 子女二 万元 万元 万元 万元 万元 5. 购房规划 预计几年后购房换房 购房时房贷成数为 购房换房装潢预算为 6.退休规划
□硕士 □硕士
□博士 □博士
年 年
万元 万元 万元 万元 万元
不动产投资 自用住宅市值 创业资本 短期借款 房屋贷款
年 % 万元
购房总价现值 贷款年限 贷款利率
万元 年 %
万元 万元 万元 %
家庭年生活支出 年房租支出 年房贷本息支出 年家庭保费支出
万元 万元 万元 万元
12
本人拟退休的年龄预计为 预估本人可领劳保退职金 退休后年家庭支出现值为
岁 万元 万元
配偶拟退休年龄 配偶退职金 预计通货膨胀率
岁 万元 %
2

1.2.1全方位理财规划参考问卷（三） 融 理 财 师 金
7. 投资规划 紧急预备金相当于几个月的家庭经常性支出? 是否有两年内必须支应的短期目标? □是□否 若是的话需现值 个月 万元 ,用途为 % % 万元 □获利性 □期货 □外币 □无
1.2.2 目标需求层次分析
遗产 高额保单节税 他益信托规划 生活质量要求 实现所有梦想 理财价值观 目标优先顺序 可用储蓄 平均生活水准理财目标
金融理财师
最差情况下可接受的年度最大亏损额为目前总投资额的多少百分比? 预期长期投资报酬率的指标应比定存利率高出多少百分比? 支出是否靠投资收益 □是□否 若是的话每年期望投资收益为 □安全性 □股票 □收益性 □不动产
生息资产
投资决策时的优先级 □流动性 (依序填1-4 ,1为最重要) 何种工具绝对避免列入投资组合中? 8. 保险规划 被保险人 本人 配偶 保险事故发生时希望给家人保障
13
自用资产
满意水准目标
寿险保额
意外险 保额
满期领回 额
尚需缴保费 年数
年金领回 额
几年后回 额
年缴保费
基本支出
基本生活水准，财务安全保障
保险保额需求 流动性准备
年
14
1.3 第三步：分析和评估客户一般 财务状况及特殊需求
一般财务状况 生涯规划：事业、退 休、家庭、居住 理财计划：投资、保 险、债务、节税 考虑家庭生命周期的 规划 特殊需求 家庭结构改变: 离婚、再婚 事业发展变化: 失业、创业 居住环境变化: 移民 意外收支处理: 保险金、中奖
金融理财师
1.3.1 分析的步骤
金融理财师
结婚、 就业、 迁居、 遗产、
诊断目前的财务状况，提出改善建议； 根据宏观经济设定基本假设参数； 考虑货币的时间价值，规划如何由现状达到目 标； 方案中涉及理财决策者，应作更详尽分析，帮 助客户作最佳抉择。
15
16
1.3.2诊断目前的财务状况提出改善建议
家庭财务比率
流动比率 资产负债率 紧急预备金倍数 财务自由度 财务负担率 平均投资报酬率 净值成长率 储蓄率 自由储蓄率
17
金融理财师
1.3.3 根据宏观经济设定基本假设参数
金融理财师
定义
流动资产/流动负债 总负债/总资产 流动资产/月支出 年理财收入/年支出 年本息支出/年收入 年理财收入/生息资产 储蓄/期初净值 储蓄/总收入 自由储蓄/总收入
合理范围
2-10 20%-60% 3-6 20%-100% 20%-40% 3%-10% 5%-20% 20%-60% 10%-40%
18
通货膨胀率：依照消费类别更佳，可算出合理的退休时生 活费用。 学费成长率：依照过去统计与未来趋势，估计各级公私立 学校与留学的费用成长率。 房屋折旧率与房价成长率：房屋未来价值=当前房价×(1折旧率×N)×(1+房价成长率)N (N=居住年数或投资年数） 建议投资报酬率：根据建议的资产组合，可算出合理的建 议投资报酬率。 折现率：将未来目标折合当前现值时，折现率=投资报酬 率-通货膨胀率 退休生活调整率：如客户未明确表示退休所需费用，可按 退休前标准的70%估计。 保险事故发生后家庭支出调整率：如客户未明确表示，可 按保险事故发生前标准的80%估计。
3

1.3.4 考虑货币的时间价值规划如 何由现状达到目标（一）
金融理财师
1.3.4 考虑货币的时间价值规划 如何由现况达到目标（二）
金融理财师
未来理想
现状
问题：
现有财富净值PV 资产-负债 多长时间？NPER 多高回报率？RATE 多大支出预算？PMT 多少应急资金？PV 风险多大？
购房购车 子女留学 旅游休闲 退休养老 遗产传承
金额FV-期待成就 时间N-何时实现
未来收入支出PMT
根据客户的理财价值观确定各项理财目标之间的优先顺 序，如子女教育、退休、购房、购车； 确定现有资源运用的优先顺序，如先配置资产再配置储 蓄； 依照风险承受度与理财目标确定合理的资产配置方案； 当各项理财目标无法同时实现时，确定调整方案； 当各项理财目标可以同时实现并且资源仍有剩余时，确 定调整方案； 动态模拟全生涯的资产净值与现金流量； 按月份模拟短期的现金流量。
19
20
1.3.5 理财方案决策
金融理财师
方案选择的层次
金融理财师
购房与租房决策：详见房产规划单元 是否出国留学决策：详见教育金规划单元 贷款组合决策：详见信用管理单元 离婚的财务考虑：详见特殊理财规划单元
所有可能方案：树形图； 可选方案：筛选各年度现金流量可达成理财目 标者； 推荐方案：可选方案超过三个时，可择优选出 三个方案供客户最后决策； 最终方案：由客户做最后的决定，根据最终方 案配置产品。
21
22
理财决策考虑依据—比较不同方案 金 融 理 财 师
单一决策：只考虑该决策所涉及的现金流量， 还原为净现值，净现值高者优先考虑。 生涯决策：把所有的理财目标涉及的现金流量 一起考虑，内部报酬率低者，表示要达成所有 理财目标所需要的投资报酬率较低，达成的可 能性较高，应优先考虑。
1.4 第四步：制定并向客户提交
个人理财规划方案
理财规划报告书的内容 内容摘要 规划分析 行动方案 产品推荐
金融理财师
23
24
4

1.4.1 内容摘要
金融理财师
1.4.2 规划分析
金融理财师
编写报告摘要，描述以上各项分析与规划的结果。 报告摘要主要包括以下内容： 针对客户最关心的理财目标或急待解决的特殊需 求，给出明确的综合诊断结果与建议。 综合财务诊断 投资规划与投资组合建议 风险管理规划与保险组合建议 理财目标达成可行性分析与调整说明 后续行动方案
25 26
客户的家庭资产负债表及财务结构 客户的家庭现金流量表及收支储蓄结构 对客户目前财务状况的诊断与建议 依照客户风险承受度，设定合理的投资报酬率 根据设定的投资报酬率与理财目标达成年限，参考各种 投资工具的历史报酬率与风险，模拟出最有机会实现理 财目标的核心投资组合，提出相应的资产配置建议。 将各项理财目标所需的未来现金流出与现有的资产及工 作能力可产生的未来现金流入相对照，测算各个时期的 资金缺口，提出应提高收入、降低支出或调整理财目标 金额与年限的建议。 依照收入弥补法或遗属需要法，测算保障型保额需求。
1.4.3 行动方案
解决客户特殊需求的行动 方案 首次咨询的一般性理财规 划 定期检查执行效果的理财 安排 因为移民、离婚或分配财 产需要做出的特殊规划 现金流量非正常变化时或 在税收方面的特别考虑
金融理财师
1.4.4 产品推荐
金融理财师
投资调整方案 比较现有的投资组合及建议 的投资组合，列出可行的投 资组合调整比率、金额及调 整时机。 保险调整方案 现有保险安排是否充分 评估可能改变生涯现金流 量的风险 衡量保险规划的合理性 提出保险规划调整方案
28
独立客观的理财师应该把理财规划与产品推荐 严格分开，只有当客户要求理财师协助其执行 规划方案时，理财师才能进行产品推荐。 理财师可以在本机构提供或者代销的产品中寻 找满足客户需要的产品，配置客户的资产。 如果本机构没有满足客户需求的产品，应该本 着以客户为中心的原则，尽可能地推荐其他金 融机构提供或代销的满足客户需要的产品。
27
1.4.5 与客户深入商谈 —讨论理财方案
金融理财师
1.4.5 与客户深入商谈 —讨论的主要问题
金融理财师
请客户检查理财规划报告书中的描述是否准 确，需求面谈时是否存在误解的地方，数据提 供是否有疏漏之处。 向客户解释理财规划报告书中客户不易理解的 内容。 向客户详细介绍理财方案和行动方案，分析各 个方案中的利弊得失，模拟并向客户说明理财 规划前后所发生的变化。
各项理财规划目标能否达成？如果能够达成，供给缺口 有多大？ 若无法达成，需求缺口有多大？应如何调整目标？ 依照客户的风险承受度，应如何配置资产？建立什么样 的投资组合？ 为了达到保障与退休需要，如何制定保险规划？ 在需要时，应制定什么样的财产转移规划方案？ 如何协助客户执行投资方案与保险安排？ 在产品搭配时应注意哪些问题？
29
30
5

1.5 第五步：执行个人理财规划方案
金融理财师
1.5.1 选择交易机构
金融理财师
在为客户选择产品时，不能仅仅在自己所属的 金融机构所提供或代销的产品中选择，而应该 严格按照理财规划方案，在整个市场上选择最 合适的产品。 理财规划绝对不是吸引客户的手段，一定不要 向客户推销他们不需要的产品，产品组合一定 要满足客户的风险承受度与理财目标的要求。 除了可以加上一些对金融市场的看法与买卖时 机的注意事项供客户参考之外，后续的协助执 行安排一定要与理财规划方案尽可能保持一致。
31 32
当客户已经在券商开户、有银行账户、持有保 险公司保单时，询问客户对现有的理财服务是 否满意。 理财师及其所属金融机构应为客户提供满意的 理财服务。 理财师可以把专业素质更高与服务态度更好的 金融机构介绍给客户，选择更适合客户需要的 产品作为客户交易的对象。
1.5.2 选择适当的理财产品
货币类资产 债券类资产 股权类资产 房地产 衍生金融工具 详见投资规划单元
金融理财师
1.5.3 理财规划建议与产品搭配 的原则
金融理财师
先保障后求利 先揭示风险再说明获利潜力 提供足够的市场与产品信息并完整说明 定期检查调整
33
34
1.6 第六步：监控个人理财规划 方案执行
金融理财师
1.6.1 检查应有储蓄与实际储蓄 之间的差异
金融理财师
随着时间的推移和环境的变化，原来制定的理 财目标与理财方案可能与现实情况不完全相符。 理财规划注重的是长期的策略性安排，不要随 意改变方向或放弃原来制定的目标或方案。 理财师监督和控制理财方案执行的内容和频率 视不同的客户和不同的理财方案而定。
应有储蓄是为了实现理财目标，牺牲现在消费 而换取未来消费的份额。 应有储蓄可以根据理财目标测算出来。 收入-应有储蓄=支出预算，执行预算来达到应 有储蓄，再充分利用应有储蓄投资，来达成理 财目标。
35
36
6

案例
金融理财师
1.6.2 检查应累积生息资产与实际 累积生息资产之间的差异
金融理财师
不考虑货币的时间价值。小王30岁，预计65岁退休，退 休后生活25年，要实现购车5万元、购房50万元与退休 后生活费3万元的理财目标，计算测算出每年应有储蓄 3.71万元，目前每年生活费4万元，每年应有收入=3.71 万元+4万元=7.71万元。 应有收入是小王努力的目标。如果收入无法达到7.71万 元，那么，可以通过削减支出来达到。如果小王年收入 只7万元，应有年储蓄为3.71万元，那么，年储蓄率为 53%，每个月的支出预算为(7万元-3.71万元)/12=2,742 元。目前月支出为4万元/12=3,333元，每月支出要削减 591元。如果削减支出不能一蹴而就，可以采取逐月降 低开支的办法。例如：每个月在前一个月的基础上削减 开支100元，经过半年修正就可以达到原定目标。
37 38
生息资产的价值会随着时间的变化而变化。 通常每个季度或每半年检查应累积生息资产与 实际累积生息资产之间的差异。 当两者之间的差异太大，超过预期范围时，理 财师应该决定是否需要调整原来制定的理财目 标。
1.6.3 资产累积较预期差时的调整 方案图示
金额
A→R A→B B→C R→D R→E B→R 累积养老金退休 一年后资产降低 提高储蓄额 降低养老金目标 延后退休年限 提高投资报酬率
金融理财师
1.6.4 意外收支的处理
金融理财师
R D
E
在定期检查期间，如果存在预料之外的大额支 出或大额收入，致使储蓄计划无法实现或者大 额收入需要资产配置，理财师要针对这些情况 进行调整并制定相应的计划。
A
C B
时间
39 40
1.6.5 到期资产的配置
金融理财师
1.6.6 理财目标未能如期实现时的 调整方案
金融理财师
在初次规划时，对于有些没有到期的资产，例 如定期存款，要事先制订规划，安排这些资产 到期时如何进行重新配置。 在定期检查时，要确定到期资产是否按原来方 案配置。如果实施新的配置方案，需要与客户 讨论确定。
调整顺序 1.在收入的能力范围内提高每月储蓄金额，用来定期定额投资国内外 基金。 2.延后退休年龄或延后购车购房创业等时间,让资产有更长时间进行财 富积累 3.降低原订理财目标需求水准，使未来目标与当前能力相配合。 4.提高投资报酬率假设,调整至积极型的投资组合来增加目标达成的可 能性。 应注意事项 1.调整后储蓄率一般不应超过50%。 2.子女教育难延后，退休最长延至65岁。 3.目标水准降低后不得低于基本水准。 4.仍应考虑改变风险承受度可行性。
41
42
7

二.使用EXCEL财务函数进行计算
EXCEL使用 EXCEL常用财务函数 用EXCEL做生涯仿真
金融理财师
理财规划运用的主要工具
工具 复利与年金表 财务计算器 EXCEL表 套装理财规划软件 优点 简单 精确 连结运算 综合考虑
金融理财师
缺点 不精确 需要熟悉 省略过程 不易解释 内容缺乏弹性
43
44
2.1.1 EXCEL使用注意事项
金融理财师
2.1.2 步骤1 打开EXCEL
金融理财师
按顺序输入RATE、NPER、PMT、PV、FV、 TYPE。 RATE输入：例如：10％，应输入0.1或10%，与 计算器输入10i不同。 TYPE：1为期初，0为期末，如为0，可缺省。 输入数字时，如投资、存款、生活费用支出、房 贷本息支出都是现金流出，输入负号；收入、赎 回投资、借入本金都是现金流入，输入正号。 只要输入任何四个变量，就可以求出第五个变量。
45 46
步骤2 在菜单中选择【插入】功能 在下拉菜单中选择【函数】
金融理财师
步骤3 出现插入函数对话框，在选 择类别框中选择【财务】
金融理财师
47
48
8

步骤4 在财务函数中选择需要用的终值、 现值或年金等函数（如FV函数）金 融 理 财 师
步骤5
依照对话框的提示，输入RATE、NPER、 PMT、PV、TYPE的数据，确定后在保存 格将得到终值FV的结果
金融理财师
49
50
步骤6 也可以在保存格先输入数据，在FV 函数输入保存格编号来求终值的结果
2.2.1 EXCEL常用财务函数
金融理财师
—PV与FV PV 现值函数 PV（R，N，PMT， FV，0/1） R为折现率 N为期数 PMT为年金 FV为终值 0代表期末年金 1代表期初年金
金融理财师
FV 终值函数 FV（R，N，PMT， PV，0/1） R为投资报酬率 N为期数 PMT为年金 PV为现值 0代表期末年金 1代表期初年金
51
52
练习：FV函数
2.2.2 EXCEL常用财务函数
金融理财师
—PMT与PPMT
PMT年金函数 PMT（R，N，PV， FV，0/1） R为折现率或贷款利率 N为期数 PV为现值 FV为终值 0代表期末年金 1代表期初年金
金融理财师
问题1：现在有20万元，如果投资到基金，每年赚 10%。请问：5年后会得到多少钱？ 解：FV(10%,5,0,-20,0)=32.2万元 问题2：如果现在不投入，但每年年底投入5万元， 假设报酬率是10%。请问：5年后会有多少钱? 解：FV(10%,5,-5,0,0)=30.5万元 问题3：如果整笔投入与每年年底投入同时进行。 假设上述条件不变，请问：5年后可累积多少钱? 解：FV(10%,5,-5,-20,0)=62.7万元
53 54
PPMT本利平均摊还本金函 数（计算某期的本金偿还额） PPMT（R，PER，N， PV，FV，0/1） R为折现率或贷款利率 PER为第几期 N为期数 PV为期初贷款总额 FV为期末贷款余额 0代表期末年金 1代表期初年金
9

练习：PV函数与PMT函数
2.2.3 EXCEL常用财务函数
金融理财师
—IPMT与ISPMT
IPMT本利平均摊还利息函
金融理财师
问题1：小宝希望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万元 问题2：有一张还有5年到期的债券，票面利率为5%， 每半年付息一次，面值100万元，当前市场利率为4%。 请问这只债券的市价是多少？ 解：PV(4%/2,5×2,100×5%/2,100,0)=104.49万元
ISPMT本金平均摊还利息
数（计算某期支付的利息额） 函数（计算某期支付的利 IPMT（R，PER，N， 息额） PV，FV，0/1） ISPMT（R，PER-1， N，PV） R为折现率或贷款利率 PER为第几期 R为折现率或贷款利率 N为期数 PER为第几期 PV为期初贷款总额 N为总期数 FV为期末贷款余额 PV为期初贷款总额 0代表期末年金 1代表期初年金
56
55
练习：贷款本利计算
2.2.4 EXCEL常用财务函数
金融理财师
—NPER与RATE
NPER期数函数 NPER（R，PMT，PV， FV，0/1） R为折现率或贷款利率 PMT为年金 PV为现值 FV为终值 0代表期末年金 1代表期初年金
金融理财师
问题：贷款30万元，利率6%，20年还清，本利平均摊还月供 额是多少？第100期本金与利息各是多少？如果采用本金平 均摊还，第100期本金与利息各是多少？ R=6%/12=0.5%， N=12×20=240 本利平均摊还法 月供额：PMT(0.5%,240,300000,0,0)=-2,149.3元 第100期本金：PPMT(0.5%,100,240,300000,0,0)=-1,063.9元 第100期利息：IPMT(0.5%,100,240,300000,0,0)=-1,085.4元 本金平均摊还法 每期本金：300,000/240=1,250元 第100期利息：ISPMT(0.5%,99,240,300000)=-881元
57 58
RATE利率函数 RATE（N，PMT， PV，FV，0/1） N为期数 PMT为年金 PV为现值 FV为终值 0代表期末年金 1代表期初年金
练习：NPER函数与RATE函数
2.2.5 EXCEL常用财务函数
金融理财师
—IRR与NPV
金融理财师
问题1：现有资产20万元，年投资1万元，报酬 率8%，几年后可以累计100万元退休金? 解：NPER(8%,-1,-20,100,0)=16.13年 问题2：现有资产20万元，每年可投资6万元， 理财目标为10年累积资产100万元，实现该理财 目标应有的报酬率是少？ 解：RATE(10,-6,-20,100,0)=3.73%
内部回报率(IRR)：它是指使净现值等于0的 贴现率，是收支平衡时的报酬率。
NPV = ∑
t =0
T
Ct =0 (1 + IRR)t
对于一个投资项目，如果IRR>r，表明该项目 财务上具备可行性；相反地，如果IRR59
60
10

IRR与NPV财务函数说明
金融理财师
IRR内部报酬率的计算
金融理财师
IRR内部报酬率函数 NPV净现值函数 IRR（投资CF0 ，现金流 NPV（R，投资CF0，现金 量数列C1，C2，…，Cn） 流量数列C1，C2，…，Cn） 现金流量为正数，表示当 期有现金流入，如为负 数，表示当期有现金流出。 在退休规划中，工作期现 金流如果设定为正数，则 退休期为负数。计算出的 IRR即为实现退休规划所 需的报酬率。 现金流量为正数，表示当期 有现金流入，若为负数，表 示当期有现金流出。 NPV通常用于比较两个投资 方案何者划算。NPV愈高者 愈划算。
61
62
NPV净现值的计算
2.2.6 EXCEL常用函数
金融理财师
－IF函数
金融理财师
IF(A,B,C)：若A成立，输出B，若A不成立， 输出C。 IF(A<=60,100000,30000)A为年龄，60岁退休， 退休前工作收入为10万元，退休后养老金收入 为3万元。 IF函数可以多层次，最多16层，如未来20年收 入成长率g从10%起每5年降低1%，表示为： IF( n<=5,10%,IF(n<=10,9%,IF(n<=15,8%,7%)))
EXCEL表格计算NPV数值×(1+折现率)=财务计算器计算的NPV数值
63 64
IF函数的运用：收入栏与退休规划 金 融 理 财 师
年数 收入 10 10 10 10 34 4 4 4 4 4
2.3 用EXCEL做生涯仿真
金融理财师
a<5 工作收入 Y=10
If m= 5年退休
1
a>5
2 3 4
a=5
养老年金收入P=4
5 6 7
一次性养老金收入B=30 +养老年金收入P=4
8 9 10
期初现金流量=当前可运用的资金CF0 按年估算每个理财目标的现金流量 当年净现金流量＝每年税后收入－每年生活支 出－当年理财目标支出－当年负债本利摊还 由期初现金流量与评估期间各期现金流量，可 算出实现长期目标所需的内部报酬率，若年内 部报酬率超过10%，目标实现的可能性低，应 延长目标实现年限、降低目标或提高储蓄额。
年数列在EXCEL表格中为A列，则第一年位置为a2 相应在收入栏b2输入：If(a2<5,10,if(a2=5,30+4,4))
65 66
11

2.3.1 长期生涯模拟表
— 计算内部报酬率
金融理财师
2.3.2 内部报酬率电子表格
几年后 工作收入 日常支出 购车 购房 子女教 育 期初生息资产 1 2 3 4 5 6 7 80,000 90,000 100,000 110,000 120,000 130,000 140,000 150,000 160,000 170,000 (56,000) (63,000) (70,000) (77,000) (84,000) (91,000) 0 (100,000) 0 0 0 0 0 (71,219) (71,219) (71,219) (71,219) (71,219)
金融理财师
现金流量 生息资产 200,000 0 (76,000) 0 (44,219) 0 (41,219) 0 (38,219) 0 (35,219) 0 (32,219) (8,000) (3,188) (2,388) (1,808) 11.88% 200,000 147,762 121,099 94,268 67,249 40,020 12,556 6,047 3,578 1,616 (0)
CF0=投资资产 第一期收入 第二期收入 第一期支出 第二期支出 理财目标支出 理财目标支出 CF1净现金流量 CF2净现金流量
(98,000) (30,000) (105,000) (26,188) (112,000) (26,188) (119,000) (26,188)
0 (20,000) 0 (22,000) 0 (24,200) 0 (26,620) IRR
第N期收入
第N期支出
理财目标支出
CFn净现金流量
8 9 10
IRR(CF0：CFn)=内部报酬率
见内部报酬率计算.xls
67 68
三.EXCEL的综合运用
用EXCEL做退休规划 用EXCEL计算保险需求 用EXCEL做生涯仿真 全生涯规划表 敏感度分析
金融理财师
3.1 用EXCEL做退休规划
夫妻二人总体规划
退休规划 支出项目 日常生活费用 通货膨胀率 3% 拟退休年龄 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 尚可工作年数 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 投资报酬率 8% 当前年龄 当前年支出额 $64,000 调整原因 子女独立 已准备退休金 $60,000 退休金资产 $416,943 $453,299 $492,563 $534,968 $580,765 $630,226 $683,644 $741,336 $803,643 $870,934 $943,609 $1,022,098 $1,106,866 $1,198,415 $1,297,288 $1,404,071 $1,519,397 退休后余命 35 34 33 32 31 30 29 28 27 26 25 24 23 22 21 20 19 30 当前年收入额 预估增减额 ($24,000) 退休金年储蓄 $3,000 退休金负债 $1,242,092 $1,265,190 $1,287,827 $1,309,894 $1,331,273 $1,351,833 $1,371,430 $1,389,908 $1,407,092 $1,422,795 $1,436,808 $1,448,904 $1,458,836 $1,466,333 $1,471,099 $1,472,812 $1,471,118 拟留遗产额 $0 退休后年支出 $40,000 90000
金融理财师
（夫妻分别规划见附件单目标规划2010.xls）
退休金净值 ($825,149) ($811,891) ($795,264) ($774,926) ($750,508) ($721,607) ($687,786) ($648,572) ($603,450) ($551,860) ($493,198) ($426,806) ($351,971) ($267,918) ($173,811) ($68,741) $48,279
69
70
66
用EXCEL做退休规划说明
退休后 生活费
3.2 用EXCEL计算保险需求
金融理财师
—生命价值法或净收入弥补法
退休前弥补收入的寿险需求 被保险人当前年龄 预计几岁退休 离退休年数 当前的个人年收入 当前的个人年支出 当前的个人年净收入 折现率(实质投资报酬率) 个人未来净收入的年金现值 弥补收入应有的的寿险保额 本人 29 55 26.0 51,160 21,600 29,560 3% 528,439 528,439
金融理财师
退休年支出现值 =当前年支出-年学费支出-子女费用支出 退休后年支出终值 =FV(通货膨胀率，离退休年数，退休年支出现值) 退休负债(总需求) =PV(实质报酬率，退休后生活年数，退休后年支出 终值，1)
配偶 29 55 26.0 41,492 21,600 19,892 3% 355,606 355,606
退休金 总需求
退休资 源配置
71
退休资产(总供给) =FV(报酬率，离退休年数，储蓄配置，资产配置) 以仿真法决定能否实现退休规划
72
应有保额=个人未来净收入的年金现值 =PV(实质投资报酬率，离退休年数，工作期净收入)
12

用EXCEL计算保险需求
—遗属需求法
弥补遗属需要的寿险需求 配偶当前年龄 当前的家庭生活费用 保险事故发生后支出调整率 配偶的个人收入 家庭年支出缺口 家庭未来生活费准备年数 家庭未来缺口的年金现值 上大学4年学费支出现值 家庭房贷余额及其他负债 丧葬最终支出当前水平 家庭生息资产 遗属需要法应有的寿险保额 本人 29 43,200 70.00% 41,492 (11,252) 26 (207,185) 40,000 0 10,000 150,000 (307,185)
金融理财师
3.3 用EXCEL做生涯仿真
金融理财师
配偶 29 43,200 70.00% 51,160 (20,920) 26 (385,203) 40,000 0 10,000 150,000 (485,203)
见生涯规划表2010.xls
应有保额=PV(实质投资报酬率，遗属生活保障年数，遗属年生活费缺口，0，1) +子女高等教育金现值+房贷及其他负债+丧葬支出-家庭生息资产 73
74
3.3.1 用EXCEL计算目标现值
金融理财师
3.3.2 用EXCEL计算供给现值
金融理财师
见生涯规划表2010.xl
见生涯规划表2010.xl
75
76
3.3.3 供需调整说明
金融理财师
3.4 全生涯理财电子表格制作流程图
货币债券股票报酬率标准差 风险承受能力 风险矩阵 风险承受态度
金融理财师
见生涯规划表2010.xl
合理投资组合资产配置 合理风险属性的 投资报酬率ROI
家庭收支资产负债
全生涯现金流量表 保险规划表
比较与调整 可达成理财目标的 内部报酬率IRR
家庭理财目标
77
78
13

3.4.1 全生涯理财规划问卷1
理财问卷 家庭成员 目前年龄 退休年龄 预计终老年龄 目前收入 目前支出 个人收入成长率 个人支出成长率 退休后收入替代率 退休后支出调整率 现有寿险保额
79
金融理财师
3.4.1 全生涯理财规划问卷2
资产 生息资产 自用房产 理财目标 养育子女 赡养父母 购房 子女大学教育 假设条件比率 房贷利率 贷款成数 保险事故发生后支 出调整率
80
金融理财师
已婚1 29 55 80
未婚0
1 配偶 29 55 80 41,492 21,600 2% 2% 25% 75% 0
金额 150,000 300,000 准备年限 2 2 2 20 比率 6% 50% 70%
负债净值 房贷负债 净值 年现值目标 10,000 20,000 800,000 10,000 假设条件年数 购房目标贷款年数 家庭生活保障年数 现有房贷剩余年数
金额 0 450,000 持续年数 22 22 1 4 年数 20 26 5 支出成长率 2% 2% 4% 3%
本人
51,160 21,600 2% 2% 25% 75% 0
3.4.2 生涯模拟表说明1
金融理财师
3.4.2 生涯模拟表说明2
金融理财师
打开案例2010.xls； 所有的输入资料都在输入资料工作表； 工作收入包括夫妻退休前后的收入，生活支出 包括夫妻退休前后的支出，用IF函数控制； 子女教养支出包括养育支出与大学学费支出； 购房还贷款栏包括旧房贷款，新房首付款，新 房贷款，换房时可减出售旧房净流入，用IF函 数控制； 保费预算栏来自保险规划的结果，只算定期险。
81 82
净现金流量=工作收入-生活支出-子女教 养费-父母赡养费-购房换贷款-保费预算； 由期初生息资产与各年净现金流量，可算 出内部报酬率，如IRR(h3:h63)； 本期生息资产=上期生息资产×（1+内部 报酬率）+本期净现金流量； 最后一期的生息资产一定为0。
3.4.3 IRR与ROI的比较与规划调整 金 融 理 财 师
比较可达成所有的理财目标的内部报酬率 （IRR），与根据风险属性及投资工具特性所算 出的投资报酬率（ROI）。 ROI>IRR，可提高当前或退休后的生活水平， 提高购房目标，提前退休或降低收入成长率假 设。 IRR>ROI，可降低当前或退休后的生活水平， 降低购房目标，延后退休或提高收入成长率假 设。
83 84
3.4.4 敏感度分析
金融理财师
目的：检验当通货膨胀率、收入成长率、投资 报酬率、负债利率等发生较大变化，并与当前 假设有明显差异时，对当前家庭财务状况及未 来理财目标实现可能带来的影响。 在敏感度分析中，一般固定三个变量，观察当 第四个变量变动时，第五个变量所受的影响。 通常被用来作敏感度分析的第四个变量为r，代 表利率、投资报酬率、折现率或通货膨胀率。 可改变输入资料工作表的假设作敏感度分析。 （见“敏感度分析2010.xls”）
14

自由
自主
自在
85
15