Question

Solve the problems below using well-formatted Excel solutions. Do not hardcode numbers in the formulas…..only use cell references to the input data. I will change the input data in your problem to check alternate solutions. You will turn in a complete working Excel spreadsheet with your solution.
Given the following set of cash flows:
Period	Cash Flow
1	$            40,000
2	$            35,000
3	$            30,000
4	$            25,000
5	$            20,000
6	$            15,000
7	$            10,000
8	$               5,000
1. If your required rate of return is 8% per year, what is the present value of the above cash flows?
2. If your required rate of return is 8% per year, what is the future value of the above cash flows?
3. Suppost that you are offered another investment that is identical, except the cash flows are reveresed. Compute the PV and FV, is this investment worth more or less than #1 and #2?
4. If you paid $150,000 for the original investment, what average annual rate of return would you earn? What return would you earn on the reversed cash flows?

Answer 1

1. For calculating PV using excel formula as follows

		A
	Period	Cash Flow
1	1	40000
2	2	35000
3	3	30000
4	4	25000
5	5	20000
6	6	15000
7	7	10000
8	8	5000
9	PV	$140,835.07	Excel formula=NPV(8%,A1:A8)
	FV	$260,675.88	Excel formula=A9*1.08^8

2. FV is 224.469.70 as given in above excel formula

3.If cash flow are reversed following excel formulas are used

		A
	Period	Cash Flow
1	1	5000
2	2	10000
3	3	15000
4	4	20000
5	5	25000
6	6	30000
7	7	35000
8	8	40000
9	PV	$117,763.69	Excel formula=NPV(8%,A1:A8)
	FV	$217,972.36	Excel formula=A9*1.06^8

In reversed case PV and FV are less .

4.

		A
	Period	Cash Flow
1	0	-150000
2	1	40000
3	2	35000
4	3	30000
5	4	25000
6	5	20000
7	6	15000
8	7	10000
9	8	5000
10	Rate of Return	5.81%	Excel formula=IRR(A0:A8)

	Period	Cash Flow
1	0	-150000
2	1	5000
3	2	10000
4	3	15000
5	4	20000
6	5	25000
7	6	30000
8	7	35000
9	8	40000
10	Rate of Return	3.31%	Excel formula=IRR(A0:A8)

IRR of original investment = 5.81%

IRR of Reversed investment = 3.31%

Please Discuss in case of Doubt

Best of Luck. God Bless
Please Rate Well