In: Accounting

Formula sheet
| A | B | C | D | E | F | G | H | I | J | 
| 2 | |||||||||
| 3 | 1) | ||||||||
| 4 | |||||||||
| 5 | Value of Annuity | =Annuity Amount*(P/A,i,n) | |||||||
| 6 | =$4000*(P/A,8%/4,3*4) | ||||||||
| 7 | =4000*PV(8%/4,3*4,-1,0) | =4000*PV(8%/4,3*4,-1,0) | |||||||
| 8 | |||||||||
| 9 | Hence Value of Annuity is | =D7 | |||||||
| 10 | |||||||||
| 11 | Valuation of preferred stock: | ||||||||
| 12 | |||||||||
| 13 | Value of Each Preferred share | =Present value of perpetuity pf dividend | |||||||
| 14 | =Dividend / Required rate of return | ||||||||
| 15 | =4/12% | =4/12% | |||||||
| 16 | |||||||||
| 17 | Hence value of each preferred share | =D15 | |||||||
| 18 | |||||||||
| 19 | Number of preferred shares | 500 | |||||||
| 20 | Price of each share | =D17 | |||||||
| 21 | |||||||||
| 22 | Value of investment | =D19*D20 | =D19*D20 | ||||||
| 23 | |||||||||
| 24 | Hence Value of investment is | =D22 | |||||||
| 25 | |||||||||
| 26 | 2) | ||||||||
| 27 | Number of shares | 100 | |||||||
| 28 | Buying Price | 5.5 | |||||||
| 29 | Sales Price | 5.75 | |||||||
| 30 | |||||||||
| 31 | Capital gain on investment | =Number of shares*(Selling Price - Buying Price)/Buying Price | |||||||
| 32 | =D27*(D29-D28) | =D27*(D29-D28) | |||||||
| 33 | |||||||||
| 34 | Total Return on investment | =(Selling Price -Buying Price + Dividends)/Buying Price | |||||||
| 35 | =(5.75-5.5+0.1+0.15)/5.5 | =(5.75-5.5+0.1+0.15)/5.5 | |||||||
| 36 | |||||||||
| 37 | Hence total return on investment | =D35 | |||||||
| 38 | |||||||||
| 39 | Dividend Yield | =Dividend Paid for the Year / Price of stock | |||||||
| 40 | =(0.1+0.15)/5.75 | =(0.1+0.15)/5.75 | |||||||
| 41 | |||||||||
| 42 | Hence Dividend Yield | =D40 | |||||||
| 43 | |||||||||