Question

Hello!

If possible can you please teach me in excel formulas?

You have recently won the super jackpot in the Washington State Lottery. On reading the fine print, you discover that you have the following two options:

  

a.	You will receive 30 annual payments of $270,000, with the first payment being delivered today. The income will be taxed at a rate of 30 percent. Taxes will be withheld when the checks are issued.
b.	You will receive $550,000 now, and you will not have to pay taxes on this amount. In addition, beginning one year from today, you will receive $220,000 each year for 29 years. The cash flows from this annuity will be taxed at 30 percent.

Using a discount rate of 6 percent, what is the present value of your winnings for each option?

Thank you!

Autumn

Answer 1

Computation of present value of annuity factor :

The formula for present value of annuity factor = (1 - PVF ) / R

where PVF = (1 /(1+i) ^ n) and R = Discount rate in %

PVF is calculated for year 29, PVF (Y ₂₉ ) = ( 1 / [ (1+ 0.06) ^ 29 ] )

= ( 1 / [ (1.06) ^ 29 ] )

= 1 / 5.4183879

PVF (Y ₂₉ ) = 0.184556739

So, PVAF = (1 - 0.184556739) / 6%

= 0.815443261 / 6%

PVAF (Y ₁ to Y ₂₉ ) = 13.5907

Option a) 30 annual payments of $ 2,70,000 before tax:

Year	Amount in $	PVAF/ PVF	PV - Cashflows in $
A	B	C	D = B * C
Y 0	189,000	1	189,000.00
Y 1 to Y29	189,000	13.5907	2,568,642.30
Total			2,757,642.30

The present value of cashflows- a) = $2,757,642.30

Option b) $ 5,50,000 received today and annual cashflows of $ 2,20,000 before tax :

Year	Amount in $	PVAF/ PVF	PV - Cashflows in $
A	B	C	D = B * C
Y 0	5,50,000	1	550,000.00
Y 1 to Y29	1,54,000	13.5907	2,092,967.80
Total			2,642,967.80

The present value of cashflows- b) = $2,642,967.80

(Note: It is easy to solve this way than using excel)