Question

A borrower arranged a 250,000,000 credit line with a bank. It drew down:

• 20% for the first quarter of the year
• An additional 20% for the second quarter
• The remaining for the second half.

              Assume the interest rate was 3% for the first quarter, and 3.2% for the rest of the year.

              The unused credit line fee was 0.2%.

              Ignore day counts for this question; that is, just use ¼ for each three month period.

1. What is the interest amount and fee amount for each quarter?
2. What is the effective “all in” cost of the borrowing? Do this by tallying up the average amount actually borrowed over the course of the year. Then calculate the total amount paid to the bank, and divide.

*To be considered on the first day of quarter

Answer 1

A)

I am attaching two snap-shots of the excel sheet containing the solution. First Snapshot shows final value obtained and second snapshot shows "formulas used in the calculation"

Total Value of Credit Line 250,000,000.00 Particulars Amount drawn during first Quarter Amount drawn during second Quarter Amount drawn in second half 50,000,000.00 50,000,000.00 150,000,000.00 20.0% 20.0% 60.0% Cumulative amount drawn upto first Quarter Cumulative amount drawn upto Second Quarter Cumulative amount drawn upto third Quarter Cumulative amount drawn upto fourth Quarter 50,000,000.00 100,000,000.00 250,000,000.00 250,000,000.00 Interest rate for first Quarter Interest rate for the remaining year Unused credit line fee 3% 3.20% 0.20% Interest amount for first Quarter Unused credit line fee for first Quarter 375,000.00 100,000.00 Interest amount for second Quarter Unused credit line fee for second Quarter 800,000.00 75,000.00 Interest amount for third Quarter Interest amount for fourth Quarter 2,000,000.00 2,000,000.00

Total Value of Credit Line 250000000 Particulars Amount drawn during first Quarter Amount drawn during second Quarter Amount drawn in second half =C1*D4 =C1*D5 =C1-C4+C5) 0.2 0.2 =100%-D4-D5 Cumulative amount drawn upto first Quarter Cumulative amount drawn upto Second Quarter Cumulative amount drawn upto third Quarter Cumulative amount drawn upto fourth Quarter =C4 =C4+C5 =C4+C5+C6 =C4+C5+C6 Interest rate for first Quarter Interest rate for the remaining year Unused credit line fee 0.03 0.032 0.002 Interest amount for first Quarter Unused credit line fee for first Quarter =C13*C4*1/4 =(C15)*(C1-C8)*1/4 Interest amount for second Quarter Unused credit line fee for second Quarter =(C14*09)*1/4 =(C15)*(C1-C9)*1/4 Interest amount for third Quarter Interest amount for fourth Quarter =(C14*C10)*1/4 =(C14*011)*1/4

Note: There will not be any "Unused credit line fee" for third & fourth quarter, as full amount was drawn at the starting of second half.

B)

Attaching two excel snapshots, first showing final values obtained and second showing formulas used:-

Cumulative amount drawn upto first Quarter 50,000,000.00 Cumulative amount drawn upto Second Quarter100,000,000.00 Cumulative amount drawn upto third Quarter 250,000,000.00 Cumulative amount drawn upto fourth Quarter 250,000,000.00 Average amount drawn upto four Quarters 162,500,000.00 Interest amount for first Quarter Interest amount for second Quarter Interest amount for third Quarter Interest amount for fourth Quarter 475,000.00 875,000.00 2,000,000.00 2,000,000.00 Total Interest paid for the year 5,350,000.00 Effective all in cost of borrowing 3.2923%

Cumulative amount drawn upto first Quarter 50000000 Cumulative amount drawn upto Second Quarter 100000000 Cumulative amount drawn upto third Quarter 250000000 Cumulative amount drawn upto fourth Quarter 250000000 Average amount drawn upto four Quarters =AVERAGE(C9:C12) Interest amount for first Quarter Interest amount for second Quarter Interest amount for third Quarter Interest amount for fourth Quarter =375000+100000 =800000+75000 2000000 2000000 Total Interest paid for the year =SUM(C16:C19) Effective all in cost of borrowing =C21/C14