Question

Part I – Original Mortgage:
When Jane and Patrick Baker were “house hunting” five years ago, the fixed rate on a 30- year mortgage was 4.90% APR (monthly compounding) while the 15-year fixed rate was at 3.25% APR (monthly compounding). After walking through many homes, they finally reached a consensus and decided to buy a $500,000 home.

The couple decided to put a 20% down payment in cash and take a loan for the remainder of the house price.   They chose the 30-year mortgage, despite the higher interest rate.

Part II – Refinance the Mortgage (pay off the old mortgage and open a new mortgage at a new rate):

         Currently, mortgage rates have come down and the refinancing frenzy in underway. Jane and Patrick have seen Bank of America advertise the 15-year fixed rates at 2.70% and 30-year fixed rates at 3.75%. The couple decided to refinance their loan over 15- years at 2.70%.

Follow the instructions below and answer all questions using formulas in excel.

1. Use the template provided below to generate cashflows and answer the below questions. In generating the cashflow scheduled you can mimic the example in your notes and book which outlines an amortizing loan.
   
2. What is Jane and Patrick’s monthly mortgage payment for the original 30-year mortgage
   (prior to the refinancing)? Use the =PMT() function in excel for this calculation, check your answer with calculating the Annuity Payment (see notes).

3. Construct a cash-flow amortization schedule in excel for the first five years of the loan using formulas. Ensure you use formulas to construct this schedule rather than typing in values. Leave all formulas in the cells for review.

4. During the first 5 years, how much in total cash has the couple paid towards the mortgage?
   What percentage of the cash was applied toward interest?
   What is the balance on their loan as of the end of the 5^th year?

5. Had the couple originally taken a 15-year mortgage rather than a 30-year mortgage, how much higher would their monthly payment be?
   What would be the balance on their loan be at the end of year 5 if they had opted for the 15-year mortgage?

6. What is Jane and Patrick’s monthly mortgage payment after refinancing their mortgage 5-years after they bought the house?

Answer 1

Part I

Original Mortgage

30 year rate	4.90%
15 year rate	3.25%
Home value	$500,000
Downpayment	20%
Downpayment value ($500,000*20%)	$100,000
Mortgage Loan taken ($500,000-$100,000)	$400,000

1. Jane and Patrick’s monthly mortgage payment for the original 30-year mortgage:

Tenure (in years)	30
Payments per year	12
Loan Amount	$400,000
Interest rate (per annum)	4.90%
Monthly mortgage payment = PMT(Interest rate/12,Tenure*Payments per year,-Loan Amount) = PMT(4.90%/12,30*12,-$400,000)	$2,123

2.  Construct a cash-flow amortization schedule in excel for the first five years of the loan using formulas:

Year	Month	Opening Principal (A)	Monthly Mortgage Payment (B)	Interest (Opening Principal * (4.9%/12) (C)	Principal (D) = (B) - (C)	Closing Principal (E) = (A)- (D)
1	1	400,000	2,123	1,633	490	399,510
1	2	399,510	2,123	1,631	492	399,019
1	3	399,019	2,123	1,629	494	398,525
1	4	398,525	2,123	1,627	496	398,030
1	5	398,030	2,123	1,625	498	397,532
1	6	397,532	2,123	1,623	500	397,032
1	7	397,032	2,123	1,621	502	396,531
1	8	396,531	2,123	1,619	504	396,027
1	9	396,027	2,123	1,617	506	395,521
1	10	395,521	2,123	1,615	508	395,013
1	11	395,013	2,123	1,613	510	394,503
1	12	394,503	2,123	1,611	512	393,991
2	13	393,991	2,123	1,609	514	393,477
2	14	393,477	2,123	1,607	516	392,961
2	15	392,961	2,123	1,605	518	392,443
2	16	392,443	2,123	1,602	520	391,922
2	17	391,922	2,123	1,600	523	391,400
2	18	391,400	2,123	1,598	525	390,875
2	19	390,875	2,123	1,596	527	390,348
2	20	390,348	2,123	1,594	529	389,819
2	21	389,819	2,123	1,592	531	389,288
2	22	389,288	2,123	1,590	533	388,755
2	23	388,755	2,123	1,587	535	388,219
2	24	388,219	2,123	1,585	538	387,682
3	25	387,682	2,123	1,583	540	387,142
3	26	387,142	2,123	1,581	542	386,600
3	27	386,600	2,123	1,579	544	386,055
3	28	386,055	2,123	1,576	547	385,509
3	29	385,509	2,123	1,574	549	384,960
3	30	384,960	2,123	1,572	551	384,409
3	31	384,409	2,123	1,570	553	383,856
3	32	383,856	2,123	1,567	555	383,300
3	33	383,300	2,123	1,565	558	382,743
3	34	382,743	2,123	1,563	560	382,183
3	35	382,183	2,123	1,561	562	381,620
3	36	381,620	2,123	1,558	565	381,056
4	37	381,056	2,123	1,556	567	380,489
4	38	380,489	2,123	1,554	569	379,919
4	39	379,919	2,123	1,551	572	379,348
4	40	379,348	2,123	1,549	574	378,774
4	41	378,774	2,123	1,547	576	378,198
4	42	378,198	2,123	1,544	579	377,619
4	43	377,619	2,123	1,542	581	377,038
4	44	377,038	2,123	1,540	583	376,455
4	45	376,455	2,123	1,537	586	375,869
4	46	375,869	2,123	1,535	588	375,281
4	47	375,281	2,123	1,532	591	374,691
4	48	374,691	2,123	1,530	593	374,098
5	49	374,098	2,123	1,528	595	373,502
5	50	373,502	2,123	1,525	598	372,904
5	51	372,904	2,123	1,523	600	372,304
5	52	372,304	2,123	1,520	603	371,702
5	53	371,702	2,123	1,518	605	371,096
5	54	371,096	2,123	1,515	608	370,489
5	55	370,489	2,123	1,513	610	369,879
5	56	369,879	2,123	1,510	613	369,266
5	57	369,266	2,123	1,508	615	368,651
5	58	368,651	2,123	1,505	618	368,034
5	59	368,034	2,123	1,503	620	367,413
5	60	367,413	2,123	1,500	623	366,791
Total			127,374	94,165	33,209

3. During the first 5 years, how much in total cash has the couple paid towards the mortgage?

The couple has totally paid $127,374 during the first 5 years ($94,165 as interest and $33,209 as principal)

4. What percentage of the cash was applied toward interest?

The couple has paid $94,165 interest out of total payment of $127,374 - thus, they have paid 73.93% ($94,165/$127,374) of cash as interest.

5. What is the balance on their loan as of the end of the 5th year?

Balance on their loan (closing principal) at the end of the 5th year is $366,791

6. Had the couple originally taken a 15-year mortgage rather than a 30-year mortgage, how much higher would their monthly payment be?

Tenure (in years)	15
Payments per year	12
Loan Amount	$400,000
Interest rate (per annum)	3.25%
Monthly mortgage payment = PMT(Interest rate/12,Tenure*Payments per year,-Loan Amount) = PMT(3.25%/12,15*12,-$400,000)	$2,811
Monthly Mortgage payment on 30 year loan	$2,123
Higher monthly mortgage payment	$688

7. What would be the balance on their loan be at the end of year 5 if they had opted for the 15-year mortgage?

Balance on thier loan (closing principal) at the end of the 5th year is $287,628

8. What is Jane and Patrick’s monthly mortgage payment after refinancing their mortgage 5-years after they bought the house?

Tenure (in years)	15
Payments per year	12
Loan Amount (Principal outstanding at the end of 5th year as per the Original loan of 30 years)	$366,791
Interest rate (per annum)	2.70%
Monthly mortgage payment = PMT(Interest rate/12,Tenure*Payments per year,-Loan Amount) = PMT(2.70%/12,15*12,-$366791)	$2,480