Question

A) If you delay saving by 5 years, what would the interest rate (APR) need to be for you to hit the target amount ($349,881.67)? Use excel RATE function and show values for arguments (nper, pmt, pv, fv, type, guess)..

B) Convert that APR to an EAR. Use excel EFFECT function and show values for arguments (nominal_rate, npery)

C) Amount you need in your account at retirement in order to spend $5000 each period? Use excel PV function and show values for arguments (rate, nper, pmt, fv, type)

D) Amount you need to save each period before retirement to have enough to meet your goal in question C. Use excel PMT function and show values for arguments (rate, nper, pv, fv, ty

$750.00	How much money do you save each period?
30	Years until your retirement
15	Years in you plan to be in retirement
4	How many periods in a year?
8.00%	Expected return on your savings before retirement (this is an EAR)
6.00%	Expected return on savings during retirement (this is an APR)
7.77%	Expected annual return on savings before retirement (convert to an APR)
$349,881.67	Amount you'll have in your account at retirement based on the savings amount in cell B2
$8,884.69	Amount you can spend each period during your retirement
5	Number of years you delay before starting to save for retirement
	If you do delay starting to save, what would the interest rate need to be for you to hit the target amount in B10? (APR)
	Convert that APR to an EAR.
$5,000.00	How much do you want to be able to spend each period during your retirement?
	Amount you need in your account at retirement in order to spend this amount (cell B17)
	Amount you need to save each period before retirement to have enough to meet your goal.

Answer 1

Solution:

A)	If you delay saving by 5 years, what would the interest rate (APR) need to be for you to hit the target amount ($349,881.67)? Use excel RATE function and show values for arguments (nper, pmt, pv, fv, type, guess).
	10.45% Answer (Rounded to two decimal places)
	Formula: RATE(nper, pmt, pv, fv, type, guess)*4 = RATE(25*4,-750,0,349881.67,0,0)*4 = 10.4520%
B)	Convert that APR to an EAR. Use excel EFFECT function and show values for arguments (nominal_rate, npery)
	10.87% Answer (Rounded to two decimal places)
	Formula: EFFECT(nominal_rate, npery) =EFFECT(10.4520%,4) = 10.8689%
C)	Amount you need in your account at retirement in order to spend $5000 each period? Use excel PV function and show values for arguments (rate, nper, pmt, fv, type)
	$196,901.34 Answer (Rounded to two decimal places)
	Formula: PV(rate, nper, pmt, fv, type)= PV(6%/4,15*4,-5000,0,0) = $196,901.3444
D)	Amount you need to save each period before retirement to have enough to meet your goal in question C. Use excel PMT function and show values for arguments (rate, nper, pv, fv, type)
	$422.13 Answer (Rounded to two decimal places)
	Formula: PMT(rate, nper, pv, fv, type) = PMT(7.77%/4, 30*4, 0,-196901.3444,0) = $422.1259