Hello, I need creating a portfolio for a 62 year old within a few years of retirement. I need to create the portfolio based on the best mean-variance efficient frontier. I must account for inflation. Information is given in attached spreadsheet.

Please advise if more time is needed.

Annual Expenses (Before taxes) Amount of Expense Income Required (After tax) (Before tax) Nondiscretionary Auto 12,000 Clothing 12,000 Dining and Entertainment 12,000 Groceries 7,500 Holiday Gifts 10,000 Life Insurance 1,800 Long term care insurance 3,600 Medical 6,500 Personal care (laundry, etc) 2,500 Professional Fees 7,000 Residence: Utilities, cleaning 22,500 Subscriptions 2,400 Summer home maintenance 16,300 Supplies and misc. expenses 13,000 Telephones 4,000 Total 133,100 190,143 Discretionary Expenses Annual Gifts to children Golf and other memberships Home improvement Occasional purchases Travel Total Tax Deductible Charitable Residence: Property Tax Summer home: Property Tax Total Total Income Required Before Taxes Assets Bank Accounts Brokerage: Money Market Equities 20,000 23,000 25,000 40,000 25,000 133,000 30,000 15,500 12,500 58,000 190,000 58,000 438,143 7,500 25,000 125,000 Proceeds from Sale of Company (needs to be invested a Trust (Earnings accrued on tax deferred basis) Total Financial Assets 7,000,000 2,850,000 10,007,500 Real Estate Residence (mortgage paid in full) Summer Home (mortgage paid in full) Total Assets 1,200,000 650,000 11,857,500 Returns and Risk in US Financial Markets Nominal Returns Stocks 1930-2001 1950-2001 1990-2001 1930-2001 1950-2001 1990-2001 Bonds 10.30% 12.60% 14.40% 5.80% 6.20% 9.10% Risk (Standard Deviation) Stocks Bonds 19.5% 15.3% 14.2% Real Return and Risk 1986-2001 Real Return and Risk Real Return Foreign bonds High yield Real Estate Foreign Stocks US Stocks US Bonds Emerging Markets Cash Commodities Correlation among Returns 1986-2001 Foreign Bonds High Yield Foreign Bonds 1.0 High Yield 0.1 Real Estate 0.4 Foreign Stocks 0.5 US Stocks 0.1 US Bonds 0.4 Emerging Markets -0.1 Cash 0.1 Commodities 0.0 7.1% 9.4% 6.3% Risk 7.3% 6.3% 6.4% 6.4% 11.3% 6.2% 4.6% 2.3% 4.9% 11.2% 5.8% 21.7% 17.7% 15.7% 4.8% 23.8% 0.4% 17.5% Real Estate 0.1 0.4 1.0 0.3 0.3 1.0 0.3 0.8 0.5 0.5 0.5 0.2 0.3 0.5 -0.1 -0.1 -0.1 0.0 Description: Susan (age 62) is selling her manufacturing company for approximately $10 million sometime within th 1. Withdraw sufficient funds each year to meet her living and other regular expenses 2. Needs sufficient liquid assets so that she can withdraw funds for emergencies that may arise. 3. Conservative- Large losses would have a major impact on her ability to live comfortably. 4. The purchasing power of the portfolio needs to rise with inflation. Additional Info: 1. Her daugher is engaged to be married. She will help her pay for the wedding in 2 years. Estimate the 2. Her mother is 79 and lives in living facility. Her mother is self sufficient, but the extended stay at nurs years, she will have to cover mothers living expenses for 10 years. Estimated to be $60,000 a year. 3. At some poing, she might sell her house and move into Boston. T-Bills Inflation 3.80% 3.30% 5.10% 3.90% 4.70% 2.60% T-Bills Inflation 0.9% 1.9% 0.7% 1.1% 0.3% 0.7% Foreign StockUS Stocks US Bonds Emerging MarCash 0.5 0.1 0.4 -0.1 0.3 0.5 0.5 0.3 0.8 0.5 0.2 0.5 1.0 0.6 0.2 0.4 0.6 1.0 0.3 0.5 0.2 0.3 1.0 -0.1 0.4 0.5 -0.1 1.0 0.0 0.0 0.1 -0.1 0.1 0.0 0.0 0.0 Commodities 0.1 0.0 -0.1 -0.1 -0.1 0.0 0.0 0.1 0.0 0.0 0.1 0.0 -0.1 0.0 1.0 0.2 0.2 1.0 million sometime within the next 2 years. She desires a portfolio that will: ncies that may arise. e comfortably. ding in 2 years. Estimate the wedding to cost $50,000. ut the extended stay at nursing home will exhaust stay. In 5 d to be $60,000 a year. Mean-Variance Efficient Frontier Two risky assets No riskless asset Short sales Program Objective MIN sw = Variables Stock 0.0000 w Constraints Sw mw = = Where Data Portfolio 0.0000 0.0000 mw sw Statistics Bond 0.0000 = = = = SP500 Tbond m s r SP500 Tbond Jan-00 Jan-00 Jan-00 Jan-00 Results Target m 0.1500 0.1200 0.1384 0.0900 0.0600 MVP 0.0549 0.0400 0.0300 0.0000 -0.0300 Stock Bond MVP Stock 12.0000 Portfolio w Bond m s 10.0000 8.0000 Return 6.0000 4.0000 2.0000 0.0000 0.0000 2.0000 4.0000 6.0000 Ris k 8.0000 10.0000 6.0000 Ris k 8.0000 10.0000 12.0000