Excel在金融分析中的應用自學筆記

Excel在金融分析中的應用

自學筆記

金融學

Chapterl基礎財務計算

1.2現值（PV）和凈現值（NPV）

Excel有關現金流量折現的語言與標準的財務命名有些不同。Excel使用NPV表示一系列

現金流量的現值（不是凈現值）。

為了使用Excel計算系列現金流量的凈現值，我們必須計算未來現金流量的現值（使用Excel

的NPV函數）并從該現值中減去時間0的現金流量（這一般是問題中該資產的成本）。

▽用編+￡尚:

1.3內部收益率（IRR）和貸款表Q%?,笫二0

內部收益率（IRR）定義為使NPV=O的復收益率r：—

貸款表把該貸款的每年償還額分割為利息部分和本金部分，每年末的利息部分是用IRR乘上

本年初本金余額。注意最后一年年初本金完全等于該年年末的本金償還。

我們在實踐上可以用貸款表去尋找內部收益率?？紤]一項現在成為￥1000的投資，分別在第

1,2-5年年末支付，下圖表明該投資的IRR大于15%：

ABCDEFG

2成本1000

3IRR?15.00%

4

5

6貸款表

7償還的分割

卜

8|=-B2利息和本金

9\年初年末

10年7金償還利息本金

11110DO.OO3004150.00150.00?---------|=C11-D11|

122ajo.oo200/127.5072.50

133/T77.50150/116.6333.38

/744.13/111.62

144/600488.38

5/

15255.74900/38.36861.64

16-605.89_t\

17|=B11-E11|l=$B$3*B11I

18

在這個例子中我們增加了一個額外的單元（B16）,假如B3單元中的利率確實為IRR,那么B16

中應該為0.現在可以使用Excel中數據-假設分析-單變量求解來計算IRR：

一ABCDEFGHI

單變量求解―況

2成本1000

3IRR?15.00%

目標單元格(E)：畫］

4

目標值：

5(Y)o

可變單元格()：型畫

6貸款表￡

7

8|=-B2|\

9\年初年末

10年，:金償還利息本金____________.

111lobo.oo3004150.00150.00?--------|=C11-D11|

122ajo.oo200/127.5072.50

133/777.50150/116.6333.38

144/744.13600/111.62488.38

155/255.74900/38.36861.64

16X-605.89

17|L1I

18I

即可以得到如下結果，除此之外我們可以使用直接IRR函數簡化該計算:

ABCDEFG

1

2成本1000

3IRR?24.44%

4

5

6貸款表

7償還的分割

81=包|、利息和本金

9\年初年末

10年7金償還利息本金

111血0.003004244.3655.64<---------1=C11-D11|

122/4.36200/230.76-30.76

133^^75.13150/238.28-88.28

/1063.41/259.86

144/600340.14

15723.26900/176.74723.26

160.00力

17

18

19

20IRR的直接計算

21

22年現金流

230-1000

241300

252200

263150

274600

285900

29

30IRR24.44%<-=IRR(B23:B28)

1.4多個內部收益率

有時一組現金流量有多個IRR。在下面的例子中，我們可以看到在單元B8：B13兩個IRR,

因為NPV曲線與X軸相交兩次。Excel的IRR函數允許我們增加一個額外參數guess幫助我們

找到兩個IRR,通過調整guess,我們能計算出兩個IRR。

1多個內部收益率

2

3折現率6%

4NPV-3.99<-=NPV(B3,B9:B13)+88數據表

室

-6]一折現率NPV

7年現金流量-3.99

~8|0-1450%-20.00

911003%-10.51

10!21006%-3.99

11j31009%0.24

121410012%2.69

宿5-27515%3.77

1418%3.80

1521%3.02

1624%1.62

1727%-0.24

1830%-2.44

1933%-4.90

2036%-7.53

2139%-10.27

22

23注意：Excel中如何建立數據表

24的討論，請見本書第二十六章

25

26

27

28

29

30

31兩個IRR的確定

32第一個IRR8.78%<-=IRR(B8：B13.0.1)

33第二個IRR26.65%<-=IRR(B8:B13,0.5)

在這個處理中有兩件事情我們應該注意:

1.參數guess知識趨近IRR,它不是唯一的。

2.為了估計和IRR的近似值，按各種不同折現率函數做一個投資的NPV圖，內部收益率

則是曲線與X軸相交點，這些點附近位置的值可以用來作為IRR函數中的guess。

1.5等額償還計劃

已知貸款本金、利率、貸款期限，求在該貸款期限內等額返還貸款及利息，在這里可以

用PMT函數確定每年償還額應該是多少。注意“PV”的前面應加上符號（它表示初始的貸款

本金），否則將返回一個負的還款額。

B6，=f=PMT（B4,B5,-B3）一

ABCDE

1等額償還計劃

2\

31貸款本金10,000

4利率7%

5貸款期限6<-貸款被償還的結束年度

6年還款額2,097.96<-每年年末的還款額

1.6終值及其應用

在年初存入一筆存款后，在接后的幾年中每年存入相同的存款，如果每年都能獲得一定

利率的利息，那么最后一年年初我們可以得到多少錢。此問題可以通過FV函數進行運算。如

圖所示，FV（利率，期限，-每年初的存款，初始存款，期初或期末）

關于這個函數需要注意以下三點：

1.對正的存款FV返回一個負數。

2.對話框行Pv是指剛開始帳戶里的存款數。

3.對話框中我們還應該注意的是“Type”（其值或0或1），如果存款發(fā)生在期初的話值

為1,在期末的話值為0。

CHOOSE-xv/AT=FV(B3,A18,-1000,,1)

E1ABCDEFG

1每年存款的終值

2

3利率10%

4

5

6年賬戶年初每年初獲得的年末總的

7余額的存款利息賬戶余額

800.001,000100.00.1,100.00<—=D8+C8+B8

911,100.001,000210.00

1022,310,0(>L1,000331.003,641.001=$B$3*(C8+B8)|

~V\\33,641.00\1,000464.105,105.10

1245,105.10\1,000610.516,715.61

1356,715.61\1,000771.568,487.17

1468,487.17\1,000948.7210,435.89

15710,435.89\1,0001,143.5912,579.48

16812,579.48\1,0001,357.9514,937.42

17914,937.42\1,0001,593.7417,531.17

\

a1017,531.17

19l=E8I

201-------

21終值=FV(B3,A18,-1000,,1)18,-1000,,1)

1.7年金問題一一復雜終值問題

目前你55歲，為了使退休后能過上安逸的生活，在0,1,2,…，4年的年初，每年在

退休金帳戶中存入一筆錢，該帳戶每年有8%在盈利；在60歲退休后，你與其還能活8年以

上。在這些年中每年提取30000元，帳戶余額將繼續(xù)獲得8%的盈利。

第一種方法是Excel的“規(guī)劃求解”，我們可以在菜單“工具”中找到它（數據一分析一

規(guī)劃求解）

第二種方法是用財務公式解決退休金問題。

■IA_—：匚一.LaL_Q1__aH_r^i二」一j

1一個退休金問題

2

199107.312.91-30,0006,185.0383,497.94

201083,497.94-30,0004,279.8457,777.78

211157,777.78-30,0002,222.2230,000.00

221230,000.00-30,0000.000.00

23

24分子126,718.54〈一=1/（1+B3）A4*PV（B3,8,-30000）

25分母4.31<-spv（B3,5,-1?1）

1.8連續(xù)復利

在Excel中寫入函數EXP即可計算連續(xù)復利帳戶余額，寫法如下：初始存款*EXP（利率）

=用EXP計算的連續(xù)復利帳戶余額

若想計算價格數據的連續(xù)復利收益，可以運用公式LN（年末存款/初始存款），若計算隱含的

年利率可通過公式（（年末余額/初始存款）“（1/連續(xù)復利的期數）-1）*連續(xù)復利的期數

Chapter2資本成本計算

2.1引言

現金流量折現（DCF）方法是企業(yè)應用最為廣泛的價值評估方法。

本章中，我們將討論如何計算該企業(yè)的資本成本和折現率。我們考慮兩個計算權益成本

的模型（權益成本是用來折現股權現金流的）：

?戈登（Gordon）模型基于該企業(yè)的預期股利來計算權益成本。

?資本資產定價模型（CAPM）基于該企業(yè)的權益收益與一個大的、分散化的市場證券投資組

合的相關性來計算權益成本。

2.2戈登（Gordon）股利模型

一股票的價值是該股票未來預期股利流量的現值，該未來預期股利是按適當風險調整權

益成本折現的。

用Di表示下期預期股利，用g表示股利的預期增長率，r為折現率：

R=宙+d+荷'田廠1..…

_VD*(l+g尸__g兒I”

一汽(l+“一店―g'假以：⑷'、在

注意后面的附帶條件：為了在該公式的無限項和有解，股利增長率一定要小于折現率。

2.2.1“超速增長”與戈登模型

注意，假如條件回<4,該公式Po=Di/(eg)的結果為負。但這并不是說股票價值就是負值；

它只是說明基本條件被違反了。在財務實例中，|g|<rE條件的破壞，通常是發(fā)生在高速成長

的公司一一至少短期內是這樣的一一我們預期有非常高的增長率，使得g>k在這情況下，

原始股利折現公式的Po就會有一個無窮大值。由于該結果明顯不合理，這可能有兩種情況：

(1)長期增長率小于折現率4，或(2)折現率陛太小。

2.2.2具有常數增長率的戈登模型

在這個模型中Po=D1/(rE-g),我們可以調整該公式以得到權益成本陛：

也=為卜預期股票增長率=腎+門

總刖服親m情re

通常我們假設Di=Do(l+g),這里Do是公司最近支付的股利；在這情況下戈登模型寫

為_幾(1十.十/

S—R十g

2.3用戈登模型計算艾博特實驗室的權益成本

2.3.1戈登模型中的增長率選擇

在戈登公式中，增長率g是預期股利增長率，它不一定等于股利的歷史增長率。因此判

斷這個“適當”的增長率就取決于你對該公司能夠并愿意在未來支付股利的預期。

2.4資本資產定價模型

資本資產定價模型(CAPM)是戈登模型計算資本成本的另一種可行的替代方法。CAPM

從公司收益和市場收益的協方差中推導出公司的資本成本。

2.5使用證券市場線(SML)計算艾博特實驗室的權益成本

2.6計算負債成本

?實際中，負債成本常常用公司現存負債的平均成本來近似代替。盡管這種方法很容易

使用，但有時我們會把實際想衡量的未來預期負債成本與過去的成本弄混。

?我們使用同等風險公司的證券收益。如果一家公司的信用級別為A級并且它的大部分

負債是中期負債，那么我們使用A級中期債券平均收益作為公司的負債成本。注意這種方法

具有一定的復雜性，因為債券的收益是它的承諾收益，而負債成本是一家公司負債的期望收

益。因為這里存在著違約風險，所以承諾收益一般要高于期望收益。

?通過估計公司負債的B值,CAPM可以用于資本成本的計算。我們可以使用證券市場線

（SML）估計公司的負債成本。原則上，這種方法，可類似用于公司的權益，然而正如我們將

要說明的一一在實際應用中，我們需要許多簡化。

?我們可以使用模型通過公司的債券價格數據來估計負債成本，估計違約的可能性，估

計在違約的情況下對股票持有人的償還。這個方法需要許多運算，其數學方法也較為復雜；

實際中，只有正在分析的公司有大量的風險負債時，我們才用這種方法計算資本成本。

2.7計算艾博特實驗室的負債成本

2.8加權平均資本成本（WACC）

2.9當模型不適用的時候

291戈登模型的問題

假如一家企業(yè)不支付股利或在未來的一段時間內，也沒有馬上支付股利的意向。顯然戈

登模型不能運算。

即使對支付股利的企業(yè)，應用該模型也可能很難。許多時候，特別棘手的問題是要從過去股

利中得出將來的股利支付率。

Chapters租賃的財務分析

5.3租賃和公司融資：約當貸款法

采用約當貸款法是要設計一筆虛擬貸款，該貸款以某種方式等于租賃，以便很容易的看

出資產租賃和購買哪一個更好。

5.4出租人問題：計算最小的可接受租賃租金

出租人的問題是與承租人相反的：

承租人的決策是對于給定的租金率，購買還是租用資產哪個更好；出租人的決策是以什

么樣的最小租金率將該資產租出去。我們可以用單變量求解，將租賃與購買的現金流量差額

IRR設置目標值為稅后利率，以此求得租賃租金支付額。

ABCDEFGH

1計算最小的可接受的租賃租金

2

單變量求解

3資產成本540,000

4利率12%目標單元格（玲：$B$23［匿］

5租賃租金支付121,047＜一耳目標值（Y）：|7.44%______________

6每年的折舊90,000

可變單元格（C）：|$B$5［摩］

7稅率38%

ft744%f..i?.一?

Excel中的規(guī)劃求解和單變量求解：

1.這些Excel工具對話框目標值單元格不接受公式。

2.可能需要多次使用重新計算功能，指定足夠的精度以便在單元格中得到目標值，如果不能

得到一個可接受的答案，我們可以將最大誤差設為一個較高的值。

3.單變量求解不記憶它的存取，因此不得不每次重新輸入數據。

Chapters杠桿租賃的財務分析

6.1引言

杠桿租賃分析的兩個主要問題是：

1.以出租人觀點來看的租賃直接財務分析。它關心的是出租人獲得的現金流量的計算，

和這些現金流量的凈現值（NPV）或內部收益率（IRR）的計算。

2.租賃的會計分析。會計人員使用多階段法（MPM）計算杠桿租賃的收益率。MPM的收

益率與內部收益率不同。普通的財務教科書對這個差異不重視，因為有效市場的假設告訴我

們只有現金流才是重要的。在弱有效的市場中，人們趨向于關心財務報表上反映的情況。因

為租賃會計收益率計算很難，所以我們使用Excel來計算它，然后再分析它的結果。

6.2一個例子

典型長期杠桿租賃的現金流量通常在租賃期開始階段是正的，然后隨時間下降為負，當

收到殘值時，再轉為正的。該現象有三個原因：

1.在租賃期結束之前，來自折舊的現金流量有停止或快速地下降的特點。在資產生命期

開始階段，折舊越快，計提折舊就越大（因此折舊避稅也越大）。

2.在租賃的后期年度中，每年償還金額中利息所占（可抵稅的）的比例下降，本金部分

所占（不能抵稅）的比例上升。

3.當然，最后，我們預期在租賃期結束時資產殘值變現會產生一個大的現金流入。

杠桿租賃的現金流量

現

金

流

1量

-200,C00

6.3用NPV或IRR分析現金流量

我們如何處理這些現金流量？一種方法（可能是最好的，至少在理論上）是以適當風險

調整折現率來折現它們以得到凈現值（NPV）。如果我們分析現金流量的組成部分，我們可以

看到主要的風險來自以下三個方面：

1.承租人可能在租金上違約。

2.稅率變化，影響折舊避稅和利息支付的現金流量。

3.殘值非常不確定。

不同利率水平下杠桿租賃現金流量的NPV

120,000

o

-20,000附

出租人通常不用凈現值。他們喜歡用內部收益率來衡量該租賃的可行性。因為租賃的現金流

量的正負號要變化兩次，它——原則上——可能有兩個IRRo既然IRR是NPV曲線圖與x軸相

交的利率，我們可以用Excel作圖來確定有幾個IRR：

部收益率是12.46%o

6.4IRR的解釋

IRR的意義涉及兩個方面：一是內部收益率的經濟含義；二是杠桿租賃收益的會計收入的

確定。

6.5杠桿租賃的會計處理：“多階段法”

財務會計標準委員會制定的第13條規(guī)定（FASB13）要求杠桿租賃的出租人在收益和投

資之間分配租賃現金流量。合理的方法是使用租賃現金流量的IRRo但是這里，FASB13的精

神顯然和人的天性沖突，人們不喜歡記錄虧損，即使它在經濟學上是許可的。

避免負收益報告的方法，有時稱為多階段方法（MPM）,而稱為“變通的IRR方法”可能更好。

年MPM解釋

出租人在第1年的投資等于她在租賃權益的初蛤投資，

]它與JRR的計算相同

在我們的例子中，投資（1）=$200,000

出租人在第r年底來自租賃的會計收入是如果收入是正的,MPM遵循把現

ZJQ*投資。）如果數字>0）金流分為投資收益和投資回收的

1C其他/標準/RR方法否則收益設置為0

出租人在任何/>1年期初的租賃投資為投資

遵循1RR方法

t=投資。一】）一］現金流量”一1）一收益"—1）］

最后一年現金流量（最后一年）=投資（最后一年）*（1+Q）類似于1RR方法

6.6MPM收益率與IRR的比較

MPM收益率廣泛地應用于杠桿租賃。如何將它與IRR比較呢？

?一般來說，MPM收益率小于或等于IRRo如果所有租賃現金流量都是正數，MPM收

益率則等于IRR,否則MPM收益率小于IRRo

?如果MPM收益率小于IRR,那么此時IRR表明該租賃的收益是負的，而MPM則表明

該租賃將會有零收益。

0川

30,

況)o

0o

)o

20,0o)o

收15,

0o)o

1Glo

益5

(0o

$o

)

cOO

>

-5,000

-10,000

Chapter7投資組合模型

7.1概述

在本章中，我們學習了投資組合計算的基本技術。每項資產i（這里是股票，但它們可以

是股票也可以使債券、不動產或其他的資產）用兩個統(tǒng)計量來描述：E（n）,資產i的預期收益；

Cov（r“j）,資產i和資產j收益的協方差。在實際應用中，為了方便，常將Cov（rijj）寫成6”,

2

將Var（n）寫成6五（通常用6i）o

7.2一個簡單的兩資產組合的例子

假如我們有兩只股票12個月度的價格數據：股票A和股票B。數據如下

E1ABC

1股票價格

2月股票A股票B

3025.0045.00

4124.1244.85

5223.3746.88

6324.7545.25

7426.6250.87

8526.5053.25

9628.0053.25

10728.8862.75

11829.7565.50

12931.3866.87

131036.2578.50

141137.1378.00

151236.8868.23

該數據是月底每只股票的收盤價。月0是股票的開盤價(也就是上一個月底的收盤價)。

我們希望計算每只股票收益的統(tǒng)計量。

首先我們計算每只股票的月收益，在Excel中很容易實現該收益的計算。適當的公式設置

如下：

18收益的計算

Wj股票A股票B

20J月價格收益價格收益

到025.0045.00

22124.12-3.58%44.85-0.33%

23]223.37-3.16%46.884.43%<-=LN（E23/E22）

24]324.755.74%45.25-3.54%

25426.627.28%50.8711.71%

26]526.50-0.45%53.254.57%

27]628.005.51%53.250.00%

28728.883.09%62.7516.42%

至829.752.97%65.504.29%

30931.385.33%66.872.07%

311036.2514.43%78.5016.03%

32J1137.132.40%78.00-0.64%

331236.88-0.68%68.23-13.38%

現在作一個大膽的假定：我們假設12個月的收益數據代表了下一個月收益分布，為此我

們假設從過去有關收益的信息可以得到一些收益未來走勢的信息。

用Excel中的Average。，Varp()和Stdevp()函數，我們可以得到該收益分布的統(tǒng)計量:

34ABCDEFGHI

35月平均值3.24%3.47%<-=AVERAGE（F22：F33）

36月方差0.23%0.65%<-=VARP（F22：F33）

37月標準差4.78%8.03%<-=STDEVP（F22：F33）

38

39年平均值38.88%41.62%<-=12*F35

40年方差2.75%7.75%<-=12*F36

41年標準差16.57%27.83%<-=SQRT（F40）

接下來我們要計算收益的協方差。協方差(與來自它的相關系數)是用來衡量兩個資產

收益之間的線性相關程度的。它的計算公式為：

Cov(rA,r?)==j",一Eg)」*EOG]

這里M是收益分布數據的個數(在我們的例子中，M=12)o這個公式很容易在Excel中

建立。

43ABCDEFGHIJ

-44|小方差和方差的計算

-45I股票A股票B/=D484BF$35I

46收益收益-均值收益收益-均值乘積

47/

J8j-0.0358-0.0682-0.0033-0.03800.00259<-=E48*B48

49-0.0316-0.06400.04430.0096-0.00061

500.05740.0250-0.0354-0.0701-0.00175

510.07280.04040.11710.08240.00333

J2j-0.0045-0.03690.04570.0110-0.00041

J3j0.05510.02270.0000-0.0347-0.00079

540.0309-0.00150.16420.1295-0.00019

1510.0297-0.00270.04290.0082-0.00002

J6j0.05330.02090.0207-0.0140-0.00029

-57I0.14430.11190.16030.12570.01406

J8j0.0240-0.0084-0.0064-0.04110.00035

59-0.0068-0.0392-0.1338-0.16850.00660

~60l

61協方差0.00191<-=AVERAGE(G48:G59)

620.00191<-=COVAR(A48:A59,D48：D59)

63相關系數0.49589<-=G62/(F37*C37)

640.49589<-=CORREL(A48：A59,D48：D59)

Excel有一個數組函數Covar(Arrayll,Array2)能直接計算協方差。用Covar計算協方差，不

必計算收益和均值之間的差額。直接在列上使用Covar就可以了，如電子表單元格G62所表

明的。我們還可以計算相關系數PAB,它定義為：

Cnv(r

pAfi-4

相關系數是一個無量綱數；正如我們要介紹的，相關系數可以使用Excel中的函數

Correl(Arrayl,Array2)直接計算，這里參數數組是列向量，用于函數Correl的協方差計算。

相關系數衡量的是股票A和股票B收益間的線性相關程度。下面是有關該相關性的說明：

?相關系數總是在+1和T之間：TWPABWI。

?如果相關系數是+1,那么兩資產的收益是線性正相關；也就是說，如果PAB=1,則

rAt=c+drBt,這里d>0.

?如果相關系數是T,那么兩資產的收益是線性負相關；也就是說如果PAB=T,則

rAt=c+drBt,這里d<0.

?如果收益分布是獨立的，那么相關系數將會是零(反過來就不對了；如果相關系數是

零，它并不必然地意味著收益是獨立的)。

7.3計算投資組合的均值和方差

一般投資組合的平均收益是組成股票的加權平均收益。如果我們用r來表示投資在股票A

上的比率，那么：E5)=yE(r&)l-Cl-y)E(rB)

但是，投資組合的方差卻不是股票兩個方差的平均數！方差公式是：

VarCr,)—/Var(rA)-f<l—Vmr(.)T2y(l-y)Cov(rzl?曲)

這個關系的另一種寫法是：g=名+(1一力'.+27(1一7的空心“0

我們經常要做的就是畫出不同投資組合比例Y下均值和標準差的圖形。為此我們使用

Excel的數據|模擬運算表建一個數據表：

ABCDE

23比率標準差均值

245.60%3.35%表頭：V-:=E18

2508.03%3.47%

260.0757.62%3.45%

270.157.21%3.43%

280.2256.82%3.42%

290.36.46%3.40%

300.3756.11%3.38%

310.455.80%3.37%

~32\0.5255.51%3.35%

330.65.26%3.33%

340.6755.06%3.31%

350.754.90%3.30%

360.8254.80%3.28%

370.9