Question

In: Finance

Exercise 1 Construct an amortization table in Excel to answer the following questions. You must use...

Exercise 1

Construct an amortization table in Excel to answer the following questions. You must use the corresponding Excel financial formulas whenever possible. Upon graduation, Federico Hernández, borrows $20,000 to finance a late model used car. The loan is made by a family member who was able to obtain an 8 % annual percent rate (APR). The loan is going to be payback in equal monthly payments over 5 years.

a) How much are the monthly payments?

b) How many total dollars of interest does Federico pay over the life of the loan?

c) How much of the third payment goes to pay interest? How much goes to pay principal?

d) How much of the 48th payment goes to pay interest? How much goes to pay principal?

e) Suppose that Federico decides to pay off at the end of year three. How much does he has to pay in order to pay off the loan in full.

Exercise 2

Construct a spreadsheet capable of generating the compound interest tables for any interest rate (i.e., if your change the interest rate the table you will automatically generate the values of the interest factors for that particular interest rate).

Please explain how to use and program each excel formula if possible

Thanks in advance

Expert Solution

As per rules I am answering the first 4 subparts of the question

a: Monthly payment = 405.53

b: Total interest paid =

$4,331.67

c: Payment No 3: Interest = 129.69

Principal = 275.84

d: Payment No 48: Interest = 33.56

Principal = 371.97

WORKINGS

Loan Amount	Interest Rate	Term in Years	Monthly Payment
$20,000.00	8.00%	5	$405.53

Month	StartingBalance	Interest	Principal	EndingBalance	TotalInterest
1	$20,000.00	$133.33	$272.19	$19,727.81	$133.33
2	$19,727.81	$131.52	$274.01	$19,453.80	$264.85
3	$19,453.80	$129.69	$275.84	$19,177.96	$394.54
4	$19,177.96	$127.85	$277.67	$18,900.29	$522.40
5	$18,900.29	$126.00	$279.53	$18,620.76	$648.40
6	$18,620.76	$124.14	$281.39	$18,339.37	$772.54
7	$18,339.37	$122.26	$283.27	$18,056.10	$894.80
8	$18,056.10	$120.37	$285.15	$17,770.95	$1,015.17
9	$17,770.95	$118.47	$287.05	$17,483.90	$1,133.65
10	$17,483.90	$116.56	$288.97	$17,194.93	$1,250.21
11	$17,194.93	$114.63	$290.90	$16,904.03	$1,364.84
12	$16,904.03	$112.69	$292.83	$16,611.20	$1,477.53
13	$16,611.20	$110.74	$294.79	$16,316.41	$1,588.27
14	$16,316.41	$108.78	$296.75	$16,019.66	$1,697.05
15	$16,019.66	$106.80	$298.73	$15,720.93	$1,803.85
16	$15,720.93	$104.81	$300.72	$15,420.21	$1,908.65
17	$15,420.21	$102.80	$302.73	$15,117.48	$2,011.46
18	$15,117.48	$100.78	$304.74	$14,812.74	$2,112.24
19	$14,812.74	$98.75	$306.78	$14,505.96	$2,210.99
20	$14,505.96	$96.71	$308.82	$14,197.14	$2,307.70
21	$14,197.14	$94.65	$310.88	$13,886.26	$2,402.34
22	$13,886.26	$92.58	$312.95	$13,573.31	$2,494.92
23	$13,573.31	$90.49	$315.04	$13,258.27	$2,585.41
24	$13,258.27	$88.39	$317.14	$12,941.13	$2,673.80
25	$12,941.13	$86.27	$319.25	$12,621.87	$2,760.07
26	$12,621.87	$84.15	$321.38	$12,300.49	$2,844.22
27	$12,300.49	$82.00	$323.52	$11,976.97	$2,926.22
28	$11,976.97	$79.85	$325.68	$11,651.29	$3,006.07
29	$11,651.29	$77.68	$327.85	$11,323.43	$3,083.74
30	$11,323.43	$75.49	$330.04	$10,993.39	$3,159.23
31	$10,993.39	$73.29	$332.24	$10,661.16	$3,232.52
32	$10,661.16	$71.07	$334.45	$10,326.70	$3,303.59
33	$10,326.70	$68.84	$336.68	$9,990.02	$3,372.44
34	$9,990.02	$66.60	$338.93	$9,651.09	$3,439.04
35	$9,651.09	$64.34	$341.19	$9,309.90	$3,503.38
36	$9,309.90	$62.07	$343.46	$8,966.44	$3,565.45
37	$8,966.44	$59.78	$345.75	$8,620.69	$3,625.22
38	$8,620.69	$57.47	$348.06	$8,272.63	$3,682.69
39	$8,272.63	$55.15	$350.38	$7,922.26	$3,737.84
40	$7,922.26	$52.82	$352.71	$7,569.54	$3,790.66
41	$7,569.54	$50.46	$355.06	$7,214.48	$3,841.12
42	$7,214.48	$48.10	$357.43	$6,857.05	$3,889.22
43	$6,857.05	$45.71	$359.81	$6,497.23	$3,934.93
44	$6,497.23	$43.31	$362.21	$6,135.02	$3,978.25
45	$6,135.02	$40.90	$364.63	$5,770.39	$4,019.15
46	$5,770.39	$38.47	$367.06	$5,403.33	$4,057.62
47	$5,403.33	$36.02	$369.51	$5,033.83	$4,093.64
48	$5,033.83	$33.56	$371.97	$4,661.86	$4,127.20
49	$4,661.86	$31.08	$374.45	$4,287.41	$4,158.28
50	$4,287.41	$28.58	$376.95	$3,910.47	$4,186.86
51	$3,910.47	$26.07	$379.46	$3,531.01	$4,212.93
52	$3,531.01	$23.54	$381.99	$3,149.02	$4,236.47
53	$3,149.02	$20.99	$384.53	$2,764.49	$4,257.46
54	$2,764.49	$18.43	$387.10	$2,377.39	$4,275.89
55	$2,377.39	$15.85	$389.68	$1,987.71	$4,291.74
56	$1,987.71	$13.25	$392.28	$1,595.43	$4,304.99
57	$1,595.43	$10.64	$394.89	$1,200.54	$4,315.63
58	$1,200.54	$8.00	$397.52	$803.02	$4,323.63
59	$803.02	$5.35	$400.17	$402.84	$4,328.99
60	$402.84	$2.69	$402.84	$0.00	$4,331.67

Formulae: Same repeated till payment No 60

jeff jeffy answered 1 month ago

Bond Amortization and adjusting entry. Use excel to create an amortization table for 10 years and...

Bond Amortization and adjusting entry. Use excel to create an amortization table for 10 years and show the adjusting entry for the bond payable for the June payment. Show calculations. Had issued $1,500,000 of 4%, 10-year bond, dated 1/1/18 for $1,383,079 when the market rate was 5%. Interest is paid on June 30 and January 1 using the effective interest rate method. The June payment is included in the Dec. 1 TB. Trial Balance December 1, 2021 Description Debit Credit...

Using the Excel sheet for Constructing Confidence Intervals, answer the following questions. 1. Construct a 90%...

Using the Excel sheet for Constructing Confidence Intervals, answer the following questions. 1. Construct a 90% confidence interval for the true mean of a normal population if a random sample size of 40 from the population yields a sample mean of 75 and the population has a standard deviation of 5. Lower limit: ______________ upper limit: ______________ 2. A random sample, consisting of the values listed below, was taken from a normally distributed population. Assuming the standard deviation of the...

Answer the following application exercise on organization and data visualization. You can use Excel. If you...

Answer the following application exercise on organization and data visualization. You can use Excel. If you use the calculator and perform the exercise manually, obtain a picture of the process performed using the cutout and pasting its result into a Word file. A sample of 30 employees who investigate cases of possible money laundering, answered a survey on the average time in the days analyzed, presented results and recommendations. Below, your answers are detailed. 5 22 20 16 13 6...

EXCEL: Complete the loan amortization schedule in the green cells below. You must use at least...

EXCEL: Complete the loan amortization schedule in the green cells below. You must use at least one of the following formulas (PMT, IPMT, PPMT) in your solution. Years Periods per year 30 12 Period PMT Interest Principal Paid Balance Annual Interest Rate Lump Sum 0 350,000.00 1 0.08 2 0.08 3 0.08 10,000.00 4 0.08 5 0.08 6 0.08 7 0.08 8 0.08 9 0.08 10 0.08 11 0.08 12 0.08 13 0.085 14 0.085 15 0.085 16 0.085

Use Excel to prepare a Bond Interest and Discount Amortization Table using the following information: $50,000,000...

Use Excel to prepare a Bond Interest and Discount Amortization Table using the following information: $50,000,000 face value coupon rate of interest - 6% market rate of interest - 7% term - 10 years payable semi-annually First calculate the proceeds received upon issuance and the amount of the discount

Answer the following questions and use Excel or this document to show your work. 1. Consider...

Answer the following questions and use Excel or this document to show your work. 1. Consider the following results for two samples randomly taken from two normal populations with equal variances. Sample I Sample II Sample Size 28 35 Sample Mean 48 44 Population Standard Deviation 9 10 a. Develop a 95% confidence interval for the difference between the two population means. b. Is there conclusive evidence that one population has a larger mean? Explain.

use Excel Solver to answer the following question.You must provide sufficient evidence that you did the...

use Excel Solver to answer the following question.You must provide sufficient evidence that you did the work on your own. For example, what equations did you use in your model? How was the excel spread sheet laid out? A student at a local university has just completed a decision modeling course. On her assignments, she has earned a 86 on the mid-term, a 94 on the final, a 93 on problem sets, and 85 for participation. She has a unique...

Exercise 1: The example exercise is to work through a loan amortization example using Excel. Open...

Exercise 1: The example exercise is to work through a loan amortization example using Excel. Open Activity 3-Workbook. Go to Exercise 1 worksheet. The example loan conditions are (enter these values under Loan Terms): Loan amount borrowed (principal or pv) $100,000 Loan interest (rate) is 7.5% Loan term (number of payments or nper) is 9 years Annual payments of principal and interest Calculate the annual loan payment in cell C7 using the PMT function in...

Interpretation Use table 1 to answer the following questions. Interpret the adjusted odds ratios on table...

Interpretation Use table 1 to answer the following questions. Interpret the adjusted odds ratios on table 1 for post-secondary education, employment, and illicit drug use. (3 points) What variables were statistically significant? How do you know they were statistically significant? (2 points) Give one recommendation on what the researchers would have done differently. (2 points) TABLE 1 Variable Crude OR (95% CI) Adjusted ORa (95% CI) Sociodemographic factors Sex 1.33 (0.80–2.23) Postsecondary education 0.45 (0.27–0.76) 0.47 (0.28–0.80)b Employment 0.74 (0.43–1.27)...

Excel Assignment 2 Direction: Use Microsoft Excel to answer the following three questions (Excel Instructions are...

Excel Assignment 2 Direction: Use Microsoft Excel to answer the following three questions (Excel Instructions are provided in previous assignment) 1. An Economist was interested in sex differences in the number of books a person reads (see file: Number of Books). Two random samples were taken, one of men and the other, and the number of books read during the last month was recorded. Is there a difference with respect to the number of books read by females and males?...