Please show the formula you used in Excel. Thank you.
A borrower takes out a fully amortizing loan for $500,000. The term of the loan is 20 years, bearing interest of 2% over CPI, adjusting annually and compounding monthly. At the start of the term, CPI is 10%. After one year, the CPI falls to 4%. After that, the CPI will fall further to 2%, and it will remain at 2% through year 8. At year 9, the CPI will rise to 6%, and it will remain at 6% through year 14. At year 15, CPI will fall to 4%, and it will remain at 4% for the rest of the term.
Fill in the rest of this spreadsheet:
Year Margin Index APR Compounding N x M PMT Balance
0 $500,000.00 2% 10% 12% 240 -$5,505.43
1 $493,589.87 2% 4% 12 228
2 ? 2% 2% 12 216
3 ? 2% 2% 12 204
4 ? 2% 2% 12 192
5 ? 2% 2% 12 180
6 ? 2% 2% 12 168
7 ? 2% 2% 12 156
8 ? 2% 2% 12 144
9 ? 2% 6% 12 132
10 ? 2% 6% 12 120
11 ? 2% 6% 12 108
12 ? 2% 6% 12 96
13 ? 2% 6% 12 84
14 ? 2% 6% 12 72
15 ? 2% 4% 12 60
16 ? 2% 4% 12 48
17 ? 2% 4% 12 36
18 ? 2% 4% 12 24
19 ? 2% 4% 12 12
20 ? 2% 4% 12