Question

Create a time value of money Excel spreadsheet for the following two investments: A. Initial investment ($100,000); Forecasts: Year 1 payback ($25,000); Year 2 payback ($25,000); Year 3 payback ($25,000) B. Initial investment ($100,000); Forecasts: Year 1 payback ($20,000); Year 2 payback ($25,000); Year 3 payback ($40,000) Use a re-investment rate of 10%. Calculate Net Present Value (=NPV) for each investment and specify which investment is the most profitable. (You are not required to use the Excel (=NPV) formula but you will find it to be the easiest and most accurate means of calculating Net Present Value.)

Answer 1

Investment which is the most profitable		Investment - A
Investment - A
year	0	1	2	3
Cash flow (a)	-$100,000	-$25,000	-$25,000	-$25,000
Pv Factor (b) @10%	1	0.9091	0.8264	0.7513
Dsicounted Cash Flows (a x b)	-$100,000	-$22,727	-$20,661	-$18,783
NPV	-$162,171
Investment - B
year	0	1	2	3
Cash flow (a)	-$100,000	-$20,000	-$25,000	-$40,000
Pv Factor (b) @10%	1	0.9091	0.8264	0.7513
Dsicounted Cash Flows (a x b)	-$100,000	-$18,182	-$20,661	-$30,053
NPV	-$168,896
Note: The NPV has no reinvestment rate assumption; therefore,
the reinvestment rate will not change the outcome of the project.