EXCEL金融計算實驗指導

1、金融學實驗指導手冊EXCEL 金融計算南京審計學院金融學院前 言本實驗指導手冊為金融學院金融學、證券投資學課程配套書。該實驗指導手冊側(cè)重于培養(yǎng)學生應用金融學、證券投資學課程所學的基本原理，利用EXCEL軟件為計算工具，分析各種金融工具的風險與收益能力。全手冊共分三部分。第一部分復習現(xiàn)值、終值、年金等財務概念，了解EXCEL工具環(huán)境與常用財務函數(shù)的使用，關于債券、股票等原生工具的定價，利用久期模型分析債券利率風險。第二部分關于現(xiàn)代投資理論構造資產(chǎn)組合，通過計算加深對有效前沿、資本市場線和證券市場線等概念的理解。第三部分關于衍生工具的定價方法，二項式期權模型定價、布萊克-斯科爾斯期權定價模型的EX

2、CEL實現(xiàn)與VBA編程計算。實驗一 更有效地使用EXCEL實驗目的與要求：熟悉EXECL的運行環(huán)境，掌握數(shù)據(jù)導入方法，掌握圖表與數(shù)據(jù)透視表的使用，了解常用函數(shù)的功能與使用方法。實驗指導：一、概念釋義：1、數(shù)據(jù)輸入與運算在Excel環(huán)境下進行計算所需要的數(shù)據(jù)大致有3種來源：手工輸入、自動生成和從外部導入。當數(shù)據(jù)量很小而且又沒有規(guī)律的情況下,一般采用手工輸入的方法。而對于那些有規(guī)律的數(shù)據(jù)，如連續(xù)的數(shù)字或字符序列可以用Excel的填充命令自動生成。當數(shù)據(jù)量比較大或有現(xiàn)成的數(shù)據(jù)來源如網(wǎng)絡資源、數(shù)據(jù)庫資源等可供應用，可以采用導入的方法。 Excel可以讀入不同來源、不同格式的多種數(shù)據(jù)文件。執(zhí)行Excel

3、上的“文件”打開”命令，就可以直接讀人數(shù)據(jù)庫文件、Web文件、XML文件、文本文件以及其他格式的電子表格 文件等。對于連接在網(wǎng)絡局域網(wǎng)、廣域網(wǎng)或Web)上的計算機，這些文件可以保存在網(wǎng)絡上的任何位置只要使用者具有訪問權限都可以接讀入Excel。在讀入非Excel格式文件時，Excel會自動將文件轉(zhuǎn)換成為工作表格式。對于某些格式的數(shù)據(jù)，在進行這種格式轉(zhuǎn)換時，可能需要用戶做出一些選擇，如在讀入文本文件(txt、rtf)時。需要用戶指定分列方式可能的方式包括按固定寬度分列或按指定字符分列。 Excel工作表中的數(shù)據(jù)有兩種基本情況：由用戶輸入或?qū)氲臄?shù)據(jù)：經(jīng)過計算得到的數(shù)據(jù)。用戶直接在單元格中輸入的數(shù)

4、據(jù)屬于前者，而后者則是通過公式或函數(shù)進行計算后得到的。在Excel中對于后面一類統(tǒng)稱為公式，它是由等號開始，包括常量、對單元格或單元格區(qū)域的引用、運算符和函數(shù)等幾種成分。在Excel中通過運算符可以執(zhí)行多種運算：算術運算、文本運算、日期運算、比較運算。2、圖表與數(shù)據(jù)透視表圖表是使用Excel進行各種運算分析時一個非常有用的工具通過圖表，可以更加直觀地揭示數(shù)據(jù)之間的內(nèi)在關系和變化趨勢。本書中使用了大量的圖表作為分析手段來說明各種金融財務原理和表示計算結果。在Excel中創(chuàng)建圖表比較簡單可以通過其內(nèi)置的圖表向?qū)碜詣由筛鞣N不同類型的圖表。單擊常用工具欄上的按鈕即可啟動圖表向?qū)?。使用圖表向?qū)е谱鲌D

5、表分為4個步驟：選擇圖表類型確定源數(shù)據(jù)設定圖表選項安排圖表位置。正確有效地制作圖表關鍵在于前兩步，首先要根據(jù)數(shù)據(jù)的性質(zhì)和圖表的目的選擇合適的圖表類型其次要正確地設置源數(shù)據(jù)區(qū)、橫縱坐標軸與數(shù)據(jù)系列標題。在圖表選項中主要是對諸如圖表標題、坐標軸標題、圖表網(wǎng)格線、圖例、數(shù)據(jù)標志等格式方面的要素進行設定，最后可以選擇圖表放置的位置，即可以放置在源數(shù)據(jù)所在工作表內(nèi)也可以建立單獨的圖表工作表。 數(shù)據(jù)透視表是Excel中一項比較重要的高級功能。Excel的工作表本身是一個二維表，如果要用常規(guī)的Excel工作表表示具有多個維度的數(shù)據(jù)，就只能把數(shù)據(jù)”攤平”。然而，在實際應用中經(jīng)常需要同時按照多個方向?qū)ν唤M數(shù)據(jù)

6、進行分析。例如在分析企業(yè)銷售收入的歷史數(shù)據(jù)時可能既要按照不同的時間如年份或季度來統(tǒng)計，又要按照部門或地區(qū)來統(tǒng)計甚至會把幾個維度結合在一起分析，這時就需要使用Excel的數(shù)據(jù)透視表。如果用一句話來概括數(shù)據(jù)透視表的本質(zhì)那就是數(shù)據(jù)透視表可以在二維的表格里表示三維或者多維的數(shù)據(jù)。3、內(nèi)置函數(shù)和自定義函數(shù) ExceI作為商務計算的工具軟件，其計算功能是通過函數(shù)和內(nèi)置工具實現(xiàn)的。函數(shù)是構成Excef計算公式的主要元素之一。在一般性的計算應用和數(shù)據(jù)管理當中函數(shù)承擔著絕大部分的工作。ExceI大約內(nèi)置有330個左右的函數(shù)，這些函數(shù)按照功能和作用的不同，可以分成11大類。包括：數(shù)據(jù)庫函數(shù)日期和時間函數(shù)，外部函數(shù)

7、、工程函數(shù)、財務函數(shù)，信息函數(shù)，邏輯函數(shù)、查找和引用函數(shù)，數(shù)學和三角函數(shù)，統(tǒng)計函數(shù)以及文本和數(shù)據(jù)函數(shù)。 所謂自定義函數(shù)是指用戶根據(jù)自己的需要通過Excel內(nèi)置的VBA(Visual Basic for Application)功能模塊編寫代碼，設計出實現(xiàn)特定功能的函數(shù)。4、假設分析工具方案是保存在工作表當中并可以在相互之間進行切換的數(shù)據(jù)。當需要調(diào)整運算參數(shù)觀察計算結果而又希望保存這些不同結果時，方案管理器是一個合適的工具。所謂敏感分析是指通過多次使用不同的參數(shù)，對某個或一組公式的運算結果進行綜合比較，從而了解不同參數(shù)在其可能的取值范圍內(nèi)對運算結果的影響程度。模擬運算表是一個理想的敏感分析工具，

8、它充分發(fā)揮表格界面和運算公式的優(yōu)勢，用表格的形式表示出評價指標對分析指標的依賴程度。已知某個公式的運算結果，要求確定其中某個參數(shù)的適當值，這種問題在數(shù)學上稱為反函數(shù)。在EXCEL中，提供一個專門處理這類問題的工具單變量求解。二、操作示例 假定某投資項目，投資金額為10 000，項目周期為5年。項目的預期現(xiàn)金流量和貼現(xiàn)率都受經(jīng)濟形勢的影響：如果經(jīng)濟不景氣，則預期每年現(xiàn)金流量為1 200，貼現(xiàn)率為6；如果經(jīng)濟繁榮，則預期每年現(xiàn)金流量為2000貼現(xiàn)率為1 O；如果經(jīng)濟形勢一般，則現(xiàn)金流量為1 600。貼現(xiàn)率為8。要求對該項目進行評價。37實驗二貨幣的時間價值實驗目的與要求：貨幣的時間價值是一切金融計

9、算的基礎，在本實驗中關鍵的概念是復利和貼現(xiàn)。通過實驗要求學生掌握EXCEL常用財務函數(shù)使用。實驗指導：一、概念釋義： 現(xiàn)值是指未來預期現(xiàn)金流現(xiàn)在的價值，凈現(xiàn)值是現(xiàn)值減去初始成本?，F(xiàn)值和凈現(xiàn)值問題是學習資金時間價值的開始，理解資金時間價值含義并利用Excel實現(xiàn)其計算過程是學好本書的良好開端。 1現(xiàn)值 若已知T期末將擁有一現(xiàn)金流CT，則其在折現(xiàn)率r下的現(xiàn)值為：PV=CT/（1+r）T 2.凈現(xiàn)值 凈現(xiàn)值是未來的現(xiàn)金流的現(xiàn)值減去初始的投資現(xiàn)金流。 3.終值 指現(xiàn)金流在未來某時刻的價值。 4年金 指一系列有穩(wěn)定規(guī)律的、持續(xù)一段固定時期的現(xiàn)金收付活動。 注：4個變量參與運算，RATE（），NPER（）

10、，函數(shù)名為計算內(nèi)容。二、操作示例例1某投資項目能在未來4年產(chǎn)生90元，100元，110元和80元的確定現(xiàn)金流，折現(xiàn)率是8%，求該投資的項目的現(xiàn)值。（多重現(xiàn)金流）步驟1步驟2步驟3步驟4例2、假設今年年初將1000存入銀行，并將在以后的9年中每年年初存入1000元，那么第十年年初的銀行存款余額為多少？（終值）例3、假設某某客戶為購房，申請了100000元的房屋抵押貸款，貸款的年利率6%，銀行要求10年內(nèi)還清，則每年末款多少？例4、計息期與連續(xù)利率例5 某項目投資300萬元后，將在未來4年每年獲100萬元的現(xiàn)金流入，采用的貼現(xiàn)率為10%，決策該項目可行。（內(nèi)部收益函數(shù)，單變量方程）實驗三 債券利率

11、風險評估 實驗目的與要求：債券的主要風險為利率風險，久期和凸性是刻畫債券的兩個重要特征。本實驗要求熟悉債券的久期概念，掌握久期的計算。（市場風險、信用風險）實驗指導：一、概念釋義1、債券的價值與收益?zhèn)挠嬎阒饕P注的兩個指標價格和收益率，其基本原則是貼現(xiàn)現(xiàn)金流。2、債券的久期久期是指固定收益?zhèn)F(xiàn)金流支付時間的加權平均。權重是指每次支付的現(xiàn)金流的現(xiàn)值在債券總價中所占的比例。久期作為債券價格相對貼現(xiàn)率的彈性，3、修正久期修正久期越大債券的價格變化幅度越大，利率風險越高。設D為久期，r為貼現(xiàn)率（市場利率），則修正久期（MD）可定義為D/（1+r）由，可得二、操作示例1、債券價格計算（債券票面利率

12、與市場利率關系）2、債券久期計算（原理）3、久期度量債券的價格相對貼現(xiàn)率的彈性（求導）4、 EXCELL久期函數(shù)計算久期5、修正久久期（將修正期與市場利率的預期相結合對債券價格變化率進行估計） 6、債券價格近似計算實驗四 投資組合模型計算 實驗目的與要求：掌握現(xiàn)代投資理論及其實現(xiàn)過程，學會證券的收益率矩陣和數(shù)字特征計算；計算投資組合的有效前沿，資本資產(chǎn)定價理論的EXCELL實現(xiàn)。實驗指導：一、概念釋義股票的月收益率計算分為連續(xù)收益率和離散收益率兩種，連續(xù)收益率為；離散收益率為；在考慮股利收入條件下，連續(xù)收益率為，離散收益率為期望收益是指持有股票的投資者在下一個時期所能獲得的收益。單個股票的期望

13、收益可通過歷史數(shù)據(jù)的樣本均值來估計。資產(chǎn)隨機收益率的方差是偏離預期的期望，標準差是方差的平方根。方差、標準差刻畫收益率變動，是風險的常用度量指標。協(xié)方差是度量兩種風險資產(chǎn)收益之間線性關聯(lián)度的統(tǒng)計指標，正協(xié)方差收益為同向變動，負協(xié)方差收益為反向變動。相關系數(shù)刻畫兩種隨機變量的線性關聯(lián)度。二、操作示例1、期望收益的計算（1）計算股票的每月收益率（2）計算股票的月期望收益率（3）計算股票的年收益率2、方差與標準差在EXCELL中方差，樣本方差，標準差，樣本標準差分別用VAR、VARP、STEDV、STEDVP。通過EXCELL中的工具欄fx/統(tǒng)計。3、協(xié)方差、相關系數(shù)選擇fx/統(tǒng)計/covar命令，

14、對話框中選擇數(shù)據(jù)區(qū)域，完成后單擊確定。選擇fx/統(tǒng)計/correl命令，對話框中選擇數(shù)據(jù)區(qū)域，完成后單擊確定。4、投資組合的期望收益和方差計算運用EXCEL中的模擬運算表可計算出兩種股票任意投資組合的期望收益和方差。（1） 建立工作表（2） 選定需要計算的單元區(qū)域（3） 單擊數(shù)據(jù)菜單中的模擬運算表（4） 單擊確定使用EXCEL的圖表功能繪制標準差收益曲線。5、多個資產(chǎn)投資組合（1）計算風險資產(chǎn)構成的給定組合的數(shù)字特征（2）由兩個組合構成標準差期望收益曲線（3）描繪資產(chǎn)在坐標中的位置（4）利用模擬運算表求不同投資比例下的期望收益與標準差6、超額收益法計算方差協(xié)方差矩陣（1）計算每個資產(chǎn)的期望收益

15、率（2）計算超額收益矩陣（3）計算超額收益轉(zhuǎn)置矩陣（4）計算方差協(xié)方差矩陣實驗五 有效前沿與資本資產(chǎn)定價模型實驗目的與要求：均值方差與有效前沿理論是現(xiàn)代投資理論的基本內(nèi)容，本實驗在上一個實驗的基礎上探討有效前沿與資本資產(chǎn)定價模型的計算問題。實驗指導：一、 概念釋義1、 可行投資組合：滿足投資比例之和為1的組合；2、 可行集：由所有可行投資組合構成的集合；3、 前沿投資組合：對于任意給定的期望收益水平，所有具有最小方差的投資組合構成的集合；4、 有效投資組合：對于任意給定的方差或標準差，具有最大收益率的投資組合；5、 有效前沿：由所有有效投資組合構成的集合；6、 資本市場線（CML）：指在標準差

16、期望收益坐標系中無風險資產(chǎn)和有效前沿曲線的切線，CML刻畫了在存在無風險證券且無借貸限制時的投資組合有效前沿，此時有效前沿退化為直線。7、 證券市場線（SML）刻畫任何資產(chǎn)或投資組合的期望收益與其對應的貝塔系數(shù)之間的線性關系。二、 操作示例1、 有效前沿計算（1） 資產(chǎn)組合（2） 兩個前沿組合的計算=MMULT(MINVERSE(C2:F5),E8:E11) =F8/SUM(F$8:F$11)3、市場組合M的計算與繪制 假設有效組合N和無風險資產(chǎn)的投資比例為a,則新的資產(chǎn)組合P的期望收益和標準差分別為： 和 4、證券市場線計算與繪制貝塔系數(shù)計算方法 =SLOPE（B2：B11，$D$2:$D$

17、11） 或 =COVAR（B2：B11，$D$2:$D$11）/VARP（$D$2:$D$11）5、有效前沿VBA計算程序?qū)嶒灹?期權定價模型計算 實驗目的與要求：衍生證券是一種重要的金融資產(chǎn)，本實驗復習期權定價理論的基礎上，給出期權的收入函數(shù)和利潤曲線，掌握期權和股票的投資策略及EXCEL計算。實驗指導：一、 概念釋義1、 看漲期權2、 看跌期權3、 執(zhí)行價格4、 終止日期5、 股票價格6、 實值、虛值和兩平二、 操作示例1、股票交易的利潤函數(shù)設7月份以市場價格40元買入或賣空一份股票A，如果持有或結清至9月份，股票交易的利潤函數(shù)。三、 期權買賣利潤函數(shù)四、 保護性看跌期權（S+P）五、拋補

18、的看漲期權（S-C）六、垂直價差套購（C1-C2）以不同的執(zhí)行價格買入一份（或多份價格不同）看漲期權和賣出一份（或多份不同價）看漲期權（看跌期權）。買入的執(zhí)行價低，賣出的執(zhí)行價高，為“牛市價差套購”；反之，為“熊市價差套購”七、對敲（C+P）同時買進具有相同執(zhí)行價格與相同到期時間的同一種股票的看漲與看跌期權。八、雙限期權（S+P-C）實驗七 二項式期權定價模型計算 實驗目的與要求：二項式期權定價模型假定標的資產(chǎn)（股票）在每個時間周期的期末價格只有兩種可能狀態(tài)（上漲或下跌）對應的股票及股票期權的價格變化過程可以用二叉樹表示，依據(jù)風險中性定價方法，通過計算每個樹叉的狀態(tài)價格來對期權定價。本實驗要求

19、學生了解期權中性風險定價機制，掌握單階段歐式二項期權定價模型的EXCEL實現(xiàn)過程，并將之推廣到美式期權定價的計算，應用VBA編定期權定價程序。實驗指導：一、概念釋義期權復制是指通過股票與無風險債券來構造一個投資組合，并使得該組合在任何狀態(tài)下（股票價格上漲或下跌）的未來現(xiàn)金流和該看漲期權的未來現(xiàn)金流完全相同。1call=A shares + B bonds。依據(jù)無套利定價原則，看漲期權的（現(xiàn)在）價格和其復制（股票和債券的組合）的（現(xiàn)在）價格相等，即期權可通過債券和股票來定價。二、操作示例1、單階段二項期權定價模型：（1）依據(jù)無套利原則通過期權復制的方法定價（2）依據(jù)風險中性原則計算狀態(tài)價格定價（3）平價關系檢驗對應看跌期權的價格為1.11元，且股票和看跌期權價值之和與看漲期權和執(zhí)行價格的現(xiàn)值之和相等，平價關系成立。2、二階段的二項式定價模型3、歐式期權的VB編程計算（1）看漲期權（2）看跌期權4、美式期權的VB編程計算（1）看漲期權（2）看跌期權實驗八 Black-Schols定價模型計算