Question

Kindly use excel and use screenshots to show formulas

1. Suppose you plan to save $8,000 per year for the 35 years you are working. In addition to the amount you are saving each year, you expect to sell your house for $600,000 in year 32 and deposit this money into your account. How much can you withdraw in equal amounts each year for the 30 years you are retired. The interest rate you will earn during the 35 years you are saving is 7%. Once you retire, you’ll reduce the amount of stock you have in your portfolio and you will now earn a return of 5% during the 30 years you are retired. Assume that you begin saving in one year and your first withdrawal is in year 36.

2. You are examining the desirability of selling widgets. To conduct the analysis, you’ve estimated that you will sell 1 million units and will sell the units for $99 each for the next five years. The variable costs for producing this product is estimated to be $32 per unit. Fixed costs will be $3 million per year. The equipment used to produce the widgets will cost $20 million and will be depreciated using the MACRS depreciation schedule for a five-year useful life. In year 5, the equipment will be sold for 20% of its original cost. The tax rate is 21%. Construct a proforma income statement to find the net income and operating cash flow for each of the five years of the project.

Answer 1

1]

First, we compute the total future value of the account at the time of retirement, 35 years from today.

FV of yearly savings is calculated using FV function in Excel :

rate = 7%

nper = 35

pmt = -8000 (This is entered with a negative sign as it is a cash outflow)

FV is calculated to be $1,105,895.03

FV of sale proceeds of house is calculated using FV function in Excel :

rate = 7%

nper = 3 (There are 3 years between year 32 and year 35)

pmt = 0 (yearly amount deposited is zero)

pv = -600000 (Sale proceeds of house. This is entered with a negative sign as it is a cash outflow into the account)

FV is calculated to be $735,025.80

Total value of account 35 years from today =  $1,105,895.03 + $735,025.80 = $1,840,920.83

Next, we calculate the yearly withdrawal using PMT function in Excel :

rate = 5%

nper = 30

pv = 1840920.83

PMT is calculated to be $119,754.54

You can withdraw $119,754.54 in equal amounts each year for the 30 years you are retired