Question

You now realize you would like to retire in 30 years (age 60). You are currently making $90K a year. After your life expenditure and taxes, you are able to save 10% of your salary. You come from a family with really good genes so you expect to live another 25 years after retirement (to an age of 85 years old). You do not want to be a burden on your family and it’s not likely there will be any social security by the time you retire so you’ll have to rely totally on your own resources. You believe you can live comfortably on $70K a year after retirement (this would cover living expenses and taxes). While you are saving for retirement, your investments will earn 5%, however, after retirement you will move your funds to a safer investment that will only pay 3%. Assume there is no inflation.

a) How much will you need to have saved by the time you retire? Use one function to get the answer

b) According to how much you are saving per year now, how much would you have in your savings at the time of your retirement? Use one function to get the answer

c) To know if your current plan is feasible or not, you subtract what you need (your answer to Q1) from what you have (your answer to Q2) in B15, and specify whether this plan is feasible or not in B16

Answer 1

a)

Excel formula for present value is =PV(RATE,NPER,PMT,[fv],[type])

RATE = 3 % or 0.03

NPER = No. of periods = 25

PMT = Periodic payment = $ 70,000

So the required formula is:

=PV(0.03,25,70000)

This will give a value of $ 1,218,920.34

Parameter [fv] needed only for consideration of a single sum of amount not for annuity.

Parameter [type] needed as 0 for ordinary annuity and 1 for annuity due.

So, last two parameters can be omitted as it is not mentioned that during retirement period amount needed at the beginning of the year. Hence is considered as ordinary annuity.

b)

Excel formula for future value is =FV(RATE,NPER,PMT,[pv],[type])

RATE = 5 % or 0.05

NPER = No. of periods = 30

PMT = Periodic payment = $ 90,000 x 10% = $ 9,000

So the required formula is:

=PV(0.05,30,9000)                                                                  

This will give a value of $ 597,949.63

Parameter [pv] needed only for consideration of a single sum of amount not for annuity.

Parameter [type] needed as 0 for ordinary annuity and 1 for annuity due.

So, last two parameters can be omitted.

NOTE:: I HOPE THIS ANSWER IS HELPFULL TO YOU.....****PLEASE SUPPORT ME WITH YOUR RATING.....THANK YOU......