Calculating Annuity Values Bilbo Baggins wants to save money to meet three objectives. First, he would like to able to retire 30 years from now with retirement income of $17,500 per month for 25 years, with the first payment received 30 years and 1 month from now. Second, he would like to purchase a cabin in Rivendell in 10 years at an estimated cost of $345,000. Third, after he passes on at the end of the 25 years of withdrawals, he would like to leave an inheritance of $2,000,000 to his nephew Frodo. He can afford to save $2,350 per month for the next 10 years. If he can earn an EAR of 10 percent before he retires and an EAR of 7 percent after he retires, how much will he have to save each month in years 11 through 30?

EXCEL FORMULAS PLEASE!

30 31,500 $ 25 10 Years until retirement Monthly retirement income Years in retirement Years until cabin purchase Cabin price Inheritance Monthly savings for first years Pre-retirement EAR Retirement EAR $ 415,000 1,375,000 4,000 10% 7% Output area: Pre-retirement APR Retirement APR PV of retirement spending PV of inheritance Amount needed at retirement Savings value until cabin purchase Savings after cabin purchase FV of initial savings Retirement shortfall Savings needed per month