Question

You received a high-yield savings account that contains $1,000,000. The account has a 7% annual interest rate and you want to take out a constant amount every year for 40 years.

1. How much would you be able to withdraw every year? Hint: the annual interest rate should be used as the discount rate in the finite time annuity formula.

2. Using Microsoft Excel, decompose your annual withdrawals into interest revenue and revenue earned from principal deduction (for example, at t=1, you get 7% x $1,000,000 in interest, and take the remaining amount from the principal – these together should equal the amount you determined in (1)). Graph interest revenue and principal revenue together, with time on the xaxis. Report the graph based on all 40 years, and only report the interest revenue and principal revenue numbers for the first 10 years.

3. Suppose you want to take out $100,000 per year. For how many years would you be able to make this exact withdrawal?

4. After your last exact withdrawal from (3), you decide to withdraw everything in your account one year later. How much money would you get from your final withdrawal?

Answer 1

Year	Balance	Interest accrued	Withdrawl	Principal
1	100000	7,000.00	7538.5	538.50
2	99462	6,962.31	7538.5	576.20
3	98885	6,921.97	7538.5	616.53
4	98269	6,878.81	7538.5	659.69
5	97609	6,832.64	7538.5	705.86
6	96903	6,783.23	7538.5	755.27
7	96148	6,730.36	7538.5	808.14
8	95340	6,673.79	7538.5	864.71
9	94475	6,613.26	7538.5	925.24
10	93550	6,548.49	7538.5	990.01