Question

Problem Set 5 – Time Value of Money – Contract Evaluation

Situation:

Your company is examining contract proposals for information technology services that must be outsourced. The three proposals are shown below. Assume that all services are equal and that any of the options provide the same quality service. Assume you will enter into a five year contract. Use a discount rate of 10%.

Contract Offer 1:

Pay $150,000 immediately (Year 0)

Pay $225,000 for the next five years (Years 1-5)

Contract Offer 2:

Pay $200,000 immediately

Pay $100,000 for the next two years at the end of the year (Years 1-2)

Pay $200,000 for years for the remaining years (Years 3-5)

Contract Offer 3:

Pay $260,000 for the next five years (Years 1-5)

Requirements: Create an EXCEL spreadsheet showing each contract offer and determine the

present value of each. Based on your analysis of the present value, write a one paragraph

summary stating what you have found and recommend the best offer.

Answer 1

Offer 1:

Discount rate	10.0000%
Cash flows	Year	Discounted CF= cash flows/(1+rate)^year	Cumulative cash flow
150,000.00	0	150,000.00	150,000.00
225,000.00	1	204,545.45	354,545.45
225,000.00	2	185,950.41	540,495.87
225,000.00	3	169,045.83	709,541.70
225,000.00	4	153,678.03	863,219.73
225,000.00	5	139,707.30	1,002,927.02

Present worth of offer 1 = 1,002,927.02

Offer 2:

Discount rate	10.0000%
Cash flows	Year	Discounted CF= cash flows/(1+rate)^year	Cumulative cash flow
200,000.00	0	200,000.00	200,000.00
100,000.00	1	90,909.09	290,909.09
100,000.00	2	82,644.63	373,553.72
200,000.00	3	150,262.96	523,816.68
200,000.00	4	136,602.69	660,419.37
200,000.00	5	124,184.26	784,603.63

Present value of Offer 2 = 784,603.63

Offer 3:

Discount rate	10.0000%
Cash flows	Year	Discounted CF= cash flows/(1+rate)^year	Cumulative cash flow
-	0	-	-
260,000.00	1	236,363.64	236,363.64
260,000.00	2	214,876.03	451,239.67
260,000.00	3	195,341.85	646,581.52
260,000.00	4	177,583.50	824,165.02
260,000.00	5	161,439.54	985,604.56

present value of Offer 3 = 985,604.56

so offer 2 is the cheapest and the best