Question

Please create a simple macro ( in Excel) to solve for the following problem:

Suppose you spend $4.00 on coffee every day (e.g., that is $28 a week or $1,456 a year). Let $1,456 be your payment. Suppose you, a 22 year old college student, put that payment ($1,456) in an account every year at the end of each year for the next 40 years. Let's assume you can earn 8% on that account.

What is the Future Value of that account? Please assume that you have nothing in the bank today so that the present value is $0.00.

Question 1: How much would you have for retirement if you avoided that $4.00 cup of coffee a day for 50 years? We assume annual payments at the END of the year.

Question 2: How much would you have for retirement if you avoided that $4.00 cup of coffee a day for 50 years and you put your savings in a retirement fund at the BEGINNING of the year?

Question 3: How much would have you have for retirement if instead of avoiding coffee for 7 days, you decided to "give up" coffee for 4 days only ($16 a week, $832 per year). Compare your future value if payments are made at the END of the year versus the BEGINNING of the year.

Question 4: Recalculate Question 1 assuming that an older relative will be saving for 20 years instead of 40 years on coffee. How did the future values compare?

Answer 1

1: Fv = P*((1+r)^n-1)/r

= 1456*((1+0.08)^50 -1)/0.008)

=835409.35

2: FV = (1+r)* P*((1+r)^n-1)/r

=(1+0.08)*1456*((1+0.08)^50 -1)/0.008)

=9022420.96

3: FV of amount saved at the end of the year

Fv = P*((1+r)^n-1)/r

= 832*((1+0.08)^50 -1)/0.08)

=477376.77

FV of amount saved at the beginning of the year

FV = (1+r)* P*((1+r)^n-1)/r

=(1+0.08)*832*((1+0.08)^50 -1)/0.008)

=5155669.12

4: Fv = P*((1+r)^n-1)/r

= 1456*((1+0.08)^20 -1)/0.008)

= 666294.20

The Value if much lower in this casedue to lesser period of investment.

EXCEL WORKINGS