Question

We decided that we need to specify the type of retirement income we want and make plans to accomplish our goal. Our goal is to plan for 25 years of retirement, at $150,000 per year, but we want to receive the

$150,000 at the beginning of each year of our retirement. So, to reach our objective, for the next 30 years, we need to set aside the right amount of money as an annual constant contribution into a retirement fund at the end of each of those years. So in total, there will be 25 annual payments (withdrawals) of 150,000 each received at the beginning of each month, preceded by 30 annual contributions made at the end of each period. (The last contribution time may correspond with that of the first withdrawal time).

We discussed this in general terms with another pension adviser, and she advised us to assume an average nominal annual rate of return of 8.50%, compounded annually, for the entire 55-year period.

Ques 1) How much money would we need in our pension plan when we retire after 30 years of work to make our pension dream come true? Please use (display + name) the excel function/ formula used for each yellow cell.

Answer 1. amount needed at beginning of retirement
APR	8.50%
period rate
annual pension	-$150,000.00
#periods	25
Amount needed at BEG of retirement:

Ques 2) In order to accumulate this amount of money, how much would we need to deposit in our pension plan at the end of each year, for each of the next 30 years of work, if we contributed a constant amount each year? Please use (display + name) the excel function/ formula used for each yellow cell.

Answer 2. required annual contribution
period rate
#periods	30
Required annual contribution:

Ques 3) If the inflation rate for the next 30 years were to be 2% per year (assuming country’s Bank manages to meet its target inflation of about 2%), what annual income now would provide the same purchasing power as a $150,000 annual income in 30 years? Please use (display + name) the excel function/ formula used for each yellow cell.

Answer 3. inflation impact after 30 years
inflation rate	0.02
payment in 30 years	$150,000.00
equivalent amount in today's dollars:

Answer 1

Question 1. The answer is present value of annuity due with the following variables:

APR= 8.5%. Therefore, period rate= 0.085

Annual Pension (periodical payments): $150,000

No. of periods: 25

Amount needed at the beginning of retirement is $1,665,614.55

Detailed working in Excel is given as screen shot below:

Question 2: The amount of periodical payment of a regular annuity for the future value of $1,66,5614.55 with period as 30 years and APR 8.5%. The payments required during 30 years is ascertained at $13,409.16

Detailed working in Excel is given as screen shot below:

Answer to Question 3:

Since the inflation rate is 2% per year, payment of $150,000 in 30 years is equivalent in purchasing power to the present value, discounted for 30 years at this rate.

The present value is ascertained using the following formula:

V= F/(1+r)^n Where V is the present equivalent.

F= future amount=$150,000

r- Rate of inflation per period (year) =2% or, 0.02

n= No. of periods (Years)= 30

Present Value= $150,000/(1+0.02)^30 . =$150,000/1.811362= $82,810.62

Hence equivalent amount of $150,000 in today's dollars is $82,810.62

Screen shot of the detailed calculations in Excel is given below: