finance question

Question

Assume that you want to retire in 30 years. You intend to invest $200 per month into a mutual fund that you expect to return 12% per year (1% monthly). If you continue making these monthly investments for 30 years, what amount of money will you have at the end of the 30th year? Use Excel to develop and present this result. Present the result in a separate page of the spreadsheet.
Given problem number 4, please develop a summary table showing the accumulation for all options with an assumed return of 10%, 11%, 12%, 13% and 14% as well as an investment of $150, $200 and $250. Use Excel to develop and present this summary table. Present the result in a separate page of the spreadsheet.

Answer 1

First you have to understand that you are looking for the future value of an ordinary annuity. An excel function will solve this easily, using the following variables. Look for the function FV

 

FV = ?

PV = 0

PMT = 200

i% = 1%

N = 30*12 = 360 months

 

FV = 698,992.83

 

In your Excel spreadsheet format columns B through E as , format

Input the value 10% into cell A1

Input 150 in cell B1

These are the variables that you will be changing to solve different annuities.

 

Number Column A5, A6, and A7 1, 2, and 3 respectively, then extend this numbering into the rest of column A for 360 rows.

In cells B3 to D3 input the headings: Payment, Interest, and Balance respectively.

Input the formula =A1/12 in cell C1. This divides the annual interest rate into a monthly rate.

 

In cell D4 input the value 0. This is the present value of the annuity at the start of year 1. Remember that in an ordinary annuity, each payment occurs at the END of each period.

 

Now you have to create the appropriate formulas in row 5. Use the F4 key when your formula accesses the variables in row 1.

 

B5: =B1  --  It should look like =$B$1

C5: =D4*C1 -- is should look like =D4*$C$1

D5: =D4+B5+C5

 

Your spreadsheet is now programmed. You just have to extend row 5 into the rest of the spreadsheet.

Copy cells B5-D5 into the next 360 rows of column B through D. Cell D364 should have the value 339,073.19

 

If you input 200 and 12% into cells A1 and A2, cell D364 should have the value 698,992.83, the same as computed above.

 

By inputting different payment amounts and different interest rates into cells A1 and A2, the spreadsheet calculates the annuity using the new values.