Peter and Carol Smith live in the silicon slope area in Lehi, Utah, and both work in technology firms there. They are both 25, and their daughter Emma is 2 years old now. After visiting their financial advisor, they became concerned that they were spending too much and not putting enough funds aside for their retirement and for Emmas future educational needs. The couple makes $150,000 per year and they spend almost all of them. In their joint investment accounts, they currently have $10,000 in retirement savings. They expect to be able to live comfortably in retirement with 80% of their current purchasing power. They plan to retire in 40 years after age of 65. They expect to live 15 years in retirement (a life expectancy of 80). Both Peter and Carol are alumni of University of CaliforniaBerkeley (UCB), and they plan to send Emma there when she turns 18. They dont have an account for Emmas college saving yet. The expense for tuition of attending UCB is estimated at about $17,000 per year now. Living expense now is estimated at $5,000 per year. (Remember those expense usually occur at the beginning of each academic year). They expect inflation for cost of living to be 0.7% per year for their investment period. Tuition expense is likely to increase at a rate of 1% per year (including inflation). In their investment, they also expect to earn 10% per year on their investments in retirement and Emmas college saving.

Requirement: Conduct an analysis of their saving needs by providing information that addresses the following questions.

For retirement saving: Assuming they will continue to earn 10% on their investments, how much money will they need to have in their retirement accounts when they retire so that it will provide the fifteen years of income? Taking into account what they currently have in savings, how much will they have to save each month to meet their retirement needs?

For college saving: What amount will be needed in the account balance when Emma starts her freshman year?

How much money will Peter and Carol have to deposit at the end of each month to allow Emma to attend UCB? Assume that the couple stops making deposits when Brady starts college.

Sensitivity analysis: Redo the analysis assuming that they only earn 7% on their investments, instead of 10%. Determine the needed amounts so they have the money they need in retirement and attending college.

Note: Assume that all payments (except tuition and living expense) will be made at the end of the period

I am having trouble converting the calculations I make on my calculator to excel formulas. Attached are pictures of the excel sheet I am using.

te Predict expense needed for each vear in retirement and in colle Relative year Age of the couple Annual income needed 80% of annual income needed How much do they need for cach How much do they need for cach year with adjustment to inflation 0.7%? year in retirement (80% ofpre- retirement purchase power) with adjustment to inflation 0.7%? 150,000 0 26? 27 ? 28? 29? 30 ? 31? 32? 33? 34? 35? 36? 37 ? 38? 39? 40? 41 ? 42? 43 ? 44? 45? 46? 47 ? 48? 49? 50? 51 ? 52? 53? 54? 55? 56? 57? 58? 59? 60? 61 ? 62? 63 ? 64? 65 ? 10 12 13 15 18 26 27 28 31 35 37 38 39 te Predict expense needed for each vear in retirement and in colle Relative year Age of the couple Annual income needed 80% of annual income needed How much do they need for cach How much do they need for cach year with adjustment to inflation 0.7%? year in retirement (80% ofpre- retirement purchase power) with adjustment to inflation 0.7%? 150,000 0 26? 27 ? 28? 29? 30 ? 31? 32? 33? 34? 35? 36? 37 ? 38? 39? 40? 41 ? 42? 43 ? 44? 45? 46? 47 ? 48? 49? 50? 51 ? 52? 53? 54? 55? 56? 57? 58? 59? 60? 61 ? 62? 63 ? 64? 65 ? 10 12 13 15 18 26 27 28 31 35 37 38 39