Please help me to fix my answer in Question 17.

The question is in the file attached.

STUDENT'S NAME: Yien San Wong MODULE 1 HOMEWORK Question 2 You just took a $10,000, 5-year loan. Payments at the end of each year are flat (equal in every year) at an interest rate of 15%. Calculate the appropriate loan table, showing the breakdown in each year between principal and interest. Cost Payment Interest 10000 $2,983.16 0.15 Division of payment between Year 1 2 3 4 5 6 Principal at beginning of year 10,000.00 8,516.84 6,811.22 4,849.74 2,594.05 0.00 Payment at end of year 2,983.16 2,983.16 2,983.16 2,983.16 2,983.16 Interest 1,500.00 1,277.53 1,021.68 727.46 389.11 Principal 1,483.16 1,705.63 1,961.47 2,255.69 2,594.05 A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 B C D E F G Question 3 You are offered an investment with the following conditions: The cost of the investment is 1,000. The investment pays out a sum X at the end of the first year; this payout grows at the rate of 10% per year for 11 years. If your discount rate is 15%, calculate the smallest X which would entice you to purchase the asset. Discount rate 0.15 Initial paymen 129.2851899 NPV 0.000001 Year 0 1 2 3 4 5 6 7 8 9 10 11 Cash flow -1000.00 129.29 142.21 156.44 172.08 189.29 208.22 229.04 251.94 277.13 304.85 335.33 H 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 A 1 B C D E F G Question 5 In this exercise we solve iteratively for the internal rate of return. Consider an investment which costs 80 has cash flows of 300, 200, 150, 122, 133 in years 1-5. Setting up the loan table below shows that 10% is than the IRR (since the return of principal at the end of year 5 is less than the principal at the beginning year): 2 3 4 IRR 5.07% 5 6 Year Cash flow 7 8 9 10 11 12 13 14 15 0 1 2 3 4 5 -800 300 200 150 122 133 IRR 5.07% LOAN TABLE Division of payment betwee Principal Payment Year at beginning Interest at end of year of year 1 800.00 300.00 40.55 2 540.55 200.00 27.40 3 367.94 150.00 18.65 4 236.59 122.00 11.99 5 126.58 133.00 6.42 6 0.00 should be zero for IRR ### H I 1 Consider an investment which costs 800 and e loan table below shows that 10% is greater ss than the principal at the beginning of the 2 3 4 5 of payment betwee Division 6 Principal 7 8 9 10 11 12 13 14 15 259.45 172.60 131.35 110.01 126.58 J K L M Question 8 You have just taken a car loan of $15,000. The loan is for 48 months at an annual interest rate of 15% (which 15%/12 = 1.25%). The 48 payments (to be made at the end of each of the next 48 months) are all equal. a. Calculate the monthly payment on the loan. b. In a loan table calculate, for each month: the principal remaining on the loan at the beginning of the month between interest and repayment of principal. c. Show that the principal at the beginning of each month is the present value of the remaining loan payments the PV functions). Loan principal Interest rate Annual Monthly Loan term (months) Monthly payment 15000 0.15 1.25% #VALUE! 48 $417.46 #VALUE! Split of payment between: Month 1 2 3 4 5 6 7 8 9 10 11 Principal at beginning of month 15,000.00 14,770.04 14,537.20 14,301.46 14,062.76 13,821.09 13,576.39 13,328.63 13,077.78 12,823.79 12,566.63 Payment Interest Principal 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 187.50 184.63 181.72 178.77 175.78 172.76 169.70 166.61 163.47 160.30 157.08 229.96 232.84 235.75 238.69 241.68 244.70 247.76 250.85 253.99 257.16 260.38 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 12,306.25 12,042.62 11,775.69 11,505.42 11,231.78 10,954.71 10,674.19 10,390.15 10,102.57 9,811.39 9,516.57 9,218.07 8,915.83 8,609.82 8,299.98 7,986.27 7,668.64 7,347.03 7,021.41 6,691.72 6,357.90 6,019.91 5,677.70 5,331.21 4,980.39 4,625.18 4,265.54 3,901.39 3,532.70 3,159.40 2,781.43 2,398.74 2,011.26 1,618.94 1,221.71 819.52 412.31 0.00 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 417.46 153.83 150.53 147.20 143.82 140.40 136.93 133.43 129.88 126.28 122.64 118.96 115.23 111.45 107.62 103.75 99.83 95.86 91.84 87.77 83.65 79.47 75.25 70.97 66.64 62.25 57.81 53.32 48.77 44.16 39.49 34.77 29.98 25.14 20.24 15.27 10.24 5.15 263.63 266.93 270.27 273.64 277.06 280.53 284.03 287.58 291.18 294.82 298.50 302.24 306.01 309.84 313.71 317.63 321.60 325.62 329.69 333.81 337.99 342.21 346.49 350.82 355.21 359.65 364.14 368.69 373.30 377.97 382.69 387.48 392.32 397.22 402.19 407.22 412.31 terest rate of 15% (which the bank translates to a monthly rate of onths) are all equal. the beginning of the month and the split of that month's payment e remaining loan payments at the loan interest rate (use either NPV or Part C Question PV of remaining payments $15,000.00 $14,770.04 $14,537.20 $14,301.46 $14,062.76 $13,821.09 $13,576.39 $13,328.63 $13,077.78 $12,823.79 #VALUE! #VALUE! #VALUE! Same answer using PV $15,000.00 $14,770.04 $14,537.20 $14,301.46 $14,062.76 $13,821.09 $13,576.39 $13,328.63 $13,077.78 $12,823.79 #VALUE! #VALUE! #VALUE! A 1 B C D 2 3 4 5 6 7 8 9 10 11 12 E F Question 15 Use XIRR to compute the internal rate of return of the following investment: Date Cash flow ### -899 ### 70 ### 70 ### 70 ### 70 ### 70 ### 1070 IRR 10.04% #VALUE! G A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 B C Question 17 Identify the two internal rates of return of the investment in exercise 16: Date 30-Jun-07 14-Feb-08 14-Feb-09 14-Feb-10 14-Feb-11 14-Feb-12 14-Feb-13 Cash flow -500 100 300 400 600 800 -1800 Net present value #NAME? Date 30-Jun-07 14-Feb-08 14-Feb-09 14-Feb-10 14-Feb-11 14-Feb-12 14-Feb-13 Guess IRR #VALUE! Cash flow -500 100 300 400 600 800 -1,800 35.00% #NAME? #VALUE! Note: As shown above, XIRR appears to be incapable of handling this problem, even with the guess parameter set for various values. I have identified the two IRR's by using Solver. Also note that XNPV returns an error when the discount rate is 0%. I have used an IF statement to correct this problem. Solver limits to identify both IRRs: Lower Upper Solver screen: A 43 44 45 46 47 48 49 50 51 52 53 54 55 B C D E F G H I J K L 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 Data table: NPV vs discount rate Discount rate NPV 0% 3% 6% 9% 12% 15% 18% 21% 24% 27% 30% 33% 36% 39% 42% 45% 48% #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? #NAME? XNPV as Function of Discount Rate 12 10 8 6 4 2 0 0% 5% 10% 15% 20% 25% 30% 35 D 43 44 45 46 47 48 49 50 51 52 53 54 55 E F G H I J K L M N O P Q 1 2 3 4 5 6 7 8 9 10 11 12 13 14 s Function of15Discount Rate 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 20% 25%35 30% 35% 36 37 38 39 40 41 42 40% 45% 50% M 43 44 45 46 47 48 49 50 51 52 53 54 55 N O P