In: Finance
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.
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.