Question

You win the lottery! Your choices are

• Take $25 million today.
• Take $1 million today and $1 million every year for the next 49 years (a total of $50 million)

a) If the interest rate is 0.1% compounded annually, which would you prefer?

b) If the interest rate is 4% compounded annually, which would you prefer?

c) At what annual interest rate would you be indifferent between the two?

please use excel and GOAL SEEK FOR 2C

Answer 1

Present Value of an annuity beginnign cash flow today=cash flow*(1-1/(1+rate)^number of payments)/(1-1/(1+rate))

1.
Present value of 1 million payout=1*(1-1/(1+0.1%)^50)/(1-1/(1+0.1%))=48.7955571190601

Choose 1 million payout as present value is more than 25

2.
Present value of 1 million payout=1*(1-1/(1+4%)^50)/(1-1/(1+4%))=22.3414720013357

Choose 25 million today as present value of 1 million payout is less than 25

3.

In cell B1, enter any number say 2%

Go to cell I14, enter the formula =1*(1-1/(1+B1)^50)/(1-1/(1+B1))-25

Go to Data Tab->What if Analysis->Goal Seek

Click OK

You will get B1=3.32939501533894%

So, at annual interest rate of 3.32939501533894%, you will be indifferent between the two