In Excel, create the amortization table for a 15-year mortgage for $950,000 at 4.2% interest. Individual...

In Excel, create the amortization table for a 15-year mortgage for $950,000 at 4.2% interest.

Individual taxpayers may be able to deduct the amount of interest that they paid on a mortgage for a personal residence. If the mortgage was issued on May 1, how much interest will be earned in the first year?

How much interest will be earned in the second year?

Upload your Excel file below.

Expert Solution

Individual taxpayers able to deduct the interest that they paid on mortgage. Interest saved in first year is $39000 and in second year is $77832.58

PMT NO	PAYMENT DATE	BEGINNING BALANCE	SCHEDULED PAYMENT	EXTRA PAYMENT	TOTAL PAYMENT	PRINCIPAL	INTEREST	ENDING BALANCE	CUMULATIVE INTEREST
1	6/28/2019	$950,000.00	$86,643.26	$100.00	$86,743.26	$46,843.26	$39,900.00	$903,156.74	$39,900.00
2	7/28/2019	$903,156.74	$86,643.26	$100.00	$86,743.26	$48,810.68	$37,932.58	$854,346.06	$77,832.58
3	8/28/2019	$854,346.06	$86,643.26	$100.00	$86,743.26	$50,860.73	$35,882.53	$803,485.34	$113,715.12
4	9/28/2019	$803,485.34	$86,643.26	$100.00	$86,743.26	$52,996.88	$33,746.38	$750,488.46	$147,461.50
5	10/28/2019	$750,488.46	$86,643.26	$100.00	$86,743.26	$55,222.75	$31,520.52	$695,265.71	$178,982.02
6	11/28/2019	$695,265.71	$86,643.26	$100.00	$86,743.26	$57,542.10	$29,201.16	$637,723.61	$208,183.18
7	12/28/2019	$637,723.61	$86,643.26	$100.00	$86,743.26	$59,958.87	$26,784.39	$577,764.75	$234,967.57
8	1/28/2020	$577,764.75	$86,643.26	$100.00	$86,743.26	$62,477.14	$24,266.12	$515,287.60	$259,233.69
9	2/28/2020	$515,287.60	$86,643.26	$100.00	$86,743.26	$65,101.18	$21,642.08	$450,186.42	$280,875.77
10	3/28/2020	$450,186.42	$86,643.26	$100.00	$86,743.26	$67,835.43	$18,907.83	$382,350.99	$299,783.60
11	4/28/2020	$382,350.99	$86,643.26	$100.00	$86,743.26	$70,684.52	$16,058.74	$311,666.47	$315,842.34
12	5/28/2020	$311,666.47	$86,643.26	$100.00	$86,743.26	$73,653.27	$13,089.99	$238,013.21	$328,932.33
13	6/28/2020	$238,013.21	$86,643.26	$100.00	$86,743.26	$76,746.71	$9,996.55	$161,266.50	$338,928.89
14	7/28/2020	$161,266.50	$86,643.26	$100.00	$86,743.26	$79,970.07	$6,773.19	$81,296.43	$345,702.08
15	8/28/2020	$81,296.43	$86,643.26	$0.00	$81,296.43	$77,881.98	$3,414.45	$0.00	$349,116.53

Rahul Sunny answered 1 year ago

Excel, create the amortization table for a 15-year mortgage for $950,000 at 4.2% interest. Individual taxpayers...

Excel, create the amortization table for a 15-year mortgage for $950,000 at 4.2% interest. Individual taxpayers may be able to deduct the amount of interest that they paid on a mortgage for a personal residence. If the mortgage was issued on May 1, how much interest will be earned in the first year? How much interest will be earned in the second year? Upload your Excel file below. I dont know how to create an ammortization in excel. It is...

In Excel, create the amortization table for a 15-year mortgage for $780,000 at 6.6% interest. Individual...

In Excel, create the amortization table for a 15-year mortgage for $780,000 at 6.6% interest. Individual taxpayers may be able to deduct the amount of interest that they paid on a mortgage for a personal residence. If the mortgage was issued on July 1, how much interest will be earned in the first year? How much interest will be earned in the second year?

Create an amortization table for the following mortgage. a. Mortgage amount is $300,000. b. The mortgage...

Create an amortization table for the following mortgage. a. Mortgage amount is $300,000. b. The mortgage is a 30 year fixed at 4% (paid monthly). c. You pay an additional $100 per month until the mortgage is paid. When will the mortgage be paid off? Please show in Excel along with formulas.

On the second tab build the full amortization table for a 15 year Constant Amortizing Mortgage...

On the second tab build the full amortization table for a 15 year Constant Amortizing Mortgage (CAM) Loan with a 6% interest rate compounded monthly. The initial loan amount should be $7,500,000. in excel

Please explain, Excel assignment. Prepare an amortization table for a 30-year mortgage where the homeowner is...

Please explain, Excel assignment. Prepare an amortization table for a 30-year mortgage where the homeowner is borrowing $170,000 at a 3.75% interest rate. In addition to the monthly table, provide a summary table showing the interest paid, principal paid, and ending balance on a yearly basis. Create three separate graphs illustrating interest paid over time, principal paid over time, and ending balance over time for the 30 annual periods in the summary table. Repeat the analysis, changing the interest rate...

Excel assignment Prepare an amortization table for a 30-year mortgage where the homeowner is borrowing $170,000...

Excel assignment Prepare an amortization table for a 30-year mortgage where the homeowner is borrowing $170,000 at a 3.75% interest rate. In addition to the monthly table, provide a summary table showing the interest paid, principal paid, and ending balance on a yearly basis. Create three separate graphs illustrating interest paid over time, principal paid over time, and ending balance over time for the 30 annual periods in the summary table. Repeat the analysis, changing the interest rate to 8.75%...

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...

Create a loan amortization table for a $100,000 2 year loan at 4.875% annual interest payable...

Create a loan amortization table for a $100,000 2 year loan at 4.875% annual interest payable semi-annually: a) Calculate the payment amount. b) Do the loan amortization table. c) What is the journal entry to receive the third payment?

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

1) Prepare an amortization table for a 30-year mortgage where the homeowner is borrowing $170,000 at...

1) Prepare an amortization table for a 30-year mortgage where the homeowner is borrowing $170,000 at a 3.75% interest rate. In addition to the monthly table, provide a summary table showing the interest paid, principal paid, and ending balance on a yearly basis. Create three separate graphs illustrating interest paid over time, principal paid over time, and ending balance over time for the 30 annual periods in the summary table. 2) Repeat the analysis, changing the interest rate to 8.75%...

Question