retirement plan using excel

[MAngeles2013]

I have an excel assignment that i'm struggling with. A little help with the functions I need to use would be greatly appreciated. The investment funds are what is throwing me off the most.

let's say i decided to invest $2000 per year from now until iam age 65 into a retirement plan. i have decided to place one-half of the funds into a stock index fun that roughly tracks the return on the Standard and Poors Index. The other half of the funds will be place in a mutual fund containing bonds. i want to use the PV, FV and annuity functions within excel to complete the schedule.
a. Assume that the stock fund averages 12 % per year compounded annually and you bond fund averages 8% per year compounded annually.
b. 3% annual inflation rate
c. How do I find the annual income I need to receive at age 65 so that you can keep the same purchasing power that I have today starting with $30,000 as my base salary?
d. Once I retire at age 65, I want fund your retirement for 20 years, until I am age 85. How do i find the present value at my retirement, age 65, using the annual income?
e. How do i find the expected savings for each year in order to afford my retirement?

Thanks

 

[JeffM]

I have an excel assignment that i'm struggling with. A little help with the functions I need to use would be greatly appreciated. The investment funds are what is throwing me off the most.

let's say i decided to invest $2000 per year from now until iam age 65 into a retirement plan. i have decided to place one-half of the funds into a stock index fun that roughly tracks the return on the Standard and Poors Index. The other half of the funds will be place in a mutual fund containing bonds. i want to use the PV, FV and annuity functions within excel to complete the schedule.
a. Assume that the stock fund averages 12 % per year compounded annually and you bond fund averages 8% per year compounded annually.
b. 3% annual inflation rate
c. How do I find the annual income I need to receive at age 65 so that you can keep the same purchasing power that I have today starting with $30,000 as my base salary?
d. Once I retire at age 65, I want fund your retirement for 20 years, until I am age 85. How do i find the present value at my retirement, age 65, using the annual income?
e. How do i find the expected savings for each year in order to afford my retirement?

Thanks

The question as posed does not quite make sense. For example, question e seems to have been eliminated from consideration by the stipulation that you will invest $2000 annually. And am I correct that the assumption is that inflation continues at 3% pa until you are 85 and you want an income of constant purchasing power throughout retirement?

I am not an expert in excel. This is a math site. But I do not believe that there is an excel function to answer question c. Of course you can build an excel expression that will calculate the answer to question c, but as far as I know, there is no built-in function to do so. There are built-in future value and present value functions in excel for an annuity, but I do not know what you mean by the annuity function.

Please clarify your question.

 

[civilservant]

I have a similar problem. Mine is a real life example. I am 25 years old and I will retire at the age of 60. I wish to calculate the amount that I receive at 60 under conditions as detailed below -

I start investing $1300 every month and there is a matching contribution by my employer. The sum ($2600) is invested in a mutual fund, whose break-up is as follows:
50% in equity, 30% in corporate debt and 20% in government bonds.
So, roughly, $1300 in equity, $780 in corporate debt and $520 in government bonds. Let us assume, there is an overall return of 8% per annum (compounded) for ease of calculation.

It is easy to calculate $2600 compounded @ 8% p.a for 35 years. But, if there is an increase of 5% every six months, how shall I calculate it?

I started working at the age of 22 and I am now 25. I invested in a fund which has delivered 8.2% interest per annum compounded. The NAV of the fund was 13.5275 3 years ago and now it is 17.2491. But, during the same period, my investment per month also has increased from $1338 to $1809 (thus, compounding at 10.3% p.a). But, it changes every 6 months. For example, it has remained 1338 for the months from January to June and from July to December it has changed to 1405 and so on till it has reached 1809 for the current month.

I wish to take into account the same 10.3% increase in the principal amount (every 6 months) and 8.2% returns annually for the entire corpus accumulated. Is there any method to calculate what amount I shall received at age 60?

Please help! If the question is not clear, kindly mail me.

Thank you.

 

[JeffM]

I have a similar problem. Mine is a real life example. I am 25 years old and I will retire at the age of 60. I wish to calculate the amount that I receive at 60 under conditions as detailed below -

I start investing $1300 every month and there is a matching contribution by my employer. The sum ($2600) is invested in a mutual fund, whose break-up is as follows:
50% in equity, 30% in corporate debt and 20% in government bonds.
So, roughly, $1300 in equity, $780 in corporate debt and $520 in government bonds. Let us assume, there is an overall return of 8% per annum (compounded) for ease of calculation.

It is easy to calculate $2600 compounded @ 8% p.a for 35 years. But, if there is an increase of 5% every six months, how shall I calculate it?

I started working at the age of 22 and I am now 25. I invested in a fund which has delivered 8.2% interest per annum compounded. The NAV of the fund was 13.5275 3 years ago and now it is 17.2491. But, during the same period, my investment per month also has increased from $1338 to $1809 (thus, compounding at 10.3% p.a). But, it changes every 6 months. For example, it has remained 1338 for the months from January to June and from July to December it has changed to 1405 and so on till it has reached 1809 for the current month.

I wish to take into account the same 10.3% increase in the principal amount (every 6 months) and 8.2% returns annually for the entire corpus accumulated. Is there any method to calculate what amount I shall received at age 60?

Please help! If the question is not clear, kindly mail me.

Thank you.

Because this is not a homework problem, I suggest that you create a spreadsheet in excel. As you say, the computations for a single month are not difficult, and then you copy, paste, and take totals. The advantages of the spread sheet are that: (1) you will fully understand what is going on, and (2) you can fairly easily impose extra constraints, etc.