2. Long ago, your grandmother decided to save for the cost of your education. Fo

Question

2. Long ago, your grandmother decided to save for the cost of your education. For the past 12 years she has been depositing $300 a month in a savings account that pays interest of 4% per year, compounded monthly. She will not be making any additional deposits to the account.

a) How much is currently in the account?

b) In addition to your grandmother’s savings, you have worked to fund your education. As a result, you only need to draw $10,000 per year for 4 years from your grandmother’s account. What is left in the account after your 4 years in university?

Attention: You can only add or subtract cash flow that is at the same point in time!!!! For example: you cannot add cash flow at time 3 to cash flow at time 4. To do so, you need the find the value of cash flows at the same point in time. So you either carry cash flow from 3 to 4 (FV at time 4) and add FV to cash flow at 4 or carry cash flow at 4 to time 3 (PV at time 3) and add PV to cash flow at time 4.

c) You would like to travel for 6 months following graduation and will use the remaining funds from your grandmother’s account. If you take out funds in equal installments over the six months how much do you withdraw every month?

Explanation / Answer

a) The amount currently in the account can be calcuated by using the FV function of excel or any finacnial calculator.

The syntax of the same is : =FV(rate,nper,pmt)

where, rate = 4/12 = 0.33% since compounding is done monthly.

nper = number of periods = 12 * 12 = 144

pmt = equal monthly deposit = $300

We are assuming that deposits are made at the end of periods.

Using the above functionality, the current value in the account comes out to be $55,330.64

b) Now I have to withraw only $10000 a year to fund my college education.

SInce, 4% is componded monthly, the effective annual yield comes out to be = (1+ (4%/12))12 - 1 = 4.074%

Assuming that these funds are withdrawn at the beginning of the year, the following amounts are ther

Calculation for year 1,

Beginning amount is the amount that was there at the end of 12 years calculated in the previous question = $55,330.64

Amount withdrawn = $10,000

Aoount after withdrawal = $55,330.64 - $10,000 = $45,330.64

Amount at the end = $45,330.64 * (1 + 4.074%) = $47,177.48

Now, this ending amount of year 1 becomes the beginning amount of year 2.

Thus, from the above table, amonut left in the account after 4 years = $20,670.29

c) Now, to take out funds in equal monthly instalments over the enxt 6 months we can use the PMT function of excel, the syntax of which is =pmt (rate, nper, pv, fv,type)

where, rate = 4/12 = 0.33%

nper = 6

PV = $20,670.29

FV = $0

Type = 1, that is we are assuming that you will take out money at the beginning of periods

Using the above function, the value of PMT comes out to be $3485.35.

Thus, if you take out funds in equal installments over the six months yo will have to withdraw $3485.35 every month.

College Year Beginning Amount Withdrawn Amount after Withdrawal Ending Amount 1 $55,330.64 10000 $45,330.64 $47,177.48 2 $47,177.48 10000 $37,177.48 $38,692.15 3 $38,692.15 10000 $28,692.15 $29,861.11 4 $29,861.11 10000 $19,861.11 $20,670.29

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.