Excel 2003个人投资理财应用详解
IT商界
随着生活水平提高和收入渠道增多，家庭投资理财的观念逐渐被大众接受。由于一般家庭的电脑都安装了Excel，为我们提供了简便易行的投资理财工具。本文以普通工薪家庭为对象，介绍Excel 2003投资理财的方法与技巧。
一、家庭收支记账
收支记账是家庭理财的基础工作，它不仅具有量入为出、保持收支平衡的功能，而且可以通过统计分析收支情况，为合理安排家庭开支和投资奠定基础。
1.设计收支账簿
“收支账簿”用于记载家庭已经发生的收入和支出，为了方便用户按“流水账”方式输入收支记录，收支账簿设计“支出记账”、“收入记账”两个工作表，分别记录每一笔支出和收入。
(1)支出记账工作表
在工作表的第一行中输入账目名称。请选中“B1:G1”区域，点击“合并及居中”按钮，在合并后的单元格中输入“家庭支出记账”，然后设置它的字体、字号等格式。从B2单元格开始，依次输入“支出序号”、“支出日期”等列标题，其作用相当于数据库的“字段”。
在Excel中，点击“工具→选项”菜单命令，在出现的对话框中点击“视图”选项卡，将“窗口选项”选项组中的“网格线”选项去掉，然后为“列标记”和其下的单元格设置“所有框线”(表格线)，就可以获得如图1所示的记账工作表。
图1
在“列标题”下的每个单元格中存放数据，事先应当根据数据类型设置单元格格式。例如，“支出日期”应当设置为“日期”(2004-9-10或2004年9月11日)，“支出金额”应当设置为“中文货币”(￥)，其他单元格的格式保持默认的“常规”即可。
由于“支出记账”表要存放很多记录，为了在滚动记录时始终显示“列标题”，我们可以在工作表中选中H3单元格，点击“窗口→冻结窗格”菜单命令即可。
(2)数据下拉列表
为了方便统计分析，“支出记账”表中的“支出类别”和“支出账户”需要按标准输入。为此，我们可以给“支出类别”和“支出账户”列的所有单元格设置下拉列表，以后输入数据只须打开列表选择。
选中“支出类别”列的空白单元格D3，点击“数据→有效性”菜单命令，在出现的对话框中点击“设置”选项卡。在“允许”下拉列表中选择“序列”选项，然后选中“忽略空值”和“提供下拉箭头”选项，在“来源”框内输入“食品，房租，水电费，煤气费，日常用品，购买服装，在外就餐，健身娱乐，休闲度假，通讯费，书籍报刊，医疗保健，交通费，交际支出，赡养老人，储蓄股票，其他支出”等内容，完成后点击“确定”按钮。
注意：相邻的两个选项之间请采用英文逗号分隔。
选中已经设置了下拉列表的D3单元格，鼠标指向选中标记右下角的拖动柄，然后向下拖动至该列的最后一个单元格，将已设置的输入方式应用于该列的所有单元格。以后输入“支出类别”列中的数据时，选中单元格的右侧自动显示的下拉按钮，点击就可以打开下拉列表选择输入内容了。
按照相同的操作方法，即可给“支出账户”设置相同的输入方式。不同的是“设置”选项卡中的“来源”要输入账户名称。例如，参与家庭经济活动的成员姓名。
由于“收入记账”工作表的结构和“支出记账”基本相同，我们只须修改一下账目名称和“列标题”，就可以得到如图2所示的“收入记账”工作表。
图2
2.家庭收支对账
当发生了财务收支事件以后，我们应当按照财务管理的要求及时入账，也就是在图1或图2的工作表中输入资金流动记录。为了便于家庭成员及时了解收支变化情况，还需要设计一个“收支对账”工作表，以便计算某个月家庭的总收入和总支出，以及“收支盈亏”和各账户(家庭成员)的收支情况。
该工作表的结构如图3所示。其中B1:J1区域合并以便输入账目名称，B2、C2等单元格输入“序号”、“日期”、“月总支出”等列标题，读者也可以根据实际需要增减“列标题”的数量。
图3
(1)月总支出公式
按惯例，家庭收支应当随时记账按月结算。为此，“日期”列的C3、C4等单元格存放记账周期(月份)，“月总支出”的D3、D4等单元格存放各月的支出合计，其他各列的意义则如列标题标示的那样。
假如，C3单元格存放的是“2004年10月”，说明D3单元格要合计2004年10月1日至2004年10月31日期间的支出，此时D3单元格中的公式为“=SUMIF(支出记账!S[KG-*5/7]\CS[KG-*5/7]\3:S[KG-*5/7]\CS[KG-*5/7]\73,"<=2004-10-31",支出记账S[KG-*5/7]\FS[KG-*5/7]\3:S[KG-*5/7]\FS[KG-*5/7]\13)-SUMIF(支出记账!S[KG-*5/7]\CS[KG-*5/7]\3:S[KG-*5/7]\CS[KG-*5/7]\73,"<=2004-10-1",S[KG-*5/7]\FS[KG-*5/7]\3:S[KG-*5/7]\FS[KG-*5/7]\13)”。
其中，“支出记账!S[KG-*5/7]\CS[KG-*5/7]\3:S[KG-*5/7]\CS[KG-*5/7]\73”是工作表所有支出记录的引用，实际使用时，“S[KG-*5/7]\CS[KG-*5/7]\3:S[KG-*5/7]\CS[KG-*5/7]\73”应当根据记录的多少修改；“支出记账!S[KG-*5/7]\FS[KG-*5/7]\3:S[KG-*5/7]\FS[KG-*5/7]\13”是10月1日至31日期间支出记录的引用，它同样需要根据该月实际发生的支出记录多少确定。
上面的公式由两个SUMIF函数构成，第一个SUMIF函数对2004年10月31日(含31日)以前的收入进行合计。其中，“支出记账!S[KG-*5/7]\CS[KG-*5/7]\3:S[KG-*5/7]\CS[KG-*5/7]\73”是供条件判断使用的区域，“<=2004-10-31”是判断哪些单元格将被合计的条件，凡在“支出记账!S[KG-*5/7]\CS[KG-*5/7]\3:S[KG-*5/7]\CS[KG-*5/7]\73”区域中满足该条件，且与“支出记账!S[KG-*5/7]\FS[KG-*5/7]\3:S[KG-*5/7]\FS[KG-*5/7]\13”区域在同一行的数据将参与合计。
第二个SUMIF函数对2004年10月1日(含1日)以前的支出进行合计，以上两个计算结果的差就是2004年10月1日至2004年10月31日之间的支出合计。
接下来就可以把D3单元格中的公式复制到D4、D5等单元格，然后根据记账周期对公式进行修改，以便对其他月份的支出进行合计。假如，C6单元格左侧存放的是“2005年1月”，就需要将D6单元格中的公式进行相应修改，即“=SUMIF(支出记账!S[KG-*5/7]\CS[KG-*5/7]\3:S[KG-*5/7]\CS[KG-*5/7]\73,"<=2005-1-31",支出记账! S[KG-*5/7]\FS[KG-*5/7]\23:S[KG-*5/7]\FS[KG-*5/7]\36)-SUMIF(支出记账!S[KG-*5/7]\CS[KG-*5/7]\3:S[KG-*5/7]\CS[KG-*5/7]\73,"<=2005-1-1",支出记账!S[KG-*5/7]\FS[KG-*5/7]\23:S[KG-*5/7]\FS[KG-*5/7]\36)”。
修改时要注意，公式中的年、月、日和实际情况保持一致，例如，2005年2月份只有28天，公式必需写成“=SUMIF(支出记账!S[KG-*5/7]\CS[KG-*5/7]\3:S[KG-*5/7]\CS[KG-*5/7]\73,"<=2005-2-28",支出记账!S[KG-*5/7]\FS[KG-*5/7]\36:S[KG-*5/7]\FS[KG-*5/7]\46)-SUMIF(支出记账!S[KG-*5/7]\CS[KG-*5/7]\3:S[KG-*5/7]\CS[KG-*5/7]\73,"<=2005-2-1",支出记账! S[KG-*5/7]\FS[KG-*5/7]\36:S[KG-*5/7]\FS[KG-*5/7]\46)”，否则计算合计时可能发生错误。
提示：对上面的公式略加修改，就可以对更长或更短时间段内的支出进行合计。例如，“=SUMIF(支出记账!S[KG-*5/7]\CS[KG-*5/7]\3:S[KG-*5/7]\CS[KG-*5/7]\73,"<=2004-12-31",支出记账!S[KG-*5/7]\FS[KG-*5/7]\3:S[KG-*5/7]\FS[KG-*5/7]\30)-SUMIF(支出记账!S[KG-*5/7]\CS[KG-*5/7]\3:S[KG-*5/7]\CS[KG-*5/7]\73,"<=2004-10-1",支出记账!S[KG-*5/7]\FS[KG-*5/7]\3:S[KG-*5/7]\FS[KG-*5/7]\30)，可以得到2004年第4季度的支出合计。
(2)月总收入公式
图3中的E2单元格存放“月总收入”，其下的E3、E4等单元格存放各月的收入合计。按照计算“月总支出”的思路，我们将上面介绍的公式略加修改，就可以得到E3单元格中的2004年10月总收入计算公式“=SUMIF(收入记账!S[KG-*5/7]\CS[KG-*5/7]\3:S[KG-*5/7]\CS[KG-*5/7]\73,"<=2004-10-31",收入记账!S[KG-*5/7]\FS[KG-*5/7]\3:S[KG-*5/7]\FS[KG-*5/7]\13)-SUMIF(收入记账!S[KG-*5/7]\CS[KG-*5/7]\3:S[KG-*5/7]\CS[KG-*5/7]\73,"<=2004-10-1",收入记账!S[KG-*5/7]\FS[KG-*5/7]\3:S[KG-*5/7]\FS[KG-*5/7]\13)”了。至于E4、E5等单元格中的其他计算公式可以按照上面介绍的方法进行修改。
(3)账户支出计算公式
为了便于了解每个家庭成员的月支出，我们在图3工作表中设立了若干“账户支出”列。如果我们需要统计“账户1”2004年10月的支出，可以在“账户1支出”列的G3单元格输入公式“=SUMIF(支出记账!S[KG-*5/7]\ES[KG-*5/7]\3:S[KG-*5/7]\ES[KG-*5/7]\73,"张迎新",支出记账!S[KG-*5/7]\FS[KG-*5/7]\3:S[KG-*5/7]\FS[KG-*5/7]\13)”。
公式中的“支出记账!S[KG-*5/7]\ES[KG-*5/7]\3:S[KG-*5/7]\ES[KG-*5/7]\73”是判断使用单元格区域的条件，它存放着“支出记账”工作表中的账户记录；“"张迎新"”则是判断哪些单元格将参与合计的条件，凡是在“支出记账!S[KG-*5/7]\ES[KG-*5/7]\3:S[KG-*5/7]\ES[KG-*5/7]\73”区域中满足上述条件，且与“支出记账!S[KG-*5/7]\FS[KG-*5/7]\3:S[KG-*5/7]\FS[KG-*5/7]\13”区域的单元格在一行的数据将被合计。
如果仅仅需要计算2004年10月的支出，那么“支出记账!S[KG-*5/7]\FS[KG-*5/7]\3:S[KG-*5/7]\FS[KG-*5/7]\13”区域只能包括2004年10月的支出纪录，它必需像前面说明的那样根据记录数量确定。
如果需要计算“账户1”其他月份的支出，只需将G3单元格中的公式复制到G4、G5等单元格，然后修改公式中的“支出记账!S[KG-*5/7]\FS[KG-*5/7]\3:S[KG-*5/7]\FS[KG-*5/7]\13”，使之包括该月的支出记录即可。
按照账户支出计算公式的设计思路，我们很容易得到账户“张迎新”的总收入计算公式“=SUMIF(收入记账!S[KG-*5/7]\ES[KG-*5/7]\3:S[KG-*5/7]\ES[KG-*5/7]\73,"张迎新",收入记账!S[KG-*5/7]\FS[KG-*5/7]\3:S[KG-*5/7]\FS[KG-*5/7]\13)”。其中除了所引用的账目工作表不同以外，其他内容与上面介绍的账户支出计算公式完全相同。
3.家庭收支情况分析
为了更好地指导家庭理财，我们有必要使用数据和图表来分析家庭支出和收入的构成。
(1)支出分析工作表
“支出分析”工作表的结构如图4所示，按惯例，仍将B1:Q1区域合并输入标题“家庭支出分析”。B2、C2、D2等单元格要逐个输入“支出类别”下拉列表中的项目，即“食品”、“房租”、“水电费”等等，每一个项目要单独占用一个单元格，其顺序也要与下拉列表中的项目顺序保持一致。
图4
假如，我们需要计算2004年10月份的“房租”支出，可以在“房租”列的C3 单元格输入公式“=SUMIF(支出记账!D3:D73,"房租",支出记账!F3:F13)”。该公式的结构与账户支出计算公式基本相同，这里不作解释。需要注意的仍然是公式中的引用，如果需要计算2004年10月的“房租”支出，那么引用“支出记账!S[KG-*5/7]\FS[KG-*5/7]\3:S[KG-*5/7]\FS[KG-*5/7]\13”只能包括2004年10月支出记录中的区域。
仿照“房租”支出的计算公式，很容易设计出“煤气费”等支出的计算公式，例如，将公式中的“房租”修改为“煤气费”即可得到“煤气费”等的支出计算公式。当某段时间的各项支出全部计算出来以后，就可以在工作表中插入图表分析支出构成了，其操作方法非常简单，这里不作介绍。
(2)收入分析工作表
如图5所示，“收入分析”工作表的结构和“支出分析”基本相同，其中的B1:I1区域合并以后输入标题“家庭收入分析”。B2、C2、D2等单元格要逐个输入“收入类别”下拉列表中的项目，即“工资”、“奖金”、“津贴”等等，其输入原则和“支出分析”工作表是一致的。
图5
这里的重点仍然是计算某月的某项收入合计。假如，我们需要计算2004年10月份的“工资”收入，可以在“工资”列下的B3 单元格输入公式“=SUMIF(收入记账!D3:D73,"工资",收入记账!F3:F13)”。我们只需将公式中的“工资”修改为“奖金”、“津贴”等，就可以在C3、D3、E3等单元格中获得计算结果。
当某段时间(它由公式中的引用“收入记账!F3:F13”决定)的各项收入全部计算出来以后，就可以在工作表中插入图表分析收入构成了。
二、规划与计算家庭贷款
当前，贷款已成为个人消费的重要融资渠道。为了方便家庭评估还本付息成本，下面对常用的三种还贷方式进行介绍。
1.等额本息还款法
“等额本息还款法”是从使用贷款的次月起，每月以相等额度平均偿还本金和利息的方法。由于这种贷款的还本付息金额固定，比较适合收入稳固的工薪阶层。
(1)等额本息还款计算
为了便于读者进行比较，我们将等额本息还款的表格和计算公式放在图6工作表中，其中“贷款金额”、“贷款利率”和“还贷期数”作为“列标题”。由于等额本息还款的金额是固定的，所以我们只要选中E5单元格，在其中输入公式“=PMT(F3,G3,E3)”，回车即可计算出每个还款期应该归还的本金总额。
图6
假如，10年期20万元贷款，月利率0.42%按月偿还，还贷期数是120个月。我们只须在图6“等额本息还款计算”的“贷款金额”下输入“200000”，然后在“贷款利率”下输入0.42(F3是百分比单元格)，最后在“还贷期数”下输入120，回车即可获得结果“-2125.22”。
(2)等额本息还款成本
由上可知，这种贷款每期(月)的还款金额为“2125.22”。即该贷款总共需要支付的利息为2125.22×120-200000=55026.4元。
2.等额本金还款法
“等额本金还款法”是借款人将贷款分摊到还款期内按季(或月)归还，同时付清上一交易日至本次还款期之间的贷款利息。这种方式的每次偿还额度逐渐减少，适合于有一定银行存款，但预期收入逐渐减少的贷款人。
(1)等额本金还款计算
为方便起见，我们还是按图6中输入的“列标题”，以便适应贷款金额和利率变化的情况。其中B3单元格应采用百分比格式，C3单元格用于输入归还贷款的期数(次数)，两者必需严格对应。假如，10年期的20万元贷款，年利率为5.04%按月偿还。则贷款的月利率就是5.04%÷12=0.42%，“还贷期数”就是12×10=120。
为了更加快捷地输入“贷款年利率”和“还贷期数”，我们可以采用前文介绍的制作下拉列表的方法，具体操作请参看前文相关介绍。
由于等额本金还款法的每期偿还金额不同，我们还需要得到各期偿还贷款金额的列表。为此，可以在A4单元格输入“还贷期数”，然后在A5、A6等单元格中依次输入还贷期数编号1、2、3……120(按月偿还贷款时)，最后在B4单元格中输入“每期本金”。完成后选中B5单元格在编辑栏输入公式“=IF(A5=1,(S[KG-*5/7]\AS[KG-*5/7]\2/S[KG-*5/7]\CS[KG-*5/7]\2+S[KG-*5/7]\AS[KG-*5/7]\2*S[KG-*5/7]\BS[KG-*5/7]\2),(S[KG-*5/7]\AS[KG-*5/7]\2/S[KG-*5/7]\CS[KG-*5/7]\2+(S[KG-*5/7]\AS[KG-*5/7]\2-(S[KG-*5/7]\AS[KG-*5/7]\2/S[KG-*5/7]\CS[KG-*5/7]\2)*A5)*S[KG-*5/7]\BS[KG-*5/7]\4))”了。
公式中的“A5=1”判断要计算的是否是首期贷款，如果是，就执行“(S[KG-*5/7]\AS[KG-*5/7]\2/S[KG-*5/7]\CS[KG-*5/7]\2+S[KG-*5/7]\AS[KG-*5/7]\2*S[KG-*5/7]\BS[KG-*5/7]\2)”，否则执行“(S[KG-*5/7]\AS[KG-*5/7]\2/S[KG-*5/7]\CS[KG-*5/7]\2+(S[KG-*5/7]\AS[KG-*5/7]\2-(S[KG-*5/7]\AS[KG-*5/7]\2/S[KG-*5/7]\CS[KG-*5/7]\2)*A5)*S[KG-*5/7]\BS[KG-*5/7]\4))”。
其中，“S[KG-*5/7]\AS[KG-*5/7]\2/S[KG-*5/7]\CS[KG-*5/7]\2”是每个还贷期应该归还的本金，如果20万元贷款10年按季度偿还，那么每个季度就要归还本金5000元。“S[KG-*5/7]\AS[KG-*5/7]\2*S[KG-*5/7]\BS[KG-*5/7]\2”是第一个还贷期的应付利息，两者之和就是第一个还贷期要偿还的本息。
按照等额本金还款法的规定，在接下来的第二、第三……还款期内，客户除了要偿还贷款本金“S[KG-*5/7]\AS[KG-*5/7]\2/S[KG-*5/7]\CS[KG-*5/7]\2”以外，还要偿还剩余部分贷款的利息。
公式中的“S[KG-*5/7]\AS[KG-*5/7]\2/S[KG-*5/7]\CS[KG-*5/7]\2+(S[KG-*5/7]\AS[KG-*5/7]\2-(S[KG-*5/7]\AS[KG-*5/7]\2/S[KG-*5/7]\CS[KG-*5/7]\2)*A5)*S[KG-*5/7]\BS[KG-*5/7]\2”就是用来计算这期间的本息合计。其中，“S[KG-*5/7]\AS[KG-*5/7]\2/S[KG-*5/7]\CS[KG-*5/7]\2”仍然是每个还贷期要归还的本金，“(S[KG-*5/7]\AS[KG-*5/7]\2/S[KG-*5/7]\CS[KG-*5/7]\2)*A5”计算出当前归还的本金总额，“(S[KG-*5/7]\AS[KG-*5/7]\2-(S[KG-*5/7]\AS[KG-*5/7]\2/S[KG-*5/7]\CS[KG-*5/7]\2)*A5)”就是尚未归还的本金总额。当公式复制到B6单元格以后，公式中的A5变成A6(即2)。于是“(S[KG-*5/7]\AS[KG-*5/7]\2/S[KG-*5/7]\CS[KG-*5/7]\2)*A6)”计算出第2个还款期已经归还的本金，“(S[KG-*5/7]\AS[KG-*5/7]\2-(S[KG-*5/7]\AS[KG-*5/7]\2/S[KG-*5/7]\CS[KG-*5/7]\2)*A6”就是尚未归还的贷款本金，乘以“S[KG-*5/7]\BS[KG-*5/7]\2”(其中存放着每个还款期的利率)得到第3个还款期内应归还的贷款利息。最后加上“S[KG-*5/7]\AS[KG-*5/7]\2/S[KG-*5/7]\CS[KG-*5/7]\2”，就可以得到第3个还款期内应当归还的本息总额。以此类推。
如果你想知道其余还款期内应归还的本息总额，只需选中B5单元格。光标指向选中标记右下角的拖动柄，光标变成小黑十字以后向下拖动至最末一个单元格(其“还款期数”120表示第120个贷款期)，即可在B5至B124各个单元格内得到各还款期内的还贷本金数额。
提示：使用上述公式时，贷款期和利率必需严格对应。例如，贷款按月归还，则表格中的“贷款利率”必需是月利率。
(2)等额本金还款成本
当我们计算出每期的还贷金额以后，只须选中B125单元格，点击“自动求和”按钮，就可以计算出贷款需要支付的本息总额为250820.00元，即整个还贷期需要支付50820.00元的利息。由此可知，在贷款期限、金额和利率相同的情况下，等额本金还款法所支付的利息要比等额本息还款法少55026.4-50820.0=4206.4元。
3.一次还本付息还款法
顾名思义，它是借贷人在贷款期内不偿还本息，而是到期后一次归还本金和利息的方法。对于收入有限的家庭和个人来说，一次还本付息的压力很大。所以个人购房、买车等长期信贷极少采用这种方式，一般用于1年期(含以下)个人住房贷款等短期信贷。
(1)一次还本付息计算
一次还本付息计算使用的表格和等额本息还款计算相同，它的“每期还款金额”计算公式是“=I3+I3*J3”。其中，I3是“贷款金额”，J3是“贷款利率”，我们只须在图6中的“每期还款金额”下的单元格中输入“=I3+I3*J3”。最后在单元格中输入数据，就可以获得贷款一次还本付息的总金额。
(2)一次还本付息成本
一次还本付息法的还贷金额计算比较简单，其公式为“本息金额=本金+本金×贷款利率”。由于这种还贷方式限于小额短期贷款，个人住房、汽车等大额贷款一般不会涉及，有关它的成本分析这里不再赘述。
提示：实际生活中的贷款很多，其利率也不相同，并不是某种贷款的还贷成本一定低。读者可以根据自己需要的贷款金额和期限，使用图6中的工具进行分析，从而找到还贷成本最低的贷款品种。
三、计算家庭投资收益
随着市场经济的发展，各种理财工具和金融产品不断涌现，居民的投资理财意识不断增强。提高家庭投资的效益，避免金融投资风险，掌握一定的家庭投资分析知识是非常必要的。
1.零存整取的管理和收益计算
“零存整取”是普通居民常用的储蓄品种，适合每个月有稳定结余资金的家庭。对它的管理一是记载有关储蓄资金的基本信息，例如，账号、起存日期、金额和存单密码等，二是计算储蓄到期后的本息金额、利息税和实得利息等。
首先按照图7所示的结构建立工作表，其中列标题的名称和数量可以根据需要修改。
图7
为了保证存储的存单账号完整正确，有关储蓄时间的计算不发生错误，表中的“账号”列需要设置为“文本”格式，“起存日期”和“支取日期”列要设置为日期格式。其余各列的格式同样需要根据存储的数据类型合理设置。“户名”和“存款银行”下面的单元格最好设置为“条件格式”，便于用户从下拉列表中选择需要的数据。
由于零存整取储蓄有1、3、5年存期的3个利率档次，为了减轻用户记忆“零存整取”储蓄利率的负担，可以设计根据“起存日期”和“支取日期”自动确定利率的公式。即选中“年利率”下方的单元格，在其中输入公式“=IF((YEAR(G3)-YEAR(D3))>=5,0.0198,IF((YEAR(G3)-YEAR(D3))>=3,0.0189,IF((YEAR(G3)-YEAR(D3))>=1,0.0171,"")))”。式中的“YEAR(G3)-YEAR(D3)”计算出“支取日期”和“起存日期”之间相隔的年份，若第一个逻辑判断“(YEAR(G3)-YEAR(D3))>=5”成立，说明它是5年期的“零存整取”储蓄，其年利率为0.0198 (1.98%)。如果这个逻辑判断不成立，则可能是1年期或3年期的“零存整取”储蓄，公式继续执行下一个逻辑判断。如果“(YEAR(G3)-YEAR(D3))>=3”和“(YEAR(G3)-YEAR(D3))>=1”都不能满足，说明它不是“零存整取”储蓄，公式会在当前单元格中填入一个空格。
提示：如果银行调整了零存整取储蓄的利率，我们只需修改公式中的利率值0.0198、0.0189和0.0171即可。
接下来需要设计零存整取储蓄的到期本息金额，这里，我们可以直接利用Excel提供的FV函数。在“税前本息合计”单元格下方输入公式“=FV(F3/12,(YEAR(G3)-YEAR(D3))*12,,-E3)”，完成后回车就可以在当前单元格中看到结果。由于“零存整取”储蓄以月为单位进行投资，所以该公式必需把F3单元格中“年利率”换算为“月利率”(F3/12)。“(YEAR(G3)-YEAR(D3))*12”用来计算零存整取的次数，“(YEAR(G3)-YEAR(D3)”得到“支取日期”和“起存日期”之间相隔的年份，乘以12得到月份(即按月储蓄的零存整取次数)。E3是每次投资(储蓄)的金额，为了避免计算结果出现负值，所以在其前面加了一个负号。
提示：通常，零存整取储蓄是月末办理的，月初办理时可以将公式修改为“=FV(F3/12,(YEAR(G3)-YEAR(D3))*12,,-E3,1)”。
由于储蓄所的利息要按20%的比例纳税，所以上述公式得到的是税前本息总金额。我们用公式“=H3-(E3*(YEAR(G3)-YEAR(D3))*12)”就可以得到“税前总利息”，它再乘以0.2(20%)得到应税利息，乘以0.8(80%)得到税后利息。
2.整存整取的管理和收益计算
“整存整取”储蓄是拥有较多资金，同时又是想规避金融风险的家庭的选择，管理它的目的与零存整取储蓄完全相同。执行管理任务的工作表结构如图8所示。
图8
整存整取储蓄有对应不同存期的6种利率，为了使用方便，我们仍然采用根据“起存日期”和“支取日期”自动计算利率的方法。即选中“年利率”下方的单元格，在其中输入公式“=IF((YEAR(G11)-YEAR(D11))>=5,0.0279,IF((YEAR(G11)-YEAR(D11))>=3,0.0252,IF((YEAR(G11)-YEAR(D11))>=2,0.0225,IF((YEAR(G11)-YEAR(D11))>=1,0.0198,IF(((MONTH(G11)-MONTH(D11))/12)>=0.5,0.0189,IF(((MONTH(G11)-MONTH(D11))/12)>=0.25,0.171,""))))))”。
由于整存整取储蓄可以分为1、2、3和5年，以及3个月、6个月两大类型，所以上面的公式也必需考虑这两种情况。如果“支取日期”和“起存日期”之间相隔的年份大于等于1，也就是逻辑判断“(YEAR(G3)-YEAR(D3))>=5”直到“(YEAR(G3)-YEAR(D3))>=1”能够成立，那么IF函数将在公式所在单元格中填入对应存期的利率值，也就是0.0279、0.0252、0.0225和0.0198。如果上面4个逻辑关系不能成立，IF函数将执行后面两个逻辑判断，即“((MONTH(G11)-MONTH(D11))/12)>=0.5”和“((MONTH(G11)-MONTH(D11))/12)>=0.25”，然后根据判断条件在单元格中填入合适的利率值。
上面两个逻辑判断关系式中，“MONTH(G11)-MONTH(D11)”计算出“支取日期”和“起存日期”之间相隔的月份，“(MONTH(G11)-MONTH(D11))/12”就可以得到对应的小数年份。如果公式所在行的其他单元格没有数据，公式将自动在当前单元格中填入一个空格。
提示：公式中的利率也可以存放在单元格中，此时只要将公式中的数值修改成单元格引用即可。
相对零存整取储蓄的收益计算而言，整存整取储蓄则需要考虑1年及以上和3个月、6个月两种情况。据此我们在“税前本息合计”列中输入公式“=IF((YEAR(G11)-YEAR(D11))>=1,E11+E11*(YEAR(G11)-YEAR(D11))*F11,IF(((MONTH(G11)-MONTH(D11))/12)>=0.5,E11+E11*F11*0.5,E11+E11*F11*0.25))”。其中，“(YEAR(G11)-YEAR(D11))>=1”判断存期是否达到1年及以上，如果是，就执行“E11+E11*(YEAR(G11)-YEAR(D11))*F11”，从而计算出存期内的税前本息合计。如果“(YEAR(G11)-YEAR(D11))>=1”不成立，则公式执行第二个IF函数。如果其中的“(MONTH(G11)-MONTH(D11))/12)>=0.5”成立，则执行6个月的本息合计公式“E11+E11*F11*0.5”，否则执行3个月的本息合计公式“E11+E11*F11*0.25”，直到获得计算结果。
由于整存整取储蓄的利息同样需要交纳利息税，其计算办法和零存整取储蓄相同，请读者参看零存整取储蓄中的介绍。
3.国债的管理和收益计算
“凭证式国债”是普通家庭投资的重要方式，它除了无须缴纳利息税以外，与整存整取储蓄非常相似。下面，我们以2004年凭证式国债的管理和收益计算为例，介绍Excel管理国债和计算收益的方法。
“家庭国债资金管理”工作表的结构如图9所示，其中的设计重点仍然是“年利率”和“本息合计”两个公式。由于2004年凭证式国债有3年期和5年期两个品种，因此利息计算较整存整取、零存整取储蓄简单得多，其“年利率”计算公式为“=IF((YEAR(G18)-YEAR(D18))>=5,0.03，0.0252)”。其中的“(YEAR(G18)-YEAR(D18))>=5”判断国债的期限，假如，兑付日期与购买日期的差大于等于5年，那么利率为0.03(3%)，否则利率为0.0252(2.52%)。
图9
提示：如果国债的利率发生了变化，则修改“=IF((YEAR(G18)-YEAR(D18))>=5,0.03,0.0252)”中的0.03(5年期利率)、0.0252(3年期利率)即可。
由于国债到期以后并不支付利息，设计“本息合计”公式必需考虑这一点，我们可以使用IF函数解决这个问题，其公式为“=IF((YEAR(G18)-YEAR(D18))>=5,E18+E18*F18*5,E18+E18*F18*3)”。其中，“(YEAR(G18)-YEAR(D18))>=5”判断所购买的国债期限，若这个关系成立，则执行“E18+E18*F18*5”。E18存放着购买国债的资金数额，F18中存放着利率，5则是当前要计算的国债期限。如果“(YEAR(G18)-YEAR(D18))>=5”不成立，则执行“E18+E18*F18*3”，从而获得3年期国债的本息总额。
由于国债不需要交纳利息税，其收益的计算很简单。我们只要用以上公式算出的本息总额减去本金，就可以得到国债的利息收益了。
4.基金的收益计算
作为介于股票和存款之间的一种投资方式，“基金”逐渐受到了广大中小投资者的注意。但是很多人对基金买卖的计算方法并不熟悉，往往不会把握买卖时机致使投资收益降低。下面介绍如何利用Excel计算基金收益，为投资基金奠定必要的基础。
为了计算和管理方便，仍然在Excel中建立如图10所示的工作表。
图10
提示：为了便于观察，工作表中有关金额的单元格应该设置为“货币”格式，同时与“费率”有关的单元格应该设置为“百分比”格式，其他单元格设置为“常规”格式即可。
为了帮助读者理解其中列标题的意义，这里做一些简单介绍。
所谓“申购金额”就是投资者购买基金时投入的资金，一般以人民币为结算单位；“申购费率”与“股票交易印花税”类似，但是不同的基金有不同的费率，一般从1%到3%不等，它对投资成本有一定影响；由于基金的性质很像股票，它的买入以“基金单位”(相当于“股”)为标准；“申购单位净值”则是基金购买时每单位的价格；“赎回费率”是卖出基金时需要支付的资金，与“申购费率”的性质相同，但是不同基金的“赎回费率”不同，往往“赎回费率”要低于“申购费率”；“赎回单位净值”相当于股票的卖出价，单位是“元/每基金单位”。上述几个数据是计算基金收益必需知道的，图10中的其他项目都是为了分析而设立的，读者很容易看懂它们的意义。
如果工作表所管理的基金品种比较多，为了防止记错基金的“申购费率”和“赎回费率”，我们可以使用公式根据基金名称自动计算出费率，例如，C3单元格中“申购费率”的计算公式是“=IF(A3="长盛平衡",0.015,IF(A3="创业高科",0.02,IF(A3="华安创新",0.03,IF(A3="招商债券",0.015,IF(A3="嘉实增长",0.015,IF(A5="","",""))))))”。据此，很容易写出E3等单元格中的“赎回费率”计算公式。
G3单元格中的“申购份额”计算公式是“=INT((B3-B3*C3)/D3)”，其中，“B3*C3”得到基金的申购费用，“(B3-B3*C3)”得到实际用于基金购买的金额，“(B3-B3*C3)/D3)”得到实际购买的基金单位数量。由于基金单位不能是小数，所以使用“INT”函数将计算结果向下取整为最接近的整数。因此，计算“申购费用”的公式就是“=B3*C3”。计算“赎回费用”时必需知道基金的“赎回单位净值”(就是基金的当前卖出价)，在表中“F3*G3”就是基金的当前价值，用公式“=F3*G3*E3”就可以计算出基金的赎回费用。显然，公式“=F3*G3- F3*G3*E3”就可以计算出卖出基金所收回的资金额。它与“申购金额”的差就是投资的盈亏金额，其公式为“=K3-B3”。最后用公式“=H3/B3”就可以得到基金买卖的“收益率”。
由于赎回基金同样需要支付交易成本，所以我们必需了解最低的“保本赎回价”是多少。我们可以在M3单元格中输入公式“=(I3+J3+D3*G3)/G3”，其中I3和J3单元格分别存放“申购费用”和“赎回费用”的金额，“D3*G3”得到申购时的资产净值，以上三个数值的合计就是赎回基金的保本总成本，除以“申购份额”就得到了每基金单位的“保本赎回价”。
提示：图10工作表需要保存为模板时，其中的所有公式需要复制到空白单元格，此时由于G3中没有数据，公式“=(I3+J3+D3*G3)/G3”会出现“#DIV/0”错误。此时只要将公式修改为“=IF(G3<>"",(I3+J3+D3*G3)/G3,"")”即可，诸如“=H3/B3”之类的公式都可以这样处理。
四、计算股票投资收益
股票是家庭中另外一种重要的投资方式，虽然有很多股票投资分析软件，但多为商业软件，我们完全可以利用Excel打造自己的“股票投资分析软件”。
1.家庭股票收益计算
为了规避风险，股民往往同时持有几只股票。由于股票收益的计算方法比较复杂，很难像银行存款利息那样处理。如果利用Excel进行股票收益管理，不仅可以做到盈亏一目了然，还可以确定每支股票的保本卖出价，为股票交易提供科学的依据。另外，利用Excel管理股票除了计算收益以外，还可以作为炒股的历史记录，从而为股票投资积累宝贵的资料。
(1)建立交易工作表
打开一个空白的Excel工作表，按图11样式在第1行各单元格输入列标记(字段)，从第2行开始依次输入股票的各项数据，例如，股票名称、买入价格和交易额等。
图11
以后，每买卖一次股票，就要按图11的数据格式顺序增加一条记录。由于家庭买卖股票的品种不会很多，也为了输入数据方便，其中的股票名称、佣金率和印花税率可以建立“有效性条件”，直接从下拉列表中选择需要的数据。
(2)股票买入计算公式
当图11的交易工作表建立好以后，就可以建立相关的计算公式了。由于买入股票不仅需要支付股票价款，还需要支付股票交易印花税和交易佣金(手续费)。所以买入股票支付金额的计算公式是“买入金额=买入价×买入数量+股票交易印花税+交易佣金+5”，其中，“股票交易印花税”的计算公式是“股票交易印花税=买入价×买入数量×印花税率”，“交易佣金”的计算公式是“交易佣金=买入价×买入数量×印花税率”，式中的“5”则是买入股票需要支付的手续费。
为了适应股票交易印花税和交易佣金的变化，可以在C2、D2单元格分别输入“佣金率”和“印花税率”字样，然后在其下方的C3、D3单元格输入当前佣金率0.003(即3‰)和印花税率0.002(即2‰)，也可以建立“有效性条件”从下拉列表中选择。
再次选中G3单元格，输入公式“=E3*B3*C3+E3*B3*D3+5”，其中，“E3*B3*C3”计算本次交易需要支付的佣金额，“E3*B3*D3”计算本次交易支付的印花税，最后加上的“5”则是交易手续费。接着在J3单元格输入公式“=E3*B3+G3”，就可以知道买入股票的总金额了。
公式设计完成后将其复制到G4、G5等单元格，只要在该行内输入股票的买入价格和数量，就可以计算出本次交易需要支付的买入成本。
(3)股票卖出计算
股票卖出支付的成本与买入相似，即在H3单元格输入公式“=F3*B3*C3+F3*B3*D3+5”即可。同理，卖出股票同样需要支付印花税、交易佣金和手续费，这三部分的价款需要从卖出金额中扣除。所以卖出股票的实际获得的资金是“卖出金额=卖出价×卖出数量-卖出成本”。我们只须选中K3单元格，按上面介绍的方法输入公式“=F3*B3-H3”，就可以得到本次股票卖出获得的实际收入金额。
(4)股票盈亏计算
股票交易盈亏的计算公式比较简单，用卖出金额减去买入金额就可以获得盈亏结果。即选中图11中的L3单元格，在其中输入公式“=K3-J3”，回车即可获得本次交易的盈亏结果。
为了看清楚交易的盈亏，可以选中L2 、L3等单元格，点击“格式→单元格”菜单命令，在出现的对话框中点击“数字”选项卡。首先选中“分类”下的“数值”选项，然后选中“负数”列表框中的负数格式。点击“确定”按钮，以后只要计算出的交易结果是亏的，其结果都会用红色数字(或其他方式)显示。
(5)收益率计算
所谓“收益率”就是股票的卖出收入与买入投资的百分比，我们在图11的M3单元格输入“=L3/J3”就可以获得结果。
2.计算参考保本价
由于股票买和卖都要支付印花税、交易佣金和过户费，并不是卖出价高于买入价就可以获利。股民有必要知道支付交易成本之后，买卖收支相抵时的卖出底价是多少。下面介绍Excel解决这一问题的两种方法，一是单变量求解工具，二是直接利用公式计算。
(1)设定求解目标
就图11所示的股票交易工作表而言，我们的目标是确定支付了交易成本之后，“卖出总金额”与买卖成本相等时的“卖出价”应该是多少。对其中名为AAAA的股票来说，K3是待求解的目标单元格。首先应选中K3单元格，点击“工具→单变量求解”菜单命令，打开“单变量求解”对话框。
根据上面确定的求解目标，“卖出金额”与买卖成本至少要相等，所以“目标值”至少是“60622”(=买入价格×交易额+买入成本+卖出成本)。如果要实现交易盈利，则“目标值”应该大于“60622”，盈利越多“目标值”就应该越大。
点击“可变单元格”框，然后选中F3单元格(其中存放着卖出价格)，使“S[KG-*5/7]\FS[KG-*5/7]\3”进入框中。它就是我们要寻找的“卖出金额”与“买入金额”相等时的卖出底价。
(2)显示求解结果
在单变量求解的各项参数确定以后，点击“确定”按钮会出现相应的对话框，说明单变量求解已经找到了符合约束条件的结果。再观察F3单元格，可以看到其中的数值已经变为“12.19”。也就是支付了股票交易成本之后，该只股票盈亏相抵的卖出底价应为“12.19”，低于这个价格卖出股票肯定要亏本。
由于我们不需要保存单变量求解的结果，在看到F3单元格中的求解结果以后，只要点击对话框中的“取消”按钮，就可以关闭对话框并将F3中的数据恢复原状。
当然，利用单变量求解的方法不仅可以知道股票的保本售出价格，更重要的是它可以计算股票达到一定盈利水平时的售价。假如我们想知道售出股票盈利500元时的价格，可以在“单变量求解”对话框的“目标值”框内填入“61022”，重复上面的操作就可以得到满足此条件的出售价格是12.26元。
(3)股票保本价计算公式
如果你感觉上面这种方法过于复杂，还可以使用公式进行计算。其思路也很简单，即“参考保本价=买入价格+(买入成本+卖出成本)/股票交易数量”，操作时在I3单元格输入公式“=E3+(G3+H3)/B3”即可。