Question

How can I Build a cashflow spreadsheet in Excel:

Use the following parameters and assumptions:

1. Initial Investment = $ 10,500

2. WACC/Discount rate = 6.0%

3. Cashflow for Year 1 = $1,400

4. Cashflow growth (after year 1) = 3%

5. Debt amount = $2,000

6. Interest on loan = 7%

7. Taxes = 22%

8. Depreciation = straight line for 6-yeras

9. Term or project = 6 years

10. Note : Cash flow = EBIT + Depreciation/Amortization - Taxes

Answer 1

Period	Cash Flows	PVIF @ 6%	PV of Cash Flow
0	$       (10,500)	1.0000	$              (10,500)
1	$            1,368	0.9434	$                  1,290
2	$            1,401	0.8900	$                  1,246
3	$            1,434	0.8396	$                  1,204
4	$            1,469	0.7921	$                  1,164
5	$            1,505	0.7473	$                  1,125
6	$            1,542	0.7050	$                  1,087
			$                (3,384)	NPV
Working Note:-
Period	Cash flow	Interest Expense	Depreciation	Net Profit	Savings on Tax	PAT	CFAT= PAT+ Dep
1	$            1,400	140	1750	$       (490)	$                 (108)	$       (382)	$                   1,368
2	$            1,442	140	1750	$       (448)	$                   (99)	$       (349)	$                   1,401
3	$            1,485	140	1750	$       (405)	$                   (89)	$       (316)	$                   1,434
4	$            1,530	140	1750	$       (360)	$                   (79)	$       (281)	$                   1,469
5	$            1,576	140	1750	$       (314)	$                   (69)	$       (245)	$                   1,505
6	$            1,623	140	1750	$       (267)	$                   (59)	$       (208)	$                   1,542