Question

The MacDonalds are at the bank in order to negotiate a loan to buy a farm where they can raise cows, horses, chickens sheep and plant spaghetti trees. They have managed to save $400,000 over the last 10 years that they can use as a down payment. The MacDonalds would like to finance their purchase over 25 years making monthly payments. According to their financial planning schedule, they will clear $7,500 a month and would like their mortgage payments to be at most 30 percent of their monthly earnings. The bank is offering a 25 year amortized mortgage loan at 5.25 percent with a 5 year term.

1. Calculate the maximum price the MacDonalds can pay for their farm.
2. Prepare an amortization table for the first 5 months of the MacDonalds’ mortgage.
3. At the end of the 5 year term of their mortgage, how much will the MacDonalds owe on their loan?

Answer 1

1.

=7500*30%/(5.25%/12)*(1-1/(1+5.25%/12)^(12*25))+400000=$775,470.77

2.

Payment	Loan beginning balance	Payment	Interest payment	Principal payment	Loan ending balance
1	375470.77	$2,250.00	$1,642.68	$607.32	$374,863.45
2	$374,863.45	$2,250.00	$1,640.03	$609.97	$374,253.48
3	$374,253.48	$2,250.00	$1,637.36	$612.64	$373,640.84
4	$373,640.84	$2,250.00	$1,634.68	$615.32	$373,025.52
5	$373,025.52	$2,250.00	$1,631.99	$618.01	$372,407.51
6	$372,407.51	$2,250.00	$1,629.28	$620.72	$371,786.79
7	$371,786.79	$2,250.00	$1,626.57	$623.43	$371,163.36
8	$371,163.36	$2,250.00	$1,623.84	$626.16	$370,537.20
9	$370,537.20	$2,250.00	$1,621.10	$628.90	$369,908.30
10	$369,908.30	$2,250.00	$1,618.35	$631.65	$369,276.65
11	$369,276.65	$2,250.00	$1,615.59	$634.41	$368,642.23
12	$368,642.23	$2,250.00	$1,612.81	$637.19	$368,005.04
13	$368,005.04	$2,250.00	$1,610.02	$639.98	$367,365.06
14	$367,365.06	$2,250.00	$1,607.22	$642.78	$366,722.28
15	$366,722.28	$2,250.00	$1,604.41	$645.59	$366,076.69
16	$366,076.69	$2,250.00	$1,601.59	$648.41	$365,428.28
17	$365,428.28	$2,250.00	$1,598.75	$651.25	$364,777.03
18	$364,777.03	$2,250.00	$1,595.90	$654.10	$364,122.93
19	$364,122.93	$2,250.00	$1,593.04	$656.96	$363,465.97
20	$363,465.97	$2,250.00	$1,590.16	$659.84	$362,806.13
21	$362,806.13	$2,250.00	$1,587.28	$662.72	$362,143.41
22	$362,143.41	$2,250.00	$1,584.38	$665.62	$361,477.78
23	$361,477.78	$2,250.00	$1,581.47	$668.53	$360,809.25
24	$360,809.25	$2,250.00	$1,578.54	$671.46	$360,137.79
25	$360,137.79	$2,250.00	$1,575.60	$674.40	$359,463.39
26	$359,463.39	$2,250.00	$1,572.65	$677.35	$358,786.04
27	$358,786.04	$2,250.00	$1,569.69	$680.31	$358,105.73
28	$358,105.73	$2,250.00	$1,566.71	$683.29	$357,422.45
29	$357,422.45	$2,250.00	$1,563.72	$686.28	$356,736.17
30	$356,736.17	$2,250.00	$1,560.72	$689.28	$356,046.89
31	$356,046.89	$2,250.00	$1,557.71	$692.29	$355,354.60
32	$355,354.60	$2,250.00	$1,554.68	$695.32	$354,659.27
33	$354,659.27	$2,250.00	$1,551.63	$698.37	$353,960.91
34	$353,960.91	$2,250.00	$1,548.58	$701.42	$353,259.48
35	$353,259.48	$2,250.00	$1,545.51	$704.49	$352,555.00
36	$352,555.00	$2,250.00	$1,542.43	$707.57	$351,847.42
37	$351,847.42	$2,250.00	$1,539.33	$710.67	$351,136.76
38	$351,136.76	$2,250.00	$1,536.22	$713.78	$350,422.98
39	$350,422.98	$2,250.00	$1,533.10	$716.90	$349,706.08
40	$349,706.08	$2,250.00	$1,529.96	$720.04	$348,986.04
41	$348,986.04	$2,250.00	$1,526.81	$723.19	$348,262.86
42	$348,262.86	$2,250.00	$1,523.65	$726.35	$347,536.51
43	$347,536.51	$2,250.00	$1,520.47	$729.53	$346,806.98
44	$346,806.98	$2,250.00	$1,517.28	$732.72	$346,074.26
45	$346,074.26	$2,250.00	$1,514.07	$735.93	$345,338.34
46	$345,338.34	$2,250.00	$1,510.86	$739.14	$344,599.19
47	$344,599.19	$2,250.00	$1,507.62	$742.38	$343,856.81
48	$343,856.81	$2,250.00	$1,504.37	$745.63	$343,111.19
49	$343,111.19	$2,250.00	$1,501.11	$748.89	$342,362.30
50	$342,362.30	$2,250.00	$1,497.84	$752.16	$341,610.13
51	$341,610.13	$2,250.00	$1,494.54	$755.46	$340,854.68
52	$340,854.68	$2,250.00	$1,491.24	$758.76	$340,095.92
53	$340,095.92	$2,250.00	$1,487.92	$762.08	$339,333.83
54	$339,333.83	$2,250.00	$1,484.59	$765.41	$338,568.42
55	$338,568.42	$2,250.00	$1,481.24	$768.76	$337,799.66
56	$337,799.66	$2,250.00	$1,477.87	$772.13	$337,027.53
57	$337,027.53	$2,250.00	$1,474.50	$775.50	$336,252.03
58	$336,252.03	$2,250.00	$1,471.10	$778.90	$335,473.13
59	$335,473.13	$2,250.00	$1,467.69	$782.31	$334,690.82
60	$334,690.82	$2,250.00	$1,464.27	$785.73	$333,905.10

3.

333905.10