How do I calculate part (c) on excel? My professor did it the following way but I can't understand why.

Why did he only use the cash inflows? Why did he convert FV to PV? Why is the NPV only calculated for the PV of the future cashflows?

Isn't there a formula relating NPV to n where I can write the formula into excel instead of using the NPV and NPER functions?

Please explain this to me or explain a better method for calculating it...Thank you.

FastTrack Bikes, Inc., is thinking of developing a new composite road bike. Development will take six years and the cost is $200,000 per year. Once in production, the bike is expected to make $300,000 per year for 10 years. The cash inflows begin at the end of year 7. Assuming the cost of capital is 10%: a. Calculate the NPV of this investment opportunity. Should the company make the investment? b. Calculate the IRR and use it to determine the maximum deviation allowable in the cost of capital estimate to leave the decision unchanged. c. How long must development last to change the decision? A B C D E F G 1 2 Year 1 Year 2 Year 3 Year 4 Year 5 Year 6 ($200,000.00) ($200,000.00) ($200,000.00) $200,000.00) ($200,000.00) ($200,000.00) Year 7 $300,000.00 0 H K L N 0 P Given Information Year 8 Year 9 Year 10 Year 11 Year 12 Year 13 Year 14 Year 15 Year 16 $300,000.00 $300,000.00 $300,000.00 $300,000.00 $300,000.00 $300,000.00 $300,000.00 $300,000.00 $300,000.00 0 0 0 0 0 0 0 0 0 4 5 6 Assuming the cost of capital is 10% Prod. L.C 7 r 8 n 10 9 (a) 10% Development 16 6 NPV = ?? NPV = $169,482.24 NPV= FORMULA TEXT =NPV(E7,A3,B3,C3, D3, E3, F3, G3, H3,13,13,K3,L3,M3,N3,03,P3) 10 11 12 13 14 Yes, the company should take the investment since the NPV calculation generates a positive amount of $169,482.84. Taking the investment would increase the company's value by the NPV amount provided that the cost of capital remains constant over the project's life-frame and that the estimated sales are to materialize over the product's life-cycle. 15 16 (b) IRR = ? 17 18 IRR = 12.659% 2.659% FORMULA TEXT =IRR(A3:P3) =F18-E7 19 Deviation Max 20 21 22 23 The IRR had been calculated as 12.659% which means at this rate, the NPV becomes zero. The maximum deviation allowable in this case is the calculated IRR minus the cost of capital estimate. This gives 2.659% of allowable deviation from the 10% to leave the decision unchanged. Any increase in the rate above the 2.659% would generate a negative NPV, changing the decision to 'reject'. 24 25 (c) 26 27 28 29 30 N = ?? FORMULA TEXT PVg at 10% cost of capital = $169,482.24 =NPV(E7,A3:P3) N= 0.85 =NPER(E7,F3,0, G27) 10 mo, 7 days In order to change the decision the NPV should be a negative value. If development lasts for more than 10 months and 7 days, the NPV would be negative. This would mean that the company's value would decrease by the NPV amount if the investment is taken. 31 32