Corporate Bond Question: I have attached the PDF file, I need these answered in excel. The Company issued Corporate Bonds on December 31, 2014, with face value $200,000, due in 12 years, a stated interest rate of 10% and an effective yield of 8.25%. Problem 1: What should be the market value of the Bond at the date of issuance? NOTE: Enter the data above in four cells, and use the appropriate Excel financial function in your software to compute present value, rather than formulas from present value tables. The PV cell(s) should reference only data cells, not show actual amounts. Then, the spreadsheet can be easily modified for different face values, maturities, stated (coupon) interest rates, and/or effective interest rates. Problem 2: Prepare an amortization schedule for the premium/discount on the above bond using the following columns; Date Beginning Balance Cash paid Interest Expense Premium Amortized Ending Balance Problem 3: Managers of the company want to know how sensitive the market price of the bond would be if the stated and market interest rates are different from the above. For the above bond, identify how the present value changes if the stated and effective market rate changes by completing the following table. PV of Bonds Stated Rate of Interest 6% 7% 8% 9% 10% 11% 12% Effective 6% Interest 7% Rate 8% 9% 10% 11% 12% NOTE: Once the Excel formulae in Problem 1 are efficiently setup, this table can be filled quite easily by clicking Data ? What-If Analysis ? Data Table tabs of the Excel spreadsheet. When the present value becomes same as the face value of the bond?
MBA8025 Financial Statement Analyses Homework #4: EXCEL ASSIGNMENT Due at Monday, December 8 The Company issued Corporate Bonds on December 31, 2014, with face value $200,000, due in 12 years, a stated interest rate of 10% and an effective yield of 8.25%. Problem 1: What should be the market value of the Bond at the date of issuance? NOTE: Enter the data above in four cells, and use the appropriate Excel financial function in your software to compute present value, rather than formulas from present value tables. The PV cell(s) should reference only data cells, not show actual amounts. Then, the spreadsheet can be easily modified for different face values, maturities, stated (coupon) interest rates, and/or effective interest rates. Problem 2: Prepare an amortization schedule for the premium/discount on the above bond using the following columns; Date Beginning Balance Cash paid Interest Expense Premium Amortized Ending Balance 12/31/2014 Year 2015 2016 .... 2026 Final table will look similar to our classroom example; Page 19 of Slide11.pptx. Problem 3: Managers of the company want to know how sensitive the market price of the bond would be if the stated and market interest rates are different from the above. For the above bond, identify how the present value changes if the stated and effective market rate changes by completing the following table. PV of Bonds Stated Rate of Interest 6% 7% 8% Effective 6% Interest 7% Rate 8% 9% 10% 11% 12% 9% 10% 11% 12% NOTE: Once the Excel formulae in Problem 1 are efficiently setup, this table can be filled quite easily by clicking Data What-If Analysis Data Table tabs of the Excel spreadsheet. When the present value becomes same as the face value of the bond? Output to be submitted: Upload your Excel file in the Dropbox of the Desire2Learn by midnight of Monday, December 8. Important: You may work in groups to help one another if difficulties are encountered. However, the final output must be solely the result of your individual efforts, not a group endeavor. Copying another student's work and/or virtually identical cell contents will receive no credit. MEMO: Useful information before you start, if you do not have a good experience. For your information, Microsoft Office is available free to all students with GSU Panther e-mail address. To find, go to the following site; http://technology.gsu.edu/technology-services/it-services/software-computer-purchase/softwaredownload-and-purchase/ Microsoft Excel is the most widely used spreadsheet software and it is very easy to get started. A small of number of very simple Excel financial functions will be used in this homework. Excel is extremely powerful in financial functions as well as in other functions. Since Excel includes so many useful functions, it might be useful to know how to get help before a beginner starts any project. (1) From Excel: The Help button in Excel is too small and can easily be ignored. Actually the Help button stays in the top right corner of the window. The button looks like a question mark circle. Or you can use the shortcut key of F1 to enable the Help window. surrounded by a (2) Internet searching: Many times, searching internet is an easier way to get help. You would be able to find more beginner-friendly explanations with numerous examples, but without using highly technical terminologies. (3) Lynda.com: All GSU students have a free access to Lynda.com which is an online learning company that helps anyone learn software, design, and business skills to achieve their personal and professional goals. To get some instructions on Excel, go to the school's homepage at http://www.gsu.edu and click on \"Student\" \"Technology.\" Then, click on \"Lynda.com Online Training\" in the middle-left side of the page. After logging in using your GSU student ID and password, click on \"Software\" \"Excel\" and \"2013\" on the left. You would find several video lectures and each one is quite lengthy. However, you can partially watch topics that you are interested in. (4) Reference book: It is strongly recommended to purchase a reference book. Most books cover same materials, but it is better to review the way of presentation in the book by yourself, and choose your favorite one
