Question

Select a home for your parents or a relative that you can imagine buying on Jan 1. They have asked you to create a amortization table on excel assuming a 20% down payment, 30 year fixed rate of 5%. Make sure to include spinners so you can calculate your payment and how much interest you will pay with 5%, 6% and 7% with a 20 year loan. Include the excel spreadsheet. Determine the payment for the 5% 30 year loan, 5% 20 year loan and a 20 year 6% and 7% loan. In year 1, they want to know the total interest paid for each of the 5 scenarios. Also, provide the outstanding balance due for each scenarios at the end of the first year. What are your recommendations

Answer 1

Let the home price be $100,000. So down payment = 20% of $100,000 = $20,000

Thus loan amount = $100,000-$20,000 = $80,000

(I am assuming that payments are made monthly as is the norm in case of home loans)

The annual payment can be computed using the "PMT" function in excel. So for 5% and 30 years (or 360 months) we have PMT (5%/12, 360,80000). This gives a value of $429.46

The amortization table is shown below:

1	80,000.00	429.46	333.33	79,903.88
2	79,903.88	429.46	332.93	79,807.35
3	79,807.35	429.46	332.53	79,710.42
4	79,710.42	429.46	332.13	79,613.09
5	79,613.09	429.46	331.72	79,515.36
6	79,515.36	429.46	331.31	79,417.21
7	79,417.21	429.46	330.91	79,318.66
8	79,318.66	429.46	330.49	79,219.70
9	79,219.70	429.46	330.08	79,120.32
10	79,120.32	429.46	329.67	79,020.54
11	79,020.54	429.46	329.25	78,920.33
12	78,920.33	429.46	328.83	78,819.71
13	78,819.71	429.46	328.42	78,718.67
358	1,277.71	429.46	5.32	853.58
359	853.58	429.46	3.56	427.68
360	427.68	429.46	1.78	0.00
		Total interest	74,604.63

The summary for different scenarios is shown below (all computed using the PMT function):

	Description	Total interest paid	Outstanding balance at the end of year 1
Scenario 1:	30 years, 5%	74,604.63	78,819.71
Scenario 2:	20 years, 5%	46,711.50	77,813.89
Scenario 3:	20 years, 6%	57,554.76	77,864.16
Scenario 4:	20 years, 7%	68,857.40	78,096.84
Scenario 5:	30 years, 6%	92,670.55	79,017.59

My recommendation is to go for 20 years loan at 5% as it leads to minimum interest expense of $46,711.50