运用Excel 软件计算NPV(净现值)和IRR(内部收益率)及投资回收期、折现率、财务净现值的解释

运用Excel 软件计算NPV(净现值)和IRR(内部收益率)及投资回收期、折现率、财务净现值的解释 运用Excel 软件计算NPV(净现值)和IRR(内部收益率)及投资回收期、折现率、财务净现值的解释 如何用Excel 软件计算NPV(净现值)和IRR(内部收益率) 投资决策时，可以借助Excel 软件通计算NPV(净现值)和IRR(内部收益率)两个重要的投资指标。 操作步骤 第一步:启动Excel电子#格#，在菜单栏“插入”里点击启动“函数”。 第二步:在粘贴函数对话框里“函数分类”选择“财务”，“函数名”选择“NPV”。 第三步:输入相关参数，求出NPV。 实例演示 某项目投资期为6年，各年末净现金流量分别为-500、200、200、200、200、100，该项目基准收益率为10%，试通过NPV法分析该项目是否可行, 在Rate栏内输入折现率0.1，在Value栏内输入一组净现金流量，并用逗号隔开-500, 200, 200, 200, 200, 100。也可单击红色箭头处，从Excel工作簿里选取数据。然后，从该对话框里直接读取计算结果“计算结果= 178.2411105”。或者点击“确定”，将NPV的计算结果放到Excel工作簿的任一单元格内。大大提高了工作效率。 lujianhua (South area ahead Office Director and to war victory)]. County Government operates 3 Civil Affairs, finance, education and Finance Committee, with espionage, sabotage, standing and political team. Soon, the tomb of the Wujiang District Committee of the Kuomintang relocated to the countryside village. Pre-war, strict tomb for seventh district, its regional range for this Taoyuan town and town of Nan Ma Street, and Tan Qiu street of rotten Brook Tang West of area, in Wujiang South border, and Zhejiang Province Tongxiang County, and Wuxing County adjacent, ancient for two province (River, and Zhejiang) three House (Suzhou, and huzhou, and jiaxing) seven County (wuxing, and return Ann, and Tongxiang, and jiaxing, and show water, and Wujiang, and earthquake Ze) of make, is located in remote, territory size Lake swing dotted, river port criss-cross, easy hidden combat, and no Highway, traffic inconvenience. Therefore, this has become the refuge of many, where Wujiang gentleman most of the refuge is not the fall of four townships, at this time of grave, there has been a monstrous prosperity, Nanxun town, even the neighboring Zhejiang Province, there are also some merchants to Yan Tao DUN Cun opened shop. Cang during the depression as a regional political center and military base in the area. In May 1938, Chairman Mao Zedong in the guerrilla war in the strategic issues of: "North zone, South of the Lake of hongze Lake area branching stream of all enemy-occupied areas and along the coastal areas, should be well organized guerrilla and in rivers, lakes and estuaries and build lasting base nearby, as one aspect of the development of the national guerrilla". 重复本文中的第一步和第二步，在第二步中的“函数名”中选择“IRR”，单击“确定”，弹出对话框遥在Value栏内输入一组净现金流量，两头用“{}”，中间用逗号隔开，即“{-500, 200, 200, 200, 200, 100}”，也可点击对话框中的红色箭头处，从Excel工作簿里直接选取。然后，即可从该对话框中直接读出计算结果，或者单击“确定”，将IRR的计算结果放到Excel工作簿中的任一单元格内。 根据显示，读出IRR=25.5280777%>10%，即该项目的内部收益率大于项目的基准收益率，因此该投资项目可行。 从上面的例子可以看出，利用Excel来计算NPV和IRR，会起到事半功倍的效果，轻松之间就省去了繁杂手工计算的烦恼。 投资回收期、折现率、财务净现值 在某一分析(指自有资金、或全投资、或投资者)中，得到各年净现金流量如 下: -100，30，33，37，40，40，40，40，80 如果说，其返本期是3年或4年，都是可以的，说返本期是 3年，是不包括建设期，说返本期是 4年，是包括建设期在内。但要说投资回收期是3年或4年，可就都错了。这是因为返本期的计算，把未来生产经营各年收回的一定数量的现金和建设期投入时的同样数量的现金看作是相同的;而投资回收期的计算，要求把未来生产经营各年收回的一定数量的现金和建设期投入时的同样数量的现金看作是不相同的，这种不相同，在许多书上都被称为货币的时间价值不同，表现在，时间越长，未来年收回的货币的价值越低;而笔者认为:这种不相同的表现还在于:投资者的投资赢利的期望值越高，未来年收回的货币的价值越低，其投资回收期就越长。对于同一个项目，同样的其他基础数据，如果投资者的期望值 不同，其投资回收期是不一样的。 lujianhua (South area ahead Office Director and to war victory)]. County Government operates 3 Civil Affairs, finance, education and Finance Committee, with espionage, sabotage, standing and political team. Soon, the tomb of the Wujiang District Committee of the Kuomintang relocated to the countryside village. Pre-war, strict tomb for seventh district, its regional range for this Taoyuan town and town of Nan Ma Street, and Tan Qiu street of rotten Brook Tang West of area, in Wujiang South border, and Zhejiang Province Tongxiang County, and Wuxing County adjacent, ancient for two province (River, and Zhejiang) three House (Suzhou, and huzhou, and jiaxing) seven County (wuxing, and return Ann, and Tongxiang, and jiaxing, and show water, and Wujiang, and earthquake Ze) of make, is located in remote, territory size Lake swing dotted, river port criss-cross, easy hidden combat, and no Highway, traffic inconvenience. Therefore, this has become the refuge of many, where Wujiang gentleman most of the refuge is not the fall of four townships, at this time of grave, there has been a monstrous prosperity, Nanxun town, even the neighboring Zhejiang Province, there are also some merchants to Yan Tao DUN Cun opened shop. Cang during the depression as a regional political center and military base in the area. In May 1938, Chairman Mao Zedong in the guerrilla war in the strategic issues of: "North zone, South of the Lake of hongze Lake area branching stream of all enemy-occupied areas and along the coastal areas, should be well organized guerrilla and in rivers, lakes and estuaries and build lasting base nearby, as one aspect of the development of the national guerrilla". 返本期和投资回收期都是反映投资收回期限的长短快慢。但前者是一种不变的静止的观念，而后者是一种变化的动态的观念。 所以，在我的软件《可研专家》中，把包括建设期的返本期叫做静态投资回收期，把从建设初年算起的投资回收 期叫做动态投资回收期。特此说明。 如何计算动态投资回收期，需要先了解折现率Ic的含义和各年净现金流量现值以及净现值NPV的意义。 在工业项目可行性研究中，要首先算出各年净现金流量 A1、A2、A3、A4、、、An ，(其中A1必须是负值)，再由投资者设定一个折现率Ic的数值，然后再算出各年净现金流量现值(下式左边的 N个分式就是各年净现金流量现值)，再把各年净现金流量现值相加之和叫做净现值NPV，表达为下式: A1 A2 A3 A4 An , -------- + ------- + ------- + ------- + …… + ---------- NPV (n-1)0124(1+Ic) (1+Ic) (1+Ic) (1+Ic) (1+Ic) 当把Ic的设定数值代入上面公式，计算出的 NPV>=0时,项目可以继续分析研究;若计算出的NPV<0,则项目被否定。其道理是什么 ? 投资者应该如何设定Ic ? 这在许多书上都从货币的时间属性、时间价值，未来年收回货币的折现率Ic，折现值，净现值NPV来进行经典的描述说明。也有书指出:折现率Ic 是投资者期望的最低的投资收益率，并说明投资者应该如何设定Ic。在我的软件〈可研专家〉中当然的依据这些经典原理进行计算设计。 在这里，笔者要进一步说明的是:“折现率Ic 是投资者期望的最低的投资回报年复利利率”。了解这一点，对于投资者确定 Ic 有一定的参考作用。也可以从另一个角度来理解NPV的意义。 为什么说折现率Ic是投资者期望的最低的投资回报年复利利率,这是从计算折现值的得出的。 ()() K-1K-1 首先，由第K年的折现值 B = A ?(1+Ic) 及 A = B × (1+Ic)KKKK 从中不难看出，以某年的现金净流量 A求其折现值 B的过程就是已知年复利KK 的本利和A和年复利利率Ic求其本金B的过程。这说明折现率Ic本质上是年KK 复利利率。 其次，从计算净现值NPV的公式可以得出，在一定的净现金流量条件下，存在一个特殊的折现率Ic*，在此折现率时，NPV=0，这个特殊的折现率Ic*就是内部利润率IRR。IRR的实际意义可以这样来理解:把建设期的净投入看作是以年复利贷出去，把经营各年的净收入看作是连本代利的正好收回，其中的年复利利率就是内部利润率IRR 。(此详见本人写的内部利润率IRR 新释) lujianhua (South area ahead Office Director and to war victory)]. County Government operates 3 Civil Affairs, finance, education and Finance Committee, with espionage, sabotage, standing and political team. Soon, the tomb of the Wujiang District Committee of the Kuomintang relocated to the countryside village. Pre-war, strict tomb for seventh district, its regional range for this Taoyuan town and town of Nan Ma Street, and Tan Qiu street of rotten Brook Tang West of area, in Wujiang South border, and Zhejiang Province Tongxiang County, and Wuxing County adjacent, ancient for two province (River, and Zhejiang) three House (Suzhou, and huzhou, and jiaxing) seven County (wuxing, and return Ann, and Tongxiang, and jiaxing, and show water, and Wujiang, and earthquake Ze) of make, is located in remote, territory size Lake swing dotted, river port criss-cross, easy hidden combat, and no Highway, traffic inconvenience. Therefore, this has become the refuge of many, where Wujiang gentleman most of the refuge is not the fall of four townships, at this time of grave, there has been a monstrous prosperity, Nanxun town, even the neighboring Zhejiang Province, there are also some merchants to Yan Tao DUN Cun opened shop. Cang during the depression as a regional political center and military base in the area. In May 1938, Chairman Mao Zedong in the guerrilla war in the strategic issues of: "North zone, South of the Lake of hongze Lake area branching stream of all enemy-occupied areas and along the coastal areas, should be well organized guerrilla and in rivers, lakes and estuaries and build lasting base nearby, as one aspect of the development of the national guerrilla". 当Ic设定值高于IRR时，必然使NPV<0，这说明投资者的期望过高或项目回报太低，项目各年的净收入按过高的年复利利率Ic扣除利息回报后，收回的本金之和小于初始投资，投资者的回报要求不能实现。 当Ic设定值低于IRR时，必然使NPV>0，这说明投资者的期望不高或项目回报更高，项目各年的净收入按不高的年复利利率Ic扣除利息回报后，收回的本金之和大于初始投资，投资者的回报要求可以实现。 实际上，任何一个投资者在投资之初，都有一个最低期望值，只有当最低期望可以满足时，才能考虑进一步研究。而这个最低期望值的名义是折现率Ic，实际是投资者期望的最低的投资回报年复利利率，当把投资者确定的Ic代入后，NPV>=0，则说项目可以满足投资者的最低回报要求，反之，则说项目不能满足投资者的最低回报要求。 投资者应如何确定自己的最低期望值Ic。我的想法是: 首先要明确Ic是投资者期望的最低的投资回报年复利利率，要知道同一个利率值(例如10%)，分别是年单利率、年复利率，其中年复利率的期望要远远高于年单利率的期望，即使您不区分这一点，但在实际上您确定的是被当作年复利利率使用的。如果您的本意以为Ic是年单利率，那么，您确定的最低期望值比您的本意就高了许多。 其次要明确Ic的构成三要素: 要素1 = 注册资本中真正自有资金改在银行储蓄、买债卷等低风险投资上的平均利润率和注册资本中需外筹部分的筹资利率的加权平均利率; 要素2 = 在项目管理中因体力和脑力的付出，每年期望得到的最低管理报酬 ? 项目注册资本 要素3 = 在项目建设经营中因承担各种风险，每年期望得到的最低风险报酬 ? 项目注册资本 在理性认识上，Ic = 要素1 + 要素2 + 要素3 ，但各要素的精确具体表述在不同的书上不尽相同。 在实际操作上，由于上述要素2和要素3的伸缩性很大，所以Ic的确定也具有很大伸缩性。为此，一般推荐使用同行业平均或较高折现率或使用稍高于当时银行贷款利率。但如果注册资金的大部分甚至全部需外筹资而来，此时Ic取值必须较大幅度地超过筹资利率，超过部分是期望的最低管理报酬和风险报酬。 现金流量表(1) 计算期年1 2 3 4 5 6 7 8 9 份 lujianhua (South area ahead Office Director and to war victory)]. County Government operates 3 Civil Affairs, finance, education and Finance Committee, with espionage, sabotage, standing and political team. Soon, the tomb of the Wujiang District Committee of the Kuomintang relocated to the countryside village. Pre-war, strict tomb for seventh district, its regional range for this Taoyuan town and town of Nan Ma Street, and Tan Qiu street of rotten Brook Tang West of area, in Wujiang South border, and Zhejiang Province Tongxiang County, and Wuxing County adjacent, ancient for two province (River, and Zhejiang) three House (Suzhou, and huzhou, and jiaxing) seven County (wuxing, and return Ann, and Tongxiang, and jiaxing, and show water, and Wujiang, and earthquake Ze) of make, is located in remote, territory size Lake swing dotted, river port criss-cross, easy hidden combat, and no Highway, traffic inconvenience. Therefore, this has become the refuge of many, where Wujiang gentleman most of the refuge is not the fall of four townships, at this time of grave, there has been a monstrous prosperity, Nanxun town, even the neighboring Zhejiang Province, there are also some merchants to Yan Tao DUN Cun opened shop. Cang during the depression as a regional political center and military base in the area. In May 1938, Chairman Mao Zedong in the guerrilla war in the strategic issues of: "North zone, South of the Lake of hongze Lake area branching stream of all enemy-occupied areas and along the coastal areas, should be well organized guerrilla and in rivers, lakes and estuaries and build lasting base nearby, as one aspect of the development of the national guerrilla". -------- 各年现金-100 30 33 37 40 40 40 40 80 净流量 现金净流-100 -70 -37 0 40 80 120 160 240 量累积 Ic=10%1 0.9091 0.8264 0.7513 0.683 0.6209 0.5645 0.5132 0.4665 折现系数 现金净流-100 27.273 27.273 27.799 27.321 24.837 22.579 20.526 37.321 量现值 净流量现-100 -72.73 -45.45 -17.66 9.665 34.501 57.081 77.607 114.93 值累积 内部利润率 IRR = 33% 静态投资回收期 = 4年 当Ic=10% 时，NPV = 114.93 动态投资回收期 = 4年8个月 现金流量表(2) 计算期年1 2 3 4 5 6 7 8 9 份 -------- 各年现金-100 30 33 37 40 40 40 40 80 净流量 现金净流-100 -70 -37 0 40 80 120 160 240 量累积 Ic=15%1 0.8696 0.7561 0.6575 0.5718 0.4972 0.4323 0.3759 0.3269 折现系数 现金净流-100 26.087 24.953 24.328 22.87 19.887 17.2931 15.037 26.152 量现值 净流量现-100 -73.91 -48.96 -24.63 -1.762 18.125 35.418 50.456 76.608 值累积 内部利润率 IRR = 33% 静态投资回收期 = 4年 当Ic=15% 时，NPV = 76.608 动态投资回收期 = 5年1个月 说明1、上面两个现金流量表中的各年现金净流量行可以是全投资的、自有资金的、投资者的，表的名称相应改为全投资的、自有资金的、投资者的现金流量表。其下各行是完全一样的。 说明2、从现金净流量累积行的正负交替处或零处可知静态投资回收期(本例lujianhua (South area ahead Office Director and to war victory)]. County Government operates 3 Civil Affairs, finance, education and Finance Committee, with espionage, sabotage, standing and political team. Soon, the tomb of the Wujiang District Committee of the Kuomintang relocated to the countryside village. Pre-war, strict tomb for seventh district, its regional range for this Taoyuan town and town of Nan Ma Street, and Tan Qiu street of rotten Brook Tang West of area, in Wujiang South border, and Zhejiang Province Tongxiang County, and Wuxing County adjacent, ancient for two province (River, and Zhejiang) three House (Suzhou, and huzhou, and jiaxing) seven County (wuxing, and return Ann, and Tongxiang, and jiaxing, and show water, and Wujiang, and earthquake Ze) of make, is located in remote, territory size Lake swing dotted, river port criss-cross, easy hidden combat, and no Highway, traffic inconvenience. Therefore, this has become the refuge of many, where Wujiang gentleman most of the refuge is not the fall of four townships, at this time of grave, there has been a monstrous prosperity, Nanxun town, even the neighboring Zhejiang Province, there are also some merchants to Yan Tao DUN Cun opened shop. Cang during the depression as a regional political center and military base in the area. In May 1938, Chairman Mao Zedong in the guerrilla war in the strategic issues of: "North zone, South of the Lake of hongze Lake area branching stream of all enemy-occupied areas and along the coastal areas, should be well organized guerrilla and in rivers, lakes and estuaries and build lasting base nearby, as one aspect of the development of the national guerrilla". 为4年)。 说明3、折现系数行的第一年是1，其后各年的数值等于其前一年数值除以(1+Ic)。 说明4、现金净流量现值行的各年的现金净流量现值=各年现金净流量× 对应各年折现系数。此行各年数值之和就是净现值NPV。 说明5、净流量现值累积行的各年净流量现值累积等于其上一年的累积加上该年的现值之和，因此，该行的最后累积值也是NPV。此行数值先是负后是正，正负交替处之前的年数就是动态投资回收期的年数，交替处的负值的绝对值占下一年的现金净流量现值的比例乘以12就是动态投资回收期的月数。 说明6、上面两个现金流量表中的折现率不同，并不影响内部利润率和静态投资回收期，只影响NPV和动态投资回收期。折现率Ic越大，NPV越小，动态投资回收期越长。如果设定Ic正好等于IRR，则必有NPV=0，动态回收期恰好等于项目计算期。 说明7、设定较小的折现率Ic，可以计算得到较小的动态投资回收期，但这是降低了项目可行性的门槛，在行业审批、特别是在金融机构的贷款请求中是不利的。 说明8、在同一个项目的自有注册资金、全投资、投资者分析中，通常使用同一个折现率Ic，但得到的NPV和动态投资回收期差别是很大的，意义也是不同的。 综合以上:笼统的说某项目投资回收期是多少年，是不确切的。如果指的是返本期，需要说明是否包括建设期。如果所指并非返本期，必须同时说明是在折现率Ic等于多少的前提下。为确定和说明本质，笔者推荐使用本文使用的静态投资回收期和动态投资回收期的观念。不仅如此，还应同时说明是在那一种分析中，所指对象是自有注册资金还是全投资或是全体投资者或是那一个投资者。 lujianhua (South area ahead Office Director and to war victory)]. County Government operates 3 Civil Affairs, finance, education and Finance Committee, with espionage, sabotage, standing and political team. Soon, the tomb of the Wujiang District Committee of the Kuomintang relocated to the countryside village. Pre-war, strict tomb for seventh district, its regional range for this Taoyuan town and town of Nan Ma Street, and Tan Qiu street of rotten Brook Tang West of area, in Wujiang South border, and Zhejiang Province Tongxiang County, and Wuxing County adjacent, ancient for two province (River, and Zhejiang) three House (Suzhou, and huzhou, and jiaxing) seven County (wuxing, and return Ann, and Tongxiang, and jiaxing, and show water, and Wujiang, and earthquake Ze) of make, is located in remote, territory size Lake swing dotted, river port criss-cross, easy hidden combat, and no Highway, traffic inconvenience. Therefore, this has become the refuge of many, where Wujiang gentleman most of the refuge is not the fall of four townships, at this time of grave, there has been a monstrous prosperity, Nanxun town, even the neighboring Zhejiang Province, there are also some merchants to Yan Tao DUN Cun opened shop. Cang during the depression as a regional political center and military base in the area. In May 1938, Chairman Mao Zedong in the guerrilla war in the strategic issues of: "North zone, South of the Lake of hongze Lake area branching stream of all enemy-occupied areas and along the coastal areas, should be well organized guerrilla and in rivers, lakes and estuaries and build lasting base nearby, as one aspect of the development of the national guerrilla".