Create an amortization table in Excel. You should assume the users will be A.S. Accounting graduates...
Create an amortization table in Excel. You should assume the users will be A.S. Accounting graduates from X College (so they know terms but are not versed in bonds). You will not be able to train them in person, so you will have to make sure your directions on how to use the table are clear.
For a passing grade (roughly 80%) - Your table must include the following:
- The table must show the interest expense, amortization (effective rate method), and carrying value for each period of the bond’s life (use a 14-year life).
- The table must be able to handle a premium or a discount.
Note: I should be able to know what information must be inputted and how the information needs to be inputted within ten seconds. If I get confused, you lose points.
To increase your grade above 80%:
- The spreadsheet is capable of amortizing bonds annually, semiannually, quarterly, etc.
- The spreadsheet is capable of amortizing using straight line method in addition to the effective rate method mentioned above.
- The spreadsheet is capable of calculating the amount of the premium or discount.
- Anything else that may impress me.
Homework Answers
| Amortization Table using Effective Interest rates | ||||||
| Present Value of Bonds | ||||||
| Face Value | 25,00,000 | |||||
| Coupon Rate | 8.00% | |||||
| PMT | 2,00,000.00 | |||||
| Nper | 14 | |||||
| Rate | 6.00% | |||||
| Per | 1.00 | Annually | ||||
| Present Value of Bonds | $ 29,64,749.20 | |||||
| Premium on Bonds | $ 4,64,749.20 | |||||
| A | B | C | D | E | F | G |
| Period | Interest Payment = 8% x face value | Interest Expenses = 6% x previous year Book value (G) | Amortization of Bonds Premium C-B | Credit Balance in bonds Premium Account | Credit Bal in Bonds Payable | Carrying Value of Bonds F+ E |
| 0 | $ 4,64,749.20 | 25,00,000 | $ 29,64,749.20 | |||
| 1 | 2,00,000.00 | $ 1,77,884.95 | - 22,115.05 | $ 4,42,634.15 | 25,00,000 | $ 29,42,634.15 |
| 2 | 2,00,000.00 | $ 1,76,558.05 | - 23,441.95 | $ 4,19,192.20 | 25,00,000 | $ 29,19,192.20 |
| 3 | 2,00,000.00 | $ 1,75,151.53 | - 24,848.47 | $ 3,94,343.73 | 25,00,000 | $ 28,94,343.73 |
| 4 | 2,00,000.00 | $ 1,73,660.62 | - 26,339.38 | $ 3,68,004.35 | 25,00,000 | $ 28,68,004.35 |
| 5 | 2,00,000.00 | $ 1,72,080.26 | - 27,919.74 | $ 3,40,084.61 | 25,00,000 | $ 28,40,084.61 |
| 6 | 2,00,000.00 | $ 1,70,405.08 | - 29,594.92 | $ 3,10,489.69 | 25,00,000 | $ 28,10,489.69 |
| 7 | 2,00,000.00 | $ 1,68,629.38 | - 31,370.62 | $ 2,79,119.07 | 25,00,000 | $ 27,79,119.07 |
| 8 | 2,00,000.00 | $ 1,66,747.14 | - 33,252.86 | $ 2,45,866.22 | 25,00,000 | $ 27,45,866.22 |
| 9 | 2,00,000.00 | $ 1,64,751.97 | - 35,248.03 | $ 2,10,618.19 | 25,00,000 | $ 27,10,618.19 |
| 10 | 2,00,000.00 | $ 1,62,637.09 | - 37,362.91 | $ 1,73,255.28 | 25,00,000 | $ 26,73,255.28 |
| 11 | 2,00,000.00 | $ 1,60,395.32 | - 39,604.68 | $ 1,33,650.60 | 25,00,000 | $ 26,33,650.60 |
| 12 | 2,00,000.00 | $ 1,58,019.04 | - 41,980.96 | $ 91,669.63 | 25,00,000 | $ 25,91,669.63 |
| 13 | 2,00,000.00 | $ 1,55,500.18 | - 44,499.82 | $ 47,169.81 | 25,00,000 | $ 25,47,169.81 |
| 14 | 2,00,000.00 | $ 1,52,830.19 | - 47,169.81 | $ 0.00 | 25,00,000 | $ 25,00,000.00 |
| Amortization Table using Straight line method | ||||||
| Present Value of Bonds | ||||||
| Face Value | 25,00,000 | |||||
| Coupon Rate | 6.00% | |||||
| PMT | 1,50,000.00 | |||||
| Nper | 14 | |||||
| Rate | 8.00% | |||||
| Per | 1.00 | Annually | ||||
| Present Value of Bonds | $ 20,87,788.15 | |||||
| Discount on Bonds | $ -4,12,211.85 | |||||
| A | B | C | D | E | F | G |
| Period | Interest Payment = 8% x face value | Interest Expenses = B-D | Amortization of Bonds Premium =Discount on Bonds/ Nper | Credit Balance in bonds discount Account | Credit Bal in Bonds Payable | Carrying Value of Bonds F+ E |
| 0 | $ -4,12,211.85 | 25,00,000 | $ 20,87,788.15 | |||
| 1 | 1,50,000.00 | $ 1,20,556.30 | - 29,443.70 | $ -3,82,768.15 | 25,00,000 | $ 21,17,231.85 |
| 2 | 1,50,000.00 | $ 1,20,556.30 | - 29,443.70 | $ -3,53,324.44 | 25,00,000 | $ 21,46,675.56 |
| 3 | 1,50,000.00 | $ 1,20,556.30 | - 29,443.70 | $ -3,23,880.74 | 25,00,000 | $ 21,76,119.26 |
| 4 | 1,50,000.00 | $ 1,20,556.30 | - 29,443.70 | $ -2,94,437.04 | 25,00,000 | $ 22,05,562.96 |
| 5 | 1,50,000.00 | $ 1,20,556.30 | - 29,443.70 | $ -2,64,993.33 | 25,00,000 | $ 22,35,006.67 |
| 6 | 1,50,000.00 | $ 1,20,556.30 | - 29,443.70 | $ -2,35,549.63 | 25,00,000 | $ 22,64,450.37 |
| 7 | 1,50,000.00 | $ 1,20,556.30 | - 29,443.70 | $ -2,06,105.92 | 25,00,000 | $ 22,93,894.08 |
| 8 | 1,50,000.00 | $ 1,20,556.30 | - 29,443.70 | $ -1,76,662.22 | 25,00,000 | $ 23,23,337.78 |
| 9 | 1,50,000.00 | $ 1,20,556.30 | - 29,443.70 | $ -1,47,218.52 | 25,00,000 | $ 23,52,781.48 |
| 10 | 1,50,000.00 | $ 1,20,556.30 | - 29,443.70 | $ -1,17,774.81 | 25,00,000 | $ 23,82,225.19 |
| 11 | 1,50,000.00 | $ 1,20,556.30 | - 29,443.70 | $ -88,331.11 | 25,00,000 | $ 24,11,668.89 |
| 12 | 1,50,000.00 | $ 1,20,556.30 | - 29,443.70 | $ -58,887.41 | 25,00,000 | $ 24,41,112.59 |
| 13 | 1,50,000.00 | $ 1,20,556.30 | - 29,443.70 | $ -29,443.70 | 25,00,000 | $ 24,70,556.30 |
| 14 | 1,50,000.00 | $ 1,20,556.30 | - 29,443.70 | $ -0.00 | 25,00,000 | $ 25,00,000.00 |
Add Answer to:
Create an amortization table in Excel. You should assume the
users will be A.S. Accounting graduates...
On January 1, 2020, Parker Company sold in exchange for cash, $25 million of bonds payable...
On January 1, 2020, Parker Company sold in exchange for cash, $25 million of bonds payable to investors. The bonds mature on December 31, 2049, have a stated interest rate of 8%, and pay interest annually on December 31 of each year. The market interest rate was 6% on the date that Parker sold the bonds and is expected to fluctuate between 5% and 9% over the 30-year life of the bonds. As required by GAAP, Parker uses the effective...
Using a Discount Amortization Table (Straight Line) Panamint Candy Company prepared the following amortization table for...
Using a Discount Amortization Table (Straight Line) Panamint Candy Company prepared the following amortization table for $300,000 of 5-year, 9% bonds issued and sold by Panamint on january 1, 2021, for $285,000: Period Cash Payment (Credit) Interest Expense (Debit) Discount on Bonds Payable (Credit) Discount on Bonds Payable Balance Carrying Value At issue $15,000 $285,000 06/30/21 $13,500 $15,000 $1,500 13,500 286,500 12/31/21 13,500 15,000 1,500 12,000 288,000 06/30/22 13,500 15,000 1,500 10,500 289,500 12/31/22 13,500 15,000 1,500 9,000 291,000 06/30/23...
Create an amortization schedule via excel, using the following data listed above. Skills & Knowledge: Calculate...
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...
Exercise 9-74 Using a Premium Amortization Table (Straight Line) For Dingle Corporation, the following amortization table...
Exercise 9-74 Using a Premium Amortization Table (Straight Line) For Dingle Corporation, the following amortization table was prepared when $400,000 of 5-year, 7% bonds were sold on January 1, 2020, for $420,000. Period Cash Payment (Credit) Interest Expense (Debit) Premium on Bonds Payable (Debit) Premium on Bonds Payable Balance Carrying Value At issue $20,000 $420,000 06/30/20 $14,000 $12,000 $2,000 18,000 418,000 12/31/20 14,000 12,000 2,000 16,000 416,000 06/30/21 14,000 12,000 2,000 14,000 414,000 12/31/21 14,000 12,000 2,000 12,000 412,000 06/30/22...
You should turn in your answers in ONE Excel document. Use financial formulas in Excel to...
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...
You will be using Excel as a tool to assist in solving this accounting problem. Students...
You will be using Excel as a tool to assist in solving this accounting problem. Students completing this assignment will demonstrate the ability to: Use arithmetic operators (addition, subtraction, multiplication, and division) in spreadsheets. Enter simple formulas into spreadsheets to calculate values Copy formulas from one cell to another. Use the sum function. Format cells using decimal points, currency, etc. Right Click; Click Format cells; Click Number; Click Currency or Date Use underline, bold, spacing (left, center, right) functions. Create...
please show excel calculations! You should turn in your answers in ONE Excel document. Use financial...
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...
Payable: Use the t-accts below to record the following entries. If you get stuck, carefully revie...
Payable: Use the t-accts below to record the following entries. If you get stuck, carefully review the online and text examples. On September 1t, Geo Inc. borrows $2,400 from State Bank and signs a 10 month short-term note payable. The interest rate on the note is 7%. Even though the note is only for 10 months, the interest rate is an annual rate (see interest calculations below). a) Record the entry to borrow the money from the bank. b) Next,...
Part Two- for Option B only: Create a loan amortization table over the length of the...
Part Two- for Option B only: Create a loan amortization table over the length of the financing. You should use the example from the asynchronous lecture. While that slide was shown in months, your analysis for this project should be shown in years. Hint: If your table is correct, the total payments and total interest should equal the numbers you calculated in the above step, and remaining principal should equal zero in the final year. Debt will be acquired to...
Please show all supporting computations. Points will be deducted if you do not show your work....
Please show all supporting computations. Points will be deducted if you do not show your work. 1. Prepare the necessary journal entries to record the following transactions relating to the long-term issuance of bonds of Pitts Company: (4 points) January 1 Issued $3.000,000 of Pitts Company 5-ycar, 4% bonds at a price of 96.5. Interest on the bonds is payable semiannually on July 1 and January 1. The bonds are callable after 2 years at a price of 102. July...
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...
You will be using Excel as a tool to assist in solving this accounting problem. Students completing this assignment will demonstrate the ability to: Use arithmetic operators (addition, subtraction, multiplication, and division) in spreadsheets. Enter simple formulas into spreadsheets to calculate values Copy formulas from one cell to another. Use the sum function. Format cells using decimal points, currency, etc. Right Click; Click Format cells; Click Number; Click Currency or Date Use underline, bold, spacing (left, center, right) functions. Create...
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...
Payable: Use the t-accts below to record the following entries. If you get stuck, carefully review the online and text examples. On September 1t, Geo Inc. borrows $2,400 from State Bank and signs a 10 month short-term note payable. The interest rate on the note is 7%. Even though the note is only for 10 months, the interest rate is an annual rate (see interest calculations below). a) Record the entry to borrow the money from the bank. b) Next,...
Please show all supporting computations. Points will be deducted if you do not show your work. 1. Prepare the necessary journal entries to record the following transactions relating to the long-term issuance of bonds of Pitts Company: (4 points) January 1 Issued $3.000,000 of Pitts Company 5-ycar, 4% bonds at a price of 96.5. Interest on the bonds is payable semiannually on July 1 and January 1. The bonds are callable after 2 years at a price of 102. July...
Most questions answered within 3 hours.
-
The random variable X is exponentially distributed, where X
represents the time it takes for a...
asked 7 minutes ago -
a) Write a verilog module for 1:4 Demultiplexer using verilog
primitives.
b) Design 1-to-4 DEMUX using...
asked 1 minute ago -
MATLAB
write a MATLAB function (1) output a third-order polynomial
function with the coefficients as the...
asked 14 minutes ago -
A z-score
communicates a raw score’s "relative standing"
under the normal curve in relation to:
asked 23 minutes ago -
An object is vibrating on a spring with the following equation
of motion:
?=(30 ??)cos((2?)/(160)?)
a)...
asked 22 minutes ago -
Vulcan Flyovers offers scenic overflights of Mount St. Helens,
the volcano in Washington State that explosively...
asked 24 minutes ago -
If organizations do not adapt fast enough and move incrementally
from the twentieth-century model to the...
asked 25 minutes ago -
A helium balloon with 2.5L of gas has a gauge pressure of 10,000
Pa. The balloon...
asked 29 minutes ago -
What is responsible for Jupiter's enormous magnetic field?
asked 45 minutes ago -
At the end of the year, a company offered to buy 5,000 units of
a product...
asked 46 minutes ago -
Implement C++ program for each of the following.
Let D = [-48, -14, -8, 0, 1,...
asked 1 hour ago -
Consider a labor market in which LD = 400 – 4W and LS = 250 +...
asked 1 hour ago