Use financial formulas in Excel to show work for Requirement #1 and #5 of each part
Bill Corporation issued six-year, 7% bonds with a total face value of $1,250,000 on January 1, 2019. Interest is paid semi-annually on June 30 and December 31. The market rate of interest on this date was 10.0%. Bill uses the effective interest rate method.
Required:
Part 1 | |||||
Table values are based on: | |||||
n= | 12 | ||||
i= | 5.0% | ||||
Cash Flow | Table Value | Amount | Present Value | ||
Interest | 8.86325 | $43,750 | $3,87,767 | ||
Principal | 0.55684 | $12,50,000 | $6,96,050 | ||
Price of Bond | $10,83,817 | ||||
Discount on Bonds =$1,250,000 - $1,083,817 =$166,183 | |||||
Part 2 | |||||
Amortization table | |||||
Date | Interest Payment($1,250,000*3.5%) | Interest expenses(Bond carrying amount*5%) | Discount amorrtization | Unamortized discount | Bond carrying amount |
Col I | Col II | Col III | Col IV(Col III - Col II) | Col V(Col VI - Col IV) | Col VI |
01-Jan-19 | 1,66,183 | 10,83,817 | |||
30-Jun-19 | 43,750 | 54,191 | 10,441 | 1,55,742 | 10,94,258 |
30-Dec-19 | 43,750 | 54,713 | 10,963 | 1,44,779 | 11,05,221 |
30-Jun-20 | 43,750 | 55,261 | 11,511 | 1,33,268 | 11,16,732 |
30-Dec-20 | 43,750 | 55,837 | 12,087 | 1,21,182 | 11,28,818 |
30-Jun-21 | 43,750 | 56,441 | 12,691 | 1,08,491 | 11,41,509 |
30-Dec-21 | 43,750 | 57,075 | 13,325 | 95,165 | 11,54,835 |
30-Jun-22 | 43,750 | 57,742 | 13,992 | 81,174 | 11,68,826 |
30-Dec-22 | 43,750 | 58,441 | 14,691 | 66,482 | 11,83,518 |
30-Jun-23 | 43,750 | 59,176 | 15,426 | 51,056 | 11,98,944 |
30-Dec-23 | 43,750 | 59,947 | 16,197 | 34,859 | 12,15,141 |
30-Jun-24 | 43,750 | 60,757 | 17,007 | 17,852 | 12,32,148 |
30-Dec-24 | 43,750 | 61,602 | 17,852 | 0 | 12,50,000 |
Part 3 & 4 | |||||
Date | Accounts and explanation | Debit(in $) | Credit(in $) | ||
01-Jan-19 | Cash | 10,83,817 | |||
Discount on Bonds Payable | 1,66,183 | ||||
Bonds Payable | 12,50,000 | ||||
30-Jun-19 | Interest expenses | 54,191 | |||
Cash | 43,750 | ||||
Discount on Bonds Payable | 10,441 | ||||
30-Dec-24 | Interest expenses | 61,602 | |||
Cash | 43,750 | ||||
Discount on Bonds Payable | 17,852 | ||||
30-Dec-24 | Bonds Payable | 12,50,000 | |||
Cash | 12,50,000 | ||||
Balance sheet extract as on June 30,2020 | |||||
Long term liabilities | $12,50,000 | ||||
Less: | Unamortized Discount | $1,33,268 | $11,16,732 | ||
Use financial formulas in Excel to show work for Requirement #1 and #5 of each part...
You should turn in your answers in ONE Excel document. Use financial formulas in Excel to show work for Requirement #1 and #5 of each part. An assignment submitted that doesn’t demonstrate your formulas within Excel will receive an unsatisfactory grade. PART A Sparty Corporation issued five-year, 8% bonds with a total face value of $500,000 on January 1, 2019. Interest is paid annually on December 31. The market rate of interest on this date was 6%. Sparty uses the...
PART B Bill Corporation issued six-year, 7% bonds with a total face value of $1,250,000 on January 1, 2019. Interest is paid semi-annually on June 30 and December 31. The market rate of interest on this date was 10.0%. Bill uses the effective interest rate method. Required: 1. Determine the proceeds of the bond sale on 1/1/19. Explain your method of calculation. 2. Using the present value of a dollar table (found in Appendix E of your text), what factor...
please show excel calculations! You should turn in your answers in ONE Excel document. Use financial formulas in Excel to show work for Requirement #1 and #5 of each part. An assignment submitted that doesn't demonstrate your formulas within Excel will receive an unsatisfactory grade. PART B Bill Corporation issued five-year, 6% bonds with a total face value of $1,000,000 on January 1, 2019. Interest is paid semi-annually on June 30 and December 31. The market rate of interest on...
PART A Sparty Corporation issued five-year, 8% bonds with a total face value of $500,000 on January 1, 2019. Interest is paid annually on December 31. The market rate of interest on this date was 6%. Sparty uses the effective interest rate method. Required: 1. Using Excel, determine the proceeds of the bond sale on 1/1/19. 2. Using the present value of a dollar table (found in Appendix E of your text), what factor would you use to calculate the...
Using Excel, determine the proceeds of the bond sale on 1/1/19. PART A Sparty Corporation issued five-year, 8% bonds with a total face value of $500,000 on January 1, 2019. Interest is paid annually on December 31. The market rate of interest on this date was 6%. Sparty uses the effective interest rate method. Required: 1. Using Excel, determine the proceeds of the bond sale on 1/1/19. 2. Using the present value of a dollar table (found in Appendix E...
Problems 1-5. Please show all work, thanks. Instructions: Problem is to be worked independently. All parts, excluding part 4, should be neatly handwritten in pencil and all work shown. January 1, 2019: Excel Corporation issued 10 year, 7% bonds with a face value of $1,550,000. The bonds were sold to yield 8%. Interest is payable annually on January 1. 1. What is the issue price of the bonds? (Show calculation or financial calculator inputs.) 2. Record the bond issuance on...
Create an amortization schedule via excel, using the following data listed above. Skills & Knowledge: Calculate the selling price of bonds using Excel and create a bond amortization schedule using Excel Task: Use Excel and the data provided below to: 1. Calculate the price of the bond and 2.Create an amortization schedule Use the amortization schedule format reflected in Chapter 14 of the required textbook. st" 1 in eto tting e il' wad nam asf ond Once you save and...
Problem 5 Use the Excel template to build a spreadsheet for a purchase of $1,000,000 face value, 6% 5-year bond with interest payments every 6 months. Market interest rate is 5%. Include the following items: Inputs: Bond initial purchase amount Stated Interest Rate Maturity in Years Number of payments/year Market interest rate Calculations section 1: --Fair value with separate calculations for interest and principal --Discount or premium --Record the journal entry required when the bonds are purchased. Calculations Section 2:...
Section 2 Assume, instead, that on January 1, 2012, Harrison Company issued $800,000 of 10-year, 7% face value bonds. The market rate of other similar bonds was 8%. The bonds pay interest semi-annually every January 1 and July 1. Required: 1. Determine the selling price of the bond using the Time Value of Money tables. You MUST show all work, including writing out the amounts (principal and interest amounts) as well as the two PV factors used to determine the...
You must incorporate as many formulas as necessary so that the worksheet automatically updates when the market rate of interest is changed. Your Excel spreadsheet should work whether your bond results in a discount or premium. I should be able to manipulate certain data, for example, if I change the present value rates or market rate of interest rate, the formulas should still work correctly when determining the present value of the bond. On January 1, 2018, Raymond Corporation sold...