Question

8 pts Question 6 Suppose that you are just about to retire, and you just turned 65. Your personal and family health history is such that you forecast that you will live to age 78. In retirement, you would like to have purchasing power of $60,000 (i.e., real dollars) before taxes. Suppose, for our example, that you anticipate receiving $20,000 in inflation-adjusted Social Security payments each year. Hence, your portfolio will need to provide $40,000 in real dollars each year. Assume that each payment is at the start of each year in retirement, where the first payment is immediately. How much do you need to have in your retirement account at retirement (in real dollars)? Assume that your portfolio earns a real annual rate of return of 7.06%, compounded annually during retirement Do not round at intermediate steps in your calculation. Round your answer to the nearest dollar. Do not type the $ symbol.

Answer 1

6]

Amount required in retirement account is calculated using PV function in Excel :

rate = 7.06%

nper = 14 (14 annual withdrawals from age 65 to 78)

pmt = 40,000 (annual withdrawal required)

fv = 0 (balance in retirement account after last withdrawal will be zero)

type = 1 (each withdrawal is made at the beginning of the year)

PV is calculated to be $373,172

7]

The annual contribution is calculated using PMT function in Excel :

rate = 5.17%

nper = 40 (40 annual contributions until age 65)

pv = 0 (amount in retirement account now is zero)

fv = 689250

PMT is calculated to be $5,473

8]

The cumulative principal repaid by the end of 13 years is calculated using CUMPRINC function in Excel :

rate = 7.75% / 12 (converting annual rate into monthly rate)

nper = 30 * 12 (30 year loan with 12 monthly payments each year)

pv = 100,000 (loan amount)

start period = 1 (we are calculating cumulative principal repaid between month 1 and month 156)

end period = 156 (we are calculating cumulative principal repaid between month 1 and month 156)

CUMPRINC is calculated to be $18,904.82

Balance outstanding on loan = $100,000 - $18,904.82 = $81,095.18