Please use the formula from below only. In excel format and explain how did you come with answer. See the screeshot of the problem.

Formulas

Accounting

• Retained Earnings = Prior Year's Value + Current Net Income - Dividends
• EFN = External Funds Needed = Forecasted Assets - Liabilities - Equity (holding debt and stock constant)
• Internal Growth = ROA*Plowback / (1-ROA*Plowback) "Rate of sales, asset, and NWC growth without need for external financing"
• Plowback = 1 - Payout; Payout = Div / Net Income
• Sustainable Growth = ROE*Plowback / (1-ROE*Plowback) "Rate of sales, asset, and NWC growth without need for new stock issuance, but debt can be issued to maintain current D/E ratio"

Cash Flows

• EBIT=Revenue - COGS - SG&A - Depreciation; EBITDA = Revenue - COGS - SG&A
• FCFF=EBIT*(1-tax rate) + Depr - Capex - Changes in NWC; NWC = A/R + Inv - A/P ?+? Cash If cash is needed for operations. If cash is needed for operations - include in NWC but do not reduce debt in stock valuation.
• FCFE=Net Income + Depr - Capex - Changes in NWC + Debt Issuance - Debt Princ. Payments
• Payment function = PMT(rate per period, number of periods, PV)

Present Value

• PVLS = FV(t) / (1+r)^t; FVLS = PV * (1+r)^t; PVGA=PMT*(((1-((1+g)/(1+r))^T))/(r-g))
• PVGP = PMT(1) / (r-g) ; PVGP = (PMT(t) / (r-g)) / (1+r)^(t-1)

Capital Budgeting, NPV, and WACC

• NPV = SUM of CF/(1+r)^t; NPV Function = NPV(rate per period, payments starting at END of first period)
• IRR = Discount rate (r) than makes NPV = 0; IRR also the average annual project rate of return. IRR function cannot be used if cashflows change sign more than once. IRR function cannot be used if CFCs use a growth perpetuity calculation.
• After-Tax Opex FCF = -Opex * (1-tax rate); Depreciation FCF (dep tax shield) = +Depreciation * Tax Rate
• After-tax revenue or opex savings = Revenue or Opex Savings x (1 - tax rate)
• Terminal Year Net Working Capital = 0 for finite life projects
• WACC = Ke * E/(D+E) + Kd *(1 - tax rate) * D (D+E); Note that D/(D+E) = (D/E)/(1+D/E)
• Ke = Rf+Levered Beta*MRP; Levered Beta = Unlevered Beta x (1+D/E); MRP is generally 4-8%, Rf = 10-yr TSY

Equity Valuation

• Enterprise Value (EV) = NPV(FCFFs@WACC); Stock Valuation = (EV - Debt + Cash (if not in NWC)) / Shares
• Equity FCFFs include a terminal value in final year = growth perpetuity, or EBITDA x EBITDA multiple

Hedging

• VaR(X%) = V*Z(X)*; where Z(X)=1.645 for 95% and 2.32 for 99%
• Seller Forward Payoff = (F-St)*Q; Buyer Forward Payoff = (St-F)*Q
• Put Option Profit = -Cost*Q+Max(0,Strike-St)*Q; Call = -Cost*Q+Max(0,St-Strike)*Q

Investing

• Portfolio Expected Return = Sum (Asset Weights x Asset Mean Returns)
• Portfolio Value = Initial Portfolio Value x (1 + return) + contributions - withdrawals
• Portfolio Standard Deviation = (Wi*Wj*Covariance(I,j) --- Not on Final Exam!

LG Copy aste Good Format Painter BIU MA. Merge & Center Conditional Format as Bad Formatting * Table Clipboard Font Alignment Number Styles extBox 1 X V A B C D E F G H K L M 0. You are considering an investment in a rental condo in the Caribbean. You have estimated the IRR of unlevered and levered cash flows of 5% and 8% assuming the condo is offered for rent 100% of the time. To evaluate if these IRRs are sufficent for the risk of the investment, you compute the cost of equity capital and weighted average cost of capital. You will compare your unlevered IRR to the WACC to ensure there is sufficent return to payoff debt and return a reasonable return on equity. You will compare your levered IRR to the cost of equity. You will use Marriott's stock beta to measure the business risk. Marriott's levered stock beta is 1.68 (as of 11/2020). Marriot has a stock market cap of $39B and net debt of $10.7B. You will use your investment's cost of debt of 3% (loan APR) and personal tax rate of 27%. Your condo will be financed using $175k of personal equity and $270k of debt. Compute the Ke and WACC for the condo investment. Hint: Unlever the Marriott beta using Marriott's D/E and relever using the condo investment's D/E. The current long term Treasury rate is about 1% and you expect the stock market risk premium to be 4%. 5.00% Condo FCFF IRR 3.00% Condo Loan Rate 1.00% Long Term Treasury Rate 8.00% Condo FCFE IRR 27.00% Condo Rax Rate 4.00% Equity Market Risk Premium 1.68 Marriot Stock Beta $ 175.00 Condo Equity Investment ($k) S 39.00 Marriott Market Cap ($B) $ 270.00 Condo Debt ($k) 10.70 Marriott Net Debt ($B) ? Marriott Unlevered Beta ? Condo Levered Beta Condo Ke (FCFE IRR Hurdle Rate) ? Condo D/E ? Condo D%=D/(D+E) Condo WACC (FCFF IRR Hurdle Rate)