| Milestone Two: Stock Valuation and Bond Issuance (fill in the YELLOW cells) |
| PART I: STOCK VALUATION |
| | Dividend from Financial Statements: |
| Read the Explanations to the right of the calculation cells for specific information on the data. | | | | | | | | | | Explanations: |
| | Year | Cash Div/share ($) | Dividend Yield | Stockholder's Equity (in millions) | Stock Price | | | | | Note:
1. The dividends declared and paid by UPS for 2015, 2016, and 2017 are found on the second page of the 2017 UPS Annual Report.
2. The dividend yield for 2015, 2016, and 2017 are found on the second page of the 2017 UPS Annual Report.
3. Stockholder's/Shareholder's equity for 2015, 2016, and 2017 are found on the second page of the UPS Annual Report. |
| | 2015 | | | | ERROR:#DIV/0! |
| | 2016 | | | | ERROR:#DIV/0! |
| | 2017 | | | | ERROR:#DIV/0! |
| 1. Stock Valuation - The new dividend yield if the company increased its dividend per share by 1.75 |
| | Year | Cash Div/Share ($) +1.75 | Dividend Yield | Stockholder's Equity (in millions) | Stock Price | | | | | Dividend Yield - annual cash dividend per share of common stock divided by the market price of a share of the common stock. (Dividend yield = Annual Dividend/Current Stock Price)
Note: Current Stock Price is not part of the Financial Statements - calculated using the formula for Dividend Yield |
| | 2015 | 1.75 | ERROR:#DIV/0! | 0 | ERROR:#DIV/0! |
| | 2016 | 1.75 | ERROR:#DIV/0! | 0 | ERROR:#DIV/0! |
| | 2017 | 1.75 | ERROR:#DIV/0! | 0 | ERROR:#DIV/0! |
| 2. The dividend yield if the firm doubled it's outstanding shares |
| | Year | Cash Div/Share ($) | Dividend Yield | Stockholder's Equity (in millions) -doubled | Stock Price | | | | | Stockholder's Equity = Assets - Liabilities. This represents the ownership of a corporations. Owners are called stockholder because they hold stocks or share of the company. The main goal of every corporate manager is to generate shareholder value. Note: Shareholder's Equity for 2015, 2016 and 2017 will be found on page 2 of the 2017 UPS Annual Report. |
| | 2015 | 0 | ERROR:#DIV/0! | 0 | ERROR:#DIV/0! |
| | 2016 | 0 | ERROR:#DIV/0! | 0 | ERROR:#DIV/0! |
| | 2017 | 0 | ERROR:#DIV/0! | 0 | ERROR:#DIV/0! |
| | | | | | | | | | | Return on Equity - for this part we will modify and use return on investment instead.
Using the formula: Dividend (+1.75)/+[(new price-old price)/old price]
|
| 3. The rate of return on equity (i.e., the cost of stock) based on the new dividend yield you calculated above |
| | Year | Cash Div/Share ($) +1.75 | Stock Price | Return on Investment |
| | 2015 | 1.75 | ERROR:#DIV/0! | | CALCULATE ROI |
| | 2016 | 1.75 | ERROR:#DIV/0! | | (Dividends + Capital gain)/ Divided by the original Price |
| | 2017 | 1.75 | ERROR:#DIV/0! | | (D1 + (P1-P0)) / PO |
| | | | | | | | | | | Bonds are a long-term debt for corporations. By buying a bond, the bond-owner lends money to the corporation. The borrower promises to pay specified interest rate during the loans lifetime and at the maturity, payback the entire principle. In case of bankruptcy, bondholders have priority over stockholders for any payment distributions.
Bonds = Debt...............Bondholders = Lenders
Stock=Equity................Stockholders = OwnersCalculation: For purposes of this exercise, assume that UPS issues a new ten-year bond for 100,000 that will mature in 2027. The Future Value of this bond is therefore $100,000. The bond was issued in December 2017 at a annual rate of 5.0% fixed for 10 years, with interest payments made semi-annually. What is the Present Value of this bond using the three scenarios in Part II: Bond Issuance. The coupon rate, which is used to calculate the semi-annual PMTs for this bond is 5% annually, fixed for 10 years.
|
| PART II: BOND ISSUANCE |
| | Newly issued 10-year bond | | Calculate the Present Value in the three scenarios below |
| | Present Value | PV |
| | Periods | N | | Semi-annual payment: 2017-2027 = 10 years *2 = 20 periods |
| | Interest | I | | Interest paid semi-annually: 5.00%/2 = 2.5% |
| | Payments | PMT | | This bond make regular semi-annual payments of interest. |
| | Future Value | FV | | Future Value in 20 years - Enter as a positive number |
| 1. The new value of the bond if overall rates in the market increased by 2% |
| | Present Value | PV |
| | Periods | N |
| | Interest | I | | Please adjust interest | | %+2% = .00%/2 = % |
| | Payments | PMT | | | | | | | | PV (Present Value Calculation) - using Excel Formula |
| | Future Value | FV | | | | | | | | Step 1) Select Formulas |
| | | | | | | | | | | Step 2) Click on Financial |
| | | | | | | | | | | Step 3) Select PV - you will see the formula below |
| 2. The new value of the bond if overall rates in the market decreased by 2% | | | | | | | | | | Step 4) Enter the following: |
| | | | | | | | | | | Rate - enter as decimal, no % sign. Example: 4% as 0.04 if paid annually. | | | | | | If paid semiannually 4/2 = 2% 0.02 |
| | Present Value | PV | | | | | | | | Nper - number of periods where dividends are paid. For example, a 10 year bond pays diviends annually. N = 10. | | | | | | | | | If semiannualy 10 X 2 = 20 N=20 |
| | Periods | N | | | | | | | | Pmt - payment - The semiannual payment of dividends in dollars |
| | Interest | I | | Please adjust interest | | .00%-2% = %/2 = % | | | | Fv - Future value. Enter as positive. Example 1,000 should be 1,000 |
| | Payments | PMT | | | | | | | | Type - leave blank |
| | Future Value | FV | | CALCULATING PV (see help on the right hand side of the sheet) |
| 3. The value of the bond if overall rates in the market stayed exactly the same - please explain |
| | | | | | | | | | | Updated: 10/2018 by RFB |
vashurov13
FIN550FinalProjectExcelWorkbook-STUDENT.xlsx
