Question

Ques 1) Pension plan queries:

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. 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.

Ans. 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.

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.

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

(I will be solving the questions using excel)

Question 1

we need to find the Present Value of the future payments that we will receive at the beginning of each year for next 25 years

The formula in Excel =PV(rate,nper,Pmt,FV,type)

Given things

PMT	$150,000
NPER	25 years
Rate	8.50%
FV	0
Type*	1

*type 1 depicts at the beginning of the year, 0 depicts at the end of the year

This is what final input would look like =PV(8.5%,25,-150000,0,1)

And the answer would be $ 1,665,615

That means the amount required at the beginning of the retirement is $1,665,615

Question 2

We need to find the annual contribution to be made every year for the next 30 years so that at the end of 30 years (or beginning of our retirement) we should have $1,665,615 in our account.

The formula to find out about that in Excel =PMT(rate,nper,pv,fv,type)

Given Things  

PV	0
NPER	30 years
Rate	8.50%
FV	$1,665,615
Type	0

We have zero savings so PV is 0, and every year we need to contribute at the end of the year so type is 0

This is what final input would look like =PMT(8.5%,30,0,-1665615,0)

And the answer would be $13,409

That means the annual contribution at the end of the year for the next 30 years is $13,409

Question 3

We need to find the equivalent value of $150,000, 30 years from now, today.

That means we need to find the present value of $150,000, 30 years from now growing at the rate of inflation which is 2%

The formula =PV(rate,nper,pmt,fv,type)

PMT would be zero and type would be at the end of the year

This is what final input would look like =PV(2%,30,0,-150000,0)

And the answer would be $82,810.63

The value of $150,000, 30 years from now, in today's dollar term, is $82,810.63

Hope I was able to help you :)