Demonstrate your understanding of financial concepts by completing the following problems. Where appropriate, show or explain your work. You may use Excel to work on the problems.

Problem 1.Calculating returns: A stock with an initial price of $55 per share paid a dividend of $1.75 per share throughout the year, with an ending price of $59. Calculate the percentage total return of the stock.

Problem 2.Calculating returns: One year ago, you bought an 8.75 percent coupon bond for $1,065. Today the bond sells for $990.

1. Calculate the total real rate of return on the investment with a $1,000 face value.
2. Calculate your total nominal rate of return on the investment this past year.

Problem 3.Holding period return: A stock has had returns of ?19.52 percent, 17.82 percent, ?11.93 percent, 21.35 percent, and 6.43 percent over the past five years, respectively. Calculate the holding period return for the stock.

Problem 4.Calculating returns: Last year you bought a share of 7.25 percent preferred stock for $63.75. Your stock's market price is now $66.92. Calculate your total return for last year.

Problem 5.Calculating returns: You bought a stock three months ago for $24.87 per share. The stock pays no dividends and is currently priced at $26.35. Calculate the APR of your investment. What is the effective annual rate (EAR)?

Problem 6.Determining portfolio weights: A portfolio contains 65 shares of Stock A selling for $32 per share and 175 shares of Stock B selling for $26 per share. Calculate the portfolio weight for each stock.

Problem 7.Portfolio expected return: You own a portfolio that has $4,600 invested in Stock Y and $5,200 invested in Stock Z. What is the expected return on the portfolio if the expected returns on these stocks are 9.75 percent and 16.50 percent?

Problem 8.Using the capital asset pricing model (CAPM): A stock has a beta of 1.65, and the projected return on the market is 12.25 percent, with a risk-free rate of 4.75 percent. Calculate the projected return on this stock.

Problem 9.Calculating cost of equity: The Denton Corporation's common stock has a beta of 1.45 and a risk-free rate of 5.75 percent. What is Denton's cost of equity if the projected return on the market is 13 percent?

Problem 10.Calculating the weighted average cost of capital (WACC): If Metro Company has the following features, what is its WACC?

• A target capital structure of 65 percent common stock.
• A target of 35 percent debt.
• Cost of equity is 14 percent.
• Cost of debt is 6 percent.
• The tax rate is 35 percent.

Excel Examples EXAMPLE 1: Discount Rate Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 ANSWER: NPV EXAMPLE 2: Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 ANSWER EXAMPLE 3: Finance Rate Reinvestment Rate ANSWER EXAMPLE 4: Year 0 Year 1 Beginning in column A, there are 16 examples of how to calculate basic accounting and math equations in Excel. Column C NET PRESENT VALUE 12% -65 10 20 40 65 -20 $18.30 INTERNAL RATE OF RETURN (IRR) -65 10 20 40 65 -20 22.41% MODIFIED INTERNAL RATE OF RETURN (MIRR) 12% 15% 18.12% PRESENT VALUE 12% 1 Year 2 Year 3 Year 4 Year 5 ANSWER EXAMPLE 5: Year 0 Year 1 Year 2 Year 3 Year 4 Year 5 ANSWER EXAMPLE 6: Present Value = Future Value = Discount Rate 5 100 $56.74 FUTURE VALUE 12% 5 100 $176.23 FINDING N (NPER) NUMBER OF PERIODS (OR YEARS) $50 $100 12% ANSWER 6.12 Present Value = Future Value = Discount Rate $100 $25 12% ANSWER -12.23 EXAMPLE 7: Present Value = FINDING I (INTEREST RATE) $100 2 Future Value = N (Nper) ANSWER $200 5 14.87% Payment (PMT) = Future Value = N (Nper) ANSWER $100 $750 5 20.40% EXAMPLE 8: Present Value = Future Value = N (Nper) Interest Rate ANSWER EXAMPLE 9: SUM AVERAGE VARIANCE STANDARD DEVIATION CORRELATION COVARIANCE FINDING THE PAYMENT AMOUNT (PMT) OR ANNUITY AMOUNT $0 $100,000 20 12% $1,387.88 SUM, AVERAGE, VARIANCE, STANDARD DEVIATION, CORRELATION, COVARIANCE 0.12 0.15 0.08 0.06 0.08 0.4900 0.0980 0.0013 0.0363 0.3928 0.0012 3 EXAMPLE 10: CALCULATING A BOND'S PRICE Suppose we have a bond with 22 years to maturity, a coupon rate of 8 percent, and a yield to maturity of 9 percent. If the bond makes semiannual payments, what is its Settlement Maturity Rate YTM 1/1/2000 1/1/2022 0.08 0.09 Redemption Frequency Basis Bond Price 100 2 0 90.49 Multiply by 10 904.91 EXAMPLE 11: CALCULATING A BOND'S YIELD TO MATURITY Suppose we have a bond with 22 years to maturity, a coupon rate of 8 percent and a price of $960.17. If the bond makes semiannual payments, what is its yield to ma Settlement Maturity Rate Pr Redemption Frequency Basis Yield to Maturity EXAMPLE 12: 1/1/2000 1/1/2022 0.08 96.017 100 2 0 8.40% CALCULATING THE EFFECTIVE ANNUAL INTEREST RATE 4 Suppose you have a Nominal Interest Rate of 5.25% that is compounded quarterly (4 times) during the year. What is the Effective Annual Interest Rate? Nominal Interest Rate Npery Effective Annual Interest Rate 5.25% 4 5.3543% EXAMPLE 13: CALCULATING THE ANNUAL NOMINAL INTEREST RATE Suppose you have an Effective Annual Interest Rate of 5.35% that is compounded quarterly (4 times) during the year. What is the Nominal Annual Interest Rate? Effective Annual Interest Rate Npery Nominal Annual Interest Rate 5.35% 4 5.2459% CALCULATING THE INTEREST RATE PER PERIOD OF A LOAN OR EXAMPLE 14: AN INVESTMENT If you make monthly payments of $200 on an $8,000 loan over 4 years, what is the Annual Interest Rate of the loan? 4 -200 8000 Years of the Loan Monthly Payment Amount of the Loan Monthly Interest Rate of the Loan 0.77% Annual Interest Rate of the Loan 9.24% EXAMPLE 15: CALCULATING THE GEOMETRIC AVERAGE RETURN (OR MEAN) A stock has produced returns of 14.6 percent, 5.3 percent, 17.6 percent, and -4.7 percent over the past four years, respectively. What is the geometric average return? Year 1 1.146 5 Year 2 Year 3 1.053 1.176 0.953 7.84% EXAMPLE 16: Adding cell B163 to cell B164: Subtracting cell B163 from cell B164: Multiplying cell B163 by cell B164: Dividing cell B164 by cell B163: Using Parentheses: Multiplying cell B163 by (cell B164 + cell B165): Calculating cell B163 to the power of cell B164: Calculating of cellofB171: Calculating the the Square Natural Root Logarithm cell B171: SIMPLE MATH CALCULATIONS 2 2 5 4 0 4 1 14 4 2 1.3863 End of worksheet 6 ulate basic accounting and math equations in Excel. Column C includes annotations for some of the examples. Don't include the year 0 cash flow of -$65 (cell B6) because the payments occur at the BEGINNING of the first period. Note: Use the yearly data from Example 2. Discount $100 back 5 years at a 12% discount rate. (Discount Rate) 7 (# Periods or years being discounted) (FV) Compound $100 up 5 years at a 12% discount rate. (Discount Rate) (# Periods or years being compounded) (PV) How long would it take to compound $50 up to $100 using a 12% discount rate? Or 6.12 years. Note that you have to make either the Future Value or Present Value input negative for the formula to work. How long would it take to discount $100 down to $25 using a 12% discount rate? Or 12.23 years. Note that years cannot be negative. You have to make either the Future Value or Present Value input negative for the formula to work. If you start with $100 and end with $200 after 5 years, what was the annual interest rate earned? 8 Or 14.87%. You must keep either the Present Value or Future Value input negative. If you receive payments of $100 each year for 5 years and end up with $750 after 5 years, what was the annual interest rate earned? Or 20.40%. Note that the Payment input or Future Value input must be negative for the formula to work. What would have to be the annual payment amount (or annuity amount) to have $100,000 after 20 years with a 12% discount rate? Note: You want the Future Value input to be negative so your answer comes out positive. 0.09 0.11 0.15 0.03 -0.12 9 9 percent. If the bond makes semiannual payments, what is its price today? Think of Settlement as the beginning of the duration of the bond. Think of Maturity as the end of the duration of the bond. (Coupon Rate) (Yield to Maturity or Required Rate of Return) (Bond's Face Value, Par Value, or Fair Price) Note that it is $100, not $1,000. You make the adjustments by multiplying the answer by 10. Coupon payments are semiannual, so you put in a 2. If they are annual, then you input a 1. Always leave it blank. The answer, but you need to multiply it by 10 to get the actual bond price. Note: Excel gives the bond price in 2 digits like in cell B109. You need to multiply it by 10 to get the actual bond price.) (ANSWER = 904.91) the bond makes semiannual payments, what is its yield to maturity? Think of Settlement as the beginning of the duration of the bond. Think of Maturity as the end of the duration of the bond. (Coupon Rate) The bond's price per $100 face value. (Bond's Face Value, Par Value, or Fair Price) Note that it is $100, not $1,000. Coupon payments are semiannual, so you put in a 2. If they are annual, then you input a 1. Always leave it blank. (ANSWER = 8.40%) 10 ear. What is the Effective Annual Interest Rate? (Number of compounding periods per year) Note: The EAR is always higher than the Nominal Rate as long as there is more than 1 compounding period per year. If you increase the compounding periods per year, the Effective Annual Rate will increase, but at a decreasing rate. (ANSWER = 5.35%) ing the year. What is the Nominal Annual Interest Rate? (Number of compounding periods per year) (ANSWER = 5.25%) Note: Multiply the years of the loan by 12 months for the monthly rate. (ANSWER = .77%) Note: Multiply the Monthly Interest Rate by 12 to get the annual rate. (ANSWER = 9.24%) our years, respectively. What is the geometric average return? Add 1 to all positive returns. 11 Add 1 to all positive returns. Add 1 to all positive returns. For negative returns, subtract it from 1. You have to do this to keep all data positive. Note: Place a minus 1 after the formula to get rid of the whole number. (ANSWER = 7.84%) 12 MBA-FP6016 Assessment Problems - Helpful Tips 1 MBA-FP6016 Assessment 2 - Financial Statements Use the inputs provided in the Excel spreadsheet to come up with formula inputs. Basic accounting equations and formulas: . This formula can be manipulated with simple algebra to place any of the three inputs separately on one side of the equation. The equity multiplier formula starts out as , but it is derived into the following formula: . The return on equity formula is , but it can be derived into the following formula: . 2 MBA-FP6016 Assessment 3 - Calculating Financial Values For all of the problems, be sure to use the correct built-in Excel formulas to derive your answers. Do not use algebra. 3 MBA-FP6016 Assessment 4 - Calculating Payback and Profitability Problem 1: When you calculate NPV using the built-in NPV formula in Excel, be sure to place the year 0 cash outflow outside of the parentheses in the formula because the payment occurs at the beginning of the first period. Problem 2: The payback period formula is the amount of time it takes to recover the cost of the project or investment. The formula to use is . Problem 4: When you find the NPV as the first step of calculating the profitability index, be sure to exclude using the year 0 cash outflow (initial cost), using the built-in NPV formula in Excel. When calculating the profitability index, make sure the year 0 initial cost is a positive number. Problem 5: When calculating the operating cash flow, use this formula: First, you will have to multiply a few of the inputs given in the assessment to come up with some of the formula inputs. For the first part of the formula, you need to multiply the number of servings produced per year by the price of each ice cream serving to derive the sales, which are not given in their entirety in the problem. You need to find this in order to derive the costs and depreciation. 4 MBA-FP6016 Assessment 6 - Calculating Risks and Returns Problem 8: Under CAPM, the cost of equity (or expected return on equity) formula is: 5 MBA-FP6016 Assessment 7 - Dividends and Stocks Problem 2: If the stock split increases the number of shares, then the stock price has to be lower than it originally was. If it is a reverse stock split, where number of shares decrease, then the stock price must be higher than it originally was. Problem 3: For this problem, rearrange what is known as the Lintner formula. You want to isolate Div1 (the dividend one year from now) so the formula should look like the following: Div1 = Div0 + s*(t *EPS1 - Div0) (Note: The * indicates multiplying.) 6 MBA-FP6016 Assessment 8 - Megaware Case Study Problem 5: Use what you know about the situation and the theory to come up with an answer that seems appropriate according to the theory. Do not focus on having all exact numbers to accomplish everything. The final number is not what is important. What does each factor stand for and what happens if one chooses one of the possibilities for each factor individually? What each variable stands for is known in problem 5: b = retention ratio. E1 = earnings next year. ROE = return on equity. Dividend-payout ratio = 1 minus b. P0 = dividend next year; the earnings next year times 1 minus the retention ratio. Rs = sustainable growth rate; the return on equity times the retention ratio. Eight years in from the start of the company, the profit is $42 million from the sale of a fixed asset. What happens to P0 and ROE if all other factors remain steady and b goes up or down? What effect does the one-time influx of $42 million have on the formula? 7 MBA-FP6016 Assessment 9 - Financing and Exchange Rates Problem 1: When calculating the cash for this problem, use the following given data to derive the answer: Net worth. Long-term debt. Net working capital (excluding cash). Fixed assets. Problem 2: For the first calculation, just find the dollar value of the current shares outstanding and add that to the value for the rights offering using the given data. For the second calculation, you will use the number of shares outstanding and number of new shares outstanding in the future (rights offering) to derive the answer. For the third calculation, you will use the new market value of the company, the number of shares outstanding, and the number of new shares outstanding in the future (rights offering) to derive the answer. For the last calculation, you will use the current stock price and ex-rights price to derive the answer. 8