problems07
Exercise 1Page 1BASIC LEVERAGED LEASE EXAMPLEcost of asset 1,000,000lease term 15residual value 300,000equity 200,000debt 800,000 15-year term loan, equal payments of interest & principalinterest 10%annual debt payment 105,179annual rent received 110,000tax rate 40%principalrental or at start loanyear equity invested salvage depreciation of year payment0 -200,0001 110,000 66,667 800,000 105,1792 110,000 66,667 774,821 105,1793 110,000 66,667 747,124 105,1794 110,000 66,667 716,657 105,1795 110,000 66,667 683,144 105,1796 110,000 66,667 646,280 105,1797 110,000 66,667 605,728 105,1798 110,000 66,667 561,122 105,1799 110,000 66,667 512,056 105,17910 110,000 66,667 458,082 105,17911 110,000 66,667 398,711 105,17912 110,000 66,667 333,403 105,17913 110,000 66,667 261,565 105,17914 110,000 66,667 182,542 105,17915 110,000 66,667 95,617 105,17916 300,000MPM 4.18% - Solved by SolverIRR 4.18%CALCULATING THE MULTIPLE-PHASES METHOD (MPM) RETURN-REQUIRES SOLVERinvestment at attribution of cash flowyear beginning of year cash flow income investment1 200,000 19,488 8,357 11,1312 188,869 18,480 7,892 10,5883 178,281 17,373 7,450 9,9234 168,358 16,154 7,035 9,1195 159,239 14,813 6,654 8,1606 151,079 13,339 6,313 7,0267 144,054 11,717 6,019 5,6978 138,356 9,933 5,781 4,1519 134,205 7,970 5,608 2,36210 131,843 5,811 5,509 30211 131,541 3,436 5,497 -2,06012 133,601 824 5,583 -4,75913 138,360 -2,050 5,781 -7,83114 146,191 -5,211 6,109 -11,31915 157,511 -8,688 6,582 -15,26916 172,780 180,000 7,220 172,78017 0Exercise 1Page 2repaymentof CASHinterest principal FLOW-200,00080,000 25,179 19,488 - =(1-tax)*C16+tax*D16-(1-tax)*G16-H1677,482 27,697 18,48074,712 30,467 17,37371,666 33,513 16,15468,314 36,865 14,81364,628 40,551 13,33960,573 44,606 11,71756,112 49,067 9,93351,206 53,973 7,97045,808 59,371 5,81139,871 65,308 3,43633,340 71,839 82426,156 79,023 -2,05018,254 86,925 -5,2119,562 95,617 -8,688180,000Explanation: When all of the cash flowsfrom the lease are positive, the MPM returnwill be the same as the IRR. When the depreciationis straight-line over the life of the asset, thiswill be the case.BASIC LEVERAGED LEASE EXAMPLECALCULATING THE MULTIPLE-PHASES METHOD (MPM) RETURN-REQUIRES SOLVERExercise 1 (中文)Page 3基本杠杆租赁为例成本资产 1,000,000租赁期 15剩余价值 300,000权益 200,000负债 800,000 15年期贷款,等于利息和本金之和利息 10%每年偿还的负债 105,179每年租金收入 110,000税率 40%出租或 贷款年 权益投资 救助 折旧 年初本金 偿付0 -200,0001 110,000 66,667 800,000 105,1792 110,000 66,667 774,821 105,1793 110,000 66,667 747,124 105,1794 110,000 66,667 716,657 105,1795 110,000 66,667 683,144 105,1796 110,000 66,667 646,280 105,1797 110,000 66,667 605,728 105,1798 110,000 66,667 561,122 105,1799 110,000 66,667 512,056 105,17910 110,000 66,667 458,082 105,17911 110,000 66,667 398,711 105,17912 110,000 66,667 333,403 105,17913 110,000 66,667 261,565 105,17914 110,000 66,667 182,542 105,17915 110,000 66,667 95,617 105,17916 300,000MPM 4.18% - Solved by SolverIRR 4.18%计算MPM回报-需要规划求解年 年初投资 现金流 收入现金流分配 投资1 200,000 19,488 8,357 11,1312 188,869 18,480 7,892 10,5883 178,281 17,373 7,450 9,9234 168,358 16,154 7,035 9,1195 159,239 14,813 6,654 8,1606 151,079 13,339 6,313 7,0267 144,054 11,717 6,019 5,6978 138,356 9,933 5,781 4,1519 134,205 7,970 5,608 2,36210 131,843 5,811 5,509 30211 131,541 3,436 5,497 -2,06012 133,601 824 5,583 -4,75913 138,360 -2,050 5,781 -7,83114 146,191 -5,211 6,109 -11,31915 157,511 -8,688 6,582 -15,26916 172,780 180,000 7,220 172,78017 0Exercise 1 (中文)Page 4利息 本金的偿付 现金流-200,00080,000 25,179 19,488 - =(1-tax)*C16+tax*D16-(1-tax)*G16-H1677,482 27,697 18,48074,712 30,467 17,37371,666 33,513 16,15468,314 36,865 14,81364,628 40,551 13,33960,573 44,606 11,71756,112 49,067 9,93351,206 53,973 7,97045,808 59,371 5,81139,871 65,308 3,43633,340 71,839 82426,156 79,023 -2,05018,254 86,925 -5,2119,562 95,617 -8,688180,000解释:当全部的租赁所得现金流为正时,MPM回报率将等于IRR。当折旧时在资产生命周期中利用直线法时,这就是我们现在讨论的案例基本杠杆租赁为例Exercise 2Page 5To answer this question, you have to run the Solver macro found on the This program can also be run by ctr+a.If the macro does not work, read Adding Solver Reference to VBA on the disk with the book.BASIC LEVERAGED LEASE EXAMPLEcost of asset 1,000,000lease term 15residual value 300,000equity 200,000debt 800,000 15-year term loan, equal payments of interest & principalinterest 10%annual debt payment 105,179annual rent received 150,000tax rate 40%principalrental or at start loanyear equity invested salvage depreciation of year payment0 -200,0001 150,000 142,800 800,000 105,1792 150,000 244,900 774,821 105,1793 150,000 174,900 747,124 105,1794 150,000 125,000 716,657 105,1795 150,000 89,200 683,144 105,1796 150,000 89,200 646,280 105,1797 150,000 89,200 605,728 105,1798 150,000 44,800 561,122 105,1799 150,000 512,056 105,17910 150,000 458,082 105,17911 150,000 398,711 105,17912 150,000 333,403 105,17913 150,000 261,565 105,17914 150,000 182,542 105,17915 150,000 95,617 105,17916 300,000Start 120,000Increase 2,000MPM 31.34%IRR 34.61% - =IRR(I19:I35,0)0Year Investment atbeginning of year Cash flow Income Investment1 200,000 73,941 62,686 11,2552 188,745 113,774 59,158 54,6163 134,129 84,666 42,040 42,6264 91,502 63,487 28,679 34,8085 56,694 47,827 17,770 30,0576 26,637 46,352 8,349 38,0037 -11,366 44,730 0 44,7308 -56,096 25,186 0 25,1869 -81,282 5,303 0 5,303Exercise 2Page 610 -86,585 3,144 0 3,14411 -89,729 769 0 76912 -90,499 -1,843 0 -1,84313 -88,656 -4,716 0 -4,71614 -83,939 -7,877 0 -7,87715 -76,062 -11,354 0 -11,35416 -64,708 180,000 0 180,00017 -244,708Resultsrent MPM IRR IRR - MPM120,000 31.34% 19.68% 11.66%122,000 31.34% 20.90%124,000 31.34% 22.07%126,000 31.34% 23.19%128,000 31.34% 24.28%130,000 31.34% 25.34%132,000 31.34% 26.36%134,000 31.34% 27.35%136,000 31.34% 28.33%138,000 31.34% 29.28%140,000 31.34% 30.21%142,000 31.34% 31.12%144,000 31.34% 32.01%146,000 31.34% 32.89%148,000 31.34% 33.76%150,000 31.34% 34.61%Exercise 2Page 7repaymentof CASHinterest principal FLOW-200,00080,000 25,179 73,94177,482 27,697 113,77474,712 30,467 84,66671,666 33,513 63,48768,314 36,865 47,82764,628 40,551 46,35260,573 44,606 44,73056,112 49,067 25,18651,206 53,973 5,30345,808 59,371 3,14439,871 65,308 76933,340 71,839 -1,84326,156 79,023 -4,71618,254 86,925 -7,8779,562 95,617 -11,354180,000BASIC LEVERAGED LEASE EXAMPLEExercise 2 (中文)Page 8要回答这个问题,你必须运行宏中的求解该程序也可以通过ctrl+a解答如果宏不起作用,请阅读“添加求解参考VBA的关于与本书的磁盘”。基本杠杆租赁为例成本资产 1,000,000租赁期 15剩余价值 300,000权益 200,000负债 800,00015年期贷款,等于利息和本金之和15年期贷款,等于利息和本金之和利息 10%每年偿还的负债 105,179每年租金收入 150,000税率 40%出租或 贷款权益投资 救助 折旧 年初本金 偿付 利息0 -200,0001 150,000 142,800 800,000 105,1792 150,000 244,900 774,821 105,1793 150,000 174,900 747,124 105,1794 150,000 125,000 716,657 105,1795 150,000 89,200 683,144 105,1796 150,000 89,200 646,280 105,1797 150,000 89,200 605,728 105,1798 150,000 44,800 561,122 105,1799 150,000 512,056 105,17910 150,000 458,082 105,17911 150,000 398,711 105,17912 150,000 333,403 105,17913 150,000 261,565 105,17914 150,000 182,542 105,17915 150,000 95,617 105,17916 300,000起初本金 120,000增长 2,000MPM 31.34%IRR 34.61% - =IRR(I19:I35,0)0年 年初投资 现金流 收入现金流分配 投资1 200,000 73,941 62,686 11,2552 188,745 113,774 59,158 54,6163 134,129 84,666 42,040 42,6264 91,502 63,487 28,679 34,8085 56,694 47,827 17,770 30,0576 26,637 46,352 8,349 38,0037 -11,366 44,730 0 44,7308 -56,096 25,186 0 25,1869 -81,282 5,303 0 5,30310 -86,585 3,144 0 3,144Exercise 2 (中文)Page 911 -89,729 769 0 76912 -90,499 -1,843 0 -1,84313 -88,656 -4,716 0 -4,71614 -83,939 -7,877 0 -7,87715 -76,062 -11,354 0 -11,35416 -64,708 180,000 0 180,00017 -244,708结果租金 MPM IRR IRR - MPM120,000 31.34% 19.68% 11.66%122,000 31.34% 20.90%124,000 31.34% 22.07%126,000 31.34% 23.19%128,000 31.34% 24.28%130,000 31.34% 25.34%132,000 31.34% 26.36%134,000 31.34% 27.35%136,000 31.34% 28.33%138,000 31.34% 29.28%140,000 31.34% 30.21%142,000 31.34% 31.12%144,000 31.34% 32.01%146,000 31.34% 32.89%148,000 31.34% 33.76%150,000 31.34% 34.61%Exercise 2 (中文)Page 10CASH本金的偿付 现金流 FLOW-200,00080,000 25,179 73,94177,482 27,697 113,77474,712 30,467 84,66671,666 33,513 63,48768,314 36,865 47,82764,628 40,551 46,35260,573 44,606 44,73056,112 49,067 25,18651,206 53,973 5,30345,808 59,371 3,14439,871 65,308 76933,340 71,839 -1,84326,156 79,023 -4,71618,254 86,925 -7,8779,562 95,617 -11,354180,000基本杠杆租赁为例Explanation of Exercise 2Page 11AN EXPLANATION OF EXERCISE 2The VBA program on this workbook runs the Solver for a number of different values of the lease rent. The startingvalue is given in the cell Start (B40), and the increases are given by the cell Increase (B41).Thus, for the values given in this particular case, ctr+a will find the MPM for rents of 120000,122000, 124000 . Note that the MPM = IRR.Here you can see that the smallest rent for which the MPM = IRR is 142,000 (this number is, of course, approximate).Explanation of Exercise 2 (中文)Page 12习题2的一个解释在此工作簿中的VBA程序运行为租赁租金的不同值的数目求解. 初值是格子“初值”(B40),增加值是由格子“增加值”(B41)。因此,在这个案例下的数值,ctrl +a 将算得租金为120000,122000,124000注意到 MPM = IRR.这里你发现最小的满足MPM = IRR的数值是142,000 (当然这个数值是近似的).因此,在这个案例下的数值,ctrl +a 将算得租金为120000,122000,124000