Reference no: EM132209876

Annuity Valuation for Individuals and Firms -

The following questions should be answered on an individual basis.

You are considering starting a new company to provide lifetime annuities to retirees. To evaluate the potential value for your customers and your shareholders, construct each of the following tabs:

1. Customer Analysis Tab: You are considering two products which are targeted to 64-year-old retirees. Product 1 provides each retiree $X per year for as long as they live. As such, they will receive their first payment at the end of year 1, provided they survive. Product 2 provides $X*(1- %haircut) per year, guaranteed for the first ten years (regardless of the retiree's survival), and then as long as they live. The first payment is at the end of year 1. Product 1's payment ($7,500), the haircut percentage (10%), and the cost ($100,000 for either product) should all be inputs to your model. Model both products to help a customer determine the better product for their individual circumstances.

a. For both products, model out the cash flows for one person (including the purchase price) and determine the IRR (use the built-in Excel formula) and NPV for each year (assuming a person dies in that year). For the NPV, assume the risk-free rate is 3% (use this assumption throughout the assignment). Note that the last cash flow to customers should occur in period 35 (age 99) since we will assume everyone dies before reaching age 100.

b. To compare the products, we must calculate the expected NPVs. To do so, we must first estimate the probabilities of dying in each year. The model template includes three scenarios of death probabilities for each age (from 65 to 100). Using the scenario choice as a model input, use the "Death Probabilities Table" to calculate the probability of living to each age. This is most easily done by forecasting the number of individuals surviving each year. Begin with 100,000 individuals (the forecasted customer base of the firm) and calculate how many individuals are left at the end of each year. To be conservative (from the firm's perspective), round up the number of individuals surviving each year to the nearest whole number. Then use the number of individuals who die each year to calculate the death probabilities. Note that the sum of the death probabilities over the 36 years will be equal to 100%. These probabilities can then be applied to the conditional NPVs (conditional on dying each year) to get the expected NPV at the beginning of each year. Use a rolling SUMPRODUCT function to calculate the expected NPV for each year, conditional on being alive at the beginning of that year. Be sure to adjust your SUMPRODUCT by dividing by the sum of the probabilities of the remaining years.

c. Using your "Customer Analysis" tab, build a data table to analyze the expected NPV, conditional on being a particular age, of both products. The rows should contain the product numbers and the columns should contain product 1's cash flows, ranging from $7,000 to $9,000 in $250 increments. Locate your data table on your "Inputs and Answers" tab and be sure to use a dynamic reference to your "Customer Analysis" tab in order to show both product 1 and product 2 NPVs. The reference should also take the age as an input to show the correct expected NPVs.

d. Use your model to answer the following questions. Be sure to indicate which cells are calculating your answers (rather than simply typing a number as text).

• Which is the better product (at age 65)? Does your answer depend on the death probability scenario?
• How long do you have to live for the product 1 NPV to be greater than zero?
• How long do you have to live for the product 2 NPV to be greater than zero?
• What percentage haircut would make the two products' expected NPVs (at age 65) equal?

2. Financial Statements Tab: Based on your customer analysis, you have decided to sell product 1 with a cash flow benefit of $7,500 per year. Assuming you will have 100,000 customers, forecast the financial statements (income statement, balance sheet, statement of cash flows and valuation cash flows) for the firm. Use the following information to complete the financial statements from year zero (balance sheet only) to year 36.

a. To calculate your income statement, you must first determine the return on your portfolio in each year. The portfolio return will depend on the risk-free rate (3%), market risk premium (5%), market standard deviation (15%) and portfolio beta (0.5), all of which should be inputs on your "Inputs and Answers" tab. The market return will also depend on a random number. Rather than using RAND(), use an input for the trial number and use that input to lookup the relevant random numbers on the "RandomNumbers" tab. This will allow your random numbers to match the check values provided. Use NORM.INV to generate a random market return from the normal distribution, and then use this market return, with your portfolio beta, to calculate your portfolio return.

b. The income statement for an annuity company looks a bit different than for a manufacturer or service provider. Four line items will make up the "revenue" portion of the income statement. The first item is the portfolio gain or loss, which is simply the invested cash (cash available at the end of the prior year) times the portfolio return (be sure to adjust for the beta of the portfolio). The second item is the payment to annuitants.

This is the total that will be paid out to all customers each year, based on the number of customers surviving at the end of that year. This line item is most easily modeled on the "Customer Analysis" tab, by simply multiplying the individual cash flows by the number of surviving customers. Link this number to the income statement by using the INDIRECT function. You will have to lookup how to use this function and I recommend using some helper cells along with it. The third item is the gain on the annuity liability. The annuity liability is the present value of all future payments owed to your customers. The gain reflects that the present value of future obligations decreases each year. You will model this based on a change in the balance sheet (details follow shortly). The fourth item is earned annuity income (loss). This reflects that the annuity is either sold at a premium (discount), and that premium (discount) is then earned over 30 years. To calculate this value, take the time zero unearned annuity income from the balance sheet and deduct 1/30th each year for the first 30 years. The costs on the income statement are straight forward. You anticipate operating costs of $1M in year 1, and you expect operating costs to grow with inflation of 1.5% per year. Both the initial operating cost and the inflation rate should be inputs. Finally, deprecation should be 20% of the prior year's net fixed assets.

c. On the asset side of the balance sheet will be required cash, surplus cash, gross fixed assets, accumulated depreciation and net fixed assets. The company will need to buy $100,000 of gross fixed assets each year, indexed to inflation ($100,000 in year 1). We will come back to required cash in a minute. The liabilities of the company include liabilities payable, annuity payments due and unearned annuity income. Operating expenses are paid either on a monthly or twice per month basis (based on an input), and the liabilities payable should always reflect the currently due payment (that hasn't been made at the end of the year). Annuity payments due is the present value of the remaining payments owed to annuitants. This is most easily modeled on the "Customer Analysis" tab, using the NPV function with the total cash flows modeled for the income statement.

Link the present value in each period to the balance sheet using the INDIRECT function. Unearned annuity income is equal to the initial PV of the annuity payment due minus the total product cost (paid by the annuitants). If this value is negative, the annuities were NPV positive for the customers, and that loss (for the firm) is divided over the next 30 years (as earned annuity income (loss) is recorded on the income statement). To provide security to the customers, the firm must provide equity as a cushion against losses. The firm is required to keep 120% (this is an input) of the annuity payments due as "cash" on hand (invested in the firm's portfolio). Do not model this additional equity cushion explicitly. Instead, it should be reflected in your surplus cash account (which is the plug to ensure the balance sheet balances).

d. The statement of cash flows and valuation cash flows should be constructed in the normal fashion.

e. While customers are paid for 35 years, the last cash flow for the firm is in year 36. In year 36 there is no income, but the existing assets are all sold, and any remaining cash is distributed to shareholders. To model this, set year 36 gross fixed assets, accumulated depreciation and net fixed assets to zero. Also, model depreciation on the statement of cash flows based on the change in accumulated depreciation (which allows for selling the assets in the last year).

f. Using the valuation cash flows, calculate the NPV of the firm. The appropriate equity discount rate should be based on the riskiness of the cash flows. Rather than estimating the riskiness of the cash flows (and their covariance with the market), assume that the equity cash flows have a beta equal to the portfolio beta. Use this beta to then estimate the required return using the CAPM.

g. To improve the readability of this tab, show all dollar values in thousands using a custom format.

3. Monte Carlo Tab: The profitability of the firm clearly depends on market returns. You also recognize that value of the business depends on the cash requirement percentage (initially 120% of annuity payments due) and the death probability scenario selected. To get an idea of how these factors affect the firm's valuation, build an additional tab and associated Visual Basic Macro to record Monte Carlo simulations. The simulations should cover 9 different scenarios: each combination of the three death probability scenarios and cash requirements of either 100%, 110% or 120%. For each of these 9 scenarios, you will run 500 simulations based on the 500 sets of random numbers (provided on the "RandomNumbers" tab). In total, you will run 4500 simulations. Be sure to set your parameters to match the Financial Statement check values before running your code.

a. For the VBA code, you should use three nested loops. The first two loops should control the death probability scenario and the cash requirement percentage. The third (most inner) loop should control the trial number from the random numbers. In each simulation, copy all 36 years of cash flows from the "Financial Statements" tab, and also record the random number trial, the death probability scenario and the cash requirement percentage. Be sure to test your code using only a few loops before running all 500 random number trials. Adding "Application.ScreenUpdating = False" to the beginning of your code will speed up the process significantly.

b. Comment your code extensively. Use comments to describe each step in the code.

c. After running all 4500 simulations, calculate the expected cash flows for each year for each death probability scenario - cash requirement percentage pair. Using the expected cash flows, then calculate the expected present value of the expected future cash flows in every year. In each year, the PV will be based on the expected cash flows in each future year. The time zero present values and time zero expected cash flows can be used to determine the initial firm NPVs.

d. Determine the probability of failure under each scenario. Failure will occur in a given year if the cash flow from that year is more negative than the expected present value of all future cash flows. If this occurs, the equity holders would not want to add additional cash to the firm in exchange for the future cash flows. Compare each Monte Carlo trial to the relevant expected present values and determine how often the equity holders would choose to shut down the firm. Divide the total number of failures by the number of trial - years to get the failure probabilities.

e. Display the firms NPVs and failure probabilities under each of the 9 scenario combinations on your "Inputs and Answers" tab.

f. Use your model to answer the following questions.

• From the firm's perspective, how important is the cash requirement?
• From the customers' perspectives, how important is the cash requirement?
• Explain why death probability scenario 2 gives the lowest firm value.

4. Final Steps. Make your model look good.

a. Add data validation for the inputs - use lists as well as restrictions on the values that can be entered. Make sure the user can easily understand the restrictions using comments or instructions.

b. Create an "Info" tab for your model, including a legend and any needed information for the user. Ensure the formatting is consistent with the legend throughout your model.

c. Make tables and answers clear and cleanly presented. Use consistent headers throughout the model to provide continuity between tabs.