problems01

Discount rate 8%NPV 71.01 0 and hence you should purchase the asset.Note that the IRR discount rate, which leads to the same decision.折现率 8%NPV 71.01 折现率，因此我们得到相同的结论Cost 10,000Payment $2,983.16 0 for interest 52% 24.26678rates between 6.34% and 60.20%, 56% 12.47534you would invest in the project 60% 0.606037if the discount rate is 20%. 64% -11.214368% -22.892572% -34.3618 0 %7 0 %6 0 %5 0 %4 0 %3 0 %2 0 %1 0 %0 %- 1 5 0- 1 0 0- 5 005 01 0 0A B C D E F G H123456789101112131415161718192021220，则如果折现率是20%，我们应该在该项目上投资52% 24.2667856% 12.4753460% 0.60603764% -11.214368% -22.892572% -34.3618 0 %7 0 %6 0 %5 0 %4 0 %3 0 %2 0 %1 0 %0 %- 1 5 0- 1 0 0- 5 005 01 0 0A B C D E F G H123456789101112131415161718192021222324- =B2 , data table header8 0 %7 0 %6 0 %5 0 %4 0 %3 0 %2 0 %1 0 %0 %- 1 5 0- 1 0 0- 5 005 01 0 0I J K L M123456789101112131415161718192021222324IRR? 10.00%LOAN TABLEDivision of paymentbetween:Year Cashflow YearPrincipalat beginningof yearPaymentat end ofyearInterest Principal0 -800 1 800.00 300.00 80.00 220.001 300 2 580.00 200.00 58.00 142.002 200 3 438.00 150.00 43.80 106.203 150 4 331.80 122.00 33.18 88.824 122 5 242.98 133.00 24.30 108.705 133 6 134.28 - Should be zero for IRRIRR 5.07% - This uses the Excel formula =IRR(B4:B9)A B C D E F G H123456789101112IRR? 10.00%贷款表 偿付额分配：年 现金流 年 期初本金 年末偿付 利息 本金0 -800 1 800.00 300.00 80.00 220.001 300 2 580.00 200.00 58.00 142.002 200 3 438.00 150.00 43.80 106.203 150 4 331.80 122.00 33.18 88.824 122 5 242.98 133.00 24.30 108.705 133 6 134.28 - 应为0IRR 5.07% 运用公式=IRR(B4:B9)A B C D E F G H123456789101112IRR? 3.00%LOAN TABLEDivision of paymentbetween:Year Cashflow YearPrincipalat beginningof yearPaymentat end ofyearInterest Principal0 -800 1 800.00 300.00 24.00 276.001 300 2 524.00 200.00 15.72 184.282 200 3 339.72 150.00 10.19 139.813 150 4 199.91 122.00 6.00 116.004 122 5 83.91 133.00 2.52 130.485 133 6 -46.57 - Should be zero for IRRIRR 5.07% This uses the Excel formula =IRR(B4:B9)A B C D E F G H I12345678910111213J K L M N12345678910111213IRR? 3.00%贷款表 偿付额分配：年 现金流 年 期初本金 年末偿付 利息 本金0 -800 1 800.00 300.00 24.00 276.001 300 2 524.00 200.00 15.72 184.282 200 3 339.72 150.00 10.19 139.813 150 4 199.91 122.00 6.00 116.004 122 5 83.91 133.00 2.52 130.485 133 6 -46.57 - 应为0IRR 5.07% 运用公式 =IRR(B4:B9)A B C D E F G H I1234567891011121314J K L M N O P Q1234567891011121314Loan principal 100,000Term (years) 5Interest 13%Annual payment $28,431.45 - =PMT(B3,B2,-B1)贷款本金 100,000期数 5利息 13%每年偿还额 $28,431.45 - =PMT(B3,B2,-B1)Loan principal 15,000Interest rateannual 15%monthly 1.25% - =B3/12Loan term (months) 48Monthly payment $417.46 - =PMT(B4,B5,-B1)Split of paymentbetween:MonthPrincipal atbeginning ofmonthPayment Interest Principal1 15,000.00 417.46 187.50 229.962 14,770.04 417.46 184.63 232.843 14,537.20 417.46 181.72 235.754 14,301.46 417.46 178.77 238.695 14,062.76 417.46 175.78 241.686 13,821.09 417.46 172.76 244.707 13,576.39 417.46 169.70 247.768 13,328.63 417.46 166.61 250.859 13,077.78 417.46 163.47 253.9910 12,823.79 417.46 160.30 257.1611 12,566.63 417.46 157.08 260.3812 12,306.25 417.46 153.83 263.6313 12,042.62 417.46 150.53 266.9314 11,775.69 417.46 147.20 270.2715 11,505.42 417.46 143.82 273.6416 11,231.78 417.46 140.40 277.0617 10,954.71 417.46 136.93 280.5318 10,674.19 417.46 133.43 284.0319 10,390.15 417.46 129.88 287.5820 10,102.57 417.46 126.28 291.1821 9,811.39 417.46 122.64 294.8222 9,516.57 417.46 118.96 298.5023 9,218.07 417.46 115.23 302.2424 8,915.83 417.46 111.45 306.0125 8,609.82 417.46 107.62 309.8426 8,299.98 417.46 103.75 313.7127 7,986.27 417.46 99.83 317.6328 7,668.64 417.46 95.86 321.6029 7,347.03 417.46 91.84 325.6230 7,021.41 417.46 87.77 329.6931 6,691.72 417.46 83.65 333.8132 6,357.90 417.46 79.47 337.9933 6,019.91 417.46 75.25 342.2134 5,677.70 417.46 70.97 346.4935 5,331.21 417.46 66.64 350.8236 4,980.39 417.46 62.25 355.2137 4,625.18 417.46 57.81 359.6538 4,265.54 417.46 53.32 364.1439 3,901.39 417.46 48.77 368.6940 3,532.70 417.46 44.16 373.3041 3,159.40 417.46 39.49 377.9742 2,781.43 417.46 34.77 382.6943 2,398.74 417.46 29.98 387.4844 2,011.26 417.46 25.14 392.3245 1,618.94 417.46 20.24 397.2246 1,221.71 417.46 15.27 402.1947 819.52 417.46 10.24 407.2248 412.31 417.46 5.15 412.3149 0.00Part c of questionPV ofremainingpaymentsSameanswerusing PV$15,000.00 - =NPV($B$4,D11:$D$58) $15,000.00 - =PV($B$4,$B$5-B11+1,-$B$7)$14,770.04 - =NPV($B$4,D12:$D$58) $14,770.04 - =PV($B$4,$B$5-B12+1,-$B$7)$14,537.20 - =NPV($B$4,D13:$D$58) $14,537.20 - =PV($B$4,$B$5-B13+1,-$B$7)$14,301.46 $14,301.46$14,062.76 $14,062.76$13,821.09 $13,821.09$13,576.39 $13,576.39$13,328.63 $13,328.63$13,077.78 $13,077.78$12,823.79 $12,823.79贷款本金 15,000利息率每年 15%每月 1.25% - =B3/12贷款期数（月） 48每月支付 $417.46 - =PMT(B4,B5,-B1)偿付额分配：月 年初本金 偿付额 利息 本金1 15,000.00 417.46 187.50 229.962 14,770.04 417.46 184.63 232.843 14,537.20 417.46 181.72 235.754 14,301.46 417.46 178.77 238.695 14,062.76 417.46 175.78 241.686 13,821.09 417.46 172.76 244.707 13,576.39 417.46 169.70 247.768 13,328.63 417.46 166.61 250.859 13,077.78 417.46 163.47 253.9910 12,823.79 417.46 160.30 257.1611 12,566.63 417.46 157.08 260.3812 12,306.25 417.46 153.83 263.6313 12,042.62 417.46 150.53 266.9314 11,775.69 417.46 147.20 270.2715 11,505.42 417.46 143.82 273.6416 11,231.78 417.46 140.40 277.0617 10,954.71 417.46 136.93 280.5318 10,674.19 417.46 133.43 284.0319 10,390.15 417.46 129.88 287.5820 10,102.57 417.46 126.28 291.1821 9,811.39 417.46 122.64 294.8222 9,516.57 417.46 118.96 298.5023 9,218.07 417.46 115.23 302.2424 8,915.83 417.46 111.45 306.0125 8,609.82 417.46 107.62 309.8426 8,299.98 417.46 103.75 313.7127 7,986.27 417.46 99.83 317.6328 7,668.64 417.46 95.86 321.6029 7,347.03 417.46 91.84 325.6230 7,021.41 417.46 87.77 329.6931 6,691.72 417.46 83.65 333.8132 6,357.90 417.46 79.47 337.9933 6,019.91 417.46 75.25 342.2134 5,677.70 417.46 70.97 346.4935 5,331.21 417.46 66.64 350.8236 4,980.39 417.46 62.25 355.2137 4,625.18 417.46 57.81 359.6538 4,265.54 417.46 53.32 364.1439 3,901.39 417.46 48.77 368.6940 3,532.70 417.46 44.16 373.3041 3,159.40 417.46 39.49 377.9742 2,781.43 417.46 34.77 382.6943 2,398.74 417.46 29.98 387.4844 2,011.26 417.46 25.14 392.3245 1,618.94 417.46 20.24 397.2246 1,221.71 417.46 15.27 402.1947 819.52 417.46 10.24 407.2248 412.31 417.46 5.15 412.3149 0.00关于问题C剩余本金的PV运用PV的相同答案$15,000.00 - =NPV($B$4,D11:$D$58) $15,000.00 - =PV($B$4,$B$5-B11+1,-$B$7)$14,770.04 - =NPV($B$4,D12:$D$58) $14,770.04 - =PV($B$4,$B$5-B12+1,-$B$7)$14,537.20 - =NPV($B$4,D13:$D$58) $14,537.20 - =PV($B$4,$B$5-B13+1,-$B$7)$14,301.46 $14,301.46$14,062.76 $14,062.76$13,821.09 $13,821.09$13,576.39 $13,576.39$13,328.63 $13,328.63$13,077.78 $13,077.78$12,823.79 $12,823.79Cost of car, cash 30,000MonthDeferred payment plan 0Cash payment 5,000 1Monthly payment 1,050 2Number of months 30 34Bank car loan rate (annual) 15% 5Bank car loan rate (monthly) 1.25% 679.a. Present value of deferred payment plan 31,133.35 - =B4+PV(B9,B6,-B5) 89Dealers monthly IRR 1.56% - =IRR(G3:G33) 10Annualized (in this case, multiplied by 12) 18.73% - =B13*12 1112131415161718192021222324252627282930A B C D123456789101112131415161718192021222324252627282930313233Cash paymentPaymentunderdeferredpaymentplanDifference30,000 5,000 25,000 - =E3-F30 1,050 -1,050 - =E4-F40 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,050E F G H123456789101112131415161718192021222324252627282930313233现金成本 30,000月延期偿付计划 0现金偿付 5,000 1月偿付额 1,050 2月数 30 34银行汽车贷款利率(按年) 15% 5银行汽车贷款利率(按月) 1.25% 679.a. 延期偿付计划现值 31,133.35 - =B4+PV(B9,B6,-B5) 89成交者每月的IRR 1.56% - =IRR(G3:G33) 10转化成按年计算 (此例中，乘以12) 18.73% - =B13*12 1112131415161718192021222324252627282930A B C D123456789101112131415161718192021222324252627282930313233现金偿付 延期支付计划下的支付额 差值30,000 5,000 25,000 - =E3-F30 1,050 -1,050 - =E4-F40 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,0500 1,050 -1,050E F G H123456789101112131415161718192021222324252627282930313233Annual payment 15,000Interest rate 10%Number of years 5Total value $91,576.50 - =FV(B2,B3,-B1,0)YearAccumulationat beginning ofyearPayment atend of yearAnnualinterest1 0 15,000 0.002 15,000 15,000 1,500.003 31,500456A B C D123456789101112年偿付额 15,000利息率 10%年数 5总值 $91,576.50 - =FV(B2,B3,-B1,0)年 年初累计值 年末偿还额 每年利息1 0 15,000 0.002 15,000 15,000 1,500.003 31,500456A B C D123456789101112Annual payment 15,000Interest rate 10%Number of years 5Total value $91,576.50 - =FV(B2,B3,-B1,0)YearAccumulationat beginning ofyearPayment atend of yearAnnualinterest1 0 15,000 0.00 - =$B$2*B72 15,000 15,000 1,500.00 - =$B$2*B83 31,500 15,000 3,150.004 49,650 15,000 4,965.005 69,615 15,000 6,961.506 91,577A B C D E123456789101112年偿付额 15,000利息率 10%年数 5总值 $91,576.50 - =FV(B2,B3,-B1,0)年 年初累计值 年末偿还额 每年利息1 0 15,000 0.00 - =$B$2*B72 15,000 15,000 1,500.00 - =$B$2*B83 31,500 15,000 3,150.004 49,650 15,000 4,965.005 69,615 15,000 6,961.506 91,577A B C D E123456789101112PAYMENTS MADE AT BEGINNING OFYEARAnnual payment 15,000Interest rate 10%Number of years 5Total value $100,734.15 - =FV(B3,B4,-B2,1)YearAccumulationat begining ofyearPayment atbeginning ofyearAnnualinterest1 0 15,000 1,500.00 - =$B$3*(B8+C8)2 16,500 15,000 3,150.00 - =$B$3*(B9+C9)3 34,650 15,000 4,965.004 54,615 15,000 6,961.505 76,577 15,000 9,157.656 100,734A B C D E12345678910111213年初偿付额年偿付额 15,000利息率 10%年数 5总值 $100,734.15 - =FV(B3,B4,-B2,1)年 年初累计值 年末偿还额 每年利息1 0 15,000 1,500.00 - =$B$3*(B8+C8)2 16,500 15,000 3,150.00 - =$B$3*(B9+C9)3 34,650 15,000 4,965.004 54,615 15,000 6,961.505 76,577 15,000 9,157.656 100,734A B C D E12345678910111213Monthly payment 250Number of months 120Effective monthly return?Accumulation - =FV(B4,B2,-B1,1)A B C12345每月偿付额 250月数 120实际月反还额累计值 - =FV(B4,B2,-B1,1)A B C12345Monthly payment 250Number of months 120Effective monthly return? 1.514%Accumulation 85,000 - =FV(B4,B2,-B1,1)Effective annual interestUsing monthly compounding 19.77% - =(1+B4)12-1Multiplying the monthly return by 12 18.17% - =B4*12A B C123456789每月偿付额 250月数 120实际月反还额 1.514%累计值 85,000 - =FV(B4,B2,-B1,1)实际年利率运用月复合 19.77% - =(1+B4)12-1每月返还额乘以12 18.17% - =B4*12A B C123456789SAVING FOR RETIREMENTInterest rate 10%YearNumber of payments 30 0Number of withdrawals 20 1Size of annual withdrawal 100,000 23Size of payment 5,175.61 45Present value of payments 53,669.00 - =PV(B2,30,-B7,1) 6Present value of withdrawals 53,669.00 - =PV(B2,20,-B5,1)/(1+B2)30 78Difference (this should be zero) 0.00 91011Check 12Future value at age 65 of payments $936,492.01 - =FV(B2,30,-B7,1) 13Present value at age 65 of withdrawals $936,492.01 - =PV(B2,20,-B5,1) 141516Use Solver to do this easily-you can also do 17this analytically. 181920This problem has a one-step analyticsolution: see next spreadsheet212223242526272829303132333435363738394041424344454647484950AgeTotal,beginning ofyearPayment atbeginning ofyearWithdrawal atbeginning ofyearTotal endof year35 0.00 5,175.61 0.00 5,693.1736 5,693.17 5,175.61 0.00 11,955.6537 11,955.65 5,175.61 0.00 18,844.3838 18,844.38 5,175.61 0.00 26,421.9939 26,421.99 5,175.61 0.00 34,757.3640 34,757.36 5,175.61 0.00 43,926.2641 43,926.26 5,175.61 0.00 54,012.0542 54,012.05 5,175.61 0.00 65,106.4343 65,106.43 5,175.61 0.00 77,310.2444 77,310.24 5,175.61 0.00 90,734.4345 90,734.43 5,175.61 0.00 105,501.0446 105,501.04 5,175.61 0.00 121,744.3147 121,744.31 5,175.61 0.00 139,611.9148 139,611.91 5,175.61 0.00 159,266.2649 159,266.26 5,175.61 0.00 180,886.0650 180,886.06 5,175.61 0.00 204,667.8351 204,667.83 5,175.61 0.00 230,827.7852 230,827.78 5,175.61 0.00 259,603.7353 259,603.73 5,175.61 0.00 291,257.2754 291,257.27 5,175.61 0.00 326,076.1655 326,076.16 5,175.61 0.00 364,376.9456 364,376.94 5,175.61 0.00 406,507.8157 406,507.81 5,175.61 0.00 452,851.7558 452,851.75 5,175.61 0.00 503,830.1059 503,830.10 5,175.61 0.00 559,906.2760 559,906.27 5,175.61 0.00 621,590.0761 621,590.07 5,175.61 0.00 689,442.2462 689,442.24 5,175.61 0.00 764,079.6363 764,079.63 5,175.61 0.00 846,180.7764 846,180.77 5,175.61 0.00 936,492.0165 936,492.01 0.00 100,000.00 920,141.2166 920,141.21 0.00 100,000.00 902,155.3367 902,155.33 0.00 100,000.00 882,370.8668 882,370.86 0.00 100,000.00 860,607.9569 860,607.95 0.00 100,000.00 836,668.7570 836,668.75 0.00 100,000.00 810,335.6271 810,335.62 0.00 100,000.00 781,369.1872 781,369.18 0.00 100,000.00 749,506.1073 749,506.10 0.00 100,000.00 714,456.7174 714,456.71 0.00 100,000.00 675,902.3875 675,902.38 0.00 100,000.00 633,492.6276 633,492.62 0.00 100,000.00 586,841.8877 586,841.88 0.00 100,000.00 535,526.0778 535,526.07 0.00 100,000.00 479,078.6879 479,078.68 0.00 100,000.00 416,986.5480 416,986.54 0.00 100,000.00 348,685.2081 348,685.20 0.00 100,000.00 273,553.72SAVING FOR RETIREMENT82 273,553.72 0.00 100,000.00 190,909.0983 190,909.09 0.00 100,000.00 100,000.0084 100,000.00 0.00 100,000.00 0.0085 0.00退休金储蓄利息率 10% 年偿付期数 30 0取钱期数 20 1每年取钱款 100,000 23年支付款 5,175.61 45支付款现值 53,669.00 - =PV(B2,30,-B7,1) 6取钱现值 53,669.00 - =PV(B2,20,-B5,1)/(1+B2)30 78差值（应该为0） 0.00 91011检查 1265岁时存钱的现值 $936,492.01 - =FV(B2,30,-B7,1) 1365岁取钱的现值 $936,492.01 - =PV(B2,20,-B5,1) 141516运用Solver进行计算，会简单些 17181920这个问题有一种一步解决问题的方法：见下张表212223242526272829303132333435363738394041424344454647484950年龄 年初总额 年初支付 年初取钱数 年末总数35 0.00 5,175.61 0.00 5,693.1736 5,693.17 5,175.61 0.00 11,955.6537 11,955.65 5,175.61 0.00 18,844.3838 18,844.38 5,175.61 0.00 26,421.9939 26,421.99 5,175.61 0.00 34,757.3640 34,757.36 5,175.61 0.00 43,926.2641 43,926.26 5,175.61 0.00 54,012.0542 54,012.05 5,175.61 0.00 65,106.4343 65,106.43 5,175.61 0.00 77,310.2444 77,310.24 5,175.61 0.00 90,734.4345 90,734.43 5,175.61 0.00 105,501.0446 105,501.04 5,175.61 0.00 121,744.3147 121,744.31 5,175.61 0.00 139,611.9148 139,611.91 5,175.61 0.00 159,266.2649 159,266.26 5,175.61 0.00 180,886.0650 180,886.06 5,175.61 0.00 204,667.8351 204,667.83 5,175.61 0.00 230,827.7852 230,827.78 5,175.61 0.00 259,603.7353 259,603.73 5,175.61 0.00 291,257.2754 291,257.27 5,175.61 0.00 326,076.1655 326,076.16 5,175.61 0.00 364,376.9456 364,376.94 5,175.61 0.00 406,507.8157