Question

For each of the following exercises you will receive 20% of the corresponding points for providing the correct formula, 40% of the corresponding points for showing the procedure, and 40% of the corresponding points for having the correct answer. The maximum amount of points that can be earned in each problem varies and it depends on level of difficulty and length of each problem.

1. Kraft Foods executives have fifteen thousand dollars placed in their 401K retirement program at the end of each year as part of their compensation. John Trump has worked for Kraft for twenty-five years and his 401K plan has had an average annual return of 10% per year.

1. What is the total value of Trump’s 401K account? (Points: 15)

2. Trump retires at the end of his twenty-fifth year and begins withdrawing from the account. He expects to live another twenty years. If the account continues to earn 10% while funds are still on deposit, how much will each annual withdrawal (as an ordinary annuity) be? Hint: Part (b) is asking you to compute PMT. (Points: 15)

2. Trump wants to leave one million dollars to his nephew when he dies in twenty years. If the account still earns 10%, how much will he be able to withdraw each year and still leave the desired inheritance in twenty years, assuming he dies on schedule? Hint: First, compute the present value of one million dollars today (twenty years before he dies). (Points: 15) Second, take that amount off the balance of his 401K account today (which you computed in Part (a)). (Points: 10) Third, recalculate how much he will be able to withdraw each year (which you computed in Part (b)). (Points: 15)

Answer 1

	a	Total value of Trump’s 401K account
	Rate	Annual Return	10%
	Nper	Number of years	25
	Pmt	Annual deposit in 401K	$15,000
	FV	Total value of Trump’s 401K account	$1,475,206	(Using FV function with Rate=10%, Nper=25, Pmt=-15000)
	b	Amount of annual withdrawal
	Rate	Annual Return	10%
	Nper	Number of years	20
	Pv	Total value of Trump’s 401K account	$1,475,206
	PMT	Amount of annual withdrawal	$173,277	(Using PMT function with Rate=10%, Nper=20, Pv=-1475206)
	c	Amount of annual withdrawal leaving 1 million dollar
	Rate	Annual Return	10%
	Nper	Number of years	20
	Pv	Total value of Trump’s 401K account	$1,475,206
	Fv	Amount left at end of 20 years	$1,000,000
	PMT	Amount of annual withdrawal	$155,818	(Using PMT function with Rate=10%, Nper=20, Pv=-1475206, Fv=1000000)
		ALTERNATIVE METHOD
	PV1	Present value of $1000000 today	$148,644	(Using PV function of excel with Rate=10%, Nper=20, Fv=-1000000)
	Pv	Total value of Trump’s 401K account	$1,475,206
	PV2=Pv-PV1	Net Amount available	$1,326,562
	Rate	Annual Return	10%
	Nper	Number of years	20
	PMT	Amount of annual withdrawal	$155,818	(Using PMT function with Rate=10%, Nper=20, Pv=-1326562)
											FB : B x v C fc =FV(FS,F6,-F7) D A F G H I J K L M 10% а Rate Nper Pmt FV Total value of Trump's 401K account Annual Return Number of years Annual deposit in 401K Total value of Trump's 401K account 25 $15,000 $1,475,206 | (Using FV function with Rate=10%, Nper=25, Pmt=-15000) F14 fic =PMT(F11,F12,-F13) D E B C F G H I J K Rate Nper Pmt FV Total value of Trump's 401K account Annual Return Number of years Annual deposit in 401K Total value of Trump's 401K account 10% 25 $15,000 $1,475,206 (Using FV function with Rate=10%, Nper=25, Pmt=-15000) 10% b Rate Nper Pv Amount of annual withdrawal Annual Return Number of years Total value of Trump's 401K account Amount of annual withdrawal 20 $1,475,206 $173,277 (Using PMT function with Rate=10%, Nper=20, Pv=-1475206) F22 : fic =PMT(F18,719,-F20,F21) F G H I Rate Nper Pmt FV Total value of Trump's 401K account Annual Return Number of years Annual deposit in 401K Total value of Trump's 401K account 10% 25 $15,000 $1,475,206 (Using FV function with Rate=10%, Nper=25, Pmt=-15000) 10% b Rate Nper Pv PMT Amount of annual withdrawal Annual Return Number of years Total value of Trump's 401K account Amount of annual withdrawal 20 $1,475,206 $173,277 (Using PMT function with Rate=10%, Nper=20, Pv=-1475206) 20 Rate Nper Pv FV PMT Amount of annual withdrawal leaving 1 million dollar Annual Return 10% Number of years Total value of Trump's 401K account $1,475,206 Amount left at end of 20 years $1,000,000 Amount of annual withdrawal $155,818 (Using PMT function with Rate=10%, Nper=20, Pv=-1475206, Fv=1000000) Clipboard Font NI Alignment Number Styles Cells Editing F25 - x fc =PV(10%,20,-1000000) G H I J K L M Nper Pmt FV Number of years Annual deposit in 401K Total value of Trump's 401K account F 25 $15,000 $1,475,206 (Using FV function with Rate=10%, Nper=25, Pmt=-15000) 10% b Rate Nper Pv PMT Amount of annual withdrawal Annual Return Number of years Total value of Trump's 401K account Amount of annual withdrawal 20 $1,475,206 $173,277 (Using PMT function with Rate=10%, Nper=20, Pv=-1475206) 10% Rate Nper Pv FV PMT Amount of annual withdrawal leaving 1 million dollar Annual Return Number of years 20 Total value of Trump's 401K account $1,475,206 Amount left at end of 20 years $1,000,000 Amount of annual withdrawal $155,818 (Using PMT function with Rate=10%, Nper=20, Pv=-1475206, Fv=1000000) ALTERNATIVE METHOD Present value of $1000000 today PV1 $148,644 (Using PV function of excel with Rate=10%, Nper=20, Fv=-1000000) Clipboard Font Alignment Number Styles Cells Editing F30 - PMT(F28,F29,-F27) A B C Ε F G H Rate Nper 20 Amount of annual withdrawal leaving 1 million dollar Annual Return 10% Number of years Total value of Trump's 401K account $1,475,206 Amount left at end of 20 years $1,000,000 Amount of annual withdrawal $155,818 (Using PMT function with Rate=10%, Nper=20, Pv=-1475206, FV Pv PMT (Using PV function of excel with Rate=10%, Nper=20, Fv=-10 ALTERNATIVE METHOD PV1 Present value of $1000000 today Py Total value of Trump's 401K account PV2=Pv-PV1 Net Amount available Rate Annual Return Nper Number of years PMT Amount of annual withdrawal $148,644 $1,475,206 $1,326,562 10% 20 $155,818 ! (Using PMT function with Rate=10%, Nper=20, Pv=-1326562)