Bid Price for Investment

Generate spreadsheet that shows best case and worse case prices that the company will consider paying for the investment.

Determine a price estimate for this company via the Net Value technique using the discount rate of 11.2%. Use best case scenario and worse case scenario cash flows given and discount each of those annual cash flows to put them into present value terms. The terminal value is essentially the perpetuity that begins at the end of year 10 of the forecast period. To calculate the terminal value divide year 10 cash flow by the difference between the disount rate and the estimate rate of growth of the perpetuity. (I’m understanding this to be 3% at end of yr 10) Then discount that terminal value number for 9 periods to put into present value terms( I confused right here). Sum the discounted terminal value with the other 9 discounted cash flows and substract the initial investment ( 62.46 million) and you should be ready.

This company has rejected bids of 30-35 million from other companies trying to buy them out. Why? What price should we pay to buy them out according to your calculations (best and worse case)?

—
I need some help making sure my PV’s and NPV’s are correct for best and worse case scenario forecasts. Also, need help deriving a terminal value and discounting it correctly. Lastly, advice for estimated price bid for best and worse case scenarios. Please see attached spreadsheet and comments. Just the bottom portion of spreadsheet under the blue line is in question. The top part is just information for myself. Mainly need to know terminal value at end ofyr 10 (3% growth to perpetuity), and how to discount that terminal value number for the 9 periods to put into present value terms. I hope I did not leave any vital information out that might be making the problem difficult-if so, i apologize.