Question

To buy a $160,000​house, you take out a 6​% ​(APR compounded​ monthly) mortgage for $130,000. Five years​ later, you sell the house for $195,000 ​(after all other selling​ expenses). What equity​ (the amount that you can keep before​ tax) would you realize with a 30​-year repayment​ term? Note: For tax​ purpose, do not consider the time value of money on​ $30,000 down payment made five years ago.

The realized equity will be $____ thousand?

Answer 1

Creating Amortization Table in excel

Total cost	160000
Down Payment	30000
Amount of loan	130000
interest rate	6.00%
time (yrs)	30
Monthly payment	779.42
Total amount paid	2,80,589.65
Total interest paid	1,50,589.65
Month	Beginning Balance	Monthly Payment	Monthly Interest	Principal Amount	Extra Pricipal paid	Ending Balance
1	130000	779.42	650.00	129.42	0.00	1,29,870.58
2	1,29,870.58	779.42	649.35	130.06	0.00	1,29,740.52
3	1,29,740.52	779.42	648.70	130.71	0.00	1,29,609.81
4	1,29,609.81	779.42	648.05	131.37	0.00	1,29,478.44
5	1,29,478.44	779.42	647.39	132.02	0.00	1,29,346.42
6	1,29,346.42	779.42	646.73	132.68	0.00	1,29,213.73
7	1,29,213.73	779.42	646.07	133.35	0.00	1,29,080.39
8	1,29,080.39	779.42	645.40	134.01	0.00	1,28,946.37
9	1,28,946.37	779.42	644.73	134.68	0.00	1,28,811.69
10	1,28,811.69	779.42	644.06	135.36	0.00	1,28,676.33
11	1,28,676.33	779.42	643.38	136.03	0.00	1,28,540.30
12	1,28,540.30	779.42	642.70	136.71	0.00	1,28,403.58
13	1,28,403.58	779.42	642.02	137.40	0.00	1,28,266.19
14	1,28,266.19	779.42	641.33	138.08	0.00	1,28,128.10

59	1,21,317.19	779.42	606.59	172.83	0.00	1,21,144.36
60	1,21,144.36	779.42	605.72	173.69	0.00	1,20,970.66

359	1,547.22	779.42	7.74	771.68	0.00	775.54
360	775.54	779.42	3.88	775.54	0.00	0.00

We see from the table that after 60th payment (5 years) total principal outstanding is 120970.66

House sold for 195000 after five years

The amount that we can keep = value of house sold - principal left to be paid to the bank

= 195000 - 120970.66

= 74029.34

Showing formula in excel

Total cost	160000
Down Payment	30000
Amount of loan	=B1-B2
interest rate	0.06
time (yrs)	30
Monthly payment	=PMT(B4/12, B5*12,-B3)
Total amount paid	=B7*B5*12
Total interest paid	=B10-B3
Month	Beginning Balance	Monthly Payment	Monthly Interest	Principal Amount	Extra Priciple paid	Ending Balance
1	=B3	=$B$7	=B14*($B$4/12)	=C14-D14	0	=B14-E14-F14
2	=G14	=$B$7	=B15*($B$4/12)	=C15-D15	0	=B15-E15-F15
3	=G15	=$B$7	=B16*($B$4/12)	=C16-D16	0	=B16-E16-F16
4	=G16	=$B$7	=B17*($B$4/12)	=C17-D17	0	=B17-E17-F17
5	=G17	=$B$7	=B18*($B$4/12)	=C18-D18	0	=B18-E18-F18
6	=G18	=$B$7	=B19*($B$4/12)	=C19-D19	0	=B19-E19-F19

I have shown just a few starting columns, you can drag down in excel and excel will take the formula automatically