Question

Question 1 (22 points total). Your sister is starting to plan for your niece's college expenses and has asked for your advice. Elle has turned 6 years old today and she will start college when she is 18 years of age. Your sister plans to start saving today, by making a deposit of $5,000 into an account that earns 6% interest per year. Then, she will continue to make deposits at the end of each of the next 12 years, increasing the amount of the deposit each year by $1,500. (a) (12 points) How much will your sister have saved by the time Elle is 18 years of age (i.e. at the end of the 12 year). P=1000.500ot 1roo (b) (10 points] While your sister is uncertain about how much Elle's education will actually cost, she does anticipate that college costs will increase by 2.5% each year over the previous year's cost. Elle will make the first withdrawal to pay her expenses at the end of her 19 year of life (.e. at the end of the 13th year), and will continue to make an end of the year withdrawal for five total years in which she is a student (year 13 through 17). Ultimately, your sister's goal is that the deposits that she will make will match exactly the expenses that Elle will have during college. If this is true, how much can Elle's first year of college cost? The interest rate for the withdrawal period remains the same as the deposit period.

Answer 1

Elle is 6 years old today and will start college after 12 years. Your sister will start today with a deposit of $5,000 and will continue depositing $5,000 into the account at the end of each year, with additional deposit of $1,500, which increases by the same amount each year. The annual rate of interest is 6%.

Ans.a) We need to calculate the future value of the deposits done by your sister at the end of 12 years. Kindly refer to the Excel file images below for calculations:

2 Rate of interest: 3 Annual deposits starting year 0: 4 Annual deposits starting year 1: 6% $5,000.000 $1,500.000 Future value = Cashflow *(1 + rate of interest% no.of years to future value Future value 8 9 =E8*((1+E2)^12) 10 11 12 13 Deposits Increasing Total deposits Elle's age Year (A) deposits (B) (A) + (B) 0 $5,000.000 $5,000.000 7 1 $5,000.000 $1,500.000 $6,500.000 2 $5,000.000 $3,000.000 $8,000.000 3 $5,000.000 $4,500.000 $9,500.000 104 $5,000.000 $6,000.000 $11,000.000 5 $5,000.000 $7,500.000 $12,500.000 12 6 $5,000.000 $9,000.000 $14,000.000 7 $5,000.000 $10,500.000 $15,500.000 8 $5,000.000 $12,000.000 $17,000.000 9 $5,000.000 $13,500.000 $18,500.000 10 $5,000.000 $15,000.000 $20,000.000 11 $5,000.000 $16,500.000 $21,500.000 18 12 $5,000.000 $18,000.000 $23,000.000 Total Future value at the end of 12 years: $10,060.982 $12,338.941 $14,326.782 $16,050.050 $17,532.329 $18,795.378 $19,859.268 $20,742.496 $21,462.108| $22,033.796 $22,472.000 $22,790.000 $23,000.000 $2,41,464.130 21 =SUM(F8:F20) 22

So, your sister would have saved $2,41,464.130 at the end of 12 years i.e. by the time Elle is 18 years of age.

Ans. b We need to calculate the expenses during Elle's first year of college. It has been mentioned the yearly college expenses will increase by 2.5% each year. Here, we will need to use the formula for calculating the present value of growing annuity. We will need to compute the present value of the growing annuity or college expenses at the end of 18 years i.e. at the beginning of the 19th year. This is to compare the present value of Elle's college expenses with the future value of her deposits at the end of Elle's age of 18 years.

Present value of growing annuity =

Here, C the annual cashflow towards Elle's fees =?

r= annual rate of return = 6% per year

g = growth rate at which the yearly cashflow will grow

n = number of years

Putting in all the given values in the above equation, except value of C, we will get:

C Present value of growing annuity = (6% - 2.50%) (1+2.5% 91 1 + 6%

Now, your sister wanted to match the deposits of 12 years with the present value or the annuities, So, we will equate the present value of her expenses with the future value that was calculated earlier as in Part a of the answer.

(693-2.5037" (1-(42*] = $2,41,464.13 = $2,41,464.13 (6%-2.50%)

$2,41,464.13*3.5% [1-(1+2.5% 91 1 + 6%

Therefore, C = $54,684.34, is the first year of college cost.