论Excel环境下普通年金现值系数表的编制原理与流程

论Excel环境下普通年金现值系数 表 关于同志近三年现实表现材料材料类招标技术评分表图表与交易pdf视力表打印pdf用图表说话 pdf 的编制原理与流程 论Excel环境下普通年金现值系数表的编制 原理与流程 EL!!! 论Excel环境下 普通年金现值系数表的编制原理与流程 ◎文/张敦力刘叶飚 >PREFACE/前言 Excel软件具有自动计算和自动填充的功能,合理运用Excel的混合引用和模拟运算表功能可以高 效编制符合特定要求的普通年金现值系数表.本文将普通年金现值系数的计算原理和Excel软件的 混合引用模拟运算表,拖曳和自动填充等功能有机结合在一起,以编制期数为1,5折现率为1%,5% (公差为O.5%)的普通年金现值系数表为例,详细说明Excel软件中编制年金现值系数表的基本原理和 详细流程. 作者简介 张敦力中南财经政法大学会计学院教授,博士生导lJ币,副院长. 刘叶飚,副教授江西赣江职业技术学院经济管理学院院长. — 财务管理中,对于每期末等额 I'_l一收付的普通年金,手工计算其 现值是件繁琐的工作,鉴于此,财务管理 教材中的惯用做法是:在附录部分列示普 通年金现值系数表(下文简称"系数表", 以供查阅特定期限和折现率条件下的普 通年金现值系数(PVIFA).于是,常规 课堂教学中,老师在集中精力讲解公式推 导过程后,重点教授学生查阅系数表的方 法,在相关考试中也列出可能涉及的系数, 以减少学生自行计算特定普通年金现值 系数的工作量.其结果是,有意无意地强 化了学生依赖系数表查阅普通年金现值 系数的意识,没有系数表就不会计算普通 年金现值的学生不在少数,以致有些学生 参加工作后难以有效解决实务问题,并将 理由归结为"学校所学知识无用"或"理 论脱离实践".殊不知,系数表怎能穷尽 所有期数和特定折现率条件下的普通年 金现值系数?如很少有系数表列示期数为 3,折现率为3.5%的普通年金现值系数. 由此可见,十分有必要编制符合特定要求 的普通年金现值系数表,进一步提高学 生计算系数的实践能力,Excel软件为此 类问题提供了快捷的解决 方案 气瓶 现场处置方案 .pdf气瓶 现场处置方案 .doc见习基地管理方案.doc关于群访事件的化解方案建筑工地扬尘治理专项方案下载 . 为简化起见,现以期数(n)为1,5, 折现率(i)为1%,5%的普通年金现值 系数表(见表1)为例,说明Excel软件 中编制年金现值系数表的基本原理和详 细流程. 一 设置系数表的基本框架 为行文方便,系数表的首列统一列 示期数(如表1,表6中单元格区域A3: A7所示的1,5),首行统一列示折现率 (如表1,表6中单元格区域B2:J2所示 的1%,5%). 表中首列,首行以外单元格中的数 值分别是由该单元格所在行的首列中列 示的期限和该单元格所在列的首行中列 示的折现率所决定的普通年金现值系数, 如表1或表2单元格G5中的2.8016就是 根据单元格G5所在行的首列单元格A5 中的期数(3)和该单元格所在列的首行 单元格G2中的折现率(3.5%)计算得出 的普通年金现值系数. 表2中'由于期数是从1到5的自然数, 折现率是从1%,5%的等差数列(公差 为0.5%),故可通过Excel的自动填充功 能快速填充这些数据.在编制期数和折 现率较多的系数表时,应合理运用自动填 充功能来快速,准确地输人数据. =自动生成普通年金现值系数 Excel软件不仅具备自动填充功能, 而且具有自动计算功能,如果在表1单 元格区域B3:J7的任一单元格中输入计 算普通年金现值系数的表达式(或内置 工作簿函数),均可立即得出相应的系数, 如为了计算期数为3,折现率为3.5%的 普通年金现值系数,应在单元格G5中 输入表达式"一(1(1+3.5%)(3)) /5%"(输入时,只要输入双引号中的内容, 不能输入双引号,下同)或内置工作簿函 数及其参数"一pv(3.5%,3,1,0,0)", 确认后,单元格G3中将显示"2.8016" 或"$2.80"之类的数值.同理,在其他单 元格中填写相应的表达式(或内置工作簿 函数),即可生成相应期数和折现率条件 下的普通年金现值系数表. 在期数和折现率较多时,采用这种方 法在各单元格中逐个填写相应的表达式 (或内置工作簿函数),不仅工作量大,而且 容易填错期数或折现率.考虑到系数与期 数,折现率之间的函数关系,如果适当运用 Excel软件的混合引用或者模拟运算表/ 数据表功能,不仅能够简化繁琐的编表过 201003财会学习l73 表1:普通年金现值系数表 _'mi,g一0 'u ---',::"1.1 ";#…一^?……c,?…娶.……… ?"::, ^,( PVilea 0j?s一…一—?: 表3:混合引用法下计算特定普通年金系数 一一 ^? 嘛:t' i;?一-_=I_?…, P??.E?【. ^j PVI^ 1帆I5'k2帏2 圊 .一. 3 { 5 n,. :,,, ,……一…'…? :lS'{【^45? j28016{- ; ' ,一?_ 表5:混合引用法下按行自动填充生成特定普通年金系数 _一'?t竹晡啼 '?】'^ ^.一-?:I._t :-',_,^t-…', ,^IIP 0^.'^',t】'…. r''.'^""}), ?群".E l, !:.:.!:!:;!:!.!暨 0990Io.9嘴}饥钟?0.97辅O.97090蛳B2096l50.9渤0蛄24 鲁每!…_…‰蠹 程,而且有利于避免出现操作性失误. (一)混合引用法 由于期数和折现率已分别列示在表 的首列和首行(如表1,表6),表中首列, 首列以外各单元格中的数值分别是根据 该单元格所在行行首对应的期限和所在 列列首对应的折现率所确定的普通年金 现值系数,因此,可在相关单元格中引用 该单元格所在行行首对应的期限和所在 列列首对应的折现率,而不直接输入期限 和折现率的具体数值,如在单元格G5中 输入基本计算公式"一(1(1+G2) (一AS))/G2"或内置工作簿函数"=pv (G2,A5,一1,0,0)"),其结果与在G5中 输入的表达式(":(1,(1+3.5%)(一3)) /5%'')或内置工作簿函数及其参数("=pv 74财会学习月 表2:普通年金现值系数表的基本框架 ?.?…'.^}. 一^ PvlF^ }.t15?2of,25?3 ‰: 表4:混合引用法下按列自 .=.妻'.盘…^……'一删' 动填充生成特定普通年金系数 .. 乏蠹:._……一 , I【I』Ij PViF^ -:L"_3i5ftI_;;f一,一7i .o. i'.llf1:l!婪竺l-lI.j . ' . 一 i音戳一 表6:运用模拟运算表功能编制的普通年金系数 一_……t .0.:.『_, ………………t,… ' ??'{,…^ ??^….,l'^:? ^'LLP..0? ? '?r'jg一: 毒品"…一, …} 28016l"II.靠【201,}2.3.o'3.8'I4.p?4.魄 I仉99O19852008o.10."56l乱91000.9B62j096I^仉?粒唾 '一一一…一…,一一.. 2I.~r/04:1.Qs?jI.9'16lt9274}1.gl靳I.目mII.?lI.辨新I.霉鞠? 32.9410-,.9122'2.8B3口}28:282B6?—&T.锄,=j融}'+?4'一^.. t置9吩O85443.?r了i3.?3置O符1;3.'翱?^氛}静O 5?e63lt.78?4.7135l?."{?5797?.8181{?.4518?.mII (3.5%,3,,1,0,0)")等效.采用这种相 对引用的方法计算系数尽管在一定程度 上减少操作性失误,但无法通过拖曳来实 现自动填充功能,仍然需要逐格填写相应 的表达式或内置工作簿函数及其参数. 进一步分析发现,对于首列以外的 任意一列,该列与每一行相交所形成单 元格中的数值,分别是根据该单元格所 在行行首对应的期数(随该列各单元格 所在行的变化而变化)和该列首行中列 明的折现率(不随该列各单元格所在行 的变化而变化)计算得出的普通年金现 值系数.现以B列为例,单元格区域B3: B7中的数值是在折现率保持不变(即单 元格B2中的1.0%),期数分别为l,5(如 单元格区域A3:A7所示)条件下的普 通年金系数.因此,最简便的方法是:在 单元格B3中输入表达式"=(1一(1+ B$2)(,$A3))/B$2"或内置工作簿函数 及其参数("=pv(B$2,一$A3,,1,0,O)"), 确认后,将光标移至单元格B3的右下方 (如表3所示),待出现加号"+"后,双击 鼠标左键(或摁住鼠标左键不放,向下拖 曳至B7),以便自动填充单元格区域B4: B7(如表4所示),然后将光标移至单元 格B7的右下方(如表4所示,请注意, 应确保单元格区域B3:B7同时被选中), 待出现加号"+"后,摁住鼠标左键不放, 向右拖曳至J7,以便自动填充单元格区域 C3:J7,结果如表1所示,注:表中各数据间 的关系是:pVIFA(i,n)=280幅=—~-— (1+3. __ 5%Fz i0.00 同理,对于首行以外的任意一行,该 一 '一一 ? 一 财会学习 行与每一列相交所形成单元格中的数值, 分别是根据该单元格所在列列首对应的 折现率(随该行各单元格所在列的变化 而变化)和该行首列中列明的期数(不随 该列各单元格所在行的变化而变化)计 算得出的普通年金现值系数.现以第3 行为例,单元格区域B3:J3中的数值是 在期数保持不变(即单元格A3中的1), 折现率分别为1%,5%(如单元格区域 A3:A7所示)条件下的普通年金现值系 数.因此,另一自动填充方法是:在单元 格B3中输入表达式"一(1(1+B$2)( SA3))/B$2"或内置工作簿函数及其参 数("=pv(B$2,$A3,一1,0,0)"),确 认后,将鼠标移至单元格B3的右下方(如 表3所示),待出现加号"+"后,摁住鼠 标左键不放,向右拖曳至J3,以便自动填 充单元格区域C3:J3(如表5所示),接 着将光标移至单元格J3的右下方(如表 5所示,请注意,应确保单元格区域B3: J3同时被选中),待出现加号"+"后,双 击鼠标左键(或摁住鼠标左键不放,向 下拖曳至J7),以便自动填充单元格区域 C3:J7,结果如表1所示. 需要说明的是,单元格B3基本计 算公式中的B$2是指单元格B3混合引用 B2单元格的内容,B3单元格的数值取 决于B2单元格中的数值,特别是从B3 单元格向右,向下拖曳或将该单元格复 制(剪贴)到其他单元格(CR)时,行号 2一直保持不变,但列号会作相应变化 (X),即单元格(CR)与第X列第2行所 对应单元格中的内容保持一致.例如将 鼠标从B3向右拖曳,依次经过C3,D3, E3,…一,J3时,基本计算公式中的B$2 将分别被C$2,D$2,E$2,…一,J$2所 替代(即折现率分别调整成鼠标所在列 列首指明的折现率),但式中的$A3保持 不变(即期数不变),替代后的公式将被 依次填入C3,D3,E3,…一,J3(请读者 亲手尝试,并仔细体会其中的变化规律). 单元格B3基本计算公式中的$A3 是指B3混合引用A3单元格的内容,B3 单元格的数值取决于A3单元格中的数 图1:"模拟运算表"对话框 合理运用Excel的混合弓矫喇以 运算高效乌寺 定要求自勺普i亘年金王见值系数 值,尤其是从B3单元格向右,向下拖曳 或将该单元格复制(剪贴)到其他单元 格(CR)时,列号A一直保持不变,但 行号会作相应变化(Y),即单元格(CR) 与第A列第Y行所对应单元格中的内容 保持一致.例如将鼠标从B3向下拖曳, 依次经过B4,B5,B6和B7时,基本 计算公式中的$A3将分别被$A4,$A5, $A6,$A7替代(即期数分别调整成鼠标 所在行行首指明的期数),但B$2保持不 变(即折现率不变),替代后的公式将依 次填人B4,B5,B6和B7. 混合引用的上述特性,决定了两种不 同的拖曳和自动填充方向,但最终结果完全 一 致.其一是"先列后行'如上例中,先从 B3单元格向下拖拽到B7,然后向右拖曳到 J7;其二"先行后列",如上例中,从B3单 元格向右拖拽到J3,然后向下拖曳到J7o (二)模拟运算表/数据表法 混合引用法编制普通年金现值系数 表的关键是必须确保基本公式中混合引 用的正确性,否则填充而成的整张表均不 可能正确.为避免设置复杂的混合引用 表达式,可以通过Excel2003及以前版 本中的模拟运算表功能或者Excel2007 中的数据表(但Excel2010中仍称为"模 拟运算表")功能来编制普通年金现值系 数表,基本流程如下: 1.设置普通年金现值系数表的基本 框架.假设普通年金现值系数表的基本 框架仍如表2所示. 2.设置普通年金现值系数的基本计 算公式.为清晰反映普通年金现值系数 与期数,折现率的函数关系,在C1,D1(或 其他任何单元格,但必须在系数表以外) 中分别输入…n'和任意期数(如3),在 E1,F1(或其他任何单元格,但必须在系 数表以外)中分别输入"i"和任意折现 率(如3.5%),然后在期数所在列和折现 率所在行交叉的单元格(如表1,表6中 的单元格A2)中输入计算普通年金现 值系数的表达式"=(1(1+F1)( D1))/F1"或内置工作簿函数"一-pv(F1, D1,1,0,0)")(注意,必须在此单位格 中输入,且一定要列明引用关系). 3.进行模拟运算.首先,选中单元格 区域A2:J7;接着,选中Excel2003或以前 版本中的"数据"菜单,打开"模拟运算 表"对话框(或选中Excel2007中的"数据" 选项卡,点击"数据工具"功能区中的"假 设分析"按钮,弹出"数据表"下拉菜单, 选中该菜单后,弹出"数据表"对话窗); 然后,将鼠标移入该对话框"输入引用行 的单元格"右侧的输入框,选中Fl(一定 要注意对应关系,因为本表首行列示的是 折现率数据,因此,只能选F1);再将鼠标 移人该对话框"输入引用列的单元格"右 侧的输入框,选中D1(同样要注意对应关系, 因为本表首列列示的是期数,因此,只能选 D1)(结果如图1所示);最后,点击"确认" 按钮,自动生成如表6所示的系数表. 三美化系数表 上文中的表l和表6是按照不同方 法编制而成的普通年金现值系数表,可 能还不尽符合特定要求,如可能需要运 用round函数将系数四合五入成四位小 数,并通过设置单元格 格式 pdf格式笔记格式下载页码格式下载公文格式下载简报格式下载 来显示四位 小数,设置对齐方式和加边框等. 综上所述,合理运用Excel的混合引 用和模拟运算表功能可以高效编制符合 特定要求的普通年金现值系数表.当然, 其原理同样适用于其他时间价值系数表 的编制.圈 201003财会学习I75