Question

Meadow Brook Manor would like to buy some additional land and build a new assisted living center. The anticipated total cost is $25 million. The CEO of the firm is quite conservative and will only do this when the company has sufficient funds to pay cash for the entire construction project. Management has decided to save $1.4 million a quarter for this purpose. The firm earns 6 percent compounded quarterly on the funds it saves. How long does the company have to wait before expanding its operations?

a. 3.99 years b. 7.99 years c. 3.49 years d. 6.79 years e. 4.89 years

Can you show me how to do this in excel? Thank you

I would like to use a formula in excel to solve it.

Answer 1

			Interest rate p.a.	6%
Year	Quarter	Quartlery Saving	Principal for Interest calculation	Interest for the quarter	Accumulated Balance
Year 1	1	1,400,000	1,400,000	21,000	1,421,000
Year 1	2	1,400,000	2,821,000	42,315	2,863,315
Year 1	3	1,400,000	4,263,315	63,950	4,327,265
Year 1	4	1,400,000	5,727,265	85,909	5,813,174
Year 2	1	1,400,000	7,213,174	108,198	7,321,371
Year 2	2	1,400,000	8,721,371	130,821	8,852,192
Year 2	3	1,400,000	10,252,192	153,783	10,405,975
Year 2	4	1,400,000	11,805,975	177,090	11,983,064
Year 3	1	1,400,000	13,383,064	200,746	13,583,810
Year 3	2	1,400,000	14,983,810	224,757	15,208,567
Year 3	3	1,400,000	16,608,567	249,129	16,857,696
Year 3	4	1,400,000	18,257,696	273,865	18,531,561
Year 4	1	1,400,000	19,931,561	298,973	20,230,535
Year 4	2	1,400,000	21,630,535	324,458	21,954,993
Year 4	3	1,400,000	23,354,993	350,325	23,705,318
Year 4	4	1,400,000	25,105,318	376,580	25,481,898

Accumulated Balance = Principal for Interest Calculation + Interest for the quarter

Principal for Interest Calculation = Accumulated Balance of the previous quarter + Quarterly Savings

Quarter 3 accumulated balance of Year 4 is 23,705,318; Quarter 4 accumulated balance of Year 4 is 25,481,898. The earnings and investment in this quarter 4 is 25,481,898 - 23,705,318 = 1,776,580

The total cost of investment is 25,000,000. If the earning and investment for the entire quarter (i.e. for 0.25 years) is 1,776,580, to reach the 25,000,000, we have to find out at what time we will reach 1,294,682 (25,000,000 - 23,705,318), which is 0.25 / 1,776,580 * 1,294,682 = 0.18 years.

So in year 4, the required time is 3 quarters + 0.18 years, if converted into year = (0.25 x 3) + 0.18 = 0.93 years

Therefore the company has to wait for 3.93 years.