Question

In another scenario (not related to part a or b), let’s assume that you prefer the 10-year loan because you want to pay off the loan faster.   Now the bank also offers a 10-year variable-interest mortgage loan with the first 3 years locked with an APR of 3%. And after 3 years, the bank will use floating interest rate based on market condition. Somehow you believe that the floating interest rate is going to be within range of 1% to 10%, with 4.5% being the most likely number.
First, calculate the two separete amortization schedules for (a) first 3 years with fixed 3% APR; (b) the remaining 7 years with 4.5% APR.
Next, conduct a sensitivity analysis of how your monthly payment (PMT) and total interest payment for these 10 years are going to differ across different assumptions of APR for the 7 years.\

First, assuming that the APR is 3% throughout the 10 years, calculate the PMT:
For tB4:G23			Sensitivity Analysis Using Data -> What-if Analysis -> Data Table
APR =	3%			PMT for 7 years	Total Int Payment for 10 Years
Yeas-to-Maturity	10		APR
PV =	$    500,000.00		1%
Compounding Periods per Year	12		1.50%
PMT (quarterly)			2.00%
			2.50%
Total Int Payment =			3.00%
			3.50%
			4.00%
			4.50%
			5.00%
			5.50%
			6.00%
			6.50%
			7.00%
			7.50%
			8.00%
			8.50%
			9.00%
			9.50%
			10.00%

Answer 1

	Rate	Monthly   Interest =(3/`12)%	0.0025
	Pv	Amount of loan	$500,000
	Nper	Number of instalments in 10years	120	(10*12)
	PMT	Monthly payment for first three years	$4,828.04	(Using PMT function of excelwith Rate=0.0025,Nper=120,Pv=-500000,
		Present value of Monthly payment of 3 yeares:
	Pmt	Monthly payment for first three years	$4,828.04
	Nper	Number of instalments in 3 years	36	(12*3)
	Rate	Monthly interest	0.0025
		Present value of payment of 3 yeares:	$166,019.13	(Using PV function of excelwith Rate=0.0025,Nper=36,Pmt=-4828.04,
		Present value of outstanding loan after three years	$333,980.87	(500000-166019.13)
		Loan Outstanding after 3 years	$365,392.23	(Using FV function of excelwith Rate=0.0025,Nper=36,Pv=-333980.87,
		Monthly instalment for 7 years calculated using excel PMT function with Rate=R,Nper=84,Pv=-365392.23
		Number of insytalment payments in 7 years=7*12=84
		First, assuming that the APR is 3% throughout the 10 years, calculate the PMT:
		For tB4:G23			Sensitivity Analysis Using Data -> What-if Analysis -> Data Table
		APR =	3%			PMT for 7 years	Total Interest Payment for 10 Years	R	A=(PMT for 7 years)*84
		Yeas-to-Maturity	10		APR		A-365392.23+39201.57	Monthly Interest	Total payment for 7 years
		PV =	$    500,000.00		1%	$4,505.74	$52,291.65	0.000833	$378,482.31
		Compounding Periods per Year	12		1.50%	$4,584.99	$58,948.42	0.00125	$385,139.08
		PMT (monthly)	$4,828.04		2.00%	$4,665.12	$65,679.55	0.001667	$391,870.21
		Total Payment =4828*36	$173,809.34		2.50%	$4,746.14	$72,484.94	0.002083	$398,675.60
		Total Principal Payment=500000-365392.23	$134,607.77		3.00%	$4,828.04	$79,364.46	0.0025	$405,555.12
		Total Interest Payment in 3 years	$39,201.57		3.50%	$4,910.82	$86,317.97	0.002917	$412,508.63
					4.00%	$4,994.48	$93,345.29	0.003333	$419,535.95
					4.50%	$5,079.01	$100,446.26	0.00375	$426,636.92
					5.00%	$5,164.42	$107,620.67	0.004167	$433,811.33
					5.50%	$5,250.70	$114,868.30	0.004583	$441,058.96
					6.00%	$5,337.85	$122,188.93	0.005	$448,379.59
					6.50%	$5,425.87	$129,582.31	0.005417	$455,772.97
					7.00%	$5,514.75	$137,048.17	0.005833	$463,238.83
					7.50%	$5,604.49	$144,586.23	0.00625	$470,776.89
					8.00%	$5,695.08	$152,196.20	0.006667	$478,386.86
					8.50%	$5,786.53	$159,877.75	0.007083	$486,068.41
					9.00%	$5,878.82	$167,630.57	0.0075	$493,821.23
					9.50%	$5,971.96	$175,454.31	0.007917	$501,644.97
					10.00%	$6,065.94	$183,348.61	0.008333	$509,539.27