Apply in excel

You bought a Mega Millions ticket (with Megaplier) and found out that you just missed thejackpot by 1 number, as the second prize winner, you received $5 million. After paying taxes, buyinga Maserati, a yacht, and a nice house, you are left with about $1 million. According to statistics,nearly 1/3 of lottery winners declare bankruptcy at one point in their lives, you dont want to be oneof them. Now you decide that you are going to blow the rest of your money like a pro! 1. Starting now, you want to put down some money once a year for 30 years to a savingsaccount as a raining day fund. Today you will put down the first saving: $5000, and the amount willgrow at 3% every year after that. The savings account with Bank of America offers you 0.03%annual interest rate. You want to know (1) How much is your rainy day fund worth in todaysdollars? (2) How much will you accumulate in your rainy day fund after 30 years?

2. Theres a piece of land (0.44 acres) for sale just off Kennedy Blvd, and you are thinking aboutbuying the land, developing it into an office building and then selling it. The seller of the land askedfor $600,000. The general contractor says that he can develop it for you and build the office in justone year. You plan to do a month-by-month capital budgeting and see if you should proceed withthis investment. If you buy the land today, your cost is the price of the land plus 2% fee for titling,real estate broker, etc. Starting in month 1, your general contractor will take care of the rest,including site development and construction. He asks for $40,000 per month for everything. After12 months, the building will be done and you will try to sell it for $2 million. You agree to pay 5% toyour real estate agent when its sold. Your income tax rate is 33%. You also think the appropriatediscount rate for this project is 8% annually. Now you have all the numbers in front of you, youwant to (1) Decide whether you should take this investment or not. (2) Create a NPV profile (3) Doa sensitivity analysis and scenario analysis to get a better picture of the risk you are facing. (4) Youmight not be able to afford the cost of construction towards the end, you need to get a $100,000loan from the bank. After talking to Fifth Third Bank, they agreed to offer you a 10-year loan of$100,000 with fixed monthly payments and floating interest rates. Your interest rates will be 3.5%for the first 2 years, 3.2% for the next 3 years, 3% for the last 2 years and 2.8% for the last 3 years.You want to create a loan amortization table to figure out what your payments look like.

3. You graduated from USF and got a job at MetLife pension department. Your supervisorneeds your help with some of the funds liabilities and risk control. The pension fund has a series ofliabilities to be paid every 6 months to the pension plan beneficiaries: $2,000,000, $2,200,000,$2,500,000, $3,200,000, $3,700,000, $4,300,000, $4,700,000, and $5,100,000. Your company wishesto construct a portfolio of assets to cover this series of liabilities, such that it is immunized againstinterest rate risk right now. The company is considering investing in four bonds: (1) a 1-yearTreasury Bill with a face value of $1,000 and no coupon, (2) a 2-year Treasury note with a face valueof $1,000 and an annual coupon rate of 1.5%, (3) a 3-year Treasury note with a face value of $1,000and an annual coupon rate of 1.90%, and (4) a 4-year Treasury note with a face value of $1,000 andan annual coupon rate of 2.30%. All Treasury notes make 2 (semi-annual) coupon payments peryear. The current APR yield on all bonds is 1.45%. Your boss wants you to find out how many ofeach of these four Treasury bonds the fund should buy in order to fully fund the liability and beimmunized against interest rate risk right now?

4. After you sold the land, you plan to invest in stock market with the money you got. First, youpicked a stock you are very interested in. You would like to simulate future daily prices of this stockfor the next calendar year. In order to simulate prices, you need expected return and annualizedvolatilities, so you decide to calculate the annual expected return for this stock using CAPM, and youmeasure the annualized volatility using this stocks historical 1-year daily holding period returns. Inthe end, you will simulate the stock prices for the next year, the certain component and certaincomponent of each price. A graph with these 3 series of prices will also be plotted.

5. Youve been holding this stock for a while and you realize its very risky to hold a single stock.You plan to diversify your portfolio by adding 3 more stocks you are interested in. The big issuenow is how to allocate your fund into these 4 stocks. You find expected returns and annualizedvolatilities for the other 3 stocks using the same method as before. You find the correlations amongthese 4 stocks using the past 1-year historical daily returns. You set up a target portfolio return anduse the theory of portfolio optimization to find out the weights of 4 stocks in your portfolio. Inaddition, you dont plan on investing in risk-free asset in your portfolio and you dont intend toshort any stocks.

6. You value education and you want your kids to receive the best possible education. Butsending kids to private schools and Ivy League colleges is not cheap. You want to start savingmoney so that when you kids need the money, you can support them. The current assumption is youwant to have kids in 5 years and you wish to have $200,000 in todays dollars when they are 15 yearsold. Based on current situation, you expect inflation to be stable at 1.5% in the foreseeable future.Your plan is to put down savings each year and you can save every year an amount thats 2% morethan the previous year. All your savings go to a stock fund that is expected to return 10% annually,of which 1% will be dividends and 9% will be capital gains. You pay income taxes at 15% ondividend incomes as you receive them at the end of each year. You re-balance your fund every year,meaning you sell all your positions and re-invest at the beginning of a new year, so capital gains arealso realized every single year and you pay 15% tax on capital gains as well. In order to have enoughmoney for your kids education, how much money do you have to save in the first year?