Tom Osborne, the CFO of Huskers Private Equity Fund, is reviewing an acquisition opportunity for the fund. Mr. Osborne is considering whether Huskers should buy the firm XYZ. XYZ is currently for sale at the book value of its equity (i.e. $30,000 to be paid immediately). Huskers investment guidelines require that each investment should earn at least 40% per year. Investment horizon in XYZ is 7 years. At the end of the 7th year (i.e. t=7 or year 2021), Huskers expects to sell XYZ at a price that is 8 times the firm's free cash-flow in 2021. Using the assumptions given below, answer the following questions: 1- What is the NPV of this acquisition given Huskers required return of 40%? 2- What is the IRR of the proposed investment? 3- Should Mr. Osborne recommend buying XYZ? - Annual revenue growth rates will be -10%, 20%, 20%, 40%, 75%, 30%, 20% during the next seven years respectively. - COGS will be 75% of the annual revenues. - Depreciation expense will be 10% of gross PP&E. - XYZ will make $25,000 per year in additional capital investments during the next seven years. - Interest rate on debt is 10% and the annual interest expense is computed as 10% of the average of the current years and the previous year's interest bearing debt balance (= long-term debt). - Minimum cash balance should be at least $5000 every year. - XYZ will pay the maximum dividends as long as there is no equity issuance during the fiscal year. In other words, there cannot be a dividend payment during a fiscal year if XYZ needs equity funding during that fiscal year. Dividend payments out of XYZ is a cash inflow for Huskers. - Accounts receivable will be 60% of annual revenues. - Accounts payable will be 60% of annual revenues. - No new debt will be issued. Long-term debt is due seven years from now (i.e. t=7 or year 2021). The principal amount of $20,000 will be paid as of the 2021 fiscal year end. - XYZ will issue equity as needed. Since Huskers is the sole owner of XYZ, XYZ's equity issuance is funded by Huskers (i.e. from Huskers' perspective any equity issuance by XYZ is a cash outflow or equivalently additional investment contribution in XYZ ). Huskers' investment guidelines permit contributing at most $100,000 in XYZ during the next 7 years (including the $30,000 acquisition price). Any dividends that are paid out reduce the total amount contributed.

I have the table set up (I believe) correctly, but am having trouble determining how to solve for NPV and IRR with the information I have.

$ 50,000.00 45,000.00 $ 54,000.00 64,800.00$ 90,720.00 158,760.00 $ 206,388.00 $ 247,665.60 $ 36,750.00 $ 33,750.00 $ 40,500.00 48,600.00 68,040.00 $ 119,070.00 $ 154,791.00 $ 185,749.20 $5,000.007,500.00 10,000.00 12,500.00$ 15,000.00 $ 17,500.00 $ 20,000.00 $ 22,500.00 $ 8,250.00 3,750.00 3,500.00 3,700.00 7,680.00 22,190.00 $ 31,597.00 39,416.40 $2,000.00 2,100.00 2,310.00 2,541.00$ 2,795.10 $ 3,074.61 $ 3,382.07$ 3,720.28 $ 6,250.00 1,650.00 1,190.00 1,159.00 4,884.90 19,115.39 28,214.93 $35,696.12 1,250.00 330.00 238.00231.80 976.98$ 3,823.08 s 5,642.99 s 7,139.22 $5,000.00 1,320.00$952.00 927.20$3,907.92 $15,292.31 $ 22,571.94 $ 28,556.90 NPV IRR COGS (Excludes depreciation expense) Depreciation expense 5 $10,000.00 $ 5,000.00 $ 5,000.00$ 5,000.00$ 5,000.00 $ 5,000.00 5,000.00$5,000.00 $ 30,000.00 27,000.00 32,400.00 $ 38,880.00 54,432.00 $ 95,256.00 123,832.80 148,599.36 $50,000.00 $ 75,000.00 $ 100,000.00 $ 125,000.00 150,000.00 175,000.00 $ 200,000.00 $ 225,000.00 Less accumulated depreciation 10,000.00 $ 17,500.00 $ 27,500.00 40,000.00$ 55,000.00 $ 72,500.00 92,500.00 115,000.00 $ 40,000.00 57,500.00 72,500.00 $ 85,000.00 $ 95,000.00 102,500.00 107,500.00 110,000.00 $80,000.00 89,500.00 $ 109,900.00 128,880.00 $ 154,432.00 $ 202,756.00 $ 236,332.80 $ 263,599.36 Accounts receivable Gross PP&E 2 Net PP&E $ 30,000.00 27,000.00 32,400.00 38,880.00 54,432.00 95,256.00 123,832.80 148,599.36 $20,000.00 $ 22,000.00$ 24,200.00 $ 26,620.00 29,282.00 $ 32,210.20 $ 35,431.22 38,974.34 Accounts payable $ 10,000.00'$ 11,320.00'$ 12,272.00'$ 13,199.20'$ 17,107.12'$ 32,399.43'$ 54,971.38 $ 83,528.27 80,000.00$60,320.00 68,872.00 78,699.20 $ 100,821.12 159,865.63 s 214,235.40S 271,101.97 Total L+E