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

實驗一更有效地使用EXCEL實驗目的與要求：熟悉EXECL的運行環(huán)境，掌握數據導入方法，掌握圖表與數據透視表的使用，了解常用函數的功能與使用方法。實驗指導：一、概念釋義：1、數據輸入與運算在Excel環(huán)境下進行計算．所需要的數據大致有3種來源：手工輸入、自動生成和從外部導入。當數據量很小而且又沒有規(guī)律的情況下,一般采用手工輸入的方法。而對于那些有規(guī)律的數據，如連續(xù)的數字或字符序列可以用Excel的填充命令自動生成。當數據量比較大或有現成的數據來源．如網絡資源、數據庫資源等可供應用，可以采用導入的方法。Excel可以讀入不同來源、不同格式的多種數據文件。執(zhí)行Excel上的“文件””打開”命令，就可以直接讀人數據庫文件、Web文件、XML文件、文本文件以與其他格式的電子表格文件等。對于連接在網絡《局域網、廣域網或Web)上的計算機，這些文件可以保存在網絡上的任何位置——只要使用者具有訪問權限都可以接讀入Excel。在讀入非Excel格式文件時，Excel會自動將文件轉換成為工作表格式。對于某些格式的數據，在進行這種格式轉換時，可能需要用戶做出一些選擇，如在讀入文本文件(．txt、．rtf)時。需要用戶指定分列方式．可能的方式包括按固定寬度分列或按指定字符分列。Excel工作表中的數據有兩種基本情況：由用戶輸入或導入的數據：經過計算得到的數據。用戶直接在單元格中輸入的數據屬于前者，而后者則是通過公式或函數進行計算后得到的。在Excel中對于后面一類統(tǒng)稱為公式，它是由等號開始，包括常量、對單元格或單元格區(qū)域的引用、運算符和函數等幾種成分。在Excel中通過運算符可以執(zhí)行多種運算：算術運算、文本運算、日期運算、比較運算。2、圖表與數據透視表圖表是使用Excel進行各種運算分析時一個非常有用的工具．通過圖表，可以更加直觀地揭示數據之間的內在關系和變化趨勢。本書中使用了大量的圖表作為分析手段．來說明各種金融財務原理和表示計算結果。在Excel中創(chuàng)建圖表比較簡單．可以通過其內置的圖表向導來自動生成各種不同類型的圖表。單擊常用工具欄上的按鈕即可啟動圖表向導。使用圖表向導制作圖表分為4個步驟：選擇圖表類型．確定源數據．設定圖表選項．安排圖表位置。正確有效地制作圖表關鍵在于前兩步，首先要根據數據的性質和圖表的目的選擇合適的圖表類型．其次要正確地設置源數據區(qū)、橫縱坐標軸與數據系列標題。在圖表選項中主要是對諸如圖表標題、坐標軸標題、圖表網格線、圖例、數據標志等格式方面的要素進行設定，最后可以選擇圖表放置的位置，即可以放置在源數據所在工作表內．也可以建立單獨的圖表工作表。數據透視表是Excel中一項比較重要的高級功能。Excel的工作表本身是一個二維表，如果要用常規(guī)的Excel工作表表示具有多個維度的數據，就只能把數據”攤平”。然而，在實際應用中．經常需要同時按照多個方向對同一組數據進行分析。例如在分析企業(yè)銷售收入的歷史數據時．可能既要按照不同的時間如年份或季度來統(tǒng)計，又要按照部門或地區(qū)來統(tǒng)計．甚至會把幾個維度結合在一起分析，這時就需要使用Excel的數據透視表。如果用一句話來概括數據透視表的本質．那就是數據透視表可以在二維的表格里表示三維或者多維的數據。3、內置函數和自定義函數ExceI作為商務計算的工具軟件，其計算功能是通過函數和內置工具實現的。函數是構成Excef計算公式的主要元素之一。在一般性的計算應用和數據管理當中函數承擔著絕大部分的工作。ExceI大約內置有330個左右的函數，這些函數按照功能和作用的不同，可以分成11大類。包括：數據庫函數．日期和時間函數，外部函數、工程函數、財務函數，信息函數，邏輯函數、查找和引用函數，數學和三角函數，統(tǒng)計函數以與文本和數據函數。所謂自定義函數是指用戶根據自己的需要通過Excel內置的VBA(VisualBasicforApplication)功能模塊編寫代碼，設計出實現特定功能的函數。4、假設分析工具方案是保存在工作表當中并可以在相互之間進行切換的數據。當需要調整運算參數觀察計算結果而又希望保存這些不同結果時，方案管理器是一個合適的工具。所謂敏感分析是指通過多次使用不同的參數，對某個或一組公式的運算結果進行綜合比較，從而了解不同參數在其可能的取值范圍內對運算結果的影響程度。模擬運算表是一個理想的敏感分析工具，它充分發(fā)揮表格界面和運算公式的優(yōu)勢，用表格的形式表示出評價指標對分析指標的依賴程度。已知某個公式的運算結果，要求確定其中某個參數的適當值，這種問題在數學上稱為反函數。在EXCEL中，提供一個專門處理這類問題的工具單變量求解。二、操作示例假定某投資項目，投資金額為10000，項目周期為5年。項目的預期現金流量和貼現率都受經濟形勢的影響：如果經濟不景氣，則預期每年現金流量為1200，貼現率為6％；如果經濟繁榮，則預期每年現金流量為2000．貼現率為1O％；如果經濟形勢一般，則現金流量為1600。貼現率為8％。要求對該項目進行評價。實驗二貨幣的時間價值實驗目的與要求：貨幣的時間價值是一切金融計算的基礎，在本實驗中關鍵的概念是復利和貼現。通過實驗要求學生掌握EXCEL常用財務函數使用。實驗指導：一、概念釋義：現值是指未來預期現金流現在的價值，凈現值是現值減去初始成本。現值和凈現值問題是學習資金時間價值的開始，理解資金時間價值含義并利用Excel實現其計算過程是學好本書的良好開端。1．現值若已知T期末將擁有一現金流CT，則其在折現率r下的現值為：PV=CT/（1+r）T2.凈現值凈現值是未來的現金流的現值減去初始的投資現金流。3.終值指現金流在未來某時刻的價值。4．年金指一系列有穩(wěn)定規(guī)律的、持續(xù)一段固定時期的現金收付活動。注：4個變量參與運算，RATE（），NPER（），函數名為計算內容。二、操作示例例1．某投資項目能在未來4年產生90元，100元，110元和80元的確定現金流，折現率是8%，求該投資的項目的現值。（多重現金流）步驟1步驟2步驟3步驟4例2、假設今年年初將1000存入銀行，并將在以后的9年中每年年初存入1000元，那么第十年年初的銀行存款余額為多少？（終值）例3、假設某某客戶為購房，申請了100000元的房屋抵押貸款，貸款的年利率6%，銀行要求10年內還清，則每年末款多少？例4、計息期與連續(xù)利率例5某項目投資300萬元后，將在未來4年每年獲100萬元的現金流入，采用的貼現率為10%，決策該項目可行。（內部收益函數，單變量方程）實驗三債券利率風險評估實驗目的與要求：債券的主要風險為利率風險，久期和凸性是刻畫債券的兩個重要特征。本實驗要求熟悉債券的久期概念，掌握久期的計算。（市場風險、信用風險）實驗指導：一、概念釋義1、債券的價值與收益?zhèn)挠嬎阒饕P注的兩個指標價格和收益率，其基本原則是貼現現金流。2、債券的久期久期是指固定收益?zhèn)F金流支付時間的加權平均。權重是指每次支付的現金流的現值在債券總價中所占的比例。久期作為債券價格相對貼現率的彈性，3、修正久期修正久期越大債券的價格變化幅度越大，利率風險越高。設D為久期，r為貼現率（市場利率），則修正久期（MD）可定義為D/（1+r）由，可得二、操作示例1、債券價格計算（債券票面利率與市場利率關系）2、債券久期計算（原理）3、久期度量債券的價格相對貼現率的彈性（求導）4、EXCELL久期函數計算久期5、修正久久期（將修正期與市場利率的預期相結合對債券價格變化率進行估計）6、債券價格近似計算實驗四投資組合模型計算實驗目的與要求：掌握現代投資理論與其實現過程，學會證券的收益率矩陣和數字特征計算；計算投資組合的有效前沿，資本資產定價理論的EXCELL實現。實驗指導：一、概念釋義股票的月收益率計算分為連續(xù)收益率和離散收益率兩種，連續(xù)收益率為；離散收益率為；在考慮股利收入條件下，連續(xù)收益率為，離散收益率為期望收益是指持有股票的投資者在下一個時期所能獲得的收益。單個股票的期望收益可通過歷史數據的樣本均值來估計。資產隨機收益率的方差是偏離預期的期望，標準差是方差的平方根。方差、標準差刻畫收益率變動，是風險的常用度量指標。協(xié)方差是度量兩種風險資產收益之間線性關聯度的統(tǒng)計指標，正協(xié)方差收益為同向變動，負協(xié)方差收益為反向變動。相關系數刻畫兩種隨機變量的線性關聯度。二、操作示例1、期望收益的計算（1）計算股票的每月收益率（2）計算股票的月期望收益率（3）計算股票的年收益率2、方差與標準差在EXCELL中方差，樣本方差，標準差，樣本標準差分別用VAR、VARP、STEDV、STEDVP。通過EXCELL中的工具欄[fx]/[統(tǒng)計]。3、協(xié)方差、相關系數選擇[fx]/[統(tǒng)計]/[covar]命令，對話框中選擇數據區(qū)域，完成后單擊確定。選擇[fx]/[統(tǒng)計]/[correl]命令，對話框中選擇數據區(qū)域，完成后單擊確定。4、投資組合的期望收益和方差計算運用EXCEL中的[模擬運算表]可計算出兩種股票任意投資組合的期望收益和方差。建立工作表選定需要計算的單元區(qū)域單擊[數據]菜單中的模擬運算表單擊[確定]使用EXCEL的圖表功能繪制標準差—收益曲線。5、多個資產投資組合（1）計算風險資產構成的給定組合的數字特征（2）由兩個組合構成標準差—期望收益曲線（3）描繪資產在坐標中的位置（4）利用模擬運算表求不同投資比例下的期望收益與標準差6、超額收益法計算方差—協(xié)方差矩陣（1）計算每個資產的期望收益率（2）計算超額收益矩陣（3）計算超額收益轉置矩陣（4）計算方差協(xié)方差矩陣實驗五有效前沿與資本資產定價模型實驗目的與要求：均值方差與有效前沿理論是現代投資理論的基本內容，本實驗在上一個實驗的基礎上探討有效前沿與資本資產定價模型的計算問題。實驗指導：概念釋義可行投資組合：滿足投資比例之和為1的組合；可行集：由所有可行投資組合構成的集合；前沿投資組合：對于任意給定的期望收益水平，所有具有最小方差的投資組合構成的集合；有效投資組合：對于任意給定的方差或標準差，具有最大收益率的投資組合；有效前沿：由所有有效投資組合構成的集合；資本市場線（CML）：指在標準差—期望收益坐標系中無風險資產和有效前沿曲線的切線，CML刻畫了在存在無風險證券且無借貸限制時的投資組合有效前沿，此時有效前沿退化為直線。證券市場線（SML）刻畫任何資產或投資組合的期望收益與其對應的貝塔系數之間的線性關系。操作示例有效前沿計算資產組合兩個前沿組合的計算=MMULT(MINVERSE(C2:F5),E8:E11)=F8/SUM(F$8:F$11)3、市場組合M的計算與繪制假設有效組合N和無風險資產的投資比例為a,則新的資產組合P的期望收益和標準差分別為：和4、證券市場線計算與繪制貝塔系數計算方法=SLOPE（B2：B11，$D$2:$D$11）或=COVAR（B2：B11，$D$2:$D$11）/VARP（$D$2:$D$11）5、有效前沿VBA計算程序實驗六期權定價模型計算實驗目的與要求：衍生證券是一種重要的金融資產，本實驗復習期權定價理論的基礎上，給出期權的收入函數和利潤曲線，掌握期權和股票的投資策略與EXCEL計算。實驗指導：概念釋義看漲期權看跌期權執(zhí)行價格終止日期股票價格實值、虛值和兩平操作示例1、股票交易的利潤函數設7月份以市場價格40元買入或賣空一份股票A，如果持有或結清至9月份，股票交易的利潤函數。期權買賣利潤函數保護性看跌期權（S+P）五、拋補的看漲期權（S-C）六、垂直價差套購（C1-C2）以不同的執(zhí)行價格買入一份（或多份價格不同）看漲期權和賣出一份（或多份不同價）看漲期權（看跌期權）。買入的執(zhí)行價低，賣出的執(zhí)行價高，為“牛市價差套購”；反之，為“熊市價差套購”七、對敲（C+P）同時買進具有相同執(zhí)行價格與相同到期時間的同一種股票的看漲與看跌期權。八、雙限期權（S+P-C）實驗七二項式期權定價模型計算實驗目的與要求：二項式期權定價模型假定標的資產（股票）在每個時間周期的期末價格只有兩種可能狀態(tài)（上漲或下跌）對應的股票與股票期權的價格變化過程可以用二叉樹表示，依據風險中性定價方法，通過計算每個樹叉的狀態(tài)價格來對期權定價。本實驗要求學生了解期權中性風險定價機制，掌握單階段歐式二項期權定價模型的EXCEL實現過程，并將之推廣到美式期權定價的計算，應用VBA編定期權定價程序。實驗指導：一、概念釋義期權復制是指通過股票與無風險債券來構造一個投資組合，并使得該組合在任何狀態(tài)下（股票價格上漲或下跌）的未來現金流和該看漲期權的未來現金流完全相同。1call=Ashares+Bbonds。依據無套利定價原則，看漲期權的（現在）價格和其復制（股票和債券的組合）的（現在）價格相等，即期權可通過債券和股票來定價。二、操作示例1、單階段二項期權定價模型：（1）依據無套利原則通過期權復制的方法定價（2）依據風險中性原則計算狀態(tài)價格定價（3）平價關系檢驗對應看跌期權的價格為1.11元，且股票和看跌期權價值之和與看漲期權和執(zhí)行價格的現值之和相等，平價關系成立。2、二階段的二項式定價模型3、歐式期權的VB編程計算（1）看漲期權（2）看跌期權4、美式期權的VB編程計算（1）看漲期權（2）看跌期權實驗八Bla