In: Finance
Iri is a newborn. Her parents are planning to contribute $4,000 a year (or possibly less) towards her college fund into an account that will grow at a constant rate of 4.5% a year. Both parents work for the same company that offered to match parental contributions dollar for dollar for the first 5 parental deposits and 30 cents for every parental dollar for subsequent parental deposits until Iri reaches 19. Once she reaches 19, both the company and parents stop their contributions. College costs are expected to be $40,000 a year and Jane spends 4 years in college once she reaches 19. Assume that the beginning balance on the account is $25,000(a)What is the smallest amount parents should contribute each year to make Iri's college affordable? The complete table below and solve the problem (15 points).
Inputs | ||||||||||||||||
Beginning Balance (at age 0) | ||||||||||||||||
Annual parental contribution | ||||||||||||||||
Interest rate |
|
|||||||||||||||
Annual college cost | ||||||||||||||||
Account | Deposit or withdrawal at | Interest | Total in | |||||||||||||
Jane's | balance | the beginning | earned | account | ||||||||||||
age | beg. year | of year | during year | end of year | ||||||||||||
0 | ||||||||||||||||
1 | ||||||||||||||||
|
2 | |||||||||||||||
3 | ||||||||||||||||
4 | ||||||||||||||||
5 | ||||||||||||||||
6 | ||||||||||||||||
7 | ||||||||||||||||
8 | ||||||||||||||||
9 | ||||||||||||||||
10 | ||||||||||||||||
11 | ||||||||||||||||
12 | ||||||||||||||||
13 | ||||||||||||||||
14 | ||||||||||||||||
|
15 | |||||||||||||||
16 | ||||||||||||||||
17 | ||||||||||||||||
18 | ||||||||||||||||
19 | ||||||||||||||||
20 | ||||||||||||||||
21 | ||||||||||||||||
22 |
Answer:
The smallest amount parents should contribute each year to make Iri's college affordable = $1,967.46
Completed table is as follows:
The formulas are built in the respective cells and the value of 'Annual parental contribution' is calculated through 'Goal Seek':
This excel with 'show formula' is also given below this table:
The above excel with 'show formula':
--------------------------------------------------------------------------------------------------------------------------------------
1. I have keyed in 'annual parental contribution' as $4000 initially as given:
The results are as follows:
2. With annual parental contribution of $4000, we find account balance of $113,663.67 (at age 22). Smallest amount parents should contribute each year is the amount at which this account balance (in the row at age 22) can be zero.
Use 'Goal seek' to get the appropriate amount of "annual parental contribution" to make this account balance (in the row at age 22) = 0 as follows:
3. On clicking 'OK' on goal seek pop up, you will get the following, which is the answer: