Original problem and it's answer (background, supporting information):

Middleton Classics, a chain of exclusive furniture stores, is considering renting space in a new shopping mall. It is anticipated that this rental will require an investment in fixtures and equipment costing $500,000, with an estimated salvage value of $25,000 at the end of its useful life in ten years. The new store is expected to generate an annual net cash flow of $110,000. The owner desires a 20% annual return on investment and wants a payback period of less than four years. Ignore the impact of taxes. Enter the formulas in excel:

Question:

Should Middleton Classics make the investment in the new store? Explain

Apparently, the picture below me is the correct response to the question, but I'm not sure how they got those numbers in excel with the formulas used. I'm not sure where the $461,172 and 17% came from.

B D E F G 1 2 3 4 5 CAPBUD Capital Budgeting Data Section $500,000 10 years $25,000 $110,000 20.00% 4.55 years 12.50% ($28,992) 17.95% 7 Cost of investment (initial outlay) 8 Estimated life of investment 9 Estimated salvage value 10 Estimated annual net cash inflow 11 Required rate of return 12 13 Answer Section 14 15 Payback period 16 Accounting (average) rate of return 17 Net present value 18 Internal rate of retur 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 Scratch Pad Cash flow table needed for NPV & IRR calculations NPV IRR Annual Salvage Combined Year Cash Flow Value Flows 0 -500000 1 110000 0 110000 2 110000 0 110000 3 110000 0 110000 4 110000 0 110000 5 110000 0 110000 6 110000 0 110000 7 110000 0 110000 8 110000 0 110000 9 110000 0 110000 10 110000 25000 135000 A B D E F CAPBUI Capital Budg years 500000 10 25000 110000 0.2 years =E7/E10 =(E10-(E7-E9)/E8)/E7 =NPV(E11, E29:E39) EIRR(E29:E39) 2 3 4 5 Data Section 6 Cost of investment initial outlay) 8 Estimated life of investment 9 Estimated salvage value 10 Estimated annual net cash inflow 11 Required rate of return 12 13 Answer Section 14 15 Payback period 16 Accounting (average) rate of return 17 Net present value 18 Internal rate of return 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 Annual Cash Flow Salvage Value Year 0 1 =(B30+1) =(B31+1) 1=(B32+1) =(B33+1) =(B34+1) =(B35+1) =(B36+1) =(B37+1) =(B38+1) =IF((E8>=1),E10,0) =IF((E8>=2).E10,0) =IF((E8>=3),E10,0) =IF ((E8>=4),E10,0) =IF((E8>=5),E10,0) =IF((E8>=6).E10,0) =IF((E8>=7),E10,0) =IF((E8>=8),E10,0) =IF((E8>=9),E10,0) =IF((E8>=10), E10,0) =IF((E8=1),E9,0) =IF((E8=2),E9,0) =IF((E8=3),E9,0) =IF((E8=4),E9,0) =IF((E8=5),E9,0) =IF((E8=6),E9,0) =IF((E8=7),E9,0) =IF((E8=8),E9,0) =IF((E8=9),E9,0) =IF((E8=10), E9,0) IRR Combined Flows -E7 =C30+D30 =C31+D31 =C32+D32 =C33+D33 =C34+D34 =C35+D35 =C36+D36 =C374037 =C38+D38 =C39+D39 Step-by-step solution Step 1 of 2^ The chart below shows the data for an investment that Company M is considering. Data $500,000 10 Years Cost of investment (ntalouttay Estimated Ife of investment Estimated salvage vale Estimated amal net cash in love Required rate of retum $25,000 $110,000 20.00% Answer 4.55 years Payback period Accounting (average rate of retum Net present value Intemal rate of retum 17.00% $461,172 17.95% Comment Step 2 of 2 A Company M wants a return rate of 20% and wants to a payback period of less than four years. Based on this information, Company M should not make this investment in the new store. The internal rate of return and the accounting rate of return are both under the desired 20%. In addition, it would cost Company M 4.55 years to pay off the new addition to their company, which is more than the desired 4 years