Please help me on this Excel homework. Show your work. Thanks
D29 4 X v fx =$G$29 A B D E F H K 11 Lin Land Ltd. (LLL) is considering investing in an apartment complex. The sale price is $450,000 and LLL expects to have positive after- tax cash flows from rents of $20,000 for the next three years. At the end of the third year, LLL anticipates selling the apartment complex for a net 10 after-tax gain on sale of $500,000. If LLL's required return is 15%, should LLL go ahead and purchase the apartment complex? 12 Finance Concept: The net present value rule states that accepting all projects that are worth more than they cost increases shareholder value. The decision rule is to accept all positive NPV projects. The internal rate of return on an 13 investment is the rate that makes the NPV equal to zero. 14 15 16 17 18 19 20 21 22 23 24 25 Numerical Solution: NPV = PV - required investment = PV cash flows - selling price =CF/(1+r) + CF,/(1+r) + CF3/(1+r)* + ATGS/(1+r) - SP = 20,000/1.15 +20,000/1.152 +20,000/1.15 + 500,000/1.15 - 450,000 = 17,391 +15,123 +13,150+328,758 - 450000 = -75,578; decision - do not accept the project Using Goal Seek: Let's find the required return that makes the NPV equal to zero. The cash flows are already entered for you from the inputs table. NPV & IRR Table: year cash flow Inputs: 0 Initial outlay 20,000 CF 20,000 20,000 ATGS 500,000 520,000 0.15 NPV $374,423 Enter =NPV(G31,D29:D31)+D28 IRR Enter: =IRR(D28:D31) 26 27 28 29 30 31 32 33 2 3 r 34 38 35 Using Goal Seek: 36 Step 1. Make sure your computer has this option installed. If you can't install 37 it on your own computer, feel free to use one of UTA's. Step 2. Go to the menu bar and hit Tools and then Goal Seek. 39 Step 3. When the Goal Seek box appears fill in the inputs: 40 set cell=>enter D32, which is the NPV 41 to value => enter 0, which is what we want NPV to be 42 by changing > enter G31, which is the rate 43 then hit the OK button 44 Step 4. See the results. Goal Seek will change the r to make the NPV=0. 45 This is also the IRR. 46 Test your skills with this problem: 47 What is the annual cash flow required to be to have the NPV = 0 and the IRR =15%? 48 set cell ==> enter D32 to value=0 50 by changing => CF (G29) $53, 101 is the answer. 49 51 D29 4 X v fx =$G$29 A B D E F H K 11 Lin Land Ltd. (LLL) is considering investing in an apartment complex. The sale price is $450,000 and LLL expects to have positive after- tax cash flows from rents of $20,000 for the next three years. At the end of the third year, LLL anticipates selling the apartment complex for a net 10 after-tax gain on sale of $500,000. If LLL's required return is 15%, should LLL go ahead and purchase the apartment complex? 12 Finance Concept: The net present value rule states that accepting all projects that are worth more than they cost increases shareholder value. The decision rule is to accept all positive NPV projects. The internal rate of return on an 13 investment is the rate that makes the NPV equal to zero. 14 15 16 17 18 19 20 21 22 23 24 25 Numerical Solution: NPV = PV - required investment = PV cash flows - selling price =CF/(1+r) + CF,/(1+r) + CF3/(1+r)* + ATGS/(1+r) - SP = 20,000/1.15 +20,000/1.152 +20,000/1.15 + 500,000/1.15 - 450,000 = 17,391 +15,123 +13,150+328,758 - 450000 = -75,578; decision - do not accept the project Using Goal Seek: Let's find the required return that makes the NPV equal to zero. The cash flows are already entered for you from the inputs table. NPV & IRR Table: year cash flow Inputs: 0 Initial outlay 20,000 CF 20,000 20,000 ATGS 500,000 520,000 0.15 NPV $374,423 Enter =NPV(G31,D29:D31)+D28 IRR Enter: =IRR(D28:D31) 26 27 28 29 30 31 32 33 2 3 r 34 38 35 Using Goal Seek: 36 Step 1. Make sure your computer has this option installed. If you can't install 37 it on your own computer, feel free to use one of UTA's. Step 2. Go to the menu bar and hit Tools and then Goal Seek. 39 Step 3. When the Goal Seek box appears fill in the inputs: 40 set cell=>enter D32, which is the NPV 41 to value => enter 0, which is what we want NPV to be 42 by changing > enter G31, which is the rate 43 then hit the OK button 44 Step 4. See the results. Goal Seek will change the r to make the NPV=0. 45 This is also the IRR. 46 Test your skills with this problem: 47 What is the annual cash flow required to be to have the NPV = 0 and the IRR =15%? 48 set cell ==> enter D32 to value=0 50 by changing => CF (G29) $53, 101 is the answer. 49 51
