Question

For this case assume:

You have located a home that you wish to purchase and wish to evaluate bank financing options in order to determine your budget. You’ve been working with a few banks on potential mortgage terms and wish to determine for yourself the payment schedule, monthly payment, and most importantly, just how much interest you will pay over the life of the mortgage. You also want to run a few scenarios to determine which is the best option for you.
To complete this exercise, you will use multiple aspects of Excel. These include:

1. Setting up and formatting a loan payment (amortization) schedule
2. Using the “absolute cell reference” and “autofill” features in Excel
3. Using the formula function PMT to calculate monthly payment based on a given interest rate and length of load
4. Copy a worksheet and adjust given values to answer various “what-if” scenarios.

The home you wish to purchase is listed at $379,900 and you expect that the seller will accept $370,000. You have saved $20,000 as a down payment and are evaluating mortgages for $350,000 from a couple of banks. Here are the mortgage terms:
1. 30-year, fixed mortgage, 3.15%
2. 15-year fixed mortgage, 2.85%

Case requirements:
Using Excel:
1. Create a loan amortization schedule for both loan options for the life of the loan. Determine the monthly payment, and the $ amount of interest and principal paid monthly. Use the format as illustrated in the text, table 8-2 and below as your guide in setting up the schedule.

FIGURE 8-2 Amortization Table for a $150,000 Loan, 6 Percent Annual Interest Rate, 10 year Term

Loan Amortization Schedule

Amount Borrowed: $150,000
Interest Rate: 6.0%
Term: 10 years
Required Payments: $20,380.19 (found using equation 8-4a)

	Col1	Col 2	Col 3 (Col 1 x.06)	Col 4 (Col 2 - Col 3)	Col 5 (Col 1 - Col 4)
YEAR	Beginning Balance	Total payment	Payment Interest	Payment Principal	Ending Balance

2. Note the following:
a. In the above image, to calculate the monthly payment, you can use the PMT formula.
b. Also, to determine interest and principal portions of the monthly payment, you can use the Excel functions IPMT and PPMT formulas.
c. You can also solve for payment using
i. Business calculator
ii. Algebraic equation
d. To easily complete the amortization tables, use the following Excel functions:
i. Absolute cell reference
ii. Autofill
Once the amortization schedules are completed, answer the following questions:
1. What is the total amount of $ interest paid for each mortgage for the entire 15 and 30 year period?
2. Evaluate the pros and cons of the 15-year versus 30-year mortgage
3. You’ve learned that making one extra payment per year can result in less interest paid as well as paying off the mortgage sooner. One can do this by making half the monthly payment every 2 weeks, which results in essentially one extra monthly payment per year.
a. Using the 15-year mortgage amortization schedule, create a new amortization schedule using a bi-monthly payment (26 payments per year)
i. Hint: use the worksheet copy function to create a copy of the first worksheet,
ii. Then adjust the interest rate and number of payments cells for a bi-monthly payment
b. What is the $ savings in interest and how many months of payments do you save as compared to the 15-year monthly amortization?
Provide a detailed summary of the loan analysis, including the pros and cons of the two mortgages. Include what you learned from the exercise, both from a finance and budget perspective as well as interest expense perspective.

Answer 1

1.Having known the PV of the mortgage as 350000

we need to use the formula , to find the PV of ordinary annuity

to find the monthly pmt

PV=Pmt.*(1-(1+r)^-n)/r

where, PV= 350000

Pmt.=the mthly pmt. To be found out---- ??

r= the monthly interest rate, 3.15%/12 or 2.85%/12

n= no.of mthly. Pmts.--- 30 yrs.*12 mths.=360   or 15 yrs. *12= 180

so, plugging in the respective values,

we find the mthly. Pmts. By solving algebraically,

pmt.=350000/((1-(1+(3.15%/12))^-360)/(3.15%/12)))

1504.08

For the 15-yr. mortgage

pmt.=350000/((1-(1+(2.85%/12))^-180)/(2.85%/12)))

2391.87

To answer the questions----
Summarising the above:	30 Yr.	15 yr.
1.Monthly pmts.	1504.08	2391.87
2.No.of mths.	360	180
3.Total amt. paid(Principal +int.)(1*2)	541468.80	430536.60
4.Original Principal	350000	350000
5.Interest paid	191468.80	80536.60

2.Pros & Cons:

30-Yr.

Pros:

Mthly pmts. Are comparatively small & hence may not be that demanding on the pocket.

Cons:

Need to pay higher total interest , as the principal repayment is delayed.

15 Yr.

Pros:

Total interest paid is less, as also the the amt. claimed as deductible annual interest expense for tax purposes , is more, creating more cash , in the immediate time period--than the 30-yr. option.

Cons:

High mthly pmts.may be demanding on the pocket.

3.a.For the 15-yr. mortgage--- Bi-monthly pmts., ie. 15*26=390 pmts.

at r= 2.85%/26

pmt.=350000/((1-(1+(2.85%/26))^-390)/(2.85%/26)))

1103.37

Total interest paid=(1103.37*390)-350000=

80314.30

b. $ savings in interest
	Total interest
15-yr mthly amortisation	80536.6
Interest as above	80314.3
$ interest saved	222.3

No.of months of payments saved as compared to the 15-year monthly amortisation

(15*12)-(390/26*12)=

0

In the above case, the no. of pmts. Have been calculated to fit the 15 yr schedule----so only the quantum / payments has come down---no.of months remain same.

Alternately,
If the same $ amt. of 2391.87 is paid bi-monthly,
then ,the no.of months of pmts. Will be
Repeating the above calculations,
For the 15-yr. mortgage--- Bi-monthly pmts.--- n--- to find--??
at r= 2.85%/26
350000=2391.87*((1-(1+(2.85%/26))^-n)/(2.85%/26)))
Solving for n, no.of pmts.= 159.58
ie. 159.58/26=6.14 yrs. Approximately
ie. 6.14*12= 74 mths . Approximately
Total interest paid=(2391.87*160)-350000=
32699.20
b. $ savings in interest
	Total interest
15-yr mthly amortisation	80536.6
Interest as above	32699.2
$ interest saved	47837.4

Thus , amounts paid , early in the  mortgage, saves interest , as it also reduces the no.of payments.

30-Yr	Col.1	Col.2	Col.1*3.15%/12	Col.4=Col.2-Col.3	Col.5=Col.1-Col.4		15-Yr	Col.1	Col.2	Col.1*2.85%/12	Col.4=Col.2-Col.3	Col.5=Col.1-Col.4		15-Yr	Col.1	Col.2	Col.1*2.85%/26	Col.4=Col.2-Col.3	Col.5=Col.1-Col.4
Mth.	Beg.bal.	Total pmt.	Pmt.Int.	Pmt.-Prin.	Ending bal.		Mth.	Beg.bal.	Total pmt.	Pmt.Int.	Pmt.-Prin.	Ending bal.		Mth.	Beg.bal.	Total pmt.	Pmt.Int.	Pmt.-Prin.	Ending bal.
0					350000		0					350000		0					350000
1	350000	-1504.08	-918.75	-585.33	349414.67		1	350000	-2391.87	-831.25	-1560.62	348439.38		1	350000	-1103.37	-383.65	-719.72	349280.28
2	349414.7	-1504.08	-917.21	-586.87	348827.81		2	348439	-2391.87	-827.54	-1564.32	346875.06		2	349280.3	-1103.37	-382.86	-720.51	348559.77
3	348827.8	-1504.08	-915.67	-588.41	348239.40		3	346875	-2391.87	-823.83	-1568.04	345307.02		3	348559.8	-1103.37	-382.08	-721.30	347838.47
4	348239.4	-1504.08	-914.13	-589.95	347649.45		4	345307	-2391.87	-820.10	-1571.76	343735.26		4	347838.5	-1103.37	-381.28	-722.09	347116.38
5	347649.4	-1504.08	-912.58	-591.50	347057.95		5	343735	-2391.87	-816.37	-1575.49	342159.77		5	347116.4	-1103.37	-380.49	-722.88	346393.50
6	347057.9	-1504.08	-911.03	-593.05	346464.90		6	342160	-2391.87	-812.63	-1579.24	340580.53		6	346393.5	-1103.37	-379.70	-723.67	345669.83
7	346464.9	-1504.08	-909.47	-594.61	345870.29		7	340581	-2391.87	-808.88	-1582.99	338997.54		7	345669.8	-1103.37	-378.91	-724.47	344945.37
8	345870.3	-1504.08	-907.91	-596.17	345274.12		8	338998	-2391.87	-805.12	-1586.75	337410.79		8	344945.4	-1103.37	-378.11	-725.26	344220.11
9	345274.1	-1504.08	-906.34	-597.73	344676.38		9	337411	-2391.87	-801.35	-1590.52	335820.28		9	344220.1	-1103.37	-377.32	-726.06	343494.05
10	344676.4	-1504.08	-904.78	-599.30	344077.08		10	335820	-2391.87	-797.57	-1594.29	334225.99		10	343494.1	-1103.37	-376.52	-726.85	342767.20
11	344077.1	-1504.08	-903.20	-600.88	343476.20		11	334226	-2391.87	-793.79	-1598.08	332627.91		11	342767.2	-1103.37	-375.73	-727.65	342039.55
12	343476.2	-1504.08	-901.63	-602.45	342873.75		12	332628	-2391.87	-789.99	-1601.87	331026.03		12	342039.6	-1103.37	-374.93	-728.45	341311.11
13	342873.8	-1504.08	-900.04	-604.04	342269.71		13	331026	-2391.87	-786.19	-1605.68	329420.35		13	341311.1	-1103.37	-374.13	-729.24	340581.86
14	342269.7	-1504.08	-898.46	-605.62	341664.09		14	329420	-2391.87	-782.37	-1609.49	327810.86		14	340581.9	-1103.37	-373.33	-730.04	339851.82
15	341664.1	-1504.08	-896.87	-607.21	341056.88		15	327811	-2391.87	-778.55	-1613.32	326197.54		15	339851.8	-1103.37	-372.53	-730.84	339120.98