Question

8. Mary has just completed her undergraduate degree from Northwestern University and is already planning on entering an MBA program four years from today. The tuition will be $20,000 per year for two years, paid at the beginning of each year. In addition, Mary would like to retire 15 years from today and receive a pension of $60,000 every year for 20 years and receive the first payment 15 years from today. Mary can borrow and lend as much as she likes at a rate of 7%, compounded annually. In order to fund her expenditures, Mary will save money at the end of years 1-3 and at the end of years 6-14. Calculate the constant annual dollar amount that Mary must save at the end of each of these years to cover all of her expenditures (tuition and retirement)? ($38254.77) I would like to know how to solve using solver in excell

Answer 1

Soln : We have divided this problem in 2 sets first we will calculate the value of first 3 years earning and compound them at 7% , and deducted the amount of tution fee each year, please also remember that after paying tution fee in the 4th year , remaining amount will be compounded again for next year and again tution fee is deducted from the amount . Remaining balance to be compounded and carry forward till the end of 14 years

Year	1	2	3	4
Amount to be saved	38254.77	38254.77	38254.77	0
Tution Fee			20000	20000
Value after compounding ,V	43797.88	40932.60	38254.77
Sum of V			122985.25
Remaining or compounded value			102985.25	90194.22

In other set, We will take discounted values of all the pension required for 15 years and do the summation. Also, we compound all the savings from year 6-14 + total amount remained in year 5 after paying the tution fee

Please refer here the table :

Year	6	7	8	9	10	11	12	13	14	15	16	17	18	19	20	21	22	23	24	25	26	27	28	29	30	31	32	33	34
Savings (S)	38254.77	38254.77	38254.77	38254.77	38254.77	38254.77	38254.77	38254.77	38254.77
Compounding of savings ,C	65728.81	61428.8	57410.09	53654.29	50144.2	46863.74	43797.88	40932.6	38254.77	60000	60000	60000	60000	60000	60000	60000	60000	60000	60000	60000	60000	60000	60000	60000	60000	60000	60000	60000	60000
Discounted values(D)										56074.77	52406.32	48977.87	45773.71	42779.17	39980.53	37364.98	34920.55	32636.02	30500.96	28505.57	26640.72	24897.87	23269.03	21746.76	20324.08	18994.46	17751.83	16590.5	15505.14
Sum of D										635640.9
Sum of C									458215.17
Final sum of savings									635640.85	0

We can see here final savings means sum of compounded values of remaining balance at year 5 and sum of C

Now, we consider one cell which can be taken as saving value, and where the constant value of saving is considered, each year saving cell to be equal to this cell in the excel

In solver , we select the objective cell as the one with (Sum of D - final sum of savings), and selected the value in solver as 0

We need to add condition that year 4 and year 5 savings is 0, and remaining values at end of year 3 and 4 should be >= 0

On solving the same we will get the desired values.

Please refer the screenshot for your reference.