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.\

After the first 3 years, we have:
APR =	4.50%
Yeas-to-Maturity	7
PV =	$                -	=ending balance at end of Y3
Compounding Periods per Year	12
PMT (quarterly)		= the monthly payment from Y4 to Y10
Year	Month	Beginning Balance	Total Payment	Interest Payment	Principal Payment	Ending Balance
4	37
4	38
4	39
4	40
4	41
4	42

Although the month as given in the above table extends to 117 (currently but I just need to know the excel formula.Filling these table will be enough.

Answer 1

Assuming the Loan taken to $1,00,000.00
Nper	10 years	or 120 months
Rate	3%	0.25%	monthly rate
Annuity	₹ 965.61	formula used =pmt(rate,nper,pv,,0)
Rate we have used monthly rate to calculate Annuity / PMT
PV= -100000.00
Loan Balance at the end of 36 months is $73,078.35
Balance Nper		84 months
Rate	4.50%	0.38%	monthly rate
Annuity	₹ 1,015.80	formula used =pmt(rate,nper,pv,,0)
Rate we have used monthly rate to calculate Annuity / PMT
PV= -73078.35

Note: The variables derivation
1. Opening Balance = In month 1 is our assumed loan borrowed and for following months the ending balance of previous month.
2. Interest = Opening Balance * Monthly Int Rate
3. Principal = Annuity -Interest
4. Annuity = PMT(Rate,nper,pv,,0) basically for first 3 years it is $ 965.61 and for last 7 years it is $1015.80. This is shown above.
5. Ending Balance = Opening Balance - Principal
6. Monthly Rate = APR / 12 for first 3 years it is 0.25% and last 7 years it is 0.38%

Month	Opening Balance	Interest	Principal	Annuity / Total Payment	Ending Balance	Monthly Int Rate
37	73078.35	274.04	741.76	1015.80	72336.59	0.38%
38	72336.59	271.26	744.54	1015.80	71592.06	0.38%
39	71592.06	268.47	747.33	1015.80	70844.73	0.38%
40	70844.73	265.67	750.13	1015.80	70094.59	0.38%
41	70094.59	262.85	752.95	1015.80	69341.65	0.38%
42	69341.65	260.03	755.77	1015.80	68585.88	0.38%