Question

You are comparing different loan rates and terms on a home purchase. The inputs are the price, percent down payment, annual interest rate and length of the loan (in months).

Price	275,000
Down Pmt	20%
Interest rate	4%
Terms (months)	360

Given the above, calculate the loan amount, the monthly payment, total cost (the down payment and the total amount of principle and interest paid).

Do scenario analysis (via a data tables) as follows:

Vary the down payment from 10% to 40% in 5% increments. Show the effect on total cost and total interest paid in a single table.

Vary the interest rate from 1% to 10% in 1% increments. Show the effect on total cost and total interest paid in a single table.

Vary the term from 180 months to 360 months in increments of 12 and vary the cost from $225, 000 to $425,000 in increments of $50,000. Show the effect on total cost in a single table.

Please show how to do this using Excel functions.

Answer 1

A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
2
3		Effect of Change in Down payments
4
5		Price			275000	275000	275000	275000	275000	275000	275000
6		Down payment			10%	15%	20%	25%	30%	35%	40%
7		Interest Rate			4%	4%	4%	4%	4%	4%	4%
8		Terms (Months)			360	360	360	360	360	360	360
9		Down payment		=E5*E6	27500	41250	55000	68750	82500	96250	110000
10		Loan Amount		=E5-E9	247500	233750	220000	206250	192500	178750	165000
11		Monthlypayments		-PMT(E7,E8,E10)	9900.01	9350.01	8800.01	8250.01	7700.01	7150.01	6600.00
12		Interest + Principle		=E11*E8	3564002.6	3366002.5	3168002.3	2970002.2	2772002	2574001.9	2376001.8
13		Total cost (Down payment + Interest + Principle)		=E12+E9	3591502.6	3407252.5	3223002.3	3038752.2	2854502	2670251.9	2486001.8
14		Total Interest Paid		=E12-E10	3316502.6	3132252.5	2948002.3	2763752.2	2579502	2395251.9	2211001.8
15
16
17		Effect of Change in Interest Rates
18
19		Price			275000	275000	275000	275000	275000	275000	275000	275000	275000	275000
20		Down payment			20%	20%	20%	20%	20%	20%	20%	20%	20%	20%
21		Interest Rate			1%	2%	3%	4%	5%	6%	7%	8%	9%	10%
22		Terms (Months)			360	360	360	360	360	360	360	360	360	360
23		Down payment		=E19*E20	55000	55000	55000	55000	55000	55000	55000	55000	55000	55000
24		Loan Amount		=E19-E23	220000	220000	220000	220000	220000	220000	220000	220000	220000	220000
25		Monthlypayments		-PMT(E21,E22,E24)	2262.95	4403.53	6600.16	8800.01	11000.00	13200.00	15400.00	17600.00	19800.00	22000.00
26		Interest + Principle		=E25*E22	814661.18	1585270.7	2376056.8	3168002.3	3960000.1	4752000	5544000	6336000	7128000	7920000
27		Total cost (Down payment + Interest + Principle)		=E26+E23	869661.18	1640270.7	2431056.8	3223002.3	4015000.1	4807000	5599000	6391000	7183000	7975000
28		Total Interest Paid		=E26-E24	594661.18	1365270.7	2156056.8	2948002.3	3740000.1	4532000	5324000	6116000	6908000	7700000
29
30		Effect of Change in Terms
31
32		Price			275000	275000	275000	275000	275000	275000	275000	275000	275000	275000	275000	275000	275000	275000	275000	275000
33		Down payment			10%	15%	20%	25%	30%	35%	40%	40%	40%	40%	40%	40%	40%	40%	40%	40%
34		Interest Rate			4%	4%	4%	4%	4%	4%	4%	4%	4%	4%	4%	4%	4%	4%	4%	4%
35		Terms (Months)			180	192	204	216	228	240	252	264	276	288	300	312	324	336	348	360
36		Down payment		=E32*E33	27500	41250	55000	68750	82500	96250	110000	110000	110000	110000	110000	110000	110000	110000	110000	110000
37		Loan Amount		=E32-E36	247500	233750	220000	206250	192500	178750	165000	165000	165000	165000	165000	165000	165000	165000	165000	165000
38		Monthlypayments		=-PMT(E34,E35,E37)	9908.51	9355.02	8802.95	8251.73	7701.01	7150.58	6600.34	6600.21	6600.13	6600.08	6600.05	6600.03	6600.02	6600.01	6600.01	6600.00
39		Interest + Principle		=E38*E35	1783532.1	1796163.7	1795801.8	1782373.1	1755829.6	1716140.1	1663284.8	1742455.5	1821636.2	1900823.6	1980015	2059210	2138406	2217604	2296803	2376002
40		Total cost (Down payment + Interest + Principle)		=E39+E36	1811032.1	1837413.7	1850801.8	1851123.1	1838329.6	1812390.1	1773284.8	1852455.5	1931636.2	2010823.6	2090015	2169210	2248406	2327604	2406803	2486002
41		Total Interest Paid		=E39-E37	1536032.1	1562413.7	1575801.8	1576123.1	1563329.6	1537390.1	1498284.8	1577455.5	1656636.2	1735823.6	1815015	1894210	1973406	2052604	2131803	2211002
42
43		Effect of Change in Cost (Price)
44
45		Price			225000	275000	325000	375000	425000
46		Down payment			20%	20%	20%	20%	20%
47		Interest Rate			1%	2%	3%	4%	5%
48		Terms (Months)			360	360	360	360	360
49		Down payment		=E45*E46	45000	55000	65000	75000	85000
50		Loan Amount		=E45-E49	180000	220000	260000	300000	340000
51		Monthlypayments		=-PMT(E47,E48,E50)	1851.50	4403.53	7800.19	12000.01	17000.00
52		Interest + Principle		=E51*E48	666540.96	1585270.7	2808067.1	4320003.2	6120000.1
53		Total cost (Down payment + Interest + Principle)		=E52+E49	711540.96	1640270.7	2873067.1	4395003.2	6205000.1
54		Total Interest Paid		=E52-E50	486540.96	1365270.7	2548067.1	4020003.2	5780000.1
55
56		Just Make 1st Table and then copy the Table and make Relevant changes such as Cost, Term, Interest Rates
57		For 1st Table write the inputs 1st and then put formulas in 1st column and copy the Cells in subsequent columns and Make Changes in the required row (in table 1 it is down payment)
58		Please provide feedback…. Thanks in advance…. :-)
59