Use the information for York Corporation to answer following question(s). Use an

Question

Use the information for York Corporation to answer following question(s). Use an Excel spreadsheet that shows all your work with step-by-step explanations. Make sure your final answer is clearly identified.

York is taking out a $10,000,000 two-year loan at a variable rate of LIBOR plus 1.50%. The LIBOR rate will be reset each year at an agreed upon date. The current LIBOR rate is 4.00% per year. The loan has an upfront fee of 1.00%

a) What is the all-in-cost (i.e., the internal rate of return) of the York loan including the LIBOR rate, fixed spread and upfront fee?

b) What portion of the cost of the loan is at risk of changing?

c) If the LIBOR rate jumps to 5.00% after the first year what will be the all-in-cost (i.e. the internal rate of return) for York for the entire loan?

d) If the LIBOR rate falls to 3.00% after the first year what will be the all-in-cost (i.e. the internal rate of return) for York for the entire loan?

Explanation / Answer

a. Statement showing cash flows @5% @7% 0 Loan    1,00,00,000.00                    1.0000    1,00,00,000.00 1 10000000 0 Upfront fee         -1,00,000.00                    1.0000         -1,00,000.00 1 -100000 1 Interest         -5,50,000.00                    0.9524         -5,23,809.52 0.934579 -514019 2 Interest         -5,50,000.00                    0.9070         -4,98,866.21 0.873439 -480391 2 Repayment -1,00,00,000.00                    0.9070      -90,70,294.78 0.873439 -8734387         -1,92,970.52 171202.7 Computation of IRR Rate @5% @7% NPV         -1,92,970.52 171202.7251 The below table shows the computation of IRR. If the rate increase by 2%, then the NPV decreases by 364173.25. Similarly x is the increase or decrease in rate by which the NPV reduces by 192970 to bring it down to zero Rate NPV Change 0.02         -3,64,173.25 x         -1,92,970.52 X = (-192970.52*.02)/364173.25 Base X IRR 5.00% 1.06% 6.06% b. The interest portion is at the risk of changing as it is marked to LIBOR plus spread c. Statement showing cash flows @5% @8% 0 Loan    1,00,00,000.00                    1.0000    1,00,00,000.00 1 10000000 0 Upfront fee         -1,00,000.00                    1.0000         -1,00,000.00 1 -100000 1 Interest @ 6.5%         -6,50,000.00                    0.9524         -6,19,047.62 0.925926 -601852 2 Interest @ 6.5%         -6,50,000.00                    0.9070         -5,89,569.16 0.857339 -557270 2 Repayment -1,00,00,000.00                    0.9070      -90,70,294.78 0.857339 -8573388         -3,78,911.56 167489.7 @5% @8%         -3,78,911.56 167489.7119 Rate NPV Change 0.03         -5,46,401.28 x         -3,78,911.56 X = (-378911.56*.03)/-564401.28 Base X IRR 5.00% 2.08% 7.08% d. Statement showing cash flows @5% @6% 0 Loan    1,00,00,000.00                    1.0000    1,00,00,000.00 1.0000 10000000 0 Upfront fee         -1,00,000.00                    1.0000         -1,00,000.00 1.0000 -100000 1 Interest @ 4.5%         -4,50,000.00                    0.9524         -4,28,571.43 0.9434 -424528 2 Interest @ 4.5%         -4,50,000.00                    0.9070         -4,08,163.27 0.8900 -400498 2 Repayment -1,00,00,000.00                    0.9070      -90,70,294.78 0.8900 -8899964               -7,029.48 175008.9 @5% @6%               -7,029.48 175008.9 Rate NPV Change 0.01         -1,82,038.38 x               -7,029.48 X = (-7029.48*.01)/-182038.38 Base X IRR 5.00% 0.04% 5.04%

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.