Question

In: Accounting

John Doe has just been offered a home loan towards purchase of house that is being...

John Doe has just been offered a home loan towards purchase of house that is being sold for

$230,000.

He will be required to make a

15%

down payment, as well as mortgage processing fees and closing costs of

$3,000.

The loan has to be paid off in monthly payments over a 30-year period at a fixed interest rate of

6%

per year compounded monthly. He will also be required to pay an additional

$92

per month as mortgage insurance. Using Excel, answer the following questions:

(a) The monthly mortgage payment is

(enter as a positive number to the nearest dollar) (b) The total monthly payment is

(enter as a positive number to the nearest dollar)(c) The nominal APR is

(to the nearest 2 decimal places) The effective APR is

(to the nearest 2 decimal places)(d) Over the 30-year period, the total amount of interest paid on the loan is

(enter as a positive number to the nearest dollar).(e) The interest amount in the month

60

payment is

(enter as a positive number to the nearest dollar) The principal amount in the month

60

payment is

(enter as a positive number to the nearest dollar)(f) The balance on the loan immediately after making the payment at the end of month

60

is

(enter as a positive number to the nearest dollar)

Expert Solution

House being sold at $230,000

Down payment - 15%

Mortgage processing fees and closing costs of $3,000

Fixed interest rate of 6% per year compounded monthly.

Additional $92 per month as mortgage insurance i.e

230000- 15% = $195,500 - $3000 = $192,500 *6% = 11550/12 = 962.5 + 92 (monthly)= 1054.5 = 12654

i.e 12654 *30 = 379,620

House being sold		2,30,000
Loan Amount	192500
Interest	6%
Additional Insurance per month	92

Down Payment	15%
Fees	3000
Monthly	0.005
No of yr	30

Year	Opening Balance	Payment	Interest	Principal	Loan Balance
1	192500.00	1154.13	962.50	191.63	192308.37
2	192308.37	1154.13	961.54	192.59	192115.77
3	192115.77	1154.13	960.58	193.56	191922.22
4	191922.22	1154.13	959.61	194.52	191727.69
5	191727.69	1154.13	958.64	195.50	191532.20
6	191532.20	1154.13	957.66	196.47	191335.72
7	191335.72	1154.13	956.68	197.46	191138.27
8	191138.27	1154.13	955.69	198.44	190939.82
9	190939.82	1154.13	954.70	199.44	190740.39
10	190740.39	1154.13	953.70	200.43	190539.95
11	190539.95	1154.13	952.70	201.43	190338.52
12	190338.52	1154.13	951.69	202.44	190136.08
13	190136.08	1154.13	950.68	203.45	189932.62
14	189932.62	1154.13	949.66	204.47	189728.15
15	189728.15	1154.13	948.64	205.49	189522.66
16	189522.66	1154.13	947.61	206.52	189316.14
17	189316.14	1154.13	946.58	207.55	189108.58
18	189108.58	1154.13	945.54	208.59	188899.99
19	188899.99	1154.13	944.50	209.63	188690.36
20	188690.36	1154.13	943.45	210.68	188479.67
21	188479.67	1154.13	942.40	211.74	188267.94
22	188267.94	1154.13	941.34	212.80	188055.14
23	188055.14	1154.13	940.28	213.86	187841.28
24	187841.28	1154.13	939.21	214.93	187626.35
25	187626.35	1154.13	938.13	216.00	187410.35
26	187410.35	1154.13	937.05	217.08	187193.27
27	187193.27	1154.13	935.97	218.17	186975.10
28	186975.10	1154.13	934.88	219.26	186755.84
29	186755.84	1154.13	933.78	220.36	186535.48
30	186535.48	1154.13	932.68	221.46	186314.03
31	186314.03	1154.13	931.57	222.56	186091.46
32	186091.46	1154.13	930.46	223.68	185867.78
33	185867.78	1154.13	929.34	224.80	185642.99
34	185642.99	1154.13	928.21	225.92	185417.07
35	185417.07	1154.13	927.09	227.05	185190.02
36	185190.02	1154.13	925.95	228.18	184961.83
37	184961.83	1154.13	924.81	229.33	184732.51
38	184732.51	1154.13	923.66	230.47	184502.04
39	184502.04	1154.13	922.51	231.62	184270.41
40	184270.41	1154.13	921.35	232.78	184037.63
41	184037.63	1154.13	920.19	233.95	183803.68
42	183803.68	1154.13	919.02	235.12	183568.57
43	183568.57	1154.13	917.84	236.29	183332.27
44	183332.27	1154.13	916.66	237.47	183094.80
45	183094.80	1154.13	915.47	238.66	182856.14
46	182856.14	1154.13	914.28	239.85	182616.29
47	182616.29	1154.13	913.08	241.05	182375.23
48	182375.23	1154.13	911.88	242.26	182132.97
49	182132.97	1154.13	910.66	243.47	181889.50
50	181889.50	1154.13	909.45	244.69	181644.82
51	181644.82	1154.13	908.22	245.91	181398.91
52	181398.91	1154.13	906.99	247.14	181151.77
53	181151.77	1154.13	905.76	248.38	180903.39
54	180903.39	1154.13	904.52	249.62	180653.77
55	180653.77	1154.13	903.27	250.87	180402.91
56	180402.91	1154.13	902.01	252.12	180150.79
57	180150.79	1154.13	900.75	253.38	179897.41
58	179897.41	1154.13	899.49	254.65	179642.76
59	179642.76	1154.13	898.21	255.92	179386.84
60	179386.84	1154.13	896.93	257.20	179129.64
61	179129.64	1154.13	895.65	258.49	178871.15
62	178871.15	1154.13	894.36	259.78	178611.37
63	178611.37	1154.13	893.06	261.08	178350.29
64	178350.29	1154.13	891.75	262.38	178087.91
65	178087.91	1154.13	890.44	263.70	177824.21
66	177824.21	1154.13	889.12	265.01	177559.20
67	177559.20	1154.13	887.80	266.34	177292.86
68	177292.86	1154.13	886.46	267.67	177025.19
69	177025.19	1154.13	885.13	269.01	176756.18
70	176756.18	1154.13	883.78	270.35	176485.83
71	176485.83	1154.13	882.43	271.71	176214.12
72	176214.12	1154.13	881.07	273.06	175941.06
73	175941.06	1154.13	879.71	274.43	175666.63
74	175666.63	1154.13	878.33	275.80	175390.83
75	175390.83	1154.13	876.95	277.18	175113.65
76	175113.65	1154.13	875.57	278.57	174835.08
77	174835.08	1154.13	874.18	279.96	174555.12
78	174555.12	1154.13	872.78	281.36	174273.76
79	174273.76	1154.13	871.37	282.77	173991.00
80	173991.00	1154.13	869.95	284.18	173706.82
81	173706.82	1154.13	868.53	285.60	173421.22
82	173421.22	1154.13	867.11	287.03	173134.19
83	173134.19	1154.13	865.67	288.46	172845.72
84	172845.72	1154.13	864.23	289.91	172555.82
85	172555.82	1154.13	862.78	291.36	172264.46
86	172264.46	1154.13	861.32	292.81	171971.65
87	171971.65	1154.13	859.86	294.28	171677.37
88	171677.37	1154.13	858.39	295.75	171381.63
89	171381.63	1154.13	856.91	297.23	171084.40
90	171084.40	1154.13	855.42	298.71	170785.69
91	170785.69	1154.13	853.93	300.21	170485.48
92	170485.48	1154.13	852.43	301.71	170183.77
93	170183.77	1154.13	850.92	303.22	169880.56
94	169880.56	1154.13	849.40	304.73	169575.82
95	169575.82	1154.13	847.88	306.26	169269.57
96	169269.57	1154.13	846.35	307.79	168961.78
97	168961.78	1154.13	844.81	309.33	168652.46
98	168652.46	1154.13	843.26	310.87	168341.58
99	168341.58	1154.13	841.71	312.43	168029.16
100	168029.16	1154.13	840.15	313.99	167715.17
101	167715.17	1154.13	838.58	315.56	167399.61
102	167399.61	1154.13	837.00	317.14	167082.47
103	167082.47	1154.13	835.41	318.72	166763.75
104	166763.75	1154.13	833.82	320.32	166443.43
105	166443.43	1154.13	832.22	321.92	166121.52
106	166121.52	1154.13	830.61	323.53	165797.99
107	165797.99	1154.13	828.99	325.14	165472.84
108	165472.84	1154.13	827.36	326.77	165146.07
109	165146.07	1154.13	825.73	328.40	164817.67
110	164817.67	1154.13	824.09	330.05	164487.62
and so on

a. The monthly mortgage payment is $1154.13

b. The total monthly payment is $415488.51

c.The nominal APR is 13%

d.Over the 30-year period, the total amount of interest paid on the loan is $222988.51

e.The interest amount in the month 60 payment is $55877.72

f.The balance on the loan immediately after making the payment at the end of month 60 is $179129.64

ekkarill92 answered 1 month ago

Jane has been offered a Price Level Adjusted Mortgage Loan of 100,000 to purchase a small...

Jane has been offered a Price Level Adjusted Mortgage Loan of 100,000 to purchase a small bar. The loan is to be amortized over 25 years, with annual payments. The rate is j1 = 4%. Annual inflation rates are as follows: Year 1-3 4% Year 4-6 3% (a) What is the adjusted OSB at the end of 3rd year?

John decides to purchase a home from Mary. Mary has John move in, pay $10,000 as...

John decides to purchase a home from Mary. Mary has John move in, pay $10,000 as a down payment, and John makes payments of $1500 per month, split between principal and interest. This is not put in writing. John pays the monthly payments for 8 months. Then, in writing, Mary sells her home to Cynthia, who pays in cash, and wants to move in. Mary tries to kick John out of the home, saying that he is only renting and...

Loan rates of interest Personal Finance Problem John Flemming has been shopping for a loan to...

Loan rates of interest Personal Finance Problem John Flemming has been shopping for a loan to finance the purchase of a used car. He has found three possibilities that seem attractive and wishes to select the one with the lowest interest rate. The information available with respect to each of the three $7,000 loans is shown in the following table, Each loan requires John to make one payment at the end of each year. Loan Principal Annual payment Term (years)...

In public school there has been a new contract system that is being offered to teachers,...

In public school there has been a new contract system that is being offered to teachers, they now have the choice to give up life contracts in exchange for increased pay, in this new system they get short term contracts that are renewed by employees based on performance. What asymmetric information problem can occur if only some school distrcts implement this program and others don’t. Use charts if needed

Michael Jordan has just arranged to purchase an $825,000vacation home in the Bahamas with a...

Michael Jordan has just arranged to purchase an $825,000 vacation home in the Bahamas with a 20% down-payment. The loan has an interest rate of 5.4% (compounded monthly) and a 30-year term. If the value of the home is expected to appreciate 2% per year, what will his equity in the house be in 8 year?

Brooks Bright (BB) has applied for a loan of $800,000 to purchase a residential house from...

Brooks Bright (BB) has applied for a loan of $800,000 to purchase a residential house from Distonic Bank. BB has the option of either paying the loan on a flat fixed rated interest or floating rate based on the declining balance of the loan. On the flat fixed interest rate pricing, Distonic Bank will charge a rate of 5 % per annum. The pricing on the floating rate based on the declining balance will be Base Lending Rate (BLR) plus...

A) A bond with 30 detachable warrants has just been offered for sale at $1,000. The...

A) A bond with 30 detachable warrants has just been offered for sale at $1,000. The bond matures in 20 years and has an annual coupon of $66. Each warrant gives the owner the right to purchase two shares of stock in the company at $56 per share. Ordinary bonds (with no warrants) of similar quality are priced to yield 9 percent. What is the value of one warrant? (Do not round intermediate calculations and round your answer to 2...

Sarah secured a bank loan of $155,000 for the purchase of a house. The mortgage is...

Sarah secured a bank loan of $155,000 for the purchase of a house. The mortgage is to be amortized through monthly payments for a term of 15 years, with an interest rate of 3%/year compounded monthly on the unpaid balance. She plans to sell her house in 5 years. How much will Sarah still owe on her house? (Round your answer to the nearest cent.)

Sarah secured a bank loan of $185,000 for the purchase of a house. The mortgage is...

Sarah secured a bank loan of $185,000 for the purchase of a house. The mortgage is to be amortized through monthly payments for a term of 15 years, with an interest rate of 3%/year compounded monthly on the unpaid balance. She plans to sell her house in 10 years. How much will Sarah still owe on her house at that time? (Round your answer to the nearest cent.) $____

You are considering the purchase of a new home offered at a price of $225,000. Create...

You are considering the purchase of a new home offered at a price of $225,000. Create an amortization table in a new workbook that shows how much interest and principal you will pay each month for the duration of the loan. The following is a list of assumptions and requirements you need to consider for this assignment: You will be making a down payment of 20% on the home. The bank will offer you a loan at an annual interest...