In: Finance
Assume a coupon of 6%. You borrow $10 million from the bank and you make annual payments over 8 years. There is a balloon payment of $1,000,000 at the end of year 8. Determine annual interest and principal payment for each year. Please make sure your answer includes a separate statement as to the exact principal payment in year 8. How to do this in excel?
p=$10000000
r= 6%
n = year
Interset = (p*r*(1+r)^n)/(1+r)^(n-1)
Year | Principle | Interest | Total repayment |
1.00 | 10000000.00 | 636000.00 | 10636000.00 |
2.00 | 9364000.00 | 595550.40 | 9959550.40 |
3.00 | 8768449.60 | 557673.39 | 9326122.99 |
4.00 | 8210776.21 | 522205.37 | 8732981.57 |
5.00 | 7688570.84 | 488993.11 | 8177563.94 |
6.00 | 7199577.73 | 457893.14 | 7657470.88 |
7.00 | 6741684.59 | 428771.14 | 7170455.73 |
8.00 | 6312913.45 | 401501.30 | 6714414.75 |
Total Repayment = Principle+ Interest