EXCEL在房地產(chǎn)投資分析的應(yīng)用（共57頁）

1、第13章 EXCEL在房地產(chǎn)投資分析的應(yīng)用13-1 Excel的簡介與基本操作一、Excel的簡介Excel是美國微軟公司發(fā)布的Office 辦公套裝軟件家族中的核心軟件之一，它是一個電子表格軟件，可以用來制作電子表格、完成許多復(fù)雜的數(shù)據(jù)運算，進行數(shù)據(jù)的分析和預(yù)測并且具有強大的制作圖表的功能。它的界面更加直觀、操作更加簡單、使用更加方便，對用戶來說學(xué)習(xí)更加輕松，辦公也更加方便、快捷，特別適用于房地產(chǎn)投資分析中對數(shù)據(jù)的分析與處理。二、Excel的基本知識與基本操作（一）Excel窗口Excel啟動成功后，就出現(xiàn)了Excel窗口，如圖13-1所示，各組成部分的功能和作用如下：1. 標(biāo)題欄圖13-1

2、 Excel窗口的組成 與所有Windows的應(yīng)用程序一樣，標(biāo)題欄中顯示窗口的名字，并有控制按鈕可以讓用戶對窗口進行移動、關(guān)閉、縮小、放大、最大化、最小化等操作。（一）Excel窗口2. 菜單欄菜單欄中給出了若干菜單項，單擊某菜單項就會出現(xiàn)相應(yīng)的下拉菜單。3. 工具欄Excel的工具欄有很多種，主要的有：常用工具欄、格式工具欄、繪圖工具欄、Web工具欄、任務(wù)窗格等。這些工具欄可以設(shè)置成顯示或隱藏（選菜單【視圖】【工具欄】的相應(yīng)項可以調(diào)出），系統(tǒng)的默認設(shè)置是顯示常用工具欄和格式工具欄。4. 編輯欄編輯欄的左邊是名稱框，用于顯示活動單元格或區(qū)域的地址（或名稱），右邊是編輯區(qū)，可以在該編輯區(qū)輸入、修

3、改數(shù)據(jù)，如圖所示。 圖13-2 編輯欄（一）Excel窗口5. 狀態(tài)欄狀態(tài)欄位于屏幕的底部，用于顯示各種狀態(tài)信息以及其他非常有用的信息。例如，狀態(tài)欄經(jīng)常顯示信息“就緒”，它表明Excel已為新的操作準(zhǔn)備就緒；當(dāng)Excel正在執(zhí)行某一操作，如保存工作簿，狀態(tài)欄上就會有一個相應(yīng)的狀態(tài)指示器。6. Excel工作區(qū)在Excel工作區(qū)中顯示的是Excel工作簿窗口（即文檔窗口），在工作區(qū)中可以有一個或多個工作簿窗口，也可以沒有。工作簿窗口由標(biāo)題欄、工作表標(biāo)簽欄、列號標(biāo)志、行號標(biāo)志、水平和垂直滾動條以及工作表區(qū)域組成。當(dāng)Excel工作簿窗口最大化時，工作簿窗口和Excel應(yīng)用程序窗口共用一個標(biāo)題欄，而工

4、作簿窗口的控制按鈕則在Excel應(yīng)用程序窗口相應(yīng)控制按鈕的正下方。一個工作簿可以由多張工作表組成。啟動EXCEL后，工作簿窗口中，通常包含三張工作表。其中白色的工作表選項卡表示的是當(dāng)前工作表。在一個工作簿中當(dāng)前工作表只能有一張。用戶可以自行插入和刪除工作表從而改變工作表的張數(shù)。（二）Excel 的基本操作1. 選擇工作表要對某一個工作表進行操作，必須先選中（或稱激活）它，使之成為當(dāng)前工作表。操作方法是：用鼠標(biāo)單擊工作簿底部的工作表標(biāo)簽，選中的工作表以高亮度顯示，則該工作表就是當(dāng)前工作表。如果要選擇多個工作表，可在按鍵的同時，用鼠標(biāo)逐一單擊所要選擇的工作表標(biāo)簽。若要取消選擇，可松開鍵后，單擊其他

5、任何未被選中的工作表標(biāo)簽即可。圖13-3 Excel工作表的編輯如果所要選擇的工作表標(biāo)簽看不到，可按標(biāo)簽欄左邊的標(biāo)簽滾動按鈕。這四個按鈕的作用按自左至右次序為：移動到第一個、向前移一個、向后移一個、移動到最后一個。（二）Excel 的基本操作2. 工作表的重新命名在實際的應(yīng)用中，一般不要使用Excel默認工作表名稱，而是要給工作表起一個有意義的名字。有三種方法可以用來對工作表改名（見圖13-3）：先選擇一個工作表，然后選菜單【格式】【工作表】【重命名】項。用鼠標(biāo)右鍵單擊某工作表標(biāo)簽，然后從快捷菜單中選擇“重命名”。雙擊工作表標(biāo)簽。這三種方法都會使標(biāo)簽上的工作表名高亮度顯示，此時可以鍵入新名稱，

6、再按回車鍵即可。（二）Excel 的基本操作3. 插入工作表要在工作簿中插入新的工作表，可以選菜單【插入】【工作表】項（見圖13-3），這樣，一個新的工作表就插入在原來當(dāng)前工作表的前面，并成為新的當(dāng)前工作表。也可以用鼠標(biāo)右鍵單擊工作表標(biāo)簽，然后從快捷菜單中選擇“插入”項插入工作表。4. 刪除工作表要刪除一個工作表，先選中該表，然后選菜單【編輯】【刪除工作表】項（見圖13-3），此時彈出對話框要求用戶確認，經(jīng)確認后才刪除。同樣也可以單擊鼠標(biāo)右鍵，在快捷菜單中選擇“刪除”項。13-2 常用公式與函數(shù)一、公式（一）公式及其輸入一個公式是由運算對象和運算符組成的一個序列。它由等號（=）開始，公式中可以

7、包含運算符，以及運算對象常量、單元格引用（地址）和函數(shù)等。Excel有數(shù)百個內(nèi)置的公式，稱為函數(shù)。這些函數(shù)也可以實現(xiàn)相應(yīng)的計算。一個Excel的公式最多可以包含1024個字符。Excel中的公式有下列基本特性：（1） 全部公式以等號開始。（2） 輸入公式后，其計算結(jié)果顯示在單元格中。（3） 當(dāng)選定了一個含有公式的單元格后，該單元格的公式就顯示在編輯欄中。編輯公式與編輯數(shù)據(jù)相同，可以在編輯欄中，也可以在單元格中。雙擊一個含有公式的單元格，該公式就在單元格中顯示。如果想要同時看到工作表中的所有公式，可按（感嘆號左邊的那個鍵），可以在工作表上交替顯示公式和數(shù)值。一、公式（二）公式中的運算符Excel

8、的運算符有三大類，其優(yōu)先級從高到低依次為：算術(shù)運算符、文本運算符、比較運算符。1. 算術(shù)運算符Excel所支持的算術(shù)運算符的優(yōu)先級從高到低依次為：%（百分比）、（乘冪）、*（乘）和/（除）、（加）和-（減）。2. 文本運算符Excel的文本運算符只有一個用于連接文字的符號 &。例如：公式 =Computer &Center 結(jié)果：Computer Center3. 比較運算符Excel中使用的比較運算符有六個，其優(yōu)先級從高到低依次為：=（等于）、（大于）、=（大于等于）、（不等于）。比較運算的結(jié)果為邏輯值TRUE（真）或FALSE（假）。一、公式（三）單元格引用在公式中引用單元格或區(qū)域，公式的

9、值會隨著所引用單元的值的變化而變化。公式中可以引用另一個工作表的單元格和區(qū)域，甚至引用另一工作簿中的單元格和區(qū)域。若要引用另一工作簿的單元格或區(qū)域，只需在引用單元格或區(qū)域的地址前冠以工作簿名稱。（四）復(fù)制公式公式的復(fù)制與數(shù)據(jù)的復(fù)制的操作方法相同。但當(dāng)公式中含有單元格或區(qū)域引用時，根據(jù)單元地址形式的不同，計算結(jié)果將有所不同。當(dāng)一個公式從一個位置復(fù)制到另一個位置時，Excel能對公式中的引用地址進行調(diào)整。（四）復(fù)制公式1. 公式中引用的單元格地址是相對地址當(dāng)公式中引用的地址是相對地址時，公式按相對尋址進行調(diào)整。例如A3中的公式=A1A2，復(fù)制到B3中會自動調(diào)整為 =B1B2。公式中的單元格地址是相

10、對地址時，調(diào)整規(guī)則為：新行地址 = 原行地址 行地址偏移量新列地址 = 原列地址 列地址偏移量2. 公式中引用的單元格地址是絕對地址不管把公式復(fù)制到哪兒，引用地址被鎖定，這種尋址稱作絕對尋址。如A3中的公式=$A$1$A$2復(fù)制到B3中，仍然是 =$A$1$A$2。3. 公式中的單元格地址是混合地址在復(fù)制過程中，如果地址的一部分固定（行或列），其他部分（列或行）是變化的，則這種尋址稱為混合尋址。如：A3中的公式=$A1$A2復(fù)制到B4中，則變?yōu)椋?$A2$A3， 其中，列固定，行變化（變換規(guī)則和相對尋址相同）。4. 被引用單元格的移動當(dāng)公式中引用的單元格或區(qū)域被移動時，因原地址的數(shù)據(jù)已不復(fù)存在

11、。Excel根據(jù)它移動的方式及地點，將會出現(xiàn)不同的后果。 不管公式中引用的是相對地址、絕對地址或混合地址，當(dāng)被引用的單元格或區(qū)域移動后，公式的引用地址都將調(diào)整為移動后的地址。即使被移動到另外一個工作表也不例外。例如，A1中有公式=$B6*C8，把B6移動到D8，把C8移動到Sheet2的A7，則A1中的公式變?yōu)?$D8*Sheet2!A7。（四）復(fù)制公式5. 移動公式當(dāng)公式被移動時，引用地址還是原來的地址。例如，C1中有公式 =A1B1，若把單元格C1移動到D8，則D8中的公式仍然是=A1B1。6. 公式中的出錯信息當(dāng)公式有錯誤時，系統(tǒng)會給出錯誤信息。表13-2中給出了一些常見的出錯信息。表1

12、3-1 公式中常見的出錯信息出錯信息可能的原因#DIV/0！公式被零除#N/A沒有可用的數(shù)值#NAME？Excel不能識別公式中使用的名字#NULL！指定的兩個區(qū)域不相交#NUM！數(shù)字有問題#REF！公式引用了無效的單元格#VALUE！參數(shù)或操作數(shù)的類型有錯二、函數(shù)函數(shù)是隨Excel附帶的預(yù)定義或內(nèi)置公式。函數(shù)可作為獨立的公式而單獨使用，也可以用于另一個公式中甚至另一個函數(shù)內(nèi)。一般來說，每個函數(shù)可以返回（而且肯定要返回）一個計算得到的結(jié)果值，而數(shù)組函數(shù)則可以返回多個值。Excel共提供了九大類，300多個函數(shù)，包括：財務(wù)函數(shù)、數(shù)學(xué)與三角函數(shù)、統(tǒng)計函數(shù)、數(shù)據(jù)庫函數(shù)、邏輯函數(shù)等。函數(shù)由函數(shù)名和參數(shù)

13、組成，格式如下：函數(shù)名（參數(shù)1，參數(shù)2，）函數(shù)的參數(shù)可以是具體的數(shù)值、字符、邏輯值，也可以是表達式、單元地址、區(qū)域、區(qū)域名字等。函數(shù)本身也可以作為參數(shù)。如果一個函數(shù)沒有參數(shù)，也必須加上括號。函數(shù)是以公式的形式出現(xiàn)的，在輸入函數(shù)時，可以直接以公式的形式編輯輸入，也可以使用Excel提供的“插入函數(shù)”工具。1. 直接輸入選定要輸入函數(shù)的單元格，鍵入“=”和函數(shù)名及參數(shù)，按回車鍵即可。例如，要在H1單元格中計算區(qū)域A1：G1中所有單元格值的和。就可以選定單元格H1后，直接輸入=SUM（A1：G1），再按回車鍵。2.使用“插入函數(shù)”工具每當(dāng)需要輸入函數(shù)時，就選菜單中的 。此時會彈出一個“插入函數(shù)”對話

14、框，如圖所示。對話框中提供了函數(shù)的搜索功能，并在“選擇類別”中列出了所有不同類型的函數(shù)，“選擇函數(shù)”中則列出了被選中的函數(shù)類型所屬的全部函數(shù)。選中某一函數(shù)后，單擊【確定】按鈕，又會彈出一個“函數(shù)參數(shù)”對話框，其中顯示了函數(shù)的名稱、它的每個參數(shù)、函數(shù)功能和參數(shù)的描述、函數(shù)的當(dāng)前結(jié)果和整個公式的結(jié)果。3. 函數(shù)出錯信息當(dāng)輸入的函數(shù)有錯誤時，Excel會提示出錯信息，表13-2給出了幾種常見的出錯信息。出錯信息可能的原因#NAME！把文本作為函數(shù)的數(shù)值型參數(shù)#NUM！函數(shù)中出現(xiàn)非法數(shù)值參數(shù)#REF！函數(shù)中引用了一個所在列或行已被刪除的單元格#VALUE！函數(shù)中引用的參數(shù)不合適表13-2 函數(shù)出錯信息

15、三、房地產(chǎn)投資分析常用函數(shù)（一）財務(wù)函數(shù)1. 終值函數(shù) FV（Rate，Nper，Pmt，Pv，Type）其中：Rate利率Nper總投資期Pmt各期支付金額Pv現(xiàn)值Type只有數(shù)值0或1，0或忽略表示收款期末2. 現(xiàn)值計算函數(shù)PV（Rate，Nper，Pmt，F(xiàn)v，Type）其中，Rate，Nper，Pmt，F(xiàn)v，Type的含義與FV函數(shù)中的參數(shù)含義相同3. 償債基金和資金回收計算函數(shù)PMT（Rate，Nper， Fv ，Pv，Type）其中，Rate，Nper， Fv，Pv，Type的含義與FV和PV函數(shù)中的參數(shù)含義相同4. NPV函數(shù)NPV（Rate，Value1，Value2）其中：R

16、ate代表各期現(xiàn)金流量折算成當(dāng)前值的貼現(xiàn)率，在各期中固定不變；Value1，Value2；代表支出和收入的1到29個參數(shù)，時間均勻分布并出現(xiàn)在每期末尾。5. NPER函數(shù)NPER （Rate， PMT， Fv ，Pv，Type）其中：Rate利率Pmt各期支付金額Fv終值Pv現(xiàn)值Type只有數(shù)值0或1，0或忽略表示收款發(fā)生在期末，1表示收款發(fā)生在期初6. IRR函數(shù)IRR（Values，Guess）其中：Values一個數(shù)組，或?qū)?shù)字單元格區(qū)的引用；Guess內(nèi)部報酬率的猜測值。如果忽略，則為0.1。IRR函數(shù)計算的是返回一組現(xiàn)金流的內(nèi)部收益率。（二）統(tǒng)計函數(shù)1. AVERAGE（x1，x2，

17、）返回所列范圍中所有數(shù)值的平均值。最多可有30個參數(shù)，參數(shù)x1，x2，可以是數(shù)值、區(qū)域或區(qū)域名字。2. COUNT （x1，x2，） 返回所列參數(shù)（最多30個）中數(shù)值的個數(shù)。函數(shù)COUNT在計數(shù)時，把數(shù)字、文本、空值、邏輯值和日期計算進去，但是錯誤值或其他無法轉(zhuǎn)化成數(shù)據(jù)的內(nèi)容則被忽略。3. COUNTA（x1，x2，）返回所列參數(shù)（最多30個）中數(shù)據(jù)項的個數(shù)。在這里，“數(shù)據(jù)”是廣義的概念，計數(shù)值可以是任何類型，它們可以包括空字符（）。4. COUNTIF （x1，x2）計算給定區(qū)域x1滿足條件x2的單元格的數(shù)目。條件x2的形式可以為數(shù)字、表達式或文本。5. COUNTBLANK（x）計算指定區(qū)

18、域x中空白單元格的數(shù)目。說明：含有返回值為（空文本）的公式單元格也計算在內(nèi)，但包含零值的單元格不計算在內(nèi)。例如：假設(shè)工作表中的區(qū)域B2：C5中有兩個空單元（沒有輸入任何內(nèi)容），則COUNTBLANK（B2：C5）等于2。6. 求最大值函數(shù)MAX（List） 返回指定List 中的最大數(shù)值，List可以是一數(shù)值、公式或包含數(shù)字或公式的單元格范圍引用的表。例如，MAX（87，A8，B1：B5） MAX（D1：D88）。7. MIN（List） 返回List中的最小數(shù)。List的意義同MAX。例如，MIN（C2：C88）。8. 求和函數(shù)SUM（x1，x2，）返回包含在引用中的值的總和。x1，x2等可

19、以是對單元格、區(qū)域或?qū)嶋H值。如：SUM（A1：A5，C6：C8）返回區(qū)域A1至A5和C6至C8中的值的總和。9. SUMIF（x1，x2，x3）根據(jù)指定條件x2對若干單元格求和。其中：x1為用于條件判斷的單元格區(qū)域。x2 為確定哪些單元格將被相加求和的條件，其形式可以為數(shù)字、表達式或文本。（三）回歸分析函數(shù)Excel中提供了9個函數(shù)用于建立回歸模型和預(yù)測。這9個函數(shù)分別是：1. INTERCEPT：返回線性回歸模型的截距。2. SLOPE：返回線性回歸模型的斜率。3. RSQ：返回線性回歸模型的判定系數(shù)。4. FORECAST：返回一元線性回歸模型的預(yù)測值。5. STEYX：計算估計的標(biāo)準(zhǔn)誤。

20、6. TREND：計算線性回歸的趨勢值。7. GROWTH：返回線性回歸模型的參數(shù)。8. LINEST：返回線性回歸模型的參數(shù)。9. LOGEST：返回指數(shù)曲線模型的參數(shù)。13-3 房地產(chǎn)投資分析excel計算演示一、財務(wù)指標(biāo)（一）凈現(xiàn)值NPV的計算例1：某房地產(chǎn)開發(fā)項目，其各時期的現(xiàn)金流量如下表所示，年利率為5 %。就其凈現(xiàn)值。年份12345現(xiàn)金流量-10050200300350計算過程：1）建立一個工作表。選中單元格，輸入“=”，在函數(shù)類別中選擇“財務(wù)”，然后在“選擇函數(shù)”中選擇“NPV”。2）在“函數(shù)參數(shù)”對話框中“Rate”中輸入利率值5%，在Value中輸入所要計算的現(xiàn)金流量的區(qū)域。

21、3）顯示結(jié)果為643.925例2：某房地產(chǎn)企業(yè)預(yù)投資一房地產(chǎn)項目，預(yù)計投資期為5年。第一年投資費為2000萬元，以后逐年增加1000萬元，年利率為10%。試計算該項目的投資現(xiàn)值。計算過程：1）建立一個工作表。2）輸入年份，在B1中輸入1，按住Ctrl拖拽鼠標(biāo)直至出現(xiàn)5。3）輸入現(xiàn)金流量，在B2中輸入2000，在C2中輸入“= B2 1000”拖動F2右下角的填充柄至最后。4）計算凈現(xiàn)值，在E9中輸入“=”，在函數(shù)類別中選擇“財務(wù)”，然后在“選擇函數(shù)”中選擇“NPV”。在“函數(shù)參數(shù)”對話框中“Rate”中輸入利率值5%，在Value中輸入所要計算的現(xiàn)金流量的區(qū)域。單擊“確定”，在目標(biāo)單元格中顯示

22、結(jié)果14 1443.38。（二）內(nèi)部收益率IRR的計算例1：某房地產(chǎn)企業(yè)各年的收益的現(xiàn)金流量如下表所示，求該企業(yè)的的內(nèi)部收益率為多少。年份12345現(xiàn)金流量-10050200300350計算過程：1）建立一個工作表。按提示要求制作一個現(xiàn)金流量表。2）計算凈現(xiàn)值，在B3中輸入“=”，在函數(shù)類別中選擇“財務(wù)”，然后在“選擇函數(shù)”中選擇“IRR”。在“函數(shù)參數(shù)”對話框中的Value中輸入所要計算的現(xiàn)金流量的區(qū)域。單擊“確定”，在目標(biāo)單元格中顯示結(jié)果。二、風(fēng)險分析（一）盈虧平衡分析例1：某房地產(chǎn)項目今年計劃開發(fā)一個房地產(chǎn)項目，其建設(shè)期為1年，經(jīng)營期為2年。建設(shè)期投資30 000萬元，計劃建成后第一年為

23、設(shè)計銷售量的50%年，第二年達到設(shè)計銷售量4000戶，每戶售價50萬元，期間年經(jīng)營成本為35萬。求以產(chǎn)量、生產(chǎn)能力利用率、銷售價格、單位變動成本表示盈虧平衡點，并以產(chǎn)量為研究對象繪制盈虧平衡分析圖。計算過程：1）建立一個工作表。按提示要求制作一個表格（如圖所示）。2）在目標(biāo)單元格“C7”輸入“=C4/（C3-C5）”，單擊回車即會顯示結(jié)果。以下各項均按各自的公式輸入公式。得出各自結(jié)果，盈虧平衡產(chǎn)量為2000戶，盈虧平衡生產(chǎn)能力利用率50%，盈虧平衡價格為42.5萬元，盈虧平衡產(chǎn)品變動成本為42.5萬元。3）繪制盈虧平衡分析圖。為了作圖需要，我們應(yīng)界定銷售量的開始值和終止值。本例設(shè)開始值為0，終

24、止值為4000。點擊工具欄上的“圖表向?qū)А?，進入“圖表數(shù)據(jù)源”界面時選擇“系列”，點擊 編輯“系列”中的“名稱（N）”和“值（Y）”以及“分類（X）軸標(biāo)志（ ）”。將所有的選項編輯完成后，點擊“下一步”。在“圖表選項”中按提示編輯各個選項（如圖所示），等所有所需內(nèi)容編輯完成后點擊下一步，在圖表中編輯插入位置和所插入的工作表，之后點擊完成。（二）敏感性分析例1：某房地產(chǎn)項目今年計劃開發(fā)一個房地產(chǎn)項目，其建設(shè)期為1年，經(jīng)營期為2年。建設(shè)期投資30 000萬元，計劃建成后第一年為設(shè)計銷售量的50%年，第二年達到設(shè)計銷售量4000戶，每戶售價50萬元，期間年經(jīng)營成本為35萬。由于對未來影響經(jīng)濟因素把

25、握不大，投資額、經(jīng)營成本、銷售收入均有可能在20%的范圍內(nèi)變動。對上述三個不確定因素作單因素敏感性分析。計算過程：1）建立一個工作表。按提示要求制作一個編制敏感性分析表（如圖所示）。 2）繪制敏感性分析圖。單擊“圖表向?qū)А?，選擇折線圖（如圖所示），點擊下一步。進入“圖表數(shù)據(jù)源”界面時選擇“系列”，點擊 編輯“系列”中的“名稱（N）”和“值（Y）”以及“分類（X）軸標(biāo)志（ ）”。將所有的選項編輯完成后，點擊“下一步”。在“圖表選項”中按提示編輯各個選項（如圖所示），等所有所需內(nèi)容編輯完成后點擊下一步，在圖表中編輯插入位置和所插入的工作表，之后點擊完成。顯示所需敏感性分析圖三、項目預(yù)測與決策分析

26、（一）一元線性回歸分析例1：某房地產(chǎn)開發(fā)公司對過去一段時間內(nèi)，一個房地產(chǎn)項目的銷售額和花費在該項目上的廣告費用之間的關(guān)系進行分析，其有關(guān)歷史數(shù)據(jù)如下：年份廣告費銷售額年份廣告費銷售額199049.2537.6199549.2537.6199151.4706.2199651.4706.2199271.21073.9199771.21073.91993111.41209.31998111.41209.31994146.52055.21999146.52055.2 居于這些數(shù)據(jù)繪制成的圖形（圖8-1）大體上表明了兩者之間存在著線性依賴關(guān)系，現(xiàn)在需要確定銷售額與廣告費之間的回歸直線的截距和斜率，并需要確定當(dāng)廣告費用等于160與300時的銷售額。此外，還需要確定本問題中回歸直線的判定系數(shù)與估計誤差的標(biāo)準(zhǔn)。某房地產(chǎn)項目廣告費與銷售額對應(yīng)關(guān)系解：打開Excel工作表，輸入廣告費用觀測值（Xi）和銷售觀測值（Yi）。在