Question

A couple want to save for their daughter’s university studies in 10 years. Their daughter, Susan, will study for 4 years with 1 term for each half year when the interest rate is 7% per annum compounded semi-annually. Susan will need $18,000 at the beginning of each term for 1 term. The couple plan to deposit a fixed amount at the end of each year for 10 years. How much should the couple deposit at the end of each year into an account that pays 6% interest per annum compounded annually?

Answer 1

Break the problem into two parts, first address the part of the amount needed at the beginning of the studies of their daughter susan which is at end of 10th year and beginning of the 11 year.

The amount required to be available is calculated in excel is as = PV (3.5%,8,-18000,0,1) which comes out to $128,061.79.

PV is the present value function, 3.5% indicates the interest paid on semi annual basis (7% divided by 2), 8 is the number of time periods (4 years * 2 terms), payment of 18000 and it is negative as it is an outflow, Future value is 0 and 1 indicates that payment is happening at the beginning of the period.

To the next part which is the amount required to be deposited on an yearly basis for 10 years by the parents are as below.

=PMT (6%,10,0,128061.79,0) = $9715.79 (amount to be deposited for 10 years on yearly basis)

This is the payment function in excel used to arrive at the amount to be deposited on a periodical basis. Interest rate is 6%, time period is 10 indicating 10 years, 0 is the present value as we do not have anything deposited at time 0, Future value is 128061.79 based on the amount we calculated in the previous step and 0 indicates the payment happens at end of time period.