Refinance Loans Home Equity Equity Refinance Loans Home Equity Refinance Loans Home Equity

Www Refinanceloanshomeequity U Comodity Szh Od Loans G Subprime Refinance Loans Home Equity 财务金融建模—图表(39xls) －元妙企业管理网

Www Refinanceloanshomeequity U Comodity Szh Od Loans G Subprime Refinance Loans Home Equity

culating the bond portfolio: Matrix of coefficients Vector of constants Solution Illustrated for the 30-year bond. Proportion of bond 1 Proportion of bond 3 Data table Explanation of the above: We want to invest proportions x1, x3, and x4 in bonds 1, 3 and 4 respectively, in order that: a) The total investment is $1000; this means x1+x2+x…………
Www aasearchfsearchr Subprime C Loans Lsearchc

a Comodity tsearch Subprime . Loans 1search7 Www P Subprime g Www 1 Www 7 Www (search� Comodity � Loans Comodity asearche Loans 1 Subprime 7

asearche Subprime 1 Refinanceloanshomeequity 7 Subprime c Loans a Refinanceloanshomeequity t Www ( Loans 衧earch�) Subprime Pg Refinanceloanshomeequity Comodity 3 Loans searchh

r Psearchgsearch

3search-1 Comodity 6 Comodity ( Szh 形 Szh )Pag Refinanceloanshomeequity 3 Www -

3 Refinanceloanshomeequity Www asearche Www 3search csearchart Loans Psearchg

1 Refinanceloanshomeequity 1search1 Comodity 4search( Www �

�

a Comodity e Comodity 2

-search2

Refinanceloanshomeequity ha Szh t d

tsearch1 Refinanceloanshomeequity 7 Www 130 Loans Psearchg Comodity search30 Refinanceloanshomeequity csearcharsearch search中文) Loans P Comodity gesearch13 Comodity hr

Pgsearch Refinanceloanshomeequity 2 searchhsearchr Szh ( Refinanceloanshomeequity 形� Loans Loans a Www e Www 1 Loans 8 Subprime char Loans Refinanceloanshomeequity ae 1 Www 7 ch Www rt

� Szh 文 Www

1search7 Comodity c Refinanceloanshomeequity ar Www Www olver_adj solver_adj solver_cvg 1.00E-04 solver_cvg 1.00E-04 solver_drv 1.00 solver_drv 1.00 solver_est 1.00 solver_est 1.00 solver_itr 100.00 solver_itr 100.00 solver_lin 2.00 solver_lin 2.00 solver_neg 2.00 solver_neg 2.00 solver_num .00 solver_num .00 solver_nwt 1.00 solver_nwt 1.00 solver_opt solver_opt solver_pre 1.00E-06 solver_pre 1.00E-06 solver_scl 2.00 solver_scl 2.00 solver_sho 2.00 solver_sho 2.00 solver_tim 100.00 solver_tim 100.00 solver_tol .05 solver_tol .05 solver_typ 2.00 solver_typ 3.00 solver_val .00 solver_val .07 Mean minus Variance-covariance matrix returns constant Constant z x y Transpose x Transpose y Mean(x) Mean(y) Var(x) Var(y) Sigma(x) Sigma(y) Cov(x,y) Corr(x,y) DATA TABLE A single portfolio calculation FOR EFFICIENT FRONTIER Proportion of x GRAPH p‘s mean return Sigma Return p‘s sigma <--data table header "jumps" in table <-- {} <-- {}…………
Page 341 (中文) Page 341 Page 340b Page 340 (中文) Page 340 Page 338-341 (中文) Page 338-341 discount discount discount discount discount discount discount discount growth growth growth growth growth growth growth growth CF1 Growth rate Discount rate Year Cash flow NPV <-- =+B6+NPV(B3,C6:I6) =B8 =B9 IRR <-- =IRR(B6:I6,0) Growth rate for Section 19.6 art: 增长率折现率年现金流量 CF1 UN-19J 234.00 .10 .15 .00 1.00 2.00 3.00 4.00 5.00 6.00 7.00 -1150.00 234.00 257.40 283.14 311.45 342.60 376.86 414.55 101.46 .18 .00 101.46 .18 .00 -176.46 .10 .05 -47.82 .14 .10 101.46 .18 .15 274.35 .22 101.46 .07 .10 .12 .00 .05 .10 .15 101.46 .18 .00 -176.46 .10 .05 -47.82 .14 .10 101.46 .18 .15 274.35 .22 UN-19J 234.00 .10 .15 .00 1.00 2.00 3.00 4.00 5.00 6.00 7.00 -1150.00 234.00 257.40 283.14 311.45 342.60 376.86 414.55 101.46 .18 .00 101.46 .18 .00 -176.46 .10 .05 -47.82 .14 .10 101.46 .18 .15 274.35 .22 101.46 .07 .10 .12 .00 .05 .10 .15 101.46 .18 .00 -176.46 .10 .05 -47.82 .14 .10 101.46 .18 .15 274.35 .22 UN-19J 234.00 .10 .15 .00 1.00 2.00 3.00 4.00 5.00 6.00 7.00 -1150.00 234.00 257.40 283.14 311.45 342.60 376.86 414.55 101.46 .18 .00 101.46 .18 .00 -176.46 .10 .05 -47.82 .14 .10 101.46 .18 .15 274.35 .2…………
Page 234 chart （中文） Page 234 chart Page 233-234 (中文) Page 233-234 Page 233 (中文) Page 233 Page 231b (中文) Page 231b Page 231(中文) Page 230 (中文) Page 230 Page 228-229 (中文) VBA functions, pp. 199-200, 203 Page 228-229 Call Call CallOption CallVolatility implied_call_volatility implied_call_volatility interest interest PutOption r_ r_ S S sigma sigma solver_adj solver_adj solver_adj solver_cvg 1.00E-04 solver_cvg 1.00E-04 solver_cvg 1.00E-04 solver_drv 1.00 solver_drv 1.00 solver_drv 1.00 solver_est 1.00 solver_est 1.00 solver_est 1.00 solver_itr 100.00 solver_itr 100.00 solver_itr 100.00 solver_lin 2.00 solver_lin 2.00 solver_lin 2.00 solver_neg 2.00 solver_neg 2.00 solver_neg 2.00 solver_num .00 solver_num .00 solver_num .00 solver_nwt 1.00 solver_nwt 1.00 solver_nwt 1.00 solver_opt solver_opt solver_opt solver_pre 1.00E-06 solver_pre 1.00E-06 solver_pre 1.00E-06 solver_scl 2.00 solver_scl 2.00 solver_scl 2.00 solver_sho 2.00 solver_sho 2.00 solver_sho 2.00 solver_tim 100.00 solver_tim 100.00 solver_tim 100.00 solver_tol .05 solver_tol .05 solver_tol .05 solver_typ 3.00 solver_typ 3.00 solver_typ 3.00 solver_val 4.00 solver_val 4.00 solver_val 4.00 T T target_call_price target_call_pri…………
Page 15-16 (中文) Page 15-16 Page 12-14 (中文) Page 12-14 Page 10b (中文) Page 10b Page 10 (中文) Page 10 Page 9（中文） Page 9 Page 8（中文） Page 8 Page 7 (中文) Page 7 Page 5-6 (中文) Page 5-6 Page 4 (中文) Page 4 solver_adj solver_adj solver_cvg 1.00E-03 solver_cvg 1.00E-03 solver_drv 1.00 solver_drv 1.00 solver_est 1.00 solver_est 1.00 solver_itr 100.00 solver_itr 100.00 solver_lin 2.00 solver_lin 2.00 solver_neg 2.00 solver_neg 2.00 solver_num .00 solver_num .00 solver_nwt 1.00 solver_nwt 1.00 solver_opt solver_opt solver_pre 1.00E-06 solver_pre 1.00E-06 solver_scl 2.00 solver_scl 2.00 solver_sho 2.00 solver_sho 2.00 solver_tim 100.00 solver_tim 100.00 solver_tol .05 solver_tol .05 solver_typ 3.00 solver_typ 3.00 solver_val .00 solver_val .00 Discount rate Present value Cash Year flow IRR LOAN TABLE NPV Division of payment Principal Payment between interest at beginning at end and return of principal year of year Interest DATA TABLE Discount rate Identifying the two IRRs First IRR Second IRR Cost Deposit at beginning Total in account end of year Account balance beg. year earned during year <-- =D8+C8+B8 A simpler way Future value A RETIREMENT PROBLEM Annual deposit <-- =E10+D10+C10 Numerator Denom…………
Page 209-210 (中文) Page208 chart Page 208 (中文) Page 208 Page 207 (中文) Page 207 VBA option functions Page 201-202 Page 199-200 (中文) Page 199-200 Page 199 Page 198 (中文) Page 198 Page 196(中文) Page 195-196 (中文) Page 195-196, AmericanCall AmericanPut BSCall BSPut EurCall EurPut getformula TWO-DATE BINOMIAL OPTION PRICING Up Down Initial stock price Interest rate Exercise price Stock price Bond price Call option A B Call price State prices qu qd Solving for the portfolio parameters: A is the number of shares and B is the number of bonds. 55*A + 108*B = 5 48.5*A + 108*B = 0 or: A*stock*(1+up)+B*(1+interest)=max(stock*(1+up)-X,0) A*stock*(1+down)+B*(1+interest)=max(stock*(1+down)-X,0) The solution is: check on state prices call price state prices Call option price FIVE DATE EUROPEAN BINOMIAL OPTION PRICING up down initial stock price interest rate exercise price stock price bond price Terminal payoff * payoff of "up" of "down" price * steps of paths # paths Option value THREE DATE BINOMIAL OPTION PRICING FOR AMERICAN CALL/PUT American put option =MAX(MAX(X-S*(1+u),0),qu*put_payoffuu+qd*put_payoffud) =MAX(MAX(X-S*(1+d),0),qu*put_payoffud+qd*put_payoffdd) =MAX(MAX(X-S,0),qu*put_valueu+qd*…………
Page 347 (中文) Page 347 Page 346 (中文) Page 346 Page 343-345 (中文) Page 343-345 Page 342, (中文) Page 342, david david jack jack simon simon terry terry x x xx xx Matrix A Matrix B Product AB Solution Matrix A of coefficients vector Y MATRICES IN EXCEL Matrix A (a row vector) Matrix D (a 4 x 3 matrix) Matrix C vector) (a column Matrix B (a square 3 x 3 matrix) MATRIX OPERATIONS Multiplication by a scalar Scalar Scalar * Matrix B <-- =D7*$B$5 Addition of matrices Sum of A + B <-- =B20+E20 Transposition of matrix Matrix E Transpose of E = ET The framed area contains To use this function: Mark off the whole area; put in the formula, then finish by pressing [Ctrl]+[Shift]+[Enter]. the array function =Transpose(A30:D32) . Multiplication of matrices <--Array contains formula =MMULT(A42:B43,D42:F43) Product BA -- this won‘t work <-- =MMULT(D42:F43,A42:B43) MATRIX INVERSE Inverse of A: Array function Minverse(A4:D7) Verifying the inverse We multiply A*Inverse A: cells contain array function MMULT(A4:D7,F4:I7) SOLVING SIMULTANEOUS EQUATIONS Column A-1 Y Checking that the solution works Contains the array function =MMULT(A5:C7,G5:G7) EXCEL中的矩阵矩阵A (一个行向量) 矩阵C (一个列向量) 矩阵B (一个3 x 3的方阵) 矩阵D (一个4 x…………
Page 150 (中文) Page 150 Page 149 (中文) Page 149 Page 148 Page 147(中文) Page 146-147 (中文) Page 146-147 Page 144,(中文) Page 144, Page 143, (中文) Page 143, AMR BS GE HR MO UK SP500 Mean Beta Intercept Slope R-squared SUMMARY OUTPUT Multiple R R Square Adjusted R Square df SS MS Coefficients t Stat X Variable 1 Regressing the means on the betas: F Significance F P-value Lower 95% Upper 95% =COVAR(B4:B13,$H$4:$H$13)/VARP($H$4:$H$13) =SLOPE(B4:B13,$H$4:$H$13) <-- =INTERCEPT(B15:G15,B16:G16) <-- =SLOPE(B15:G15,B16:G16) <-- =RSQ(B15:G15,B16:G16) THE SECURITY MARKET LINE--A SIMPLE EXAMPLE Variance-covariance matrix Means Minus Constant Calculating two efficient portfolios z x y Variance Covariance <-- =MMULT(TRANSPOSE(E17:E22),J6:J11) <-- =MMULT(MMULT(TRANSPOSE(E17:E22),C6:H11),E17:E22) <-- =SQRT(E25) <-- =MMULT(MMULT(TRANSPOSE(E17:E22),C6:H11),K17:K22) <-- =C19/SUM($C$17:$C$22) Sigma Data for SP500 returns <-- =AVERAGE(P4:P13) <-- =STDEVP(P4:P13) Calculation for a single portfolio Proportion x Proportion y <-- =D30*D24+D31*K24 <-- =D30^2*D25+D31^2*K25+2*D30*D31*D27 <-- =SQRT(D34) Portfolio proportion mean Step Cell D39 is the change in the portfolio proportion in the data table to the right. <-…………
Page 123-124 (中文) Page 123-124 Page 123 (中文) Page 123 Page 122， (中文) Page 122， Page 122 (中文) Page 122 Page 120-121 (中文) Page 120-121 Page 118， (中文) Page 118， junk junk varcovar RETURN DATA FOR VARIANCE-COVARIANCE CALCULATIONS AMR BS GE HR MO UK SP500 AMR American Airlines BS Bethlehem Steel GE General Electric HR International Harvester MO Philip Morris UK Union Carbide Excess return matrix Transpose of excess return matrix Beta Difference between two var-cov matrices: Product of transpose[excess return] times [excess return] / 10 Variance-covariance matrix based on return data Return data for 4 stocks (in columns) The variance-covariance matrix Mean CALCULATING THE VARIANCE-COVARIANCE MATRIX FROM EXCESS RETURNS A VBA FUNCTION FOR THE VARIANCE-COVARIANCE MATRIX My thanks go to Amir Kirsh for this suggestion. USING THE OFFSET FUNCTION TO COMPUTE THE VAR-COV MATRIX SINGLE-INDEX MODEL =COVAR(B4:B13,$H$4:$H$13)/VARP($H$4:$H$13) =SLOPE(B4:B13,$H$4:$H$13) Var(SP500) COMPUTING THE SINGLE-INDEX VARIANCE-COVARIANCE MATRIX <-- =AVERAGE(G4:G13) <-- =G12-$G$14 <-- =G13-$G$14 <-- =C$14*$B15*$C$11 My thanks go to Shay Safrir for this suggestion. 方差和协方差计算的收益数据美国航空公司伯利恒钢铁厂通用电气公司国际收割机公司菲利普莫里斯公…………
Page 117 (中文) Page 117 Page 116 (中文) Page 116 Page 115 (中文) Page 115 Page 112, (中文) Page 112, Page 109-110 (中文) Page 109-110 Page 108 Page 107 (中文) Page 107 Page 106, graph (中文) Page 106, graph Page 104-105 (中文) Page 104-105 Stock prices Month Stock A Stock B stock A stock B Return Return-mean Product Covariance Correlation CALCULATING THE MEAN AND SIGMA OF A PORTFOLIO R A t RBt Rpt A FOUR-ASSET PORTFOLIO PROBLEM Variance-covariance Mean returns Portfolio 1 Mean Variance Portfolio 2 Transposes Calculating returns of combinations of Portfolio 1 and Portfolio 2 Proportion of Portfolio 1 Mean return Variance of return Stand. dev. of return Table of returns (uses this example and Data|Table) Proportion Stand. dev. <--the content of these cells is given below: Monthly mean Monthly variance Monthly stand. dev. Annual mean Annual variance Annual stand. dev. C pWww Refinanceloanshomeequity U Comodity Szh Od Loans G Subprime Refinance Loans Home Equity 财务金融建模—图表(39xls) －元妙企业管理网j Refinance Loans Home Equity lWww Refinanceloanshomeequity U Comodity Szh Od Loans G Subprime Refinance Loans Home Equity 财务金融建模—图表(39xls) －元妙企业管理网w k Refinance Loans Home Equity