Question

Say that you purchase a house for $248,000 by getting a mortgage for $220,000 and paying a $28,000 down payment. If you get a 30-year mortgage with a 8 percent interest rate, what are the monthly payments? (Do not round intermediate calculations and round your final answer to 2 decimal places.)

What would the loan balance be in ten years? (Round the payment amount to the nearest cent but do not round any other interim calculations. Round your final answer to 2 decimal places.)

If the house appreciates at 4 percent per year, what will be the value of the house in ten years? (Do not round intermediate calculations and round your final answer to 2 decimal places.)

How much of this value is your equity? (Do not round intermediate calculations and round your final answer to 2 decimal places.) Equity $

Answer 1

a) Cost of House = $248000, Down payment = 28000, Loan = 220000, Interest rate = 8% p.a. , Loan Tenure = 30 year = 30 x 12 months = 360 months

Monthly rate = annual rate / 12 = 8%/12

We will pmt function in excel to find the monthly payment

Formula to be used in excel: =pmt(rate,nper,-pv)

                                          =pmt(8%/12,360,-220000)

Calculating Monthly payments
Loan (pv)	220000
Monthly rate (rate)(	8%/12
No of months (nper)	360
Monthly payment (pmt)	1614.28.

Using pmt function in excel, we get monthly payment = 1614.28

b) To find the loan balance after 10 years,we need to develop an amortization schedule

Interest for a month = Beginning balance x monthly interest rate = Beginning balance x (8%/12)

Principal for the month = Monthly payment - interest for the month

Ending balance of a month = Beginning balance of a month - principal for the month

Opening balance for a month = Ending balance for previous month

For example in 1st month

Beginning balance = 220000, interest = 220000 x (8%/12) = 1466.67, Principal = 1614.28 -1466.67 = 147.61 Ending balance = 220000 - 147.61 = 219852.39

After which Beginning balance of 2nd month = 219852.39

Similarly amortization schedule can be found out for other months

Amortization schedule is shown for first ten months

Month	Beginning Balance	Monthly Payment	Interest	Principal	Ending Balance
1	220000	1614.28	1466.67	147.61	219852.39
2	219852.39	1614.28	1465.68	148.60	219703.79
3	219703.79	1614.28	1464.69	149.59	219554.20
4	219554.20	1614.28	1463.69	150.59	219403.62
5	219403.62	1614.28	1462.69	151.59	219252.03
6	219252.03	1614.28	1461.68	152.60	219099.43
7	219099.43	1614.28	1460.66	153.62	218945.81
8	218945.81	1614.28	1459.64	154.64	218791.17
9	218791.17	1614.28	1458.61	155.67	218635.50
10	218635.50	1614.28	1457.57	156.71	218478.79

Continuing in this way we we can get amortization schedule for other months and amortization schedule for month 110 to 120 is shown below

Month	Beginning Balance	Monthly Payment	Interest	Principal	Ending Balance
110	196458.76	1614.28	1309.73	304.55	196154.21
111	196154.21	1614.28	1307.69	306.59	195847.62
112	195847.62	1614.28	1305.65	308.63	195538.99
113	195538.99	1614.28	1303.59	310.69	195228.31
114	195228.31	1614.28	1301.52	312.76	194915.55
115	194915.55	1614.28	1299.44	314.84	194600.71
116	194600.71	1614.28	1297.34	316.94	194283.76
117	194283.76	1614.28	1295.23	319.05	193964.71
118	193964.71	1614.28	1293.10	321.18	193643.53
119	193643.53	1614.28	1290.96	323.32	193320.20
120	193320.20	1614.28	1288.80	325.48	192994.73

Balance at end of ten years = Balance at end of 120 months = 192994.73

c) If the house appreciates at 4% per year

Then value of property after 10 years = Cost of house x (1 + 4%)¹⁰ = 248000 x (1+4%)¹⁰ = 248000 x(1.04)¹⁰ = 367100.58

d) Value of equity after 10 years = Value of property after - Loan outstanding after 10 years = 367100.58 - 192994.73 = 174105.85