Question

Excel template - Saved Home Insert Data Review View Help Tell me what you want to do C D Required annuity payments $50,000 25 Retirement income today Years to retirement Years of retirement Inflation rate Savings Rate of return 5.00% $140,000 7.00% Formulas Calculate value of savings in 10 years: Savings att - 10 Calculate value of fixed retirement income in 10 years: Retirement income att - 10 Calculate value of 25 beginning-of-year retirement payments att 10: Retirement payments at t = 10 NA Calculate net amount needed at t = 10: Value of retirement payments Value of savings Net amount needed #N/A #N/A #N/A Calculate annual savings needed for next 10 years: Annual savings needed for retirement #N/A

Answer 1

The answer to every question is in dollars.

1. The value of savings in 10 years is 140,000 * ((1+7%) ^ 10)=

2,75,401.19

2. The value of fixed retirement income in 10 years. Explanation: In 10 years time the amount you need should be increased by the inflation rate = 50000 * (1.05 ^10) =

81,444.73

3. For 25 years starting from year-10 beginning each year you need at (t=10) 81,444.73

At t=11, 81444.73*(1.05)^1

and so on...

4. At t=10 calculating the NPV at 7% where the values are as calculated above for each of the 25 years, the answer we get is

$15,31,433.33

5. The net amount needed is

$15,31,433.33 (Value of retirement payments)

- $ 275,401.190 (Value of savings)

= $ 12,56,032.14 (Net amount needed)

6. Annual savings needed for retirement is to be calculated using PMT function in Excel as PMT(7%,10,,- $ 12,56,032.14) which comes out to be

$ 90,908.47 per year for the next 10 years.