On the first tab build the full amortization table for a 30 year Interest Only (IO)...

On the first tab build the full amortization table for a 30 year Interest Only (IO) Loan with a 5.5% interest rate compounded monthly. The initial loan amount should be $5,000,000. In Excel

Expert Solution

	P =	Regular Payments
	PV =	Loan Amount
	r =	rate of interest
	n =	no of periods

	P =	r (PV)
		1 - (1 + r )-n

	P =	(5.5%/12)*5000000
		1 - (1 / (1 + 5.5%/12)^360))

	P =	22916.66667
		0.807224748

	P =	28389.45

	Beginning Balance	Interest	Principal	Ending Balance
1	$5,000,000.00	$22,916.67	$5,472.78	$4,994,527.22
2	$4,994,527.22	$22,891.58	$5,497.87	$4,989,029.35
3	$4,989,029.35	$22,866.38	$5,523.07	$4,983,506.28
4	$4,983,506.28	$22,841.07	$5,548.38	$4,977,957.90
5	$4,977,957.90	$22,815.64	$5,573.81	$4,972,384.09
6	$4,972,384.09	$22,790.09	$5,599.36	$4,966,784.74
7	$4,966,784.74	$22,764.43	$5,625.02	$4,961,159.72
8	$4,961,159.72	$22,738.65	$5,650.80	$4,955,508.92
9	$4,955,508.92	$22,712.75	$5,676.70	$4,949,832.22
10	$4,949,832.22	$22,686.73	$5,702.72	$4,944,129.50
11	$4,944,129.50	$22,660.59	$5,728.86	$4,938,400.64
12	$4,938,400.64	$22,634.34	$5,755.11	$4,932,645.53
Year #1 End
13	$4,932,645.53	$22,607.96	$5,781.49	$4,926,864.04
14	$4,926,864.04	$22,581.46	$5,807.99	$4,921,056.05
15	$4,921,056.05	$22,554.84	$5,834.61	$4,915,221.44
16	$4,915,221.44	$22,528.10	$5,861.35	$4,909,360.08
17	$4,909,360.08	$22,501.23	$5,888.22	$4,903,471.87
18	$4,903,471.87	$22,474.25	$5,915.20	$4,897,556.66
19	$4,897,556.66	$22,447.13	$5,942.32	$4,891,614.35
20	$4,891,614.35	$22,419.90	$5,969.55	$4,885,644.80
21	$4,885,644.80	$22,392.54	$5,996.91	$4,879,647.89
22	$4,879,647.89	$22,365.05	$6,024.40	$4,873,623.49
23	$4,873,623.49	$22,337.44	$6,052.01	$4,867,571.48
24	$4,867,571.48	$22,309.70	$6,079.75	$4,861,491.73
Year #2 End
25	$4,861,491.73	$22,281.84	$6,107.61	$4,855,384.12
26	$4,855,384.12	$22,253.84	$6,135.61	$4,849,248.51
27	$4,849,248.51	$22,225.72	$6,163.73	$4,843,084.79
28	$4,843,084.79	$22,197.47	$6,191.98	$4,836,892.81
29	$4,836,892.81	$22,169.09	$6,220.36	$4,830,672.45
30	$4,830,672.45	$22,140.58	$6,248.87	$4,824,423.58
31	$4,824,423.58	$22,111.94	$6,277.51	$4,818,146.07
32	$4,818,146.07	$22,083.17	$6,306.28	$4,811,839.79
33	$4,811,839.79	$22,054.27	$6,335.18	$4,805,504.61
34	$4,805,504.61	$22,025.23	$6,364.22	$4,799,140.39
35	$4,799,140.39	$21,996.06	$6,393.39	$4,792,747.00
36	$4,792,747.00	$21,966.76	$6,422.69	$4,786,324.30
Year #3 End
37	$4,786,324.30	$21,937.32	$6,452.13	$4,779,872.17
38	$4,779,872.17	$21,907.75	$6,481.70	$4,773,390.47
39	$4,773,390.47	$21,878.04	$6,511.41	$4,766,879.06
40	$4,766,879.06	$21,848.20	$6,541.25	$4,760,337.81
41	$4,760,337.81	$21,818.21	$6,571.24	$4,753,766.57
42	$4,753,766.57	$21,788.10	$6,601.35	$4,747,165.22
43	$4,747,165.22	$21,757.84	$6,631.61	$4,740,533.61
44	$4,740,533.61	$21,727.45	$6,662.00	$4,733,871.60
45	$4,733,871.60	$21,696.91	$6,692.54	$4,727,179.07
46	$4,727,179.07	$21,666.24	$6,723.21	$4,720,455.85
47	$4,720,455.85	$21,635.42	$6,754.03	$4,713,701.83
48	$4,713,701.83	$21,604.47	$6,784.98	$4,706,916.84
Year #4 End
49	$4,706,916.84	$21,573.37	$6,816.08	$4,700,100.76
50	$4,700,100.76	$21,542.13	$6,847.32	$4,693,253.44
51	$4,693,253.44	$21,510.74	$6,878.71	$4,686,374.73
52	$4,686,374.73	$21,479.22	$6,910.23	$4,679,464.50
53	$4,679,464.50	$21,447.55	$6,941.90	$4,672,522.60
54	$4,672,522.60	$21,415.73	$6,973.72	$4,665,548.88
55	$4,665,548.88	$21,383.77	$7,005.68	$4,658,543.19
56	$4,658,543.19	$21,351.66	$7,037.79	$4,651,505.40
57	$4,651,505.40	$21,319.40	$7,070.05	$4,644,435.35
58	$4,644,435.35	$21,287.00	$7,102.45	$4,637,332.89
59	$4,637,332.89	$21,254.44	$7,135.01	$4,630,197.88
60	$4,630,197.88	$21,221.74	$7,167.71	$4,623,030.17
Year #5 End

Due to length constraint only this much data can be provided.

jeff jeffy answered 3 weeks ago

On the third tab build the full amortization table for a 30 year Constant Payment Mortgage...

On the third tab build the full amortization table for a 30 year Constant Payment Mortgage (CPM) Loan with a 4.5% interest rate compounded monthly. The initial loan amount should be $2,500,000. in excel

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

Assume the effective interest method in accounting for these bonds. Build a 10 year amortization table....

Assume the effective interest method in accounting for these bonds. Build a 10 year amortization table. Bond $650,000 Mature 10 years State annual interest 9.25% Effective annual rate 6% Interest is paid every 6 months

Complete the first 4 months of an amortization table for the 30 year loan. Include payment...

Complete the first 4 months of an amortization table for the 30 year loan. Include payment number, amount applied to interest, amount applied to principal, balance, and equity (%) as columns in your table. Repeat for the 15 year loan given in the same exercises.

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

Your 30 year mortgage is $350,000 with an interest rate of 4%. Prepare the amortization schedule...

Your 30 year mortgage is $350,000 with an interest rate of 4%. Prepare the amortization schedule for repayment of the mortgage

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?

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.

A full amortization schedule for a note that is payed monthly (Interest and principle). The time...

A full amortization schedule for a note that is payed monthly (Interest and principle). The time frame is 5 years=60 months, the Interest Rate=5%, the Note is for $75,000.

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

Question