In: Finance
Ralph wants to quit his job and move to Hawaii on December 25, 2015. Once there, he anticipates that he will need to make annual withdrawals of 14500 dollars (starting on December 25, 2016) to supplement his income from working as a cabana boy, and he wants the money to last 10 years (i.e. he'll make 10 withdrawals total). His plan is to make annual deposits, starting on December 25, 2000 and ending on December 25, 2015, into an account paying 9.2 percent effective interest. How large should each deposit be for Ralph to realize his goal?
First, we calculate the present value of the withdrawals as on December 25, 2015.
Present value is calculated using PV function in Excel :
rate = 9.2% (annual interest rate)
nper = 10 (10 annual withdrawals to be made)
pmt = -14500 (annual withdrawal amount. This is entered as a negative number because it is a cash outflow from the account)
PV is calculated to be $92,242.41
This is the amount required to be in the account to fund the withdrawals for 10 years.
Next, we calculate the annual deposit so that the value of account as on December 25, 2015 is $92,242.41.
Annual deposit is calculated using PMT function in Excel :
rate = 9.2% (annual interest rate)
nper = 16 (16 annual deposits from 2000 to 2015)
pv = 0 (beginning amount in account is zero)
fv = 92242.41 (amount required to be in account at end of 16 years, i.e. on December 25, 2015)
PMT is calculated to be $2,747.72
The annual deposit is $2,747.72