11. In cell E9, input a formula that adds 365 days to cell E7. This gives...

Transcribed Image Text:

11. In cell E9, input a formula that adds 365 days to cell E7. This gives the date of the next interest payment. 12. Copy the formula in cell E9 down through cell E26. You want to see the dates for June 30 and December 31 through the years. However, there is a problem in the leap years (2028 and 2032). Look down the numbers in column E to find the first incident that reads 6/29/2028. Change that formula to add 366 days instead of 365. Do the same thing for the next cell down the column to change the 12/30/2028 to 12/31/2028. Also correct the dates in the year 2032. 13. Input a formula or cell reference in cell F7 to show the interest payment amount. Make sure the formula can be copied down the column through cell F26. Remember that the amount of the interest payment stays the same each time a payment is made. 14. Input a formula in cell G7 to calculate the interest expense to be recognized. Make sure the formula can be copied down the column through cell G26. When the formula is first copied, it will show O's down the column. This will be corrected when you complete the formulas in columns H and I. 15. Input a formula in cell H7 to determine the amount of the discount or premium amortized. It will make the next formula easier if the amount of discount amortized is a positive number and the amount of premium amortized is a negative number. Copy the formula down through cell H26. 16. Input a formula in cell 17 to compute the new carrying value of the bonds and copy it down through cell 126. The amount in cell 126 should be equal to the face value of the bonds if your formulas are correct. A B 1 ACC 2102 Intermediate Accounting II 2 Ch 14 Excel Problem 3 4 Face value of bonds 5 Stated interest rate 6 Market interest rate 7 Term in years 8 Issue date 9 First interest payment date 10 Second interest payment date 11 12 Semi-annual Interest Payment 13 14 PV of the bond principal 15 PV of interest payments 16 Cash Proceeds from Bond issuance 17 18 Did bonds sell at a discount or premium? 19 20 21 22 23 24 25 26 $ 8,000,000.00 5% 8% 10 1/1/2024 6/30/2024 12/31/2024 $ 200,000.00 $3,651,095.57 $2,718,065.27 $6,369,160.84 Discount D E Payment Date 06/30/2024 1 12/31/2024 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 F Interest Payment 0 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 G Interest Expense 0 H I Discount Carrying Amortized Amount 0 $6,369,160.84 11. In cell E9, input a formula that adds 365 days to cell E7. This gives the date of the next interest payment. 12. Copy the formula in cell E9 down through cell E26. You want to see the dates for June 30 and December 31 through the years. However, there is a problem in the leap years (2028 and 2032). Look down the numbers in column E to find the first incident that reads 6/29/2028. Change that formula to add 366 days instead of 365. Do the same thing for the next cell down the column to change the 12/30/2028 to 12/31/2028. Also correct the dates in the year 2032. 13. Input a formula or cell reference in cell F7 to show the interest payment amount. Make sure the formula can be copied down the column through cell F26. Remember that the amount of the interest payment stays the same each time a payment is made. 14. Input a formula in cell G7 to calculate the interest expense to be recognized. Make sure the formula can be copied down the column through cell G26. When the formula is first copied, it will show O's down the column. This will be corrected when you complete the formulas in columns H and I. 15. Input a formula in cell H7 to determine the amount of the discount or premium amortized. It will make the next formula easier if the amount of discount amortized is a positive number and the amount of premium amortized is a negative number. Copy the formula down through cell H26. 16. Input a formula in cell 17 to compute the new carrying value of the bonds and copy it down through cell 126. The amount in cell 126 should be equal to the face value of the bonds if your formulas are correct. A B 1 ACC 2102 Intermediate Accounting II 2 Ch 14 Excel Problem 3 4 Face value of bonds 5 Stated interest rate 6 Market interest rate 7 Term in years 8 Issue date 9 First interest payment date 10 Second interest payment date 11 12 Semi-annual Interest Payment 13 14 PV of the bond principal 15 PV of interest payments 16 Cash Proceeds from Bond issuance 17 18 Did bonds sell at a discount or premium? 19 20 21 22 23 24 25 26 $ 8,000,000.00 5% 8% 10 1/1/2024 6/30/2024 12/31/2024 $ 200,000.00 $3,651,095.57 $2,718,065.27 $6,369,160.84 Discount D E Payment Date 06/30/2024 1 12/31/2024 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 F Interest Payment 0 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 200,000.00 G Interest Expense 0 H I Discount Carrying Amortized Amount 0 $6,369,160.84

Answer rating: 100% (QA)

It appears you are describing a series of steps to set up a financial spreadsheet presumably in a pr... View the full answer