Question

Problem Four: (10%)

You have won the lottery!

You were offered two options to claim your prize:

Option One: You will collect a payment of $12,000 at beginning of each year for 10 years, then a final payment of $20,000 will be made at the end of year 10.

Option Two: You will be offered a payment of $7,000 at the end of each year for 5 years, then a payment of $8,000 at the end of each year for another 10 years.

Required: If your opportunity cost is 5%, which option should you choose?

(SOLUTION MUST BE ON EXCEL)

Answer 1

Option 1:

Excel formula: =PV(0.05,10,12000,20000,1)

The answer will be 1,09,572.13

Mathematical answer is as follows:

We are given the following information:

Annual payment	PMT	12000
rate of interest	r	5.00%
number of years	n	10
Payment timing	beginning	1
Future value	FV	20000
Present Value	PV	To be calculated

We need to solve the following equation to arrive at the required PV

So the PV is $109,572.13

We can generate an excel table for the same:

Year	CF	Discount Factor	Discounted CF
0	$ 12,000.00	1/(1+0.05)^0=	1	1*12000=	12,000.00
1	$ 12,000.00	1/(1+0.05)^1=	0.952380952	0.952380952380952*12000=	11,428.57
2	$ 12,000.00	1/(1+0.05)^2=	0.907029478	0.90702947845805*12000=	10,884.35
3	$ 12,000.00	1/(1+0.05)^3=	0.863837599	0.863837598531476*12000=	10,366.05
4	$ 12,000.00	1/(1+0.05)^4=	0.822702475	0.822702474791882*12000=	9,872.43
5	$ 12,000.00	1/(1+0.05)^5=	0.783526166	0.783526166468459*12000=	9,402.31
6	$ 12,000.00	1/(1+0.05)^6=	0.746215397	0.746215396636628*12000=	8,954.58
7	$ 12,000.00	1/(1+0.05)^7=	0.71068133	0.710681330130121*12000=	8,528.18
8	$ 12,000.00	1/(1+0.05)^8=	0.676839362	0.676839362028687*12000=	8,122.07
9	$ 12,000.00	1/(1+0.05)^9=	0.644608916	0.644608916217797*12000=	7,735.31
10	$ 20,000.00	1/(1+0.05)^10=	0.613913254	0.613913253540759*20000=	12,278.27
				PV = Sum of all Discounted CF	1,09,572.13

Basically as the payments are made at the beggining of the year 1-10, it simply means they are made at the end of years 0-9 and 20000 is made at the end of year 10

Option 2:

This is a multi step process.

Step 1 is to calculate the PV of the first annuity of 7000 for 5 years.

Excel formula =PV(0.05,5,7000,0,0)

Mathematical answer is as follows:

We are given the following information:

Annual payment	PMT	$            7,000.00
rate of interest	r	5.00%
number of years	n	5
Payment time	end	0
Present value	PV1	To be calculated

We need to solve the following equation to arrive at the required PV

So the PV₁ is 30306.34

Step 2:

Find the PV of the annuity for the next 10 years at the beginning of year 5

Excel formula =PV(0.05,10,8000,0,0)

Mathematical answer is as follows:

We are given the following information

Annual payment	PMT	$            8,000.00
rate of interest	r	5.00%
number of years	n	5
Payment time	end	0
Present value	PV2	To be calculated

We need to solve the following equation to arrive at the required PV

So the PV₂ is $61773.88

Step 3: Discount the PV₂ back to time 0 by discounting it for 5 years as the PV is at the start of year 6 or at the end of year 5

Excel formula =PV(0.05,5,0,61773,0)

Mathematical answer is as follows:

We are given the following information:

Value of account at time 0	PV0	To be calculated
rate of interest	r	5.00%
number of years	n	5
Annual Compounding	frequency	1
Future value	FV	$          61,773.00

We need to solve the following equation to arrive at the required PV

So the PV₀ is $ 48,401.45

Step 4:

Add PV₁ and PV₀ to arrive at the required PV

30306.34+ 48401.45 = 78707.79

Cashflow chart is as follows

Year	CF	Discount Factor	Discounted CF
0	$ -	1/(1+0.05)^0=	1	1*0=	-
1	$   7,000.00	1/(1+0.05)^1=	0.952380952	0.952380952380952*7000=	6,666.67
2	$   7,000.00	1/(1+0.05)^2=	0.907029478	0.90702947845805*7000=	6,349.21
3	$   7,000.00	1/(1+0.05)^3=	0.863837599	0.863837598531476*7000=	6,046.86
4	$   7,000.00	1/(1+0.05)^4=	0.822702475	0.822702474791882*7000=	5,758.92
5	$   7,000.00	1/(1+0.05)^5=	0.783526166	0.783526166468459*7000=	5,484.68
6	$   8,000.00	1/(1+0.05)^6=	0.746215397	0.746215396636628*8000=	5,969.72
7	$   8,000.00	1/(1+0.05)^7=	0.71068133	0.710681330130121*8000=	5,685.45
8	$   8,000.00	1/(1+0.05)^8=	0.676839362	0.676839362028687*8000=	5,414.71
9	$   8,000.00	1/(1+0.05)^9=	0.644608916	0.644608916217797*8000=	5,156.87
10	$   8,000.00	1/(1+0.05)^10=	0.613913254	0.613913253540759*8000=	4,911.31
11	$   8,000.00	1/(1+0.05)^11=	0.584679289	0.584679289086437*8000=	4,677.43
12	$   8,000.00	1/(1+0.05)^12=	0.556837418	0.556837418177559*8000=	4,454.70
13	$   8,000.00	1/(1+0.05)^13=	0.530321351	0.530321350645295*8000=	4,242.57
14	$   8,000.00	1/(1+0.05)^14=	0.505067953	0.505067952995519*8000=	4,040.54
15	$   8,000.00	1/(1+0.05)^15=	0.481017098	0.48101709809097*8000=	3,848.14
				PV = Sum of all Discounted CF	78,707.79

As all the CFs are at the end of the year, the CF for year 0 is 0