Question

Month	housing index
0	183.28
1	187.11
2	191.62
3	196.02
4	200.47
5	202.31
6	204.82
7	207.20
8	209.31
9	210.72
10	212.70
11	214.13
12	215.51
13	216.41
14	217.40
15	218.82
16	219.29
17	218.82
18	218.03
19	216.55
20	214.98
21	214.31
22	213.56
23	213.37
24	213.27
25	213.16
26	214.62
27	214.88
28	215.32
29	213.53
30	210.78
31	207.97
32	205.57
33	204.52
34	203.11
35	199.65
36	194.70
37	190.00
38	186.64
39	178.85
40	172.55
41	166.74
42	162.56
43	158.50
44	154.10
45	148.16
46	142.83
47	137.59
48	134.68
49	130.64
50	126.66
51	123.96
52	121.08
53	120.18
54	119.87
55	123.34
56	126.15
57	129.24
58	131.55
59	134.03
60	136.12
61	137.03
62	138.50
63	139.29
64	140.93
65	141.74
66	141.40
67	140.53
68	139.85
69	139.18
70	138.88
71	137.24
72	136.98
73	136.91
74	136.54
75	134.74
76	133.72
77	133.53
78	133.20
79	134.00
80	134.60

use the index to determine the value of the condo each month.

An investor bought a $120,000 condo by putting 20% down and borrowing the rest using a 15 year mortgage with an annual rate of 4.8%. ignore the opportunity cost of the down payment to the investor. Calculate the monthly payments. Three years later she sold the condo, what was her return on her investment?( think IRR or Rate) What percentage of her equity at the time of the sale was due to the change in the value of the condo?

create a data table with the return on investment as the output. the column input is the down payment percent ,going from 0% to 20% in increments of 5%. the row input is the mortgage rate, going from 4% to 6% in increments of 0.5%. Discuss your findings from the data table. what price would she have to sell the condo after three years in order to make a return of 0.5% a month?

Answer 1

As per the PMT formula, monthly payment = $ 749.2.

The table below gives the calculation for return on investment. Second row gives the payment outgo. The rate at which the future value (at 36 months) is equal to the property price is the return on investment. This works out to -0.43% per month. Thus, the investor is making a negative return on the investment.

Month	0	1	2	----	35		36	36
Payment outgo	24,000.0	749.2	749.2	----	749.2		749.2	81,889.4	Final payment
FV after 36 months	20,550.8	644.3	647.1	----	746.0		749.2	81,889.4
Total FV (Sum of previous row)					127,477.0
Index change	194.7/183.28	1.062309
Change in property price	1.062*120,000	127,477.1
Monthly rate at which future value of payment outgo equal the future property value = - 0.43%

Return at different down payment	4%
0%	-1.19%
5%	-0.77%
10%	-0.52%
15%	-0.36%
20%	-0.25%