In: Finance
Your company is planning to borrow $1.75 million on a 5-year, 12%, annual payment, fully amortized term loan.
The data has been collected in the Microsoft Excel Online file below. Open the spreadsheet and perform the required analysis to answer the question below.
Loan amount | $1,750,000 | ||||
Term in years | 5 | ||||
Annual coupon rate | 12.00% | ||||
Calculation of Loan Payment | Formula | ||||
Loan payment = | #N/A | ||||
Loan Amortization Schedule | |||||
Year | Beginning Balance | Payment | Interest | Principal | Ending Balance |
1 | |||||
2 | |||||
3 | |||||
4 | |||||
5 | |||||
Formulas | |||||
Loan Amortization Schedule | |||||
Year | Beginning Balance | Payment | Interest | Principal | Ending Balance |
1 | #N/A | #N/A | #N/A | #N/A | #N/A |
2 | #N/A | #N/A | #N/A | #N/A | #N/A |
3 | #N/A | #N/A | #N/A | #N/A | #N/A |
4 | #N/A | #N/A | #N/A | #N/A | #N/A |
5 | #N/A | #N/A | #N/A | #N/A | #N/A |
Percentage of 2nd Year Payment Representing Repayment of Principal |
What fraction of the payment made at the end of the second year will represent repayment of principal? Do not round intermediate calculations. Round your answer to two decimal places.
The complete loan amortisation schedule with formula is as shown below
Loan amount | 1750000.00 | ||||
Term in years | 5 | ||||
Annual coupon rate | 12.00% | ||||
Calculation of Loan Payment | Loan amount *Interest /(1-1/(1+interest rate)^term in years) | ||||
Loan payment = | 485467.03 | ||||
Loan Amortization Schedule | |||||
Year | Beginning Balance | Payment | Interest | Principal Repayment | Ending Balance |
1 | 1750000.00 | 485467.03 | 210000 | 275467.03 | 1474532.97 |
2 | 1474532.97 | 485467.03 | 176944 | 308523.07 | 1166009.89 |
3 | 1166009.89 | 485467.03 | 139921 | 345545.84 | 820464.05 |
4 | 820464.05 | 485467.03 | 98456 | 387011.34 | 433452.71 |
5 | 433452.71 | 485467.03 | 52014 | 433452.71 | 0.00 |
Formulas | Same as ending balance of previous year | same as loan payment calculated above | beginning balance * annual coupon rate | Payment - interest | Beginning balance - principal repayment |
In the second year , out of $485467.03 paid , $308523.07 is the principal repayment i.e.
308523.07/485467.03 = 0.635518 or 63.55%
0.6355 or 63.55% of the 2nd year's payment represent principal repayment in the 2nd year