Hi, this assignment is Urgent!

The instruction is in the attachment ?final project guidelines and rubric?.

Using Home Depot Financial Report, (here?s the link)

http://www.sec.gov/Archives/edgar/data/354950/000035495015000008/hd-212015x10xk.htm

I need to calculate

Milestone 1: the time value of money,

Milestone 2: stock valuation and bond issuance,

Milestone 3: capital budgeting data and lastly

Milestone 4: interest rate implications.

Calculations must be done in the excel sheet (Final Project Student Workbook).

Additionally, can you please provide me explanations on how to get the calculations?

FIN 550 Final Project Guidelines and Rubric Overview The final project for this course is the creation of a financial analysis report. Financial analysis involves examining historical data to gain information about the current and future financial health of a company. Financial analysis can be applied in a wide variety of situations to give business managers the information they need to make critical decisions. The ability to understand financial data is essential for any business manager. For this summative assessment, you will provide a financial analysis report for Home Depot Inc. based on the data in the case study provided (see link in prompt). You will be asked to take the topics that you have covered throughout the course and display your mathematical and conceptual mastery of them. You will conduct background calculations and provide managerial analysis for the following topics: time value of money, stock valuation, bond valuation, and capital budgeting. The project is divided into four milestones, which will be submitted at various points throughout the course to scaffold learning and ensure quality final submissions. These milestones will be submitted in Modules Two, Four, Six, and Seven. The final submission will be in Module Nine. In this assignment, you will demonstrate your mastery of the following course outcomes: Predict the effects of the time value of money on potential investments for ensuring an effective portfolio balance between risk and return Assess the stock valuation process as a viable financing and investment option for maximizing shareholder value Assess the bond issuance process for its viability as a financing option for raising adequate capital Forecast the feasibility of corporate investment opportunities by utilizing capital budgeting estimates for ensuring effective decision making Analyze macroeconomic variables that impact corporate financial decision making for ensuring alignment with strategic objectives Prompt Using this case study, prepare a financial analysis report for Home Depot Inc. For your calculations, use the Final Project Student Workbook, which includes tabs specific to each milestone. Be sure to include in your analysis the background calculations and managerial analysis for each of the following topics: time value of money, stock and bond valuation, and capital budgeting. Also include a discussion of macroeconomic variables that might impact the company's financial decision making and strategic objectives. Note that while these elements may seem separate and unrelated, together they will present a well-rounded view of the company's finances with regard to the topics. Specifically, the following critical elements must be addressed: I. Time Value of Money A. Calculate the following time value of money figures: 1. Calculate the present value of the company based on the given interest rate and expected revenues over time. 2. Suppose the risk of the company changes based on an internal event. Recalculate the present value of the company. 3. Suppose that a potential buyer has offered to buy this company in five years. Based on the present value you calculated above, what would be a reasonable amount for which the company should be sold at that future time? B. What are the implications of the change in present value based on risk? In other words, what does the change mean to the company, and how would you, as a financial manager, interpret it? Be sure to justify your reasoning. C. Based on the future value of the company that you calculated, and being mindful of the need to effectively balance portfolio risk with return, what recommendation would you make about purchasing the company as an investment at that price? Be sure to substantiate your reasoning. II. Stock Valuation A. Based on the figures provided, calculate each of the following: 1. The new dividend yield if the company increased its dividend per share by 1.75 2. The dividend yield if the firm doubled its outstanding shares 3. The rate of return on equity (i.e., the cost of stock) based on the new dividend yield you calculated above B. What effect would you expect each of the calculations you performed to have in terms of shareholder value? In other words, suppose the company's goal is to maximize shareholder value. How will each of the situations support or inhibit that goal? Be sure to justify your reasoning. C. To what extent do you feel the company's dividend policies support or hinder their strategies? For example, if the company is attempting to grow, are they retaining and reinvesting their earnings rather than distributing them to investors through dividends? Be sure to substantiate your claims. III. Bond Issuance A. Assuming this company already has bonds outstanding, calculate the following: 1. The new value of the bond if overall rates in the market increased by 5% 2. The new value of the bond if overall rates in the market decreased by 5% 3. The value of the bond if overall rates in the market stayed exactly the same B. What effect would you expect each of the calculations you performed to have in terms of the company's decision to raise capital in this manner? In other words, for each situation, would you consider bond valuation to be a viable option for increasing capital? Be sure to justify your reasoning. C. To what extent do you feel the company's bond issuance policies support or hinder their strategies? For example, if the company is attempting to fund operating expenses, refinance old debt, or change its capital structure, are they issuing sufficient bonds to achieve these goals? Be sure to substantiate your claims. IV. Capital Budgeting Data A. Suppose the company is considering a potential investment project to add to its portfolio. Calculate the following items: 1. The net present value (NPV) of the project 2. The internal rate of return (IRR) of the project B. What are the implications of these calculations? In other words, based on each of the calculations, and being mindful of the need to balance portfolio risk with return, would you recommend that the company pursue the investment? Why or why not? Be sure to substantiate your claims. C. What is the difference between NPV and IRR? Which one would you choose for evaluating a potential investment and why? Be sure to support your reasoning with evidence. V. Macroeconomic Items: The CEO of the company is convinced that financial analysis should hinge only on what is happening internally within the company. Convince him otherwise based on the following: A. Analyze the implications of interest rate changes on any of the calculations you performed. Be sure to substantiate your claims. B. How might an issue (negative or positive) within the overall stock market impact the company's stock valuation numbers, other financial variables, or its overall portfolio management? Be sure your response is supported by evidence. C. Analyze the impact of any external factor (i.e., external to the company) discussed throughout the course on the company's financial position. Be sure to justify your reasoning. Milestones Milestone One: Time Value of Money (Section I) In Module Two, you will submit a draft of the Time Value of Money section of the final project, along with your supporting explanations. Submit your calculations on the designated tab of the Final Project Student Workbook and your supporting explanations as a Microsoft Word document. This milestone will be graded with the Milestone One Rubric. Milestone Two: Stock Valuation and Bond Issuance (Sections II and III) In Module Four, you will submit a draft of the Stock Valuation and Bond Issuance sections of the final project, along with your supporting explanations. Submit your calculations on the designated tab of the Final Project Student Workbook and your supporting explanations as a Microsoft Word document. This milestone will be graded with the Milestone Two Rubric. Milestone Three: Capital Budgeting Data (Section IV) In Module Six, you will submit a draft of the Capital Budgeting Data section of the final project, along with your supporting explanations. Submit your calculations on the designated tab of the Final Project Student Workbook and your supporting explanations as a Microsoft Word document. This milestone will be graded with the Milestone Three Rubric. Milestone Four: Macroeconomic Items (Section V) In Module Seven, you will submit a draft of the Macroeconomic Items section of the final project, along with your supporting explanations. Submit your calculations on the designated tab of the Final Project Student Workbook and your supporting explanations as a Microsoft Word document. This milestone will be graded with the Milestone Four Rubric. Final Project Submission: Financial Analysis Report In Module Nine, you will submit your financial analysis report along with your completed Final Project Student Workbook. It should be a complete, polished artifact containing all of the critical elements of the final project. It should reflect the incorporation of feedback gained throughout the course. This submission will be graded with the Final Project Rubric. Deliverables Milestone Deliverable Module Due Grading One Time Value of Money (Section I) Two Graded separately; Milestone One Rubric Two Stock Valuation and Bond Issuance (Sections II and III) Four Graded separately; Milestone Two Rubric Three Capital Budgeting Data (Section IV) Six Graded separately; Milestone Three Rubric Four Macroeconomic Items (Section V) Seven Graded separately; Milestone Four Rubric Final Project Submission: Financial Analysis Report Nine Graded separately; Final Project Rubric Final Project Rubric Guidelines for Submission: Your financial analysis report should be 7 to 12 pages, not including a title page and references page. It should use 12-point Times New Roman font, double spacing, and one-inch margins. All citations and references should be formatted according to APA style. Also submit your completed Final Project Student Workbook. Instructor Feedback: This activity uses an integrated rubric in Blackboard. Students can view instructor feedback in the Grade Center. For more information, review these instructions. Critical Elements Time Value of Money: Figures Exemplary Time Value of Money: Meets \"Proficient\" criteria and Implications demonstrates keen insight into the interrelationship between risk and present value (100%) Time Value of Money: Meets \"Proficient\" criteria and Future Value demonstrates keen insight into using time value of money for recommending investments (100%) Stock Valuation: Calculations Stock Valuation: Shareholder Value Stock Valuation: Dividend Policies Bond Issuance: Bonds Meets \"Proficient\" criteria and demonstrates keen insight into the effects of changing financial variables on shareholder value (100%) Meets \"Proficient\" criteria and demonstrates keen insight into the relationship between dividend policies and strategies for increasing shareholder value (100%) Proficient Accurately calculates requested figures (100%) Needs Improvement Calculates figures, but with gaps in accuracy or detail (70%) Not Evident Does not calculate figures (0%) Value 6.33 Analyzes implications of change in Analyzes implications of change in present value based on risk, present value based on risk, but justifying reasoning (90%) response or reasoning is cursory or illogical (70%) Makes recommendation about Makes recommendation about purchasing company at future purchasing company at future price, substantiating claims (90%) price, but response or substantiation is cursory or illogical (70%) Accurately calculates requested Calculates figures, but with gaps figures (100%) in accuracy or detail (70%) Does not analyze implications of change in present value based on risk (0%) 6.33 Does not make recommendation about purchasing company at future price (0%) 6.33 Does not calculate figures (0%) 6.33 Analyzes the effects of each calculation on shareholder value, justifying reasoning (90%) Analyzes the effects of each calculation on shareholder value, but response or reasoning is cursory or illogical (70%) Does not analyze the effects of each calculation on shareholder value (0%) 6.33 Assesses the extent to which dividend policies support or hinder company strategies, justifying reasoning (90%) Assesses the extent to which dividend policies support or hinder company strategies, but response or reasoning is cursory or illogical (70%) Does not assess the extent to which dividend policies support or hinder company strategies (0%) 6.33 Accurately calculates requested figures (100%) Calculates figures, but with gaps in accuracy or detail (70%) Does not calculate figures (0%) 6.33 Bond Issuance: Raising Meets \"Proficient\" criteria and Capital demonstrates keen insight into the effects of changing market conditions on decisions to raise capital (100%) Bond Issuance: Bond Meets \"Proficient\" criteria and Issuance Policies demonstrates keen insight into the relationship between bond issuance policies and strategies for raising capital (100%) Capital Budgeting Data: Potential Investment Analyzes the effects of each calculation on the company's decision to raise capital, justifying reasoning (90%) Analyzes the effects of each calculation on the company's decision to raise capital, but response or reasoning is cursory or illogical (70%) Assesses the extent to which Assesses the extent to which bond issuance policies support or bond issuance policies support or hinder company strategies, hinder company strategies, but justifying reasoning (90%) response or reasoning is cursory or illogical (70%) Accurately calculates requested Calculates figures, but with gaps figures (100%) in accuracy or detail (70%) Does not analyze the effects of each calculation on the company's decision to raise capital (0%) 6.33 Does not assess the extent to which bond issuance policies support or hinder company strategies (0%) 6.33 Does not calculate figures (0%) 6.33 Capital Budgeting Data: Meets \"Proficient\" criteria and Pursuing the demonstrates keen insight into Investment using NPV and IRR to judge potential investment opportunities (100%) Analyzes the implications of each calculation on the recommendation to pursue the investment, substantiating claims (90%) Does not analyze the implications of each calculation on the recommendation to pursue the investment (0%) 6.33 Capital Budgeting Data: Meets \"Proficient\" criteria and Difference demonstrates keen insight into using NPV and IRR to judge potential investment opportunities (100%) Accurately characterizes the difference between NPV and IRR and explains which would be chosen for evaluating a potential investment and why, supporting reasoning with evidence (90%) Does not characterize the difference between NPV and IRR and does not explain which would be chosen for evaluating a potential investment and why (0%) 6.33 Macroeconomic Items: Meets \"Proficient\" criteria and Implications demonstrates keen insight into the relationship between interest rate changes and financial variables in a company (100%) Macroeconomic Items: Meets \"Proficient\" criteria and Stock Market demonstrates keen insight into the relationship between stock market fluctuations and financial variables in a company (100%) Analyzes implications of interest rate changes, substantiating claims (90%) Analyzes the implications of each calculation on the recommendation to pursue the investment, but response or substantiation is cursory or illogical (70%) Characterizes the difference between NPV and IRR and explains which would be chosen for evaluating a potential investment and why, but response is cursory or inaccurate or evidence is not supportive (70%) Analyzes implications of interest rate changes, but response or substantiation is cursory or illogical (70%) Does not analyze implications of interest rate changes (0%) 6.33 Assesses the impact of an issue within the overall stock market on the company's stock valuation numbers or any other financial variable, but response is cursory, illogical, or weakly supported (70%) Does not assess the impact of an issue within the overall stock market on the company's stock valuation numbers or any other financial variable (0%) 6.33 Assesses the impact of an issue within the overall stock market on the company's stock valuation numbers or any other financial variable, supporting response with evidence (90%) Macroeconomic Items: Meets \"Proficient\" criteria and External Factor demonstrates keen insight into the relationship between external factors and a company's financial position (100%) Articulation of Submission is free of errors Response related to citations, grammar, spelling, syntax, and organization and is presented in a professional and easy to read format (100%) Analyzes the impact of a factor external to the company on the company's financial position, justifying reasoning (90%) Submission has no major errors related to citations, grammar, spelling, syntax, or organization (90%) Analyzes the impact of a factor external to the company on the company's financial position, but response is cursory, illogical, or weakly supported (70%) Submission has major errors related to citations, grammar, spelling, syntax, or organization that negatively impact readability and articulation of main ideas (70%) Does not analyze the impact of a factor external to the company on the company's financial position (0%) 6.33 Submission has critical errors related to citations, grammar, spelling, syntax, or organization that prevent understanding of ideas (0%) 5.05 Earned Total 100% Milestone One: Time Value of Money FCF1 FCF2 FCF3 Amounts Pv $0.00 Total Pv $0.00 $0.00 $0.00 Interest Rate FCF4 FCF5 $0.00 $0.00 Milestone Two: Stock Valuation and Bond Issuance Growth D1 = D2 = D3 = x x x = = = Sum 0 0 0 0 Milestone Three: Capital Budgeting Data Interest Rate Initial Outlay FCF1 FCF2 Amount NPV $0.00 IRR Err:523 FCF3 FCF4 FCF5 Milestone Four: Microeconomic Items Milestone One: Time Value of Money Interest Rate Year 1 Total Pv 4 5 7,930.12 8,692.57 9,528.31 10,444.41 $6,571.09 Pv 3 7,234.56 FCF Amounts 2 $6,542.30 $6,513.64 $6,485.10 10.10% $6,456.69 $32,568.81 Basis for FCF Projections In the table below, we estimate historical FCF as follows: FCF = Net Cash Provided by Operating Activities - Capital Expenditures - Payments for businesses acquired Historical FCF (Amounts in millions) Net Cash Provided by Operating Activities Capital Expenditures Payments for businesses acquired Free Cash Flow FCF growth rate Average FCF growth rate 2015 8242 -1442 -200 6600 9% 9.61% 2014 7628 -1389 -206 6033 10% 2013 6975 -1312 -170 5493 Notes Reference for the FCF fomula used is that used by Morningstar, as described on the following link: http://seekingalpha.com/article/1046901-calculating-free-cash-flow-identifying-methods-used-by-mainstream-sites The historical FCF is then used to project the FCF trajectory for the next five years. That is: FCF(t) = FCF(t-1) * (1 - g) where FCF(t) is the free cash flow at time t; and g is the free cash flow growth rate. Determining The Discount Rate USA treasury bond yield Beta Risk premium Discount rate 1.46% https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yield 0.96 http://finance.yahoo.com/q?s=HD&ql=1 9% 10.10% ... Risk free rate + beta *(risk premium). This is according to CAPM (capital asset pricing model). Note: Since we are discounting five-year cash-flows, we used the 5-year treasury bond yield as the proxy for the risk free rate of return. Inter-relationship Between Risk and Time Value 7% 8% 9% 10% 11% 12% 13% 14% 15% Discount Rate Present Value $32,568.81 8.2% 9.1% 10.1% 11.1% 12.0% 13.0% 13.9% 14.9% 15.9% Relationship Between Risk Premium and Present Value 12 10 Pre se nt value Risk perimum 8 6 4 2 7% 8% 9% 10% 11% 12% 13% 14% 15% Risk pre mium Comment There is an inverse relationship between risk premium and present value. Here, the risk premium compensates investors for taking on risk associated with the specific investment. The more risk a company, the more investors require to be compensated for investing in that company. In turn, a higher risk premium implies that the discount rate used is higher. This, in turn results in assigning less weight (ie, deeper discount) to the more risky future cash flows. How Can Time Value of Money Be Used for Recommending Investments? Firstly, we need to project FCF in perpetuity and then discount all the future FCFs to find an estimate of the value of the company. Thereafter, we compare the 'theoretical' price with the actual market value of the company. If the theoretical price is higher than the market price, this suggests that the current price of the company can be perceived as being cheap. Thus, it would be ideal to consider buying stock in the company. Conversly, if the theoretical price is lower than the market price, then this suggests that the company is undervalued. In such cases, we may consider selling the stock. Milestone Two: Stock Valuation and Bond Issuance Growth D1= D2 = D3 = x x x = = = Sum 0 0 0 0 Milestone Three: Capital Budgeting Data Interest Rate Initial Outlay FCF1 FCF2 Amount NPV $0.00 IRR Err:523 FCF3 FCF4 FCF5 Milestone Four: Microeconomic Items Milestone One: Time Value of Money Interest Rate Year 1 Total Pv 4 5 7,930.12 8,692.57 9,528.31 10,444.41 $6,571.09 Pv 3 7,234.56 FCF Amounts 2 $6,542.30 $6,513.64 $6,485.10 10.10% $6,456.69 $32,568.81 Basis for FCF Projections In the table below, we estimate historical FCF as follows: FCF = Net Cash Provided by Operating Activities - Capital Expenditures - Payments for businesses acquired Historical FCF (Amounts in millions) Net Cash Provided by Operating Activities Capital Expenditures Payments for businesses acquired Free Cash Flow FCF growth rate Average FCF growth rate 2015 8242 -1442 -200 6600 9% 9.61% 2014 7628 -1389 -206 6033 10% 2013 6975 -1312 -170 5493 Notes Reference for the FCF fomula used is that used by Morningstar, as described on the following link: http://seekingalpha.com/article/1046901-calculating-free-cash-flow-identifying-methods-used-by-mainstream-sites The historical FCF is then used to project the FCF trajectory for the next five years. That is: FCF(t) = FCF(t-1) * (1 - g) where FCF(t) is the free cash flow at time t; and g is the free cash flow growth rate. Determining The Discount Rate USA treasury bond yield Beta Risk premium Discount rate 1.46% https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yield 0.96 http://finance.yahoo.com/q?s=HD&ql=1 9% 10.10% ... Risk free rate + beta *(risk premium). This is according to CAPM (capital asset pricing model). Note: Since we are discounting five-year cash-flows, we used the 5-year treasury bond yield as the proxy for the risk free rate of return. Inter-relationship Between Risk and Time Value 7% 8% 9% 10% 11% 12% 13% 14% 15% Discount Rate Present Value $32,568.81 8.2% 9.1% 10.1% 11.1% 12.0% 13.0% 13.9% 14.9% 15.9% Relationship Between Risk Premium and Present Value 12 10 Pre se nt value Risk perimum 8 6 4 2 7% 8% 9% 10% 11% 12% 13% 14% 15% Risk pre mium Comment There is an inverse relationship between risk premium and present value. Here, the risk premium compensates investors for taking on risk associated with the specific investment. The more risk a company, the more investors require to be compensated for investing in that company. In turn, a higher risk premium implies that the discount rate used is higher. This, in turn results in assigning less weight (ie, deeper discount) to the more risky future cash flows. How Can Time Value of Money Be Used for Recommending Investments? Firstly, we need to project FCF in perpetuity and then discount all the future FCFs to find an estimate of the value of the company. Thereafter, we compare the 'theoretical' price with the actual market value of the company. If the theoretical price is higher than the market price, this suggests that the current price of the company can be perceived as being cheap. Thus, it would be ideal to consider buying stock in the company. Conversly, if the theoretical price is lower than the market price, then this suggests that the company is undervalued. In such cases, we may consider selling the stock. To illustrate this concept, suppose we assume that Home Depot's FCF grows by 5% per year from year 6 onwards. The value of the firm is thus equal to the present value of the first five-years FCFs and the present value of the FCFs from year 6 onwards. That is: Present value of FCF from first 5 years Present value of FCFs from year 6 onwards Present value of firm $32,568.81 $133,014.46 *** $165,583.27 ... million ***: The formula used is FCF(6)/(i-g) * (1 + i)^(-5). Where i=interest, g=growth rate from year 6 onwards = 0.05. The term FCF(6)/(i-g) is the present value of all FCF from year 6 onwards as at the end of year 5. This PV is discounted back to now by discounting by the term (1+i)^(-5). Looking at yahoo finance, the market value of Home depot as at 19 January 2016 was about $151 billion. This suggests that Home depot seems cheap relative to its perceived intrinsic value of about $165.5 billion. Milestone Two: Stock Valuation and Bond Issuance Growth D1= D2 = D3 = x x x = = = Sum 0 0 0 0 Milestone Three: Capital Budgeting Data Interest Rate Initial Outlay FCF1 FCF2 Amount NPV $0.00 IRR Err:523 FCF3 FCF4 FCF5 Milestone Four: Microeconomic Items Milestone One: Time Value of Money Interest Rate Year 1 Total Pv 4 5 7,930.12 8,692.57 9,528.31 10,444.41 $6,571.09 Pv 3 7,234.56 FCF Amounts 2 $6,542.30 $6,513.64 $6,485.10 10.10% $6,456.69 $32,568.81 Basis for FCF Projections In the table below, we estimate historical FCF as follows: FCF = Net Cash Provided by Operating Activities - Capital Expenditures - Payments for businesses acquired Historical FCF (Amounts in millions) Net Cash Provided by Operating Activities Capital Expenditures Payments for businesses acquired Free Cash Flow FCF growth rate Average FCF growth rate 2015 8242 -1442 -200 6600 9% 9.61% 2014 7628 -1389 -206 6033 10% 2013 6975 -1312 -170 5493 Notes Reference for the FCF fomula used is that used by Morningstar, as described on the following link: http://seekingalpha.com/article/1046901-calculating-free-cash-flow-identifying-methods-used-by-mainstream-sites The historical FCF is then used to project the FCF trajectory for the next five years. That is: FCF(t) = FCF(t-1) * (1 - g) where FCF(t) is the free cash flow at time t; and g is the free cash flow growth rate. Determining The Discount Rate USA treasury bond yield Beta Risk premium Discount rate 1.46% https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yield 0.96 http://finance.yahoo.com/q?s=HD&ql=1 9% 10.10% ... Risk free rate + beta *(risk premium). This is according to CAPM (capital asset pricing model). Note: Since we are discounting five-year cash-flows, we used the 5-year treasury bond yield as the proxy for the risk free rate of return. Inter-relationship Between Risk and Time Value 7% 8% 9% 10% 11% 12% 13% 14% 15% Discount Rate Present Value $32,568.81 8.2% 9.1% 10.1% 11.1% 12.0% 13.0% 13.9% 14.9% 15.9% Relationship Between Risk Premium and Present Value 12 10 Pre se nt value Risk perimum 8 6 4 2 7% 8% 9% 10% 11% 12% 13% 14% 15% Risk pre mium Comment There is an inverse relationship between risk premium and present value. Here, the risk premium compensates investors for taking on risk associated with the specific investment. The more risk a company, the more investors require to be compensated for investing in that company. In turn, a higher risk premium implies that the discount rate used is higher. This, in turn results in assigning less weight (ie, deeper discount) to the more risky future cash flows. How Can Time Value of Money Be Used for Recommending Investments? Firstly, we need to project FCF in perpetuity and then discount all the future FCFs to find an estimate of the value of the company. Thereafter, we compare the 'theoretical' price with the actual market value of the company. If the theoretical price is higher than the market price, this suggests that the current price of the company can be perceived as being cheap. Thus, it would be ideal to consider buying stock in the company. Conversly, if the theoretical price is lower than the market price, then this suggests that the company is undervalued. In such cases, we may consider selling the stock. To illustrate this concept, suppose we assume that Home Depot's FCF grows by 5% per year from year 6 onwards. The value of the firm is thus equal to the present value of the first five-years FCFs and the present value of the FCFs from year 6 onwards. That is: Present value of FCF from first 5 years Present value of FCFs from year 6 onwards Present value of firm $32,568.81 $133,014.46 *** $165,583.27 ... million ***: The formula used is FCF(6)/(i-g) * (1 + i)^(-5). Where i=interest, g=growth rate from year 6 onwards = 0.05. The term FCF(6)/(i-g) is the present value of all FCF from year 6 onwards as at the end of year 5. This PV is discounted back to now by discounting by the term (1+i)^(-5). Looking at yahoo finance, the market value of Home depot as at 19 January 2016 was about $151 billion. This suggests that Home depot seems cheap relative to its perceived intrinsic value of about $165.5 billion. Milestone Two: Stock Valuation and Bond Issuance Growth D1= D2 = D3 = x x x = = = Sum 0 0 0 0 A. Dividend Yield A.1. The New Dividend Yield if the Company Increased its Dividend Per Share by 1.75 Current Dividend Yield Annual dividend Stock price Dividend yield 2.36 ...yahoo finance 119.76 ...yahoo finance 1.97% ...Dividend yield = (annual dividend) / (stock price) New Dividend Yield Annual dividend Stock price Dividend yield 4.11 ... Current annual dividend + 1.75 119.76 ...yahoo finance 3.43% ...Dividend yield = (annual dividend) / (stock price) A.2. The Dividend Yield if Firm Doubled its Outstanding Shares Annual dividend Stock price Dividend yield 1.18 ... Stock price will likely reduce by half, all else held constant, as a result of the increased supply of shares. 59.88 ... Annual dividend reduces by half since the dividend amount has to be shared across double the amount of shares. 1.97% ...Dividend yield will almost certaintly remain unchanged as a result of doubling the amount of shares. A.3. The Rate of Return on Equity Rate of return on equity = (capital gain + dividend) / (initial investment) In what follows, I assume that the Home Depot stock was bought exactly one year ago. Scenario Div increase by 1.75 Outstanding shares double Dividend Initial investment Current Price 4.11 104.42 119.76 2.36 104.42 119.76 Return 18.63% 16.95% Notes ....Data on initial investment is extracted from yahoo finance. ...Tax on dividends are ignored in computing the return. ...We assumed that the initial investment was at the closing price of 20 January 2015. B. Effect of Increasing Dividend on Shareholder Value As shown in A.3., increasing the dividend results in an increase in the return on equity, implying an increase in shareholder value. In contrast, increasing the outstanding share will almost certainly not result in an increase in shareholder value as it will not increase the total dividend received by an equity investor nor increase the capital gains. Hence, if the company's goal is to maximise shareholder value, they should implement strategies that increase the amount of dividends (or net income) of the firm. C. Dividend Policy Paying out dividends may result in the company having less capital to finance investments that provide future growth of the company. In such cases, an aggressive dividend policy may be harmful to the firm. Recall that funding the new investments is expected to lead to higher future dividends. If however, there are few growth opportunities available to the firm, it may be worthwhile to pay-out most of the returns as dividend. This is normally the case with large companies in mature indutries. Milestone Three: Capital Budgeting Data Interest Rate Initial Outlay FCF1 FCF2 Amount NPV $0.00 IRR Err:523 FCF3 FCF4 FCF5 Milestone Four: Microeconomic Items Milestone One: Time Value of Money Interest Rate Year 1 Total Pv 4 5 7,930.12 8,692.57 9,528.31 10,444.41 $6,571.09 Pv 3 7,234.56 FCF Amounts 2 $6,542.30 $6,513.64 $6,485.10 10.10% $6,456.69 $32,568.81 Basis for FCF Projections In the table below, we estimate historical FCF as follows: FCF = Net Cash Provided by Operating Activities - Capital Expenditures - Payments for businesses acquired Historical FCF (Amounts in millions) Net Cash Provided by Operating Activities Capital Expenditures Payments for businesses acquired Free Cash Flow FCF growth rate Average FCF growth rate 2015 8242 -1442 -200 6600 9% 9.61% 2014 7628 -1389 -206 6033 10% 2013 6975 -1312 -170 5493 Notes Reference for the FCF fomula used is that used by Morningstar, as described on the following link: http://seekingalpha.com/article/1046901-calculating-free-cash-flow-identifying-methods-used-by-mainstream-sites The historical FCF is then used to project the FCF trajectory for the next five years. That is: FCF(t) = FCF(t-1) * (1 - g) where FCF(t) is the free cash flow at time t; and g is the free cash flow growth rate. Determining The Discount Rate USA treasury bond yield Beta Risk premium Discount rate 1.46% https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yield 0.96 http://finance.yahoo.com/q?s=HD&ql=1 9% 10.10% ... Risk free rate + beta *(risk premium). This is according to CAPM (capital asset pricing model). Note: Since we are discounting five-year cash-flows, we used the 5-year treasury bond yield as the proxy for the risk free rate of return. Inter-relationship Between Risk and Time Value 7% 8% 9% 10% 11% 12% 13% 14% 15% Discount Rate Present Value $32,568.81 8.2% 9.1% 10.1% 11.1% 12.0% 13.0% 13.9% 14.9% 15.9% Relationship Between Risk Premium and Present Value 12 10 Pre se nt value Risk perimum 8 6 4 2 7% 8% 9% 10% 11% 12% 13% 14% 15% Risk pre mium Comment There is an inverse relationship between risk premium and present value. Here, the risk premium compensates investors for taking on risk associated with the specific investment. The more risk a company, the more investors require to be compensated for investing in that company. In turn, a higher risk premium implies that the discount rate used is higher. This, in turn results in assigning less weight (ie, deeper discount) to the more risky future cash flows. How Can Time Value of Money Be Used for Recommending Investments? Firstly, we need to project FCF in perpetuity and then discount all the future FCFs to find an estimate of the value of the company. Thereafter, we compare the 'theoretical' price with the actual market value of the company. If the theoretical price is higher than the market price, this suggests that the current price of the company can be perceived as being cheap. Thus, it would be ideal to consider buying stock in the company. Conversly, if the theoretical price is lower than the market price, then this suggests that the company is undervalued. In such cases, we may consider selling the stock. To illustrate this concept, suppose we assume that Home Depot's FCF grows by 5% per year from year 6 onwards. The value of the firm is thus equal to the present value of the first five-years FCFs and the present value of the FCFs from year 6 onwards. That is: Present value of FCF from first 5 years Present value of FCFs from year 6 onwards Present value of firm $32,568.81 $133,014.46 *** $165,583.27 ... million ***: The formula used is FCF(6)/(i-g) * (1 + i)^(-5). Where i=interest, g=growth rate from year 6 onwards = 0.05. The term FCF(6)/(i-g) is the present value of all FCF from year 6 onwards as at the end of year 5. This PV is discounted back to now by discounting by the term (1+i)^(-5). Looking at yahoo finance, the market value of Home depot as at 19 January 2016 was about $151 billion. This suggests that Home depot seems cheap relative to its perceived intrinsic value of about $165.5 billion. Milestone Two: Stock Valuation and Bond Issuance Growth D1= D2 = D3 = x x x = = = Sum 0 0 0 0 A. Dividend Yield A.1. The New Dividend Yield if the Company Increased its Dividend Per Share by 1.75 Current Dividend Yield Annual dividend Stock price Dividend yield 2.36 ...yahoo finance 119.76 ...yahoo finance 1.97% ...Dividend yield = (annual dividend) / (stock price) New Dividend Yield Annual dividend Stock price Dividend yield 4.11 ... Current annual dividend + 1.75 119.76 ...yahoo finance 3.43% ...Dividend yield = (annual dividend) / (stock price) A.2. The Dividend Yield if Firm Doubled its Outstanding Shares Annual dividend Stock price Dividend yield 1.18 ... Stock price will likely reduce by half, all else held constant, as a result of the increased supply of shares. 59.88 ... Annual dividend reduces by half since the dividend amount has to be shared across double the amount of shares. 1.97% ...Dividend yield will almost certaintly remain unchanged as a result of doubling the amount of shares. A.3. The Rate of Return on Equity Rate of return on equity = (capital gain + dividend) / (initial investment) In what follows, I assume that the Home Depot stock was bought exactly one year ago. Scenario Div increase by 1.75 Outstanding shares double Dividend Initial investment Current Price 4.11 104.42 119.76 2.36 104.42 119.76 Return 18.63% 16.95% Notes ....Data on initial investment is extracted from yahoo finance. ...Tax on dividends are ignored in computing the return. ...We assumed that the initial investment was at the closing price of 20 January 2015. B. Effect of Increasing Dividend on Shareholder Value As shown in A.3., increasing the dividend results in an increase in the return on equity, implying an increase in shareholder value. In contrast, increasing the outstanding share will almost certainly not result in an increase in shareholder value as it will not increase the total dividend received by an equity investor nor increase the capital gains. Hence, if the company's goal is to maximise shareholder value, they should implement strategies that increase the amount of dividends (or net income) of the firm. C. Dividend Policy Paying out dividends may result in the company having less capital to finance investments that provide future growth of the company. In such cases, an aggressive dividend policy may be harmful to the firm. Recall that funding the new investments is expected to lead to higher future dividends. If however, there are few growth opportunities available to the firm, it may be worthwhile to pay-out most of the returns as dividend. This is normally the case with large companies in mature indutries. Milestone Three: Capital Budgeting Data Interest Rate Initial Outlay FCF1 FCF2 Amount NPV $0.00 IRR Err:523 FCF3 FCF4 FCF5 Milestone Four: Microeconomic Items Milestone One: Time Value of Money Interest Rate Year 1 Total Pv 4 5 7,930.12 8,692.57 9,528.31 10,444.41 $6,571.09 Pv 3 7,234.56 FCF Amounts 2 $6,542.30 $6,513.64 $6,485.10 10.10% $6,456.69 $32,568.81 Basis for FCF Projections In the table below, we estimate historical FCF as follows: FCF = Net Cash Provided by Operating Activities - Capital Expenditures - Payments for businesses acquired Historical FCF (Amounts in millions) Net Cash Provided by Operating Activities Capital Expenditures Payments for businesses acquired Free Cash Flow FCF growth rate Average FCF growth rate 2015 8242 -1442 -200 6600 9% 9.61% 2014 7628 -1389 -206 6033 10% 2013 6975 -1312 -170 5493 Notes Reference for the FCF fomula used is that used by Morningstar, as described on the following link: http://seekingalpha.com/article/1046901-calculating-free-cash-flow-identifying-methods-used-by-mainstream-sites The historical FCF is then used to project the FCF trajectory for the next five years. That is: FCF(t) = FCF(t-1) * (1 - g) where FCF(t) is the free cash flow at time t; and g is the free cash flow growth rate. Determining The Discount Rate USA treasury bond yield Beta Risk premium Discount rate 1.46% https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yield 0.96 http://finance.yahoo.com/q?s=HD&ql=1 9% 10.10% ... Risk free rate + beta *(risk premium). This is according to CAPM (capital asset pricing model). Note: Since we are discounting five-year cash-flows, we used the 5-year treasury bond yield as the proxy for the risk free rate of return. Inter-relationship Between Risk and Time Value 7% 8% 9% 10% 11% 12% 13% 14% 15% Discount Rate Present Value $32,568.81 8.2% 9.1% 10.1% 11.1% 12.0% 13.0% 13.9% 14.9% 15.9% Relationship Between Risk Premium and Present Value 12 10 Pre se nt value Risk perimum 8 6 4 2 7% 8% 9% 10% 11% 12% 13% 14% 15% Risk pre mium Comment There is an inverse relationship between risk premium and present value. Here, the risk premium compensates investors for taking on risk associated with the specific investment. The more risk a company, the more investors require to be compensated for investing in that company. In turn, a higher risk premium implies that the discount rate used is higher. This, in turn results in assigning less weight (ie, deeper discount) to the more risky future cash flows. How Can Time Value of Money Be Used for Recommending Investments? Firstly, we need to project FCF in perpetuity and then discount all the future FCFs to find an estimate of the value of the company. Thereafter, we compare the 'theoretical' price with the actual market value of the company. If the theoretical price is higher than the market price, this suggests that the current price of the company can be perceived as being cheap. Thus, it would be ideal to consider buying stock in the company. Conversly, if the theoretical price is lower than the market price, then this suggests that the company is undervalued. In such cases, we may consider selling the stock. To illustrate this concept, suppose we assume that Home Depot's FCF grows by 5% per year from year 6 onwards. The value of the firm is thus equal to the present value of the first five-years FCFs and the present value of the FCFs from year 6 onwards. That is: Present value of FCF from first 5 years Present value of FCFs from year 6 onwards Present value of firm $32,568.81 $133,014.46 *** $165,583.27 ... million ***: The formula used is FCF(6)/(i-g) * (1 + i)^(-5). Where i=interest, g=growth rate from year 6 onwards = 0.05. The term FCF(6)/(i-g) is the present value of all FCF from year 6 onwards as at the end of year 5. This PV is discounted back to now by discounting by the term (1+i)^(-5). Looking at yahoo finance, the market value of Home depot as at 19 January 2016 was about $151 billion. This suggests that Home depot seems cheap relative to its perceived intrinsic value of about $165.5 billion. Milestone Two: Stock Valuation and Bond Issuance Growth D1= D2 = D3 = x x x = = = Sum 0 0 0 0 A. Dividend Yield A.1. The New Dividend Yield if the Company Increased its Dividend Per Share by 1.75 Current Dividend Yield Annual dividend Stock price Dividend yield 2.36 ...yahoo finance 119.76 ...yahoo finance 1.97% ...Dividend yield = (annual dividend) / (stock price) New Dividend Yield Annual dividend Stock price Dividend yield 4.11 ... Current annual dividend + 1.75 119.76 ...yahoo finance 3.43% ...Dividend yield = (annual dividend) / (stock price) A.2. The Dividend Yield if Firm Doubled its Outstanding Shares Annual dividend Stock price Dividend yield 1.18 ... Stock price will likely reduce by half, all else held constant, as a result of the increased supply of shares. 59.88 ... Annual dividend reduces by half since the dividend amount has to be shared across double the amount of shares. 1.97% ...Dividend yield will almost certaintly remain unchanged as a result of doubling the amount of shares. A.3. The Rate of Return on Equity Rate of return on equity = (capital gain + dividend) / (initial investment) In what follows, I assume that the Home Depot stock was bought exactly one year ago. Scenario Div increase by 1.75 Outstanding shares double Dividend Initial investment Current Price 4.11 104.42 119.76 2.36 104.42 119.76 Notes ....Data on initial investment is extracted from yahoo finance. ...Tax on dividends are ignored in computing the return. ...We assumed that the initial investment was at the closing price of 20 January 2015. B. Effect of Increasing Dividend on Shareholder Value As shown in A.3., increasing the dividend results in an increase in the return on equity, implying an increase in shareholder value. In contrast, increasing the outstanding share will almost certainly not result in an increase in shareholder value as it will not increase the total dividend received by an equity investor nor increase the capital gains. Hence, if the company's goal is to maximise shareholder value, they should implement strategies that increase the amount of dividends (or net income) of the firm. C. Dividend Policy Paying out dividends may result in the company having less capital to finance investments that provide future growth of the company. In such cases, an aggressive dividend policy may be harmful to the firm. Recall that funding the new investments is expected to lead to higher future dividends. If however, there are few growth opportunities available to the firm, it may be worthwhile to pay-out Return 18.63% 16.95% most of the returns as dividend. This is normally the case with large companies in mature indutries. Bond Issuance A In what follows, we use the bond called Hm Depot 5.4% | Maturity: 2040. This is a conventional bond issued by Home Depot with the following key properties: Hm Depot 5.4% Bond Properties Amount outstanding Coupon rate Coupon frequency Current yield Coupon amount Effective annual yield 500,000,000 ...Face value 5.40% per year 2 per year 4.62% per year 13,500,000.00 ...Amount outstanding * coupon rate / coupon frequency. 4.67% ... Here, we convert the current yield to an effective annual yield, which is then used for discounting cahsflows. Source of information: http://quicktake.morningstar.com/StockNet/Bondsquote.aspx?cid=0C00000BB5&bid=d6a353d8bc64d2ef546a00533e6a9b7b&bname=Hm+Depot+5.4%25+%7c+Maturity%3a2040&ticker=HD&country=USA&clientid=dotcom Below, we value the bond issue by discounting all the future bond payments: Present Value of Hm Depot 5.4% Bond Date 1/20/2016 6:26 15-Mar-16 15-Sep-16 15-Mar-17 15-Sep-17 15-Mar-18 15-Sep-18 15-Mar-19 15-Sep-19 15-Mar-20 15-Sep-20 15-Mar-21 15-Sep-21 15-Mar-22 15-Sep-22 15-Mar-23 15-Sep-23 15-Mar-24 15-Sep-24 15-Mar-25 15-Sep-25 15-Mar-26 15-Sep-26 15-Mar-27 15-Sep-27 15-Mar-28 15-Sep-28 15-Mar-29 15-Sep-29 15-Mar-30 15-Sep-30 15-Mar-31 15-Sep-31 15-Mar-32 15-Sep-32 15-Mar-33 Time to Payment 0.15 0.65 1.15 1.65 2.15 2.65 3.15 3.65 4.15 4.66 5.15 5.66 6.15 6.66 7.15 7.66 8.16 8.66 9.16 9.66 10.16 10.66 11.16 11.66 12.16 12.66 13.16 13.66 14.16 14.66 15.16 15.66 16.16 16.67 17.16 Payment 0 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 Discount Factor 1 0.9931745377 0.970568012 0.9488321844 0.9272349743 0.9064695882 0.8858366307 0.8659983586 0.8462866026 0.8272305293 0.8084012022 0.7902970932 0.7723084408 0.7550126275 0.7378271161 0.7213035106 0.7048852822 0.6890131822 0.6733299426 0.6582507484 0.6432677198 0.6288617678 0.6145476879 0.6007849197 0.5871099218 0.5738898026 0.5608269883 0.5482672928 0.5357876951 0.5237887535 0.5118663335 0.5004031097 0.4890129911 0.4780017481 0.4671215268 0.4566603609 Present Value 13,407,856.26 13,102,668.16 12,809,234.49 12,517,672.15 12,237,339.44 11,958,794.51 11,690,977.84 11,424,869.14 11,167,612.15 10,913,416.23 10,669,010.76 10,426,163.95 10,192,670.47 9,960,666.07 9,737,597.39 9,515,951.31 9,301,677.96 9,089,954.22 8,886,385.10 8,684,114.22 8,489,633.87 8,296,393.79 8,110,596.42 7,925,983.94 7,747,512.34 7,571,164.34 7,401,608.45 7,233,133.88 7,071,148.17 6,910,195.50 6,755,441.98 6,601,675.38 6,453,023.60 6,306,140.61 6,164,914.87 15-Sep-33 15-Mar-34 15-Sep-34 15-Mar-35 15-Sep-35 15-Mar-36 15-Sep-36 15-Mar-37 15-Sep-37 15-Mar-38 15-Sep-38 15-Mar-39 15-Sep-39 15-Mar-40 15-Sep-40 Value of bond issue 17.67 18.16 18.67 19.16 19.67 20.16 20.67 21.16 21.67 22.16 22.67 23.16 23.67 24.17 24.67 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 513,500,000.00 0.4462659098 6,024,589.78 0.4362718045 5,889,669.36 0.4263414354 5,755,609.38 0.4167935379 5,626,712.76 0.4073065309 5,498,638.17 0.3981350952 5,374,823.79 0.3890727895 5,252,482.66 0.3803595217 5,134,853.54 0.3717018216 5,017,974.59 0.3633775758 4,905,597.27 0.3551064168 4,793,936.63 0.3471538243 4,686,576.63 0.3392519485 4,579,901.30 0.331612917 4,476,774.38 0.3240647816 166,407,265.34 566,158,604.54 Valuation of Bond Under Different Changes in Market Rates Since yields are quoted on a simple interest basis, we apply the change in market rates to the current yield. Using excel's data table, we establish the sensitivity of the bond value to different market rate changes below. Scenario Description Market rates stayed exactly the same Overall rates in the market increased by 5% Overall rates in the market decreased by 5% Market rate change 0 5% -5% New Yield Value of Bond 566,158,604.54 Err:522 Err:522 Err:522 Err:522 0 1,175,000,000.00 Note: yields have a lower bound of 0%. This explains why the scenario where market rates decrease by 5% is truncated to 0%. B From the table above, it can be seen that when yields increase, the issuer of the bond receives less money in exchange for paying the promised coupons and redemption value in future. In contrast, the issuer receives more money at inception if yield decrease. Thus, from the issuer's point of view, the propensity to issue additional bonds increases as the market yields descrease. This explains why most governments and monetary authorities reduce interest rates as a way of stimulating the economy. For the instance where the yield is 0%, the company can borrow at no cost. This is almost certainly a scenario where issuing bonds would be ideal. The actual financing decision should nonetheless take into account the cost of raising other forms of finance. For example it could be that although market yields increase by 5%, bonds may still remain cheap relative to other forms of finance, eg cost of equity. In such a case, issuing bonds is relatively the best alternative. Other costs to issuing bonds need to also be considered. For example, bond issuers often pay to obtain a credit rating of their issue. Furthermore, there may be other underwriting fees to pay the bond underwriter. All these costs may make a bond issue relatively more expensive. There is also need to consider the tax advantages for issuing debt. This stems from the fact that interest payments on bonds are contribute to reducing the taxable income. C According to Blooberg, Home Depot has issued bonds over the past two years as a way of refinancing maturing bonds (rolling over) and to also finance share buy-backs. The startegy of borrowing to finance share buy-backs seems to work effectively in altering the capital structure of the company. This is so since it increases debt while reducing equity (increased gearing). References Bloomberg: http://www.bloomberg.comews/articles/2014-06-09/home-depot-plans-2-billion-debt-sale-to-buy-back-shares Milestone Three: Capital Budgeting Data Interest Rate Initial Outlay FCF1 FCF2 Amount NPV $0.00 IRR Err:523 FCF3 FCF4 FCF5 Milestone Four: Microeconomic Items Milestone One: Time Value of Money Interest Rate Year 1 Total Pv 4 5 7,930.12 8,692.57 9,528.31 10,444.41 $6,571.09 Pv 3 7,234.56 FCF Amounts 2 $6,542.30 $6,513.64 $6,485.10 10.10% $6,456.69 $32,568.81 Basis for FCF Projections In the table below, we estimate historical FCF as follows: FCF = Net Cash Provided by Operating Activities - Capital Expenditures - Payments for businesses acquired Historical FCF (Amounts in millions) Net Cash Provided by Operating Activities Capital Expenditures Payments for businesses acquired Free Cash Flow FCF growth rate Average FCF growth rate 2015 8242 -1442 -200 6600 9% 9.61% 2014 7628 -1389 -206 6033 10% 2013 6975 -1312 -170 5493 Notes Reference for the FCF fomula used is that used by Morningstar, as described on the following link: http://seekingalpha.com/article/1046901-calculating-free-cash-flow-identifying-methods-used-by-mainstream-sites The historical FCF is then used to project the FCF trajectory for the next five years. That is: FCF(t) = FCF(t-1) * (1 - g) where FCF(t) is the free cash flow at time t; and g is the free cash flow growth rate. Determining The Discount Rate USA treasury bond yield Beta Risk premium Discount rate 1.46% https://www.treasury.gov/resource-center/data-chart-center/interest-rates/Pages/TextView.aspx?data=yield 0.96 http://finance.yahoo.com/q?s=HD&ql=1 9% 10.10% ... Risk free rate + beta *(risk premium). This is according to CAPM (capital asset pricing model). Note: Since we are discounting five-year cash-flows, we used the 5-year treasury bond yield as the proxy for the risk free rate of return. Inter-relationship Between Risk and Time Value 7% 8% 9% 10% 11% 12% 13% 14% 15% Discount Rate Present Value $32,568.81 8.2% 9.1% 10.1% 11.1% 12.0% 13.0% 13.9% 14.9% 15.9% Relationship Between Risk Premium and Present Value 12 10 Pre se nt value Risk perimum 8 6 4 2 7% 8% 9% 10% 11% 12% 13% 14% 15% Risk pre mium Comment There is an inverse relationship between risk premium and present value. Here, the risk premium compensates investors for taking on risk associated with the specific investment. The more risk a company, the more investors require to be compensated for investing in that company. In turn, a higher risk premium implies that the discount rate used is higher. This, in turn results in assigning less weight (ie, deeper discount) to the more risky future cash flows. How Can Time Value of Money Be Used for Recommending Investments? Firstly, we need to project FCF in perpetuity and then discount all the future FCFs to find an estimate of the value of the company. Thereafter, we compare the 'theoretical' price with the actual market value of the company. If the theoretical price is higher than the market price, this suggests that the current price of the company can be perceived as being cheap. Thus, it would be ideal to consider buying stock in the company. Conversly, if the theoretical price is lower than the market price, then this suggests that the company is undervalued. In such cases, we may consider selling the stock. To illustrate this concept, suppose we assume that Home Depot's FCF grows by 5% per year from year 6 onwards. The value of the firm is thus equal to the present value of the first five-years FCFs and the present value of the FCFs from year 6 onwards. That is: Present value of FCF from first 5 years Present value of FCFs from year 6 onwards Present value of firm $32,568.81 $133,014.46 *** $165,583.27 ... million ***: The formula used is FCF(6)/(i-g) * (1 + i)^(-5). Where i=interest, g=growth rate from year 6 onwards = 0.05. The term FCF(6)/(i-g) is the present value of all FCF from year 6 onwards as at the end of year 5. This PV is discounted back to now by discounting by the term (1+i)^(-5). Looking at yahoo finance, the market value of Home depot as at 19 January 2016 was about $151 billion. This suggests that Home depot seems cheap relative to its perceived intrinsic value of about $165.5 billion. Milestone Two: Stock Valuation and Bond Issuance Growth D1= D2 = D3 = x x x = = = Sum 0 0 0 0 A. Dividend Yield A.1. The New Dividend Yield if the Company Increased its Dividend Per Share by 1.75 Current Dividend Yield Annual dividend Stock price Dividend yield 2.36 ...yahoo finance 119.76 ...yahoo finance 1.97% ...Dividend yield = (annual dividend) / (stock price) New Dividend Yield Annual dividend Stock price Dividend yield 4.11 ... Current annual dividend + 1.75 119.76 ...yahoo finance 3.43% ...Dividend yield = (annual dividend) / (stock price) A.2. The Dividend Yield if Firm Doubled its Outstanding Shares Annual dividend Stock price Dividend yield 1.18 ... Stock price will likely reduce by half, all else held constant, as a result of the increased supply of shares. 59.88 ... Annual dividend reduces by half since the dividend amount has to be shared across double the amount of shares. 1.97% ...Dividend yield will almost certaintly remain unchanged as a result of doubling the amount of shares. A.3. The Rate of Return on Equity Rate of return on equity = (capital gain + dividend) / (initial investment) In what follows, I assume that the Home Depot stock was bought exactly one year ago. Scenario Div increase by 1.75 Outstanding shares double Dividend Initial investment Current Price 4.11 104.42 119.76 2.36 104.42 119.76 Notes ....Data on initial investment is extracted from yahoo finance. ...Tax on dividends are ignored in computing the return. ...We assumed that the initial investment was at the closing price of 20 January 2015. B. Effect of Increasing Dividend on Shareholder Value As shown in A.3., increasing the dividend results in an increase in the return on equity, implying an increase in shareholder value. In contrast, increasing the outstanding share will almost certainly not result in an increase in shareholder value as it will not increase the total dividend received by an equity investor nor increase the capital gains. Hence, if the company's goal is to maximise shareholder value, they should implement strategies that increase the amount of dividends (or net income) of the firm. C. Dividend Policy Paying out dividends may result in the company having less capital to finance investments that provide future growth of the company. In such cases, an aggressive dividend policy may be harmful to the firm. Recall that funding the new investments is expected to lead to higher future dividends. If however, there are few growth opportunities available to the firm, it may be worthwhile to pay-out most of the returns as dividend. This is normally the case with large companies in mature indutries. Return 18.63% 16.95% Bond Issuance A In what follows, we use the bond called Hm Depot 5.4% | Maturity: 2040. This is a conventional bond issued by Home Depot with the following key properties: Hm Depot 5.4% Bond Properties Amount outstanding Coupon rate Coupon frequency Current yield Coupon amount Effective annual yield 500,000,000 ...Face value 5.40% per year 2 per year 4.62% per year 13,500,000.00 ...Amount outstanding * coupon rate / coupon frequency. 4.67% ... Here, we convert the current yield to an effective annual yield, which is then used for discounting cahsflows. Source of information: http://quicktake.morningstar.com/StockNet/Bondsquote.aspx?cid=0C00000BB5&bid=d6a353d8bc64d2ef546a00533e6a9b7b&bname=Hm+Depot+5.4%25+%7c+Maturity%3a2040&ticker=HD&country=USA&clientid=dotcom Below, we value the bond issue by discounting all the future bond payments: Present Value of Hm Depot 5.4% Bond Date 1/22/2016 21:38 15-Mar-16 15-Sep-16 15-Mar-17 15-Sep-17 15-Mar-18 15-Sep-18 15-Mar-19 15-Sep-19 15-Mar-20 15-Sep-20 15-Mar-21 15-Sep-21 15-Mar-22 15-Sep-22 15-Mar-23 15-Sep-23 15-Mar-24 15-Sep-24 15-Mar-25 15-Sep-25 15-Mar-26 15-Sep-26 15-Mar-27 15-Sep-27 15-Mar-28 15-Sep-28 15-Mar-29 15-Sep-29 15-Mar-30 15-Sep-30 15-Mar-31 15-Sep-31 15-Mar-32 15-Sep-32 15-Mar-33 15-Sep-33 15-Mar-34 Time to Payment 0.14 0.65 1.14 1.65 2.14 2.65 3.14 3.65 4.15 4.65 5.15 5.65 6.15 6.65 7.15 7.65 8.15 8.65 9.15 9.65 10.15 10.65 11.15 11.65 12.15 12.66 13.15 13.66 14.15 14.66 15.15 15.66 16.15 16.66 17.15 17.66 18.15 Payment 0 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 Discount Factor 1 0.9935018649 0.9708878886 0.9491448975 0.9275405694 0.9067683395 0.8861285819 0.8662837716 0.8465655191 0.8275031653 0.8086676325 0.7905575568 0.7725629757 0.7552614621 0.7380702868 0.7215412354 0.705117596 0.6892402649 0.6735518565 0.6584676926 0.643479726 0.629069026 0.6147502285 0.6009829244 0.5873034196 0.5740789434 0.5610118239 0.548447989 0.5359642783 0.5239613821 0.5120350328 0.5005680309 0.4891741584 0.4781592864 0.4672754792 0.4568108656 0.4464129887 0.4364155896 Present Value 13,412,275.18 13,106,986.50 12,813,456.12 12,521,797.69 12,241,372.58 11,962,735.86 11,694,830.92 11,428,634.51 11,171,292.73 10,917,013.04 10,672,527.02 10,429,600.17 10,196,029.74 9,963,948.87 9,740,806.68 9,519,087.55 9,304,743.58 9,092,950.06 8,889,313.85 8,686,976.30 8,492,431.85 8,299,128.09 8,113,269.48 7,928,596.16 7,750,065.74 7,573,659.62 7,404,047.85 7,235,517.76 7,073,478.66 6,912,472.94 6,757,668.42 6,603,851.14 6,455,150.37 6,308,218.97 6,166,946.69 6,026,575.35 5,891,610.46 15-Sep-34 15-Mar-35 15-Sep-35 15-Mar-36 15-Sep-36 15-Mar-37 15-Sep-37 15-Mar-38 15-Sep-38 15-Mar-39 15-Sep-39 15-Mar-40 15-Sep-40 Value of bond issue 18.66 19.15 19.66 20.16 20.66 21.16 21.66 22.16 22.66 23.16 23.66 24.16 24.66 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 13,500,000.00 513,500,000.00 0.4264819477 5,757,506.29 0.4169309034 5,628,567.20 0.4074407696 5,500,450.39 0.3982663113 5,376,595.20 0.3892010189 5,254,213.75 0.3804848793 5,136,545.87 0.3718243258 5,019,628.40 0.3634973366 4,907,214.04 0.3552234516 4,795,516.60 0.3472682382 4,688,121.22 0.3393637581 4,581,410.73 0.3317222089 4,478,249.82 0.3241715858 166,462,109.31 566,345,197.28 Valuation of Bond Under Different Changes in Market Rates Since yields are quoted on a simple interest basis, we apply the change in market rates to the current yield. Using excel's data table, we establish the sensitivity of the bond value to different market rate changes below. Scenario Description Market rates stayed exactly the same Overall rates in the market increased by 5% Overall rates in the market decreased by 5% Market rate change 0 5% -5% New Yield Value of Bond 566,345,197.28 Err:522 Err:522 Err:522 Err:522 0 1,175,000,000.00 Note: yields have a lower bound of 0%. This explains why the scenario where market rates decrease by 5% is truncated to 0%. B From the table above, it can be seen that when yields increase, the issuer of the bond receives less money in exchange for paying the promised coupons and redemption value in future. In contrast, the issuer receives more money at inception if yield decrease. Thus, from the issuer's point of view, the propensity to issue additional bonds increases as the market yields descrease. This explains why most governments and monetary authorities reduce interest rates as a way of stimulating the economy. For the instance where the yield is 0%, the company can borrow at no cost. This is almost certainly a scenario where issuing bonds would be ideal. The actual financing decision should nonetheless take into account the cost of raising other forms of finance. For example it could be that although market yields increase by 5%, bonds may still remain cheap relative to other forms of finance, eg cost of equity. In such a case, issuing bonds is relatively the best alternative. Other costs to issuing bonds need to also be considered. For example, bond issuers often pay to obtain a credit rating of their issue. Furthermore, there may be other underwriting fees to pay the bond underwriter. All these costs may make a bond issue relatively more expensive. There is also need to consider the tax advantages for issuing debt. This stems from the fact that interest payments on bon