In: Finance
What is the amortization schedule for a mortgage of $1,600,000 in with 1). an initial ten year fixed rate of 6.5%; twenty year amortization (monthly); ballooning at the end of the tenth year; and 2) Second ten year rate "capped" at 7.5%; monthly amortization over the remaining ten years on the balance rolled-over from #1.
1) | |||||||||||
Pv | Amount of mortgage | $1,600,000 | |||||||||
Nper | Number of months of amortization | 240 | (20*12) | ||||||||
Rate | Monthly interest rate=(6.5/12)% | 0.005416667 | |||||||||
PMT | Monthly payment | $11,929.17 | (Using PMT function of excelwith Rate=0.00541667,Nper=240, Pv=-1600000) | ||||||||
Number of months of payment | 120 | (10*12) | |||||||||
A | Present Value of monthly payments | $1,050,584 | (Using PV function of excelwith Rate=0.00541667,Nper=120, Pmt=-11929.17) | ||||||||
P=Pv-A | Present value of Loan balance | $549,416 | |||||||||
FV | Baloon payment at the end of 10 year | $1,050,584.12 | (Using FV function of excelwith Rate=0.00541667,Nper=120, Pv=-549416) | ||||||||
2) | SECOND TEN YEAR AT 7.5% Monthly Amortization | ||||||||||
Pv | Loan amount at beging of the term | $1,050,584 | |||||||||
Rate | Monthly interest rate=(7.5/12)% | 0.00625 | |||||||||
Nper | Number of months of amortization | 120 | (10*12) | ||||||||
PMT | Monthly payment | $12,470.62 | (Using PMT function of excelwith Rate=0.00625,Nper=120, Pv=-1050584) | ||||||||
AMORTIZATION SCHEDULE 1 | |||||||||||
A | B | C=A*0.005416667 | D=B-C | E=A-D | |||||||
Month | Beginning balance | Total Payment | Interest | Principal | Ending balance | ||||||
1 | $1,600,000 | $11,929.17 | $8,666.67 | $3,262.50 | $1,596,737 | ||||||
2 | $1,596,737 | $11,929.17 | $8,649.00 | $3,280.17 | $1,593,457 | ||||||
3 | $1,593,457 | $11,929.17 | $8,631.23 | $3,297.94 | $1,590,159 | ||||||
4 | $1,590,159 | $11,929.17 | $8,613.36 | $3,315.81 | $1,586,844 | ||||||
5 | $1,586,844 | $11,929.17 | $8,595.40 | $3,333.77 | $1,583,510 | ||||||
6 | $1,583,510 | $11,929.17 | $8,577.35 | $3,351.82 | $1,580,158 |
|