Question

include an Excel spreadsheet as an attachment that shows how you arrived at your answers. Make sure to use the Excel functions for finding the answers; do not merely but a formula in a cell. You plan on retiring in 40 years and would like to have a nest egg of $5,000,000 saved by then. a. How much do you need to save each month if you can earn a 15% annual return? b. How much do you need to save each month if you can only earn a 5% annual return? c. Still working with the 5% annual return, how much do you need to save each month if you wait ten years to start (meaning that you will only have 30 years of saving instead of 40 years). d. Reflect on your answers for a, b, and c above. Briefly tell your group mates one take-away that you have learned related to interest rates, time, and money.

Answer 1

Use PMT function to find the deposits that need to be paid monthly. (12 months in ayear)

=PMT(rate,nper,pv,fv,type)

a. rate=15%/12=1.25%

nper=12*40=480

pv=0

fv=5000000

=PMT(1.25%,480,0,5000000,0)=$161.21

b. If annual rate=5%, then rate=5%/12=0.4167%

=PMT(0.4167%,480,0,5000000,0)=$3276.50

c. If Years becomes 30, then nper=12*30=360

=PMT(0.4167%,360,0,5000000,0)=$6007.75

d. If interest rates comes down, we need to deposit more money to achieve the target Future value and at teh same time, we need to deposits more if the time decreases to achieve the future value