Question

3. You are planning for your retirement. You have 3,000 today to invest and plan on putting in 300 a month until you retire in 30 years at an interest rate of 11%.The month of retire, you estimate needing 20,000 in expenses. After that, you wish to pull money each month so that there is still 500,000 at the end of your retirement 35 years after you retire. During this time, you can only earn 7% per year. How much can you pull out each month during retirement under this plan?

Use This information for the next 3 questions:

You buy a 2 million dollar building for an 8 year project. You can depreciate it over 20 years. In 8 years, you sell the building for 1.6 million dollars.

Over the 8 year project, you project needing net working capital as follows

Years 0, 1, 2, you need 100,000 of capital

Years 3, 4, 5 you need 140,000 of capital

Years 6, 7, 8 you need 175,000 of capital

Because you took this project, you foresee sales increasing by 540,000. Cogs will increase by 200,000. You’ll pay 50,000 of interest on the debt for the building. Your tax rate is 25%

4) Find the cash flow from selling the machine in year 8

5) Find the change in net working capital for year 3

6) find the operating cash flow each year of the project.

Answer 1

3. We will first calculate the future of investments.

We will use FV function in excel (One can use the same logic in financial calculators as well)

Here,

Rate = 11%/12 (Since monthly deposits are done)

Time (NPER) = 30 years = 30 x 12 = 360 months

Monthly Deposits (PMT) = - $300 (Negative sign indicates a cash outflow)

Present Value of Investment (PV) = - $3000 (Negative sign indicates a cash outflow)

Type = 1 (Deposits are done at the start. In excel, one can choose 1 / 0 as type. 1 is for investments done at the start or 0 at the end. This function comes in use if there is a non zero value of PMT)

So Value of accumulated deposits at the time of retirement

= FV(11%/12,360,300,3000,1)

= $929,192.64 (A Positive sign here is indicative of cash inflow)

Now at retirement a sum of $20,000 is withdrawn from this accumulated value

Balance fund = $929,192.64 - $20,000 = $909,192.64

From here onwards equal monthly withdrawals will happen, such that fund still has $500,000 after 35 years. While the fund keeps growing at rate of 7% per year.

We will use PMT function to calculate the monthly withdrawals

Here, Rate = 7%/12, NPER = 35x12 = 420 months, PV = -909192.164 (Negative sign is used to indicate a cash flow for investor), FV = 500,000, Type =1 (Drawings are done at start of month)

Monthly drawings

= PMT(7%/12,420,-909192.16,500000,1)

= $5498.74

II).

Cash flow from selling the machine in year 8 = Cash flows from sale - Taxes on gain

= Cash flow from sales - (Sales Price - Book Value) x Tax rate

Book Value = Cost - Accumulated depreciation

Depreciation = (Cost - Salvage Value)/ Years of expected life (Using Straight line method)

Here Depreciation = (2,000,000 - 0) / 20 = 100,000

Book Value after 8 years = 2,000,000 - 8 x 100,000 = 1,200,000

Cash Flow from Sales = 1,600,000 - (1,600,000 - 1,200,000) x 25%

= 1,600,000 - 400,000 x 25%

= 1,600,000 - 100,000

Post tax Cash Flow from Sales= $1,500,000

5. Change in Net working capital = Current year Working Capital - Previous year working capital

Change in Net working capital in year = 3rd Year WC - 2nd year WC

= 140,000 - 100,000 = 40,000 (Increase, this would reduce cash flows in year 3)

6. The cash flow of the firm would be as below

All costs & values that impact cash flow and profits negatively are shown in negative values

Sales and COGs are given.

Depreciation = (Equipment cost - Salvage Value) / Number of years of depreciation

Tax is 25% on EBIT

In terminal year after tax Cash Flow from sale are calculated

Cash Flow = (Net Income + Depreciation + Net working capital Investments + Capital Expenditure + After tax Salvage Value) *

*Sign of cash flows are important here. One which increases CF is positive, other which decreases Cf is negative

Revenue forecast	0	1	2	3	4	5	6	7	8
Revenues (A)		540,000	540,000	540,000	540,000	540,000	540,000	540,000	540,000
COGS (B)		(200,000)	(200,000)	(200,000)	(200,000)	(200,000)	(200,000)	(200,000)	(200,000)
Depreciation (C = I/20)		(100,000)	(100,000)	(100,000)	(100,000)	(100,000)	(100,000)	(100,000)	(100,000)
Interest (D)		(50,000)	(50,000)	(50,000)	(50,000)	(50,000)	(50,000)	(50,000)	(50,000)
EBT (E = A + B + C + D)		190,000	190,000	190,000	190,000	190,000	190,000	190,000	190,000
Taxes @ 25% (F = E x 25%)		(47,500)	(47,500)	(47,500)	(47,500)	(47,500)	(47,500)	(47,500)	(47,500)
Net Income (G = E - F)		142,500	142,500	142,500	142,500	142,500	142,500	142,500	142,500
Depreciation (D)		100,000	100,000	100,000	100,000	100,000	100,000	100,000	100,000
Net Working Capital Investments (H = Current WC - Previous WC)	(100,000)	-	-	(40,000)	-	-	(35,000)	-	-
Capital Expenditure on Building (I)	(2,000,000)
Cash Flow from Sale of building (J)									1,600,000
Tax paid on sale of building (K)									(100,000)
Free Cash Flow (O =G + D+ H + I+ J+ K)	(2,100,000)	242,500	242,500	202,500	242,500	242,500	207,500	242,500	1,742,500
Working Capital P	100,000	100,000	100,000	140,000	140,000	140,000	175,000	175,000	175,000