50.1K

Verified Solution

Link Copied!

Worksheet Objectives:

Create an Excel worksheet based on the following information: 1. A mortgage real estate investment trust (mREIT) is a company that borrows money at a very low interest rate to buy mortgages that pay the company a higher rate. They make their money on the spread between the two rates and the use of leverage (debt) to amplify the rate of return. Regulations require that REITs pay out at least 90 percent of their profit as dividends after taking out a fee for their services. Consequently, these companies can have very high dividend yields. 2. Mia invests a lump-sum amount in a fund of mREITs, setting up her brokerage account (a Roth IRA) to automatically reinvest the annual dividend back into the fund at the end of every year when it is paid. This means that she will be buying more shares of the fund with the dividend. 3. After 35 years of reinvesting, she plans to retire and start spending the annual dividend, rather than reinvest it. In retirement, she will not buy or sell any shares of the fund, only spend the dividend. 4. Securities that promise a very high yield can be very risky. Unfortunately, the day after Mia invests the lump-sum, the price of the fund drops significantly and stays at this lower price throughout the 35-year dividend reinvestment period and all of Mias retirement (when she spends the annual dividend). Although this assumption is unrealistic, we are trying to test Mias plan using worst-case assumptions. The price movements of the fund are not likely to be this negative, but if we can input really pessimistic assumptions and still get acceptable results, it will reduce the probability that Mia will panic at some point and abandon her plan. Her knowledge of worst-case scenarios is one way to reduce her risk (and her fear). 5. The fund is an ETF (exchange-traded fund), so it has a market price per share like a stock. 6. Dividend yield is defined as the annual dividend per share divided by the current market price of the fund. Assume that the yield is a constant percentage throughout this assignment. Be sure to include a column which shows the dividend per share each year.

Create an Excel worksheet which shows the portfolio value and the total dollar amount of the annual dividend for each of the 35 years. Be sure to create a separate input section of your worksheet so that you can easily change the inputs.

Create a two-variable data table where the row input cell is the percentage drop in the price the day after Mia invests, and the column input cell is the percentage dividend yield. You can Google Excel two-variable data table to learn how to create one.

Create a vertical bar graph which has the numbers 1 through 35 on the X-axis and shows the amount of the total dollar dividend for each year.

Inputs

Starting portfolio value : $2,000

Starting price per share : $ 10.00

Drop in price the day after : 60%

Annual Dividend Yield : 20%

Numbers for the data table (the output in the table is the total dollar dividend in retirement):

Price drop: 0% to 80% in increments of 20% across the top of the table

Percentage Dividend Yield: 10% to 30% in increments of 2% down the side of the table Assignment

Answer the following:

Blank #1 - Put the dollar dividend that Mia will receive each year in retirement.

Blank #2 - From the data table - put the difference between the highest annual dollar dividend and the lowest annual dollar dividend in.

Answer & Explanation Solved by verified expert

See Answer