chapter33
UN-22BEXCELS NPV FUNCTIONDiscount rate 10%Year 0 1 2 3 4 5Cash flow -100 35 33 34 25 16NPV $11.65 -0.0890 14.3285Standard error of slope 0.0148 0.8770R2 (also =Rsq(C3:C12,B3:B12) ) 0.8189 0.8117F statistic 36.1825 8SSxy = Slope*(summed product of observations from means) 23.8377 5.2705Slope -0.0890Intercept 14.3285R2 0.8189t-statistic 16.3376Slope -0.0890Standard error of slope 0.0148t-statistic -6.0152A B C1234567891011121314151617181920212223242526272829 -0.0890 14.3285斜率标准差 0.0148 0.8770R平方(也可用=Rsq(C3:C12,B3:B12) ) 0.8189 0.8117F统计量 36.1825 8SSxy = 斜率*(均值与观察值乘积的和) 23.8377 5.2705斜率 -0.0890截距 14.3285R平方 0.8189t统计量 16.3376斜率 -0.0890斜率的标准差 0.0148t统计量 -6.0152A B C1234567891011121314151617181920212223242526272829 0.0089 -0.0987 14.1705 0.0030 0.0110 0.6271R2 0.9196 0.5783 #N/AF statistic 40.0228 7.0000 #N/ASSxy 26.7674 2.3408 #N/A=LINEST(E3:E12,C3:D12,TRUE)The regression equation isY = 14.1705 - 0.0987*x1 + 0.0089*x2The chart shows the predicted versus the actual Y. If all thepredictions were exact (i.e., R2 = 100%), then the predictedpoints would all fall on the45-degree line (the dark line).1 31 197567891 01 11 2A c tu a l YPredictedYA B C D E F G H I J K L M N1234567891011121314151617181920使用LINEST作多元回归分析观测 X1 X2 Y 预测值Y1 35.3 81.2 10.98 11.4071 0.0089 -0.0987 14.1705 0.0030 0.0110 0.6271R平方 0.9196 0.5783 #N/AF值 40.0228 7.0000 #N/ASSxy 26.7674 2.3408 #N/A=LINEST(E3:E12,C3:D12,TRUE)回归方程是Y = 14.1705 - 0.0987*x1 + 0.0089*x2该表显示出了预测的和实际的Y。如果所有的预测都是正确的，（也就是说，R平方=100%）,那么所有的预测点都会落到45度线（黑线）上。1 31 197567891 01 11 2实 际 Y预测Y2%) Data table: number of returns above cutoffCutoff 5% CutoffReturns over cutoff 2 &TEXT(B5,0.00%) 0%1%DateMerckprice Return 2%3-一月-06 31.82 3%9-一月-06 32.15 1.03% 2%)Cutoff 5%超过cutoff的收益的期数 2 &TEXT(B5,0.00%)日期 默克股价 收益3-一月-06 31.829-一月-06 32.15 1.03% y? TRUE B3)Multiplying 0 B3)相乘 0 y? TRUE B3)Multiplying 1 B3)相乘 1 lower bound? 191-十一月-05 32.05 8.02%1-十二月-05 33.27 3.74%How many between upperand lower bounds? 113-一月-06 33.11 -0.48%1-二月-06 33.98 2.59%Average of returnsbetween the bounds 1.36%1-三月-06 34.14 0.47%3-四月-06 36.36 6.30%1-五月-06 35.99 -1.02%1-六月-06 38 5.43%3-七月-06 35.07 -8.02%1-八月-06 37.61 6.99%1-九月-06 38.59 2.57%2-十月-06 41.71 7.77%1-十一月-06 45.09 7.79%1-十二月-06 47.72 5.67%3-一月-07 45.1 -5.65%&G9)=SUMPRODUCT(C4:C27G9)*(C4:C27G9)*(C4:C27G9)*(C4:C27G9)*C4:C27/SUMPRODUCT(C4:C27G9)*(C4:C27G9)*(C4:C27&G9)=SUMPRODUCT(C4:C27G9)*(C4:C27G9)*(C4:C27G9)*(C4:C27G9)*C4:C27/SUMPRODUCT(C4:C27G9)*(C4:C27G9)*(C4:C27G8)使用布尔函数USING OFFSETStarting cornerRows down 3Columns over 2Range to be summedNumber of rows 4Number of columns 5Sum 811 - =SUM(OFFSET(A11:H31,B3,B4,B6,B7)Check 811 - =SUM(C14:G17)89 34 72 42 41 89 75 4133 6 49 7 62 50 38 1771 69 42 68 39 75 32 771 69 8 79 40 8 67 4670 12 44 48 88 27 38 5185 0 23 35 83 30 17 5230 50 16 28 73 4 55 6835 56 31 24 15 47 89 8899 31 55 60 45 24 28 393 72 7 75 90 81 52 7162 56 55 19 73 81 33 7687 27 80 38 65 61 38 6810 59 27 81 6 83 51 170 88 44 35 70 35 0 8298 45 17 45 89 19 58 4283 75 21 13 80 9 18 6432 23 4 86 88 52 52 6976 61 72 28 83 1 32 3864 87 32 67 50 73 19 8354 55 57 64 80 29 17 9212 95 66 59 48 78 87 23A B C D E F G H12345678910111213141516171819202122232425262728293031使用OFFSET起始点向下行数 3向右列数 2求和范围行数 4列数 5和 811 - =SUM(OFFSET(A11:H31,B3,B4,B6,B7)检查 811 - =SUM(C14:G17)89 34 72 42 41 89 75 4133 6 49 7 62 50 38 1771 69 42 68 39 75 32 771 69 8 79 40 8 67 4670 12 44 48 88 27 38 5185 0 23 35 83 30 17 5230 50 16 28 73 4 55 6835 56 31 24 15 47 89 8899 31 55 60 45 24 28 393 72 7 75 90 81 52 7162 56 55 19 73 81 33 7687 27 80 38 65 61 38 6810 59 27 81 6 83 51 170 88 44 35 70 35 0 8298 45 17 45 89 19 58 4283 75 21 13 80 9 18 6432 23 4 86 88 52 52 6976 61 72 28 83 1 32 3864 87 32 67 50 73 19 8354 55 57 64 80 29 17 9212 95 66 59 48 78 87 23USING OFFSETwith negative valueStarting cornerRows down -5Columns over 1Range to be summedNumber of rows 4Number of columns 5Sum 899 - =SUM(OFFSET(B22:H31,B3,B4,B6,B7)Check 899 - =SUM(C17:G20)89 34 72 42 41 89 75 4133 6 49 7 62 50 38 1771 69 42 68 39 75 32 771 69 8 79 40 8 67 4670 12 44 48 88 27 38 5185 0 23 35 83 30 17 5230 50 16 28 73 4 55 6835 56 31 24 15 47 89 8899 31 55 60 45 24 28 393 72 7 75 90 81 52 7162 56 55 19 73 81 33 7687 27 80 38 65 61 38 6810 59 27 81 6 83 51 170 88 44 35 70 35 0 8298 45 17 45 89 19 58 4283 75 21 13 80 9 18 6432 23 4 86 88 52 52 6976 61 72 28 83 1 32 3864 87 32 67 50 73 19 8354 55 57 64 80 29 17 9212 95 66 59 48 78 87 23A B C D E F G H12345678910111213141516171819202122232425262728293031使用OFFSET带有负值起始点向下行数 -5向右列数 1求和范围行数 4列数 5和 899 - =SUM(OFFSET(B22:H31,B3,B4,B6,B7)检查 899 - =SUM(C17:G20)89 34 72 42 41 89 75 4133 6 49 7 62 50 38 1771 69 42 68 39 75 32 771 69 8 79 40 8 67 4670 12 44 48 88 27 38 5185 0 23 35 83 30 17 5230 50 16 28 73 4 55 6835 56 31 24 15 47 89 8899 31 55 60 45 24 28 393 72 7 75 90 81 52 7162 56 55 19 73 81 33 7687 27 80 38 65 61 38 6810 59 27 81 6 83 51 170 88 44 35 70 35 0 8298 45 17 45 89 19 58 4283 75 21 13 80 9 18 6432 23 4 86 88 52 52 6976 61 72 28 83 1 32 3864 87 32 67 50 73 19 8354 55 57 64 80 29 17 9212 95 66 59 48 78 87 23
