In: Finance
4) Nandana invests $500 at the start of each year for 20 years in a bank account paying interest at the effective annual rate i. She takes the interest paid at the end of each year and invests it in a different account paying an effective annual rate i/2. The effective annual rate she earns on her combined investments is 6%.
a) How much money does she have at the end of 20 years? (Total of both accounts.)
b) What is i?
This question can be easily solved using MS-Excel.
We have been given the following information:
Nandana invests $500 at the start of each year for 20 years paying effective annual rate (EAR) 'i' and the interest is then invested in different account with EAR of 'i/2'.
Now we have been given that the EAR she earns on the combined investments is 6%, which means that at the end of 20 years, the total value of her investment ($500 yearly) can be calculated using the time value of money concept. (FV formula in excel)
Using FV formula in excel:
=FV(rate,nper,pmt,[pv],[type])
here,
rate = 6%
nper = number of periods = 20
pmt = payment every period = 500
pv = present value = 0
type = 1 = amount invested at the start of each period (year)
this gives the value of FV as 19,496.36
Hence, the money she has at the end of 20 years is $19,496.36
Now, for part b:
We can build a time line in excel stating the position of amount in both options at the end of each period.
The same is as below:
For investment 1, $500 is added every year.
For investment 2, interest of amount accumulated and interest from investment 1 is added.
We have taken the interest as 'i' in cell F2.
The total is the amount added for both the investments at the end of year 20.
We know from answer of part a that the total should be $19,496.3633, we apply goal seek function and set the value of cell B24 to 19,496.3633 by changing the value of cell F2 (i.e., i).
And we get the value of 'i' as 7.1373%.
Please refer to the below image for the position at the end of each year:
Hence, the answers are as:
a) $19,496.36
b) 7.1373%