EXCEL在房地产投资分析的应用（共57页）

1、第13章 EXCEL在房地产投资分析的应用13-1 Excel的简介与基本操作一、Excel的简介Excel是美国微软公司发布的Office 办公套装软件家族中的核心软件之一，它是一个电子表格软件，可以用来制作电子表格、完成许多复杂的数据运算，进行数据的分析和预测并且具有强大的制作图表的功能。它的界面更加直观、操作更加简单、使用更加方便，对用户来说学习更加轻松，办公也更加方便、快捷，特别适用于房地产投资分析中对数据的分析与处理。二、Excel的基本知识与基本操作（一）Excel窗口Excel启动成功后，就出现了Excel窗口，如图13-1所示，各组成部分的功能和作用如下：1. 标题栏图13-1

2、 Excel窗口的组成 与所有Windows的应用程序一样，标题栏中显示窗口的名字，并有控制按钮可以让用户对窗口进行移动、关闭、缩小、放大、最大化、最小化等操作。（一）Excel窗口2. 菜单栏菜单栏中给出了若干菜单项，单击某菜单项就会出现相应的下拉菜单。3. 工具栏Excel的工具栏有很多种，主要的有：常用工具栏、格式工具栏、绘图工具栏、Web工具栏、任务窗格等。这些工具栏可以设置成显示或隐藏（选菜单【视图】【工具栏】的相应项可以调出），系统的默认设置是显示常用工具栏和格式工具栏。4. 编辑栏编辑栏的左边是名称框，用于显示活动单元格或区域的地址（或名称），右边是编辑区，可以在该编辑区输入、修

3、改数据，如图所示。 图13-2 编辑栏（一）Excel窗口5. 状态栏状态栏位于屏幕的底部，用于显示各种状态信息以及其他非常有用的信息。例如，状态栏经常显示信息“就绪”，它表明Excel已为新的操作准备就绪；当Excel正在执行某一操作，如保存工作簿，状态栏上就会有一个相应的状态指示器。6. Excel工作区在Excel工作区中显示的是Excel工作簿窗口（即文档窗口），在工作区中可以有一个或多个工作簿窗口，也可以没有。工作簿窗口由标题栏、工作表标签栏、列号标志、行号标志、水平和垂直滚动条以及工作表区域组成。当Excel工作簿窗口最大化时，工作簿窗口和Excel应用程序窗口共用一个标题栏，而工

4、作簿窗口的控制按钮则在Excel应用程序窗口相应控制按钮的正下方。一个工作簿可以由多张工作表组成。启动EXCEL后，工作簿窗口中，通常包含三张工作表。其中白色的工作表选项卡表示的是当前工作表。在一个工作簿中当前工作表只能有一张。用户可以自行插入和删除工作表从而改变工作表的张数。（二）Excel 的基本操作1. 选择工作表要对某一个工作表进行操作，必须先选中（或称激活）它，使之成为当前工作表。操作方法是：用鼠标单击工作簿底部的工作表标签，选中的工作表以高亮度显示，则该工作表就是当前工作表。如果要选择多个工作表，可在按键的同时，用鼠标逐一单击所要选择的工作表标签。若要取消选择，可松开键后，单击其他

5、任何未被选中的工作表标签即可。图13-3 Excel工作表的编辑如果所要选择的工作表标签看不到，可按标签栏左边的标签滚动按钮。这四个按钮的作用按自左至右次序为：移动到第一个、向前移一个、向后移一个、移动到最后一个。（二）Excel 的基本操作2. 工作表的重新命名在实际的应用中，一般不要使用Excel默认工作表名称，而是要给工作表起一个有意义的名字。有三种方法可以用来对工作表改名（见图13-3）：先选择一个工作表，然后选菜单【格式】【工作表】【重命名】项。用鼠标右键单击某工作表标签，然后从快捷菜单中选择“重命名”。双击工作表标签。这三种方法都会使标签上的工作表名高亮度显示，此时可以键入新名称，

6、再按回车键即可。（二）Excel 的基本操作3. 插入工作表要在工作簿中插入新的工作表，可以选菜单【插入】【工作表】项（见图13-3），这样，一个新的工作表就插入在原来当前工作表的前面，并成为新的当前工作表。也可以用鼠标右键单击工作表标签，然后从快捷菜单中选择“插入”项插入工作表。4. 删除工作表要删除一个工作表，先选中该表，然后选菜单【编辑】【删除工作表】项（见图13-3），此时弹出对话框要求用户确认，经确认后才删除。同样也可以单击鼠标右键，在快捷菜单中选择“删除”项。13-2 常用公式与函数一、公式（一）公式及其输入一个公式是由运算对象和运算符组成的一个序列。它由等号（=）开始，公式中可以

7、包含运算符，以及运算对象常量、单元格引用（地址）和函数等。Excel有数百个内置的公式，称为函数。这些函数也可以实现相应的计算。一个Excel的公式最多可以包含1024个字符。Excel中的公式有下列基本特性：（1） 全部公式以等号开始。（2） 输入公式后，其计算结果显示在单元格中。（3） 当选定了一个含有公式的单元格后，该单元格的公式就显示在编辑栏中。编辑公式与编辑数据相同，可以在编辑栏中，也可以在单元格中。双击一个含有公式的单元格，该公式就在单元格中显示。如果想要同时看到工作表中的所有公式，可按（感叹号左边的那个键），可以在工作表上交替显示公式和数值。一、公式（二）公式中的运算符Excel

8、的运算符有三大类，其优先级从高到低依次为：算术运算符、文本运算符、比较运算符。1. 算术运算符Excel所支持的算术运算符的优先级从高到低依次为：%（百分比）、（乘幂）、*（乘）和/（除）、（加）和-（减）。2. 文本运算符Excel的文本运算符只有一个用于连接文字的符号 &。例如：公式 =Computer &Center 结果：Computer Center3. 比较运算符Excel中使用的比较运算符有六个，其优先级从高到低依次为：=（等于）、（大于）、=（大于等于）、（不等于）。比较运算的结果为逻辑值TRUE（真）或FALSE（假）。一、公式（三）单元格引用在公式中引用单元格或区域，公式的

9、值会随着所引用单元的值的变化而变化。公式中可以引用另一个工作表的单元格和区域，甚至引用另一工作簿中的单元格和区域。若要引用另一工作簿的单元格或区域，只需在引用单元格或区域的地址前冠以工作簿名称。（四）复制公式公式的复制与数据的复制的操作方法相同。但当公式中含有单元格或区域引用时，根据单元地址形式的不同，计算结果将有所不同。当一个公式从一个位置复制到另一个位置时，Excel能对公式中的引用地址进行调整。（四）复制公式1. 公式中引用的单元格地址是相对地址当公式中引用的地址是相对地址时，公式按相对寻址进行调整。例如A3中的公式=A1A2，复制到B3中会自动调整为 =B1B2。公式中的单元格地址是相

10、对地址时，调整规则为：新行地址 = 原行地址 行地址偏移量新列地址 = 原列地址 列地址偏移量2. 公式中引用的单元格地址是绝对地址不管把公式复制到哪儿，引用地址被锁定，这种寻址称作绝对寻址。如A3中的公式=$A$1$A$2复制到B3中，仍然是 =$A$1$A$2。3. 公式中的单元格地址是混合地址在复制过程中，如果地址的一部分固定（行或列），其他部分（列或行）是变化的，则这种寻址称为混合寻址。如：A3中的公式=$A1$A2复制到B4中，则变为：=$A2$A3， 其中，列固定，行变化（变换规则和相对寻址相同）。4. 被引用单元格的移动当公式中引用的单元格或区域被移动时，因原地址的数据已不复存在

11、。Excel根据它移动的方式及地点，将会出现不同的后果。 不管公式中引用的是相对地址、绝对地址或混合地址，当被引用的单元格或区域移动后，公式的引用地址都将调整为移动后的地址。即使被移动到另外一个工作表也不例外。例如，A1中有公式=$B6*C8，把B6移动到D8，把C8移动到Sheet2的A7，则A1中的公式变为=$D8*Sheet2!A7。（四）复制公式5. 移动公式当公式被移动时，引用地址还是原来的地址。例如，C1中有公式 =A1B1，若把单元格C1移动到D8，则D8中的公式仍然是=A1B1。6. 公式中的出错信息当公式有错误时，系统会给出错误信息。表13-2中给出了一些常见的出错信息。表1

12、3-1 公式中常见的出错信息出错信息可能的原因#DIV/0！公式被零除#N/A没有可用的数值#NAME？Excel不能识别公式中使用的名字#NULL！指定的两个区域不相交#NUM！数字有问题#REF！公式引用了无效的单元格#VALUE！参数或操作数的类型有错二、函数函数是随Excel附带的预定义或内置公式。函数可作为独立的公式而单独使用，也可以用于另一个公式中甚至另一个函数内。一般来说，每个函数可以返回（而且肯定要返回）一个计算得到的结果值，而数组函数则可以返回多个值。Excel共提供了九大类，300多个函数，包括：财务函数、数学与三角函数、统计函数、数据库函数、逻辑函数等。函数由函数名和参数

13、组成，格式如下：函数名（参数1，参数2，）函数的参数可以是具体的数值、字符、逻辑值，也可以是表达式、单元地址、区域、区域名字等。函数本身也可以作为参数。如果一个函数没有参数，也必须加上括号。函数是以公式的形式出现的，在输入函数时，可以直接以公式的形式编辑输入，也可以使用Excel提供的“插入函数”工具。1. 直接输入选定要输入函数的单元格，键入“=”和函数名及参数，按回车键即可。例如，要在H1单元格中计算区域A1：G1中所有单元格值的和。就可以选定单元格H1后，直接输入=SUM（A1：G1），再按回车键。2.使用“插入函数”工具每当需要输入函数时，就选菜单中的 。此时会弹出一个“插入函数”对话

14、框，如图所示。对话框中提供了函数的搜索功能，并在“选择类别”中列出了所有不同类型的函数，“选择函数”中则列出了被选中的函数类型所属的全部函数。选中某一函数后，单击【确定】按钮，又会弹出一个“函数参数”对话框，其中显示了函数的名称、它的每个参数、函数功能和参数的描述、函数的当前结果和整个公式的结果。3. 函数出错信息当输入的函数有错误时，Excel会提示出错信息，表13-2给出了几种常见的出错信息。出错信息可能的原因#NAME！把文本作为函数的数值型参数#NUM！函数中出现非法数值参数#REF！函数中引用了一个所在列或行已被删除的单元格#VALUE！函数中引用的参数不合适表13-2 函数出错信息

15、三、房地产投资分析常用函数（一）财务函数1. 终值函数 FV（Rate，Nper，Pmt，Pv，Type）其中：Rate利率Nper总投资期Pmt各期支付金额Pv现值Type只有数值0或1，0或忽略表示收款期末2. 现值计算函数PV（Rate，Nper，Pmt，Fv，Type）其中，Rate，Nper，Pmt，Fv，Type的含义与FV函数中的参数含义相同3. 偿债基金和资金回收计算函数PMT（Rate，Nper， Fv ，Pv，Type）其中，Rate，Nper， Fv，Pv，Type的含义与FV和PV函数中的参数含义相同4. NPV函数NPV（Rate，Value1，Value2）其中：R

16、ate代表各期现金流量折算成当前值的贴现率，在各期中固定不变；Value1，Value2；代表支出和收入的1到29个参数，时间均匀分布并出现在每期末尾。5. NPER函数NPER （Rate， PMT， Fv ，Pv，Type）其中：Rate利率Pmt各期支付金额Fv终值Pv现值Type只有数值0或1，0或忽略表示收款发生在期末，1表示收款发生在期初6. IRR函数IRR（Values，Guess）其中：Values一个数组，或对数字单元格区的引用；Guess内部报酬率的猜测值。如果忽略，则为0.1。IRR函数计算的是返回一组现金流的内部收益率。（二）统计函数1. AVERAGE（x1，x2，

17、）返回所列范围中所有数值的平均值。最多可有30个参数，参数x1，x2，可以是数值、区域或区域名字。2. COUNT （x1，x2，） 返回所列参数（最多30个）中数值的个数。函数COUNT在计数时，把数字、文本、空值、逻辑值和日期计算进去，但是错误值或其他无法转化成数据的内容则被忽略。3. COUNTA（x1，x2，）返回所列参数（最多30个）中数据项的个数。在这里，“数据”是广义的概念，计数值可以是任何类型，它们可以包括空字符（）。4. COUNTIF （x1，x2）计算给定区域x1满足条件x2的单元格的数目。条件x2的形式可以为数字、表达式或文本。5. COUNTBLANK（x）计算指定区

18、域x中空白单元格的数目。说明：含有返回值为（空文本）的公式单元格也计算在内，但包含零值的单元格不计算在内。例如：假设工作表中的区域B2：C5中有两个空单元（没有输入任何内容），则COUNTBLANK（B2：C5）等于2。6. 求最大值函数MAX（List） 返回指定List 中的最大数值，List可以是一数值、公式或包含数字或公式的单元格范围引用的表。例如，MAX（87，A8，B1：B5） MAX（D1：D88）。7. MIN（List） 返回List中的最小数。List的意义同MAX。例如，MIN（C2：C88）。8. 求和函数SUM（x1，x2，）返回包含在引用中的值的总和。x1，x2等可

19、以是对单元格、区域或实际值。如：SUM（A1：A5，C6：C8）返回区域A1至A5和C6至C8中的值的总和。9. SUMIF（x1，x2，x3）根据指定条件x2对若干单元格求和。其中：x1为用于条件判断的单元格区域。x2 为确定哪些单元格将被相加求和的条件，其形式可以为数字、表达式或文本。（三）回归分析函数Excel中提供了9个函数用于建立回归模型和预测。这9个函数分别是：1. INTERCEPT：返回线性回归模型的截距。2. SLOPE：返回线性回归模型的斜率。3. RSQ：返回线性回归模型的判定系数。4. FORECAST：返回一元线性回归模型的预测值。5. STEYX：计算估计的标准误。

20、6. TREND：计算线性回归的趋势值。7. GROWTH：返回线性回归模型的参数。8. LINEST：返回线性回归模型的参数。9. LOGEST：返回指数曲线模型的参数。13-3 房地产投资分析excel计算演示一、财务指标（一）净现值NPV的计算例1：某房地产开发项目，其各时期的现金流量如下表所示，年利率为5 %。就其净现值。年份12345现金流量-10050200300350计算过程：1）建立一个工作表。选中单元格，输入“=”，在函数类别中选择“财务”，然后在“选择函数”中选择“NPV”。2）在“函数参数”对话框中“Rate”中输入利率值5%，在Value中输入所要计算的现金流量的区域。

21、3）显示结果为643.925例2：某房地产企业预投资一房地产项目，预计投资期为5年。第一年投资费为2000万元，以后逐年增加1000万元，年利率为10%。试计算该项目的投资现值。计算过程：1）建立一个工作表。2）输入年份，在B1中输入1，按住Ctrl拖拽鼠标直至出现5。3）输入现金流量，在B2中输入2000，在C2中输入“= B2 1000”拖动F2右下角的填充柄至最后。4）计算净现值，在E9中输入“=”，在函数类别中选择“财务”，然后在“选择函数”中选择“NPV”。在“函数参数”对话框中“Rate”中输入利率值5%，在Value中输入所要计算的现金流量的区域。单击“确定”，在目标单元格中显示

22、结果14 1443.38。（二）内部收益率IRR的计算例1：某房地产企业各年的收益的现金流量如下表所示，求该企业的的内部收益率为多少。年份12345现金流量-10050200300350计算过程：1）建立一个工作表。按提示要求制作一个现金流量表。2）计算净现值，在B3中输入“=”，在函数类别中选择“财务”，然后在“选择函数”中选择“IRR”。在“函数参数”对话框中的Value中输入所要计算的现金流量的区域。单击“确定”，在目标单元格中显示结果。二、风险分析（一）盈亏平衡分析例1：某房地产项目今年计划开发一个房地产项目，其建设期为1年，经营期为2年。建设期投资30 000万元，计划建成后第一年为

23、设计销售量的50%年，第二年达到设计销售量4000户，每户售价50万元，期间年经营成本为35万。求以产量、生产能力利用率、销售价格、单位变动成本表示盈亏平衡点，并以产量为研究对象绘制盈亏平衡分析图。计算过程：1）建立一个工作表。按提示要求制作一个表格（如图所示）。2）在目标单元格“C7”输入“=C4/（C3-C5）”，单击回车即会显示结果。以下各项均按各自的公式输入公式。得出各自结果，盈亏平衡产量为2000户，盈亏平衡生产能力利用率50%，盈亏平衡价格为42.5万元，盈亏平衡产品变动成本为42.5万元。3）绘制盈亏平衡分析图。为了作图需要，我们应界定销售量的开始值和终止值。本例设开始值为0，终

24、止值为4000。点击工具栏上的“图表向导” ，进入“图表数据源”界面时选择“系列”，点击 编辑“系列”中的“名称（N）”和“值（Y）”以及“分类（X）轴标志（ ）”。将所有的选项编辑完成后，点击“下一步”。在“图表选项”中按提示编辑各个选项（如图所示），等所有所需内容编辑完成后点击下一步，在图表中编辑插入位置和所插入的工作表，之后点击完成。（二）敏感性分析例1：某房地产项目今年计划开发一个房地产项目，其建设期为1年，经营期为2年。建设期投资30 000万元，计划建成后第一年为设计销售量的50%年，第二年达到设计销售量4000户，每户售价50万元，期间年经营成本为35万。由于对未来影响经济因素把

25、握不大，投资额、经营成本、销售收入均有可能在20%的范围内变动。对上述三个不确定因素作单因素敏感性分析。计算过程：1）建立一个工作表。按提示要求制作一个编制敏感性分析表（如图所示）。 2）绘制敏感性分析图。单击“图表向导” ，选择折线图（如图所示），点击下一步。进入“图表数据源”界面时选择“系列”，点击 编辑“系列”中的“名称（N）”和“值（Y）”以及“分类（X）轴标志（ ）”。将所有的选项编辑完成后，点击“下一步”。在“图表选项”中按提示编辑各个选项（如图所示），等所有所需内容编辑完成后点击下一步，在图表中编辑插入位置和所插入的工作表，之后点击完成。显示所需敏感性分析图三、项目预测与决策分析

26、（一）一元线性回归分析例1：某房地产开发公司对过去一段时间内，一个房地产项目的销售额和花费在该项目上的广告费用之间的关系进行分析，其有关历史数据如下：年份广告费销售额年份广告费销售额199049.2537.6199549.2537.6199151.4706.2199651.4706.2199271.21073.9199771.21073.91993111.41209.31998111.41209.31994146.52055.21999146.52055.2 居于这些数据绘制成的图形（图8-1）大体上表明了两者之间存在着线性依赖关系，现在需要确定销售额与广告费之间的回归直线的截距和斜率，并需要确定当广告费用等于160与300时的销售额。此外，还需要确定本问题中回归直线的判定系数与估计误差的标准。某房地产项目广告费与销售额对应关系解：打开Excel工作表，输入广告费用观测值（Xi）和销售观测值（Yi）。在