COMPUTING THE PRESENT VALUEDiscount rate 10%Year Cash flowPresentvalue1 100 90.9091 - =B5/(1+$B$2)A52 100 82.6446 - =B6/(1+$B$2)A63 100 75.1315 - =B7/(1+$B$2)A74 100 68.3013 - =B8/(1+$B$2)A85 100 62.0921 - =B9/(1+$B$2)A9Net present valueSumming cells C5:C9 379.08 - =SUM(C5:C9)Using Excels NPV function 379.08 - =NPV(B2,B5:B9)Using Excels PV function 379.08 - =PV(B2,5,-100)A B C D1234567891011121314现值计算折现率 10%年 现金流 现值1 100 90.9091 - =B5/(1+$B$2)A52 100 82.6446 - =B6/(1+$B$2)A63 100 75.1315 - =B7/(1+$B$2)A74 100 68.3013 - =B8/(1+$B$2)A85 100 62.0921 - =B9/(1+$B$2)A9净现值求和 C5:C9 379.08 - =SUM(C5:C9)运用EXCEL中的NPV函数 379.08 - =NPV(B2,B5:B9)运用EXCEL中的PV函数 379.08 - =PV(B2,5,-100)A B C D1234567891011121314COMPUTING THE PRESENT VALUEIn this example the cash flows are not equalEither discount each cash flow separately or use Excels NPVfunctionExcels PV doesnt work for this caseDiscount rate 10%YearCashflowPresentvaluePresent valueof each cash flow1 100 90.9091 - =B5/(1+$B$2)A52 200 165.2893 - =B6/(1+$B$2)A63 300 225.3944 - =B7/(1+$B$2)A74 400 273.2054 - =B8/(1+$B$2)A85 500 310.4607 - =B9/(1+$B$2)A9Net present valueSumming cells C5:C9 1065.26 - =SUM(C5:C9)Using Excels NPV function 1065.26 - =NPV(B2,B5:B9)A B C D12345678910111213计算现值该例子中现金流是不等额的单独折现每笔现金流或应用NPV函数PV函数对该例不适用折现率 10%年 现金流 现值 每一笔现金流的现值1 100 90.9091 - =B5/(1+$B$2)A52 200 165.2893 - =B6/(1+$B$2)A63 300 225.3944 - =B7/(1+$B$2)A74 400 273.2054 - =B8/(1+$B$2)A85 500 310.4607 - =B9/(1+$B$2)A9净现值求和C5:C9 1065.26 - =SUM(C5:C9)运用EXCEL中的函数 1065.26 - =NPV(B2,B5:B9)A B C D12345678910111213COMPUTING THE NET PRESENT VALUEDiscount rate 10%Year Cash flowPresentvalue0 -250 -250.00 - =B5/(1+$B$2)A51 100 90.91 - =B6/(1+$B$2)A62 100 82.64 - =B7/(1+$B$2)A73 100 75.13 - =B8/(1+$B$2)A84 100 68.30 - =B9/(1+$B$2)A95 100 62.09 - =B10/(1+$B$2)A10Net present valueSumming cells C5:C10 129.08 - =SUM(C5:C10)Using Excels NPV function 129.08 - =B5+NPV(B2,B6:B10)A B C D1234567891011121314计算净现值折现率 10%年 现金流 现值0 -250 -250.00 - =B5/(1+$B$2)A51 100 90.91 - =B6/(1+$B$2)A62 100 82.64 - =B7/(1+$B$2)A73 100 75.13 - =B8/(1+$B$2)A84 100 68.30 - =B9/(1+$B$2)A95 100 62.09 - =B10/(1+$B$2)A10净现值求和C5:C10 129.08 - =SUM(C5:C10)运用EXCEL中的NPV函数 129.08 - =B5+NPV(B2,B6:B10)A B C D1234567891011121314COMPUTING THE VALUE OF A FINITE ANNUITYPeriodic payment, C 1,000Number of future periods paid, n 5Discount rate, r 12%Present value of annuityUsing formula 3,604.78 - =B2*(1-1/(1+B4)B3)/B4Using Excels PV function 3,604.78 - =PV(B4,B3,-B2)PeriodAnnuitypayment1 1,000.00 - =B22 1,000.003 1,000.004 1,000.005 1,000.00Present value using Excels NPV function 3,604.78 - =NPV(B4,B10:B14)A B C12345678910111213141516计算有限期年金的值每期偿付额, C 1,000未来偿付期数, n 5折现率, r 12%年金现值运用公式 3,604.78 - =B2*(1-1/(1+B4)B3)/B4运用函数 3,604.78 - =PV(B4,B3,-B2)期数 年金1 1,000.00 - =B22 1,000.003 1,000.004 1,000.005 1,000.00运用函数计算现值 3,604.78 - =NPV(B4,B10:B14)A B C12345678910111213141516COMPUTING THE VALUE OF AN INFINITE ANNUITYPeriodic payment, C 1,000Discount rate, r 12%Present value of annuity 8,333.33 - =B2/B3A B C1234计算无限期年金的值每期偿付额, C 1,000折现率, r 12%年金现值 8,333.33 - =B2/B3A B C1234COMPUTING THE VALUE OF A GROWING FINITE ANNUITYFirst payment, C 1,000Growth rate of payments, g 6%Number of future periods paid, n 5Discount rate, r 12%Present value of annuityUsing formula 4,010.91 - =B2*(1-(1+B3)/(1+B5)B4)/(B5-B3)PeriodAnnuitypayment1 1,000.00 - =B22 1,060.00 - =$B$2*(1+$B$3)(A11-1)3 1,123.60 - =$B$2*(1+$B$3)(A12-1)4 1,191.02 - =$B$2*(1+$B$3)(A13-1)5 1,262.48 - =$B$2*(1+$B$3)(A14-1)Present value using Excels NPV function 4,010.91 - =NPV(B5,B10:B14)A B C12345678910111213141516计算有限期增长年金的值首期偿付额, C 1,000增长率, g 6%未来偿付期数, n 5折现率, r 12%年金现值运用公式 4,010.91 - =B2*(1-(1+B3)/(1+B5)B4)/(B5-B3)期数 年金1 1,000.00 - =B22 1,060.00 - =$B$2*(1+$B$3)(A11-1)3 1,123.60 - =$B$2*(1+$B$3)(A12-1)4 1,191.02 - =$B$2*(1+$B$3)(A13-1)5 1,262.48 - =$B$2*(1+$B$3)(A14-1)运用NPV函数进行计算 4,010.91 - =NPV(B5,B10:B14)A B C12345678910111213141516COMPUTING THE VALUE OF A GROWING INFINITE ANNUITYPeriodic payment, C 1,000 - Starting at date 1Growth rate of payments, g 6%Discount rate, r 12%Present value of annuity 16,666.67 - =B2/(B4-B3)A B C12345计算无限期增长年金的值每期偿付额, C 1,000 - Starting at date 1增长率, g 6%折现率, r 12%年金现值 16,666.67 - =B2/(B4-B3)A B C12345INTERNAL RATE OF RETURNYear Cash flow0 -8001 2002 2503 3004 3505 400Internal rate of return 22.16% - =IRR(B3:B8)A B C12345678910内部收益率年 现金流量0 -8001 2002 2503 3004 3505 400内部收益率 22.16% - =IRR(B3:B8)A B C12345678910INTERNAL RATE OF RETURNDiscount rate 12%Year Cash flow0 -8001 2002 2503 3004 3505 400Net present value (NPV) 240.81 - =B5+NPV(B2,B6:B10)A B C123456789101112内部收益率折现率 12%年 现金流0 -8001 2002 2503 3004 3505 400净现值 (NPV) 240.81 - =B5+NPV(B2,B6:B10)A B C123456789101112INTERNAL RATE OF RETURNDiscount rate 22.16%Year Cash flow0 -8001 2002 2503 3004 3505 400Net present value (NPV) 0.00 - =B5+NPV(B2,B6:B10)A B C123456789101112内部收益率折现率 12.00%年 现金流0 -8001 2002 2503 3004 3505 400净现值 (NPV) 240.81 - =B5+NPV(B2,B6:B10)A B C123456789101112INTERNAL RATE OF RETURNYear Cash flow0 -8001 2002 2503 3004 3505 400Internal rate of return 22.16% - 0.2216USING THE IRR IN A LOAN TABLEDivision of cash flowbetween investmentincome and return ofprincipalYearInvestment atbeginning ofyearCash flowat end of year IncomeReturn ofprincipal1 800.00 200.00 177.28 22.72 - =C15-D152 777.28 250.00 172.25 77.753 699.53 300.00 155.01 144.994 554.54 350.00 122.89 227.115 327.43 400.00 72.56 327.446 -0.02= -B 3= B 1 5 -E 1 5= $ B $ 1 0 * B 1 5T h e re m a in in g in v e s tm e n t p rin c ip a l in th e y e a r a f te r th e la s t c a s h f lo w is z e ro , in d ic a tin g th a t a ll th e p rin c ip a l h a s b e e n re p a id .A B C D E F1234567891011121314151617181920212223242526内部收益率年 现金流0 -8001 2002 2503 3004 3505 400内部收益率 22.16% - 0.2216在贷款表中应用内部收益率现金流被分割为投资收益和本金偿还两个部分年 年初投资 年末现金流 收益 本金偿还1 800.00 200.00 177.28 22.72 - =C15-D152 777.28 250.00 172.25 77.753 699.53 300.00 155.01 144.994 554.54 350.00 122.89 227.115 327.43 400.00 72.56 327.446 -0.02= -B 3= B 1 5 -E 1 5= $ B $ 1 0 * B 1 5偿 还 了 最 后 一 期 贷 款 后 的 现 金 流 是 ， 说 明 所 有 的 本 金 已 经 被 偿 还 。A B C D E F12345678910111213141516171819202122232425USING A LOAN TABLE TO FIND THE IRRIRR? 15.00%Division of cash flowbetween investmentincome and return ofprincipalYearPrincipalat beginningof yearCash flowat end ofyear Income Principal1 1,000.00 300 150.00 150.00 - =C6-D62 850.00 200 127.50 72.503 777.50 150 116.63 33.384 744.13 600 111.62 488.385 255.74 900 38.36 861.646 -605.89Direct calculation of IRRYear Cash flow0 -1,0001 3002 2003 1504 6005 900IRR 24.44% - =IRR(B17:B22)= $ B $ 2 * B 6= B 6 -E 6A B C D E F G H123456789101112131415161718192021222324I J K123456789101112131415161718192021222324通过贷款表计算内部收益率内部收益率？ 15.00%偿还分割为收益和本金年 年初本金 年末现金流 收益 本金1 1,000.00 300 150.00 150.00 - =C6-D62 850.00 200 127.50 72.503 777.50 150 116.63 33.384 744.13 600 111.62 488.385 255.74 900 38.36 861.646 -605.89IRR的直接计算年 现金流0 -1,0001 3002 2003 1504 6005 900IRR 24.44% - =IRR(B17:B22)= $ B $ 2 * B 6= B 6 -E 6A B C D E F G H123456789101112131415161718192021222324I J K123456789101112131415161718192021222324USING EXCELS RATE FUNCTIONTO COMPUTE THE IRRInitial investment 1,000Periodic cash flow 100Number of payments 30IRR 9.307% - =RATE(B4,B3,-B2)A B C12345运用实际利率函数计算内部收益率期初投资 1,000每期现金流 100偿付期数 30内部收益率 9.307% - =RATE(B4,B3,-B2)A B C12345MULTIPLE INTERNAL RATES OF RETURNDiscount rate 6%NPV -3.99 - =NPV(B2,B7:B11)+B6 DATA TABLEDiscountrate NPVYear Cash flow -3.990 -145 0% -20.001 100 3% -10.512 100 6% -3.993 100 9% 0.244 100 12% 2.695 -275 15% 3.7718% 3.8021% 3.0224% 1.6227% -0.2430% -2.4433% -4.9036% -7.5339% -10.27Note: For a discussion of how to create data tables in Excelsee Chapter 30.Identifying the two IRRsFirst IRR 8.78% - =IRR(B6:B11,0)Second IRR 26.65% - =IRR(B6:B11,0.3)T w o I R R s4 0 %3 5 %3 0 %2 5 %2 0 %1 5 %1 0 %5 %0 %- 2 5 .0 0- 2 0 .0 0- 1 5 .0 0- 1 0 .0 0- 5 .0 00 .0 05 .0 0D i s c o u n t r a t eNetpresentvalueA B C D E F G H12345678910111213141516171819202122232425262728293031Table header, - =B3MULTIPLE INTERNAL RATES OF RETURNNote: For a discussion of how to create data tables in ExcelI12345678910111213141516171819202122232425262728293031多个内部收益率折现率 6%净现值 -3.99 - =NPV(B2,B7:B11)+B6 数据表折现率 净现值年 现金流 -3.990 -145 0% -20.001 100 3% -10.512 100 6% -3.993 100 9% 0.244 100 12% 2.695 -275 15% 3.7718% 3.8021% 3.0224% 1.6227% -0.2430% -2.4433% -4.9036% -7.5339% -10.27注意：EXCEL中如何建立数据表的讨论，请见本书第二十六页两个IRR的确定第一个IRR 8.78% - =IRR(B6:B11,0)第二个IRR 26.65% - =IRR(B6:B11,0.3)两 个 IRR4 0 %3 5 %3 0 %2 5 %2 0 %1 5 %1 0 %5 %0 %- 2 5 .0 0- 2 0 .0 0- 1 5 .0 0- 1 0 .0 0- 5 .0 00 .0 05 .0 0折 现 率净现值A B C D E F G H12345678910111213141516171819202122232425262728293031表头, - =B3多个内部收益率I12345678910111213141516171819202122232425262728293031MULTIPLE INTERNAL RATES OF RETURNDiscount rate 6%NPV -3.99 - =NPV(B2,B7:B11)+B6 DATA TABLEDiscountrate NPVYear Cash flow -3.990 -145 0% -20.001 100 3% -10.512 100 6% -3.993 100 9% 0.244 100 12% 2.695 -275 15% 3.7718% 3.8021% 3.0224% 1.6227% -0.2430% -2.4433% -4.9036% -7.5339% -10.27Note: For a discussion of how to create data tables in Excelsee Chapter 30.Identifying the two IRRsFirst IRR 8.78% - =IRR(B6:B11,0.1)Second IRR 26.65% - =IRR(B6:B11,0.5)T w o I R R s4 0 %3 5 %3 0 %2 5 %2 0 %1 5 %1 0 %5 %0 %- 2 5 .0 0- 2 0 .0 0- 1 5 .0 0- 1 0 .0 0- 5 .0 00 .0 05 .0 0D i s c o u n t r a t eNetpresentvalueA B C D E F G H12345678910111213141516171819202122232425262728293031Table header, - =B3MULTIPLE INTERNAL RATES OF RETURNNote: For a discussion of how to create data tables in ExcelI12345678910111213141516171819202122232425262728293031多个内部收益率折现率 6%净现值 -3.99 - =NPV(B2,B7:B11)+B6 数据表折现率 净现值年 现金流 -3.990 -145 0% -20.001 100 3% -10.512 100 6% -3.993 100 9% 0.244 100 12% 2.695 -275 15% 3.7718% 3.8021% 3.0224% 1.6227% -0.2430% -2.4433% -4.9036% -7.5339% -10.27注意：EXCEL中如何建立数据表的讨论，请见本书第二十六页两个的确定第一个 8.78% - =IRR(B6:B11,0)第二个 26.65% - =IRR(B6:B11,0.3)T w o I R R s4 0 %3 5 %3 0 %2 5 %2 0 %1 5 %1 0 %5 %0 %- 2 5 .0 0- 2 0 .0 0- 1 5 .0 0- 1 0 .0 0- 5 .0 00 .0 05 .0 0D i s c o u n t r a t eNetpresentvalue两 个 IRR折 现 率净现值A B C D E F G H12345678910111213141516171819202122232425262728293031Table header, - =B3多个内部收益率I12345678910111213141516171819202122232425262728293031BOND CASH FLOWS: NPV CROSSES x-AXIS ONLY ONCE, SO THERE IS ONLY ONE IRRYear Cash flow Data table: Effect of0 -800 discount rate on NPV1 100 1,000.00 - =NPV(E4,B4:B11)+B3, table header2 100 0% 1,000.003 100 2% 786.044 100 4% 603.965 100 6% 448.396 100 8% 314.937 100 10% 200.008 1100 12% 100.6514% 14.45IRR 14.36% - =IRR(B3:B11) 16% -60.6218% -126.2120% -183.72N P V o f B o n d C a s h F l o w s2 0 %1 5 %1 0 %5 %0 %- 4 0 0- 2 0 002 0 04 0 06 0 08 0 01 0 0 01 2 0 0D i s c o u n t r a t eNPVA B C D E F G H I1234567891011121314151617BOND CASH FLOWS: NPV CROSSES x-AXIS ONLY ONCE, SO THERE IS ONLY ONE IRR- =NPV(E4,B4:B11)+B3, table headerN P V o f B o n d C a s h F l o w s2 0 %1 5 %1 0 %5 %0 %- 4 0 0- 2 0 002 0 04 0 06 0 08 0 01 0 0 01 2 0 0D i s c o u n t r a t eNPVJ K1234567891011121314151617债券的现金流量：NPV曲线与X轴相交一次，因此只有一个IRR年 现金流 数据表：0 -800 折现率对的影响1 100 1,000.00- =NPV(E4,B4:B11)+B3, 表头2 100 0% 1,000.003 100 2% 786.044 100 4% 603.965 100 6% 448.396 100 8% 314.937 100 10% 200.008 1100 12% 100.6514% 14.45IRR 14.36% - =IRR(B3:B11) 16% -60.6218% -126.2120% -183.72债 券 现 金 流 量 的 NPV2 0 %1 5 %1 0 %5 %0 %- 4 0 0- 2 0 002 0 04 0 06 0 08 0 01 0 0 01 2 0 0折 现 率NPVA B C D E F G H I1234567891011121314151617债券的现金流量：NPV曲线与X轴相交一次，因此只有一个IRR债 券 现 金 流 量 的 NPV2 0 %1 5 %1 0 %5 %0 %- 4 0 0- 2 0 002 0 04 0 06 0 08 0 01 0 0 01 2 0 0折 现 率NPVJ K1234567891011121314151617FLAT PAYMENT SCHEDULESLoan principal 10,000Interest rate 7%Loan term 6 - Number of years over which loan is repaidAnnual payment 2,097.96 - =PMT(B3,B4,-B2)Split payment into:YearPrincipalat beginningof yearPayment atend of year InterestReturn ofprincipal1 10,000.00 2,097.96 700.00 1,397.962 8,602.04 2,097.96 602.14 1,495.823 7,106.23 2,097.96 497.44 1,600.524 5,505.70 2,097.96 385.40 1,712.565 3,793.15 2,097.96 265.52 1,832.446 1,960.71 2,097.96 137.25 1,960.717 0.00= $ B $ 3 * C 9= D 9 -E 9= C 9 -F 9A B C D E F G123456789101112131415等额偿还计划贷款本金 10,000利率 7%贷款期限 6 - 贷款被偿还的结束年度年还款额 2,097.96 - =PMT(B3,B4,-B2)偿还额的分解：年 年初的本金 年末偿还额 利息 本金返还1 10,000.00 2,097.96 700.00 1,397.962 8,602.04 2,097.96 602.14 1,495.823 7,106.23 2,097.96 497.44 1,600.524 5,505.70 2,097.96 385.40 1,712.565 3,793.15 2,097.96 265.52 1,832.446 1,960.71 2,097.96 137.25 1,960.717 0.00= $ B $ 3 * C 9= D 9 -E 9= C 9 -F 9A B C D E F G123456789101112131415SIMPLE FUTURE VALUEInterest 10%YearAccountbalance,beginning ofyearInterestearnedduring yearTotal inaccount,end year1 1,000.00 100.00 1,100.00 - =C5+B52 1,100.00 110.00 1,210.00 - =C6+B63 1,210.00 121.00 1,331.004 1,331.00 133.10 1,464.105 1,464.10 146.41 1,610.516 1,610.51 161.05 1,771.567 1,771.56 177.16 1,948.728 1,948.72 194.87 2,143.599 2,143.59 214.36 2,357.9510 2,357.95 235.79 2,593.7411 2,593.74A simpler way 2,593.74 - =B5*(1+B2)10= D 5= $ B $ 2 * B 5A B C D E1234567891011121314151617简单终值利率 10%年 年初账户余额 年内获得利息 年末账户合计1 1,000.00 100.00 1,100.00 - =C5+B52 1,100.00 110.00 1,210.00 - =C6+B63 1,210.00 121.00 1,331.004 1,331.00 133.10 1,464.105 1,464.10 146.41 1,610.516 1,610.51 161.05 1,771.567 1,771.56 177.16 1,948.728 1,948.72 194.87 2,143.599 2,143.59 214.36 2,357.9510 2,357.95 235.79 2,593.7411 2,593.74简便方法 2,593.74 - =B5*(1+B2)10= D 5= $ B $ 2 * B 5A B C D E1234567891011121314151617FUTURE VALUE WITH ANNUAL DEPOSITSInterest 10%Annual deposit 1,000 - Made today and at beginning of each of next 9 yearsNumber of deposits 10YearAccountbalance,beginning ofyearDeposit atbeginningof yearInterestearnedduring yearTotal inaccount,end year1 0.00 1,000 100.00 1,100.00 - =D7+C7+B72 1,100.00 1,000 210.00 2,310.00 - =D8+C8+B83 2,310.00 1,000 331.00 3,641.004 3,641.00 1,000 464.10 5,105.105 5,105.10 1,000 610.51 6,715.616 6,715.61 1,000 771.56 8,487.177 8,487.17 1,000 948.72 10,435.898 10,435.89 1,000 1,143.59 12,579.489 12,579.48 1,000 1,357.95 14,937.4210 14,937.42 1,000 1,593.74 17,531.17Future value 17,531.17 - =FV(B2,B4,-B3,1)= $ B $ 2 * (B 7 + C 7 )= E 7A B C D E F12345678910111213141516171819每年存款的终值利率 10%年金存款 1,000 - Made today and at beginning of each of next 9 years存款期数 10年 账户年初余额 每年初的存款 获得的利息 年末总的账户余额1 0.00 1,000 100.00 1,100.002 1,100.00 1,000 210.00 2,310.003 2,310.00 1,000 331.00 3,641.004 3,641.00 1,000 464.10 5,10