Objectives

Section Learning Objectives

2.1

The Basic Interest Formula

📖 Fundamental Formula

Interest Expense = Principal × Interest Rate × Time

Where:
• Principal = Debt balance during the period
• Interest Rate = Annual rate (as decimal)
• Time = Fraction of year (Days / Year Basis)

📊 Three Common Approaches

Beginning Balance

Interest = Beg Balance × Rate

Simple, no circularity

Average Balance

Interest = (Beg+End)/2 × Rate

More accurate, circular

Ending Balance

Interest = End Balance × Rate

Rarely used

💡 Best Practice

For most financial models, use the Beginning Balance method for simplicity and to avoid circular references. The difference from using average balance is typically immaterial for forecasting purposes.

📝 Worked Example

Beginning vs. Average Balance Impact

Scenario: Reliance Retail has a ₹200 Crore term loan at 8.00% p.a. During the year, they make a scheduled principal repayment of ₹40 Crore.
• Beginning Balance: ₹200 Cr
• Ending Balance: ₹160 Cr
• Interest Rate: 8.00%

Question: Calculate the interest expense using both the Beginning Balance and Average Balance methods. What is the difference?
1
Calculate Interest using Beginning Balance Method
Interest = Beginning Balance × Rate
Interest = ₹200 Cr × 8.00% = ₹16.00 Crore
2
Calculate Average Balance
Average Balance = (Beginning + Ending) / 2
Average Balance = (₹200 Cr + ₹160 Cr) / 2 = ₹180 Crore
3
Calculate Interest using Average Balance Method
Interest = Average Balance × Rate
Interest = ₹180 Cr × 8.00% = ₹14.40 Crore
4
Compare the results
Difference = ₹16.00 Cr - ₹14.40 Cr = ₹1.60 Crore
Standard modeling practice uses ₹16.00 Cr (Beginning Balance) to avoid circular references.
2.2

Fixed vs. Floating Interest Rates

🔒 Fixed Rate

Interest rate stays constant over the life of the debt

Interest = Principal × Fixed Rate

Example:
Principal = ₹500 Cr
Fixed Rate = 8.5%
Annual Interest = ₹42.5 Cr
  • Predictable cash flows
  • Common for bonds
  • No interest rate risk

🌊 Floating Rate

Rate varies with a benchmark (SOFR, LIBOR)

Total Rate = Benchmark + Spread

Example:
Repo Rate = 6.5%
Spread = 2.0%
Total Rate = 8.5%
Interest = ₹500 Cr × 8.5% = ₹42.5 Cr
  • Common for bank debt
  • Interest rate risk exists
  • Model needs rate assumptions

📈 Benchmark Rates: LIBOR to SOFR Transition

The financial industry has transitioned from LIBOR to alternative reference rates

Benchmark Region Status Typical Use
SOFR United States Active USD-denominated debt
SONIA United Kingdom Active GBP-denominated debt
EURIBOR Eurozone Active EUR-denominated debt
LIBOR Global Phased Out Legacy contracts only
📝 Worked Example

Floating Rate Reset Mid-Year

Scenario: Tata Steel has a ₹1,000 Crore term loan linked to the RBI Repo Rate plus a 2.50% spread. The loan requires quarterly interest payments.
• Q1 (Apr-Jun) Repo Rate: 6.50%
• On July 1st, RBI increases the Repo Rate to 6.75%.
• Q2 (Jul-Sep) Repo Rate: 6.75%
• The principal remains ₹1,000 Cr throughout the half-year.

Question: Calculate the interest expense for Q1 and Q2. (Assume 1 quarter = 3/12 of a year)
1
Calculate Q1 Effective Rate and Interest
Q1 Rate = 6.50% (Repo) + 2.50% (Spread) = 9.00%
Q1 Interest = ₹1,000 Cr × 9.00% × (3/12) = ₹22.50 Crore
2
Calculate Q2 Effective Rate and Interest
Q2 Rate = 6.75% (New Repo) + 2.50% (Spread) = 9.25%
Q2 Interest = ₹1,000 Cr × 9.25% × (3/12) = ₹23.125 Crore
3
Calculate Total Half-Year Interest
Total = Q1 Interest + Q2 Interest
Total = ₹22.50 Cr + ₹23.125 Cr = ₹45.625 Crore
Total H1 Interest = ₹45.625 Crore
2.3

Day Count Conventions

📅 How Days Are Counted Matters

Different markets use different conventions for calculating interest periods

Actual/360 (Act/360)

Actual days in period / 360 day year

Interest = P × R × (Actual Days / 360)

30-day month:
= ₹100 Cr × 9% × (30/360)
= ₹0.75 Cr

Used for: US commercial paper, bank loans

Actual/365 (Act/365)

Actual days in period / 365 day year

Interest = P × R × (Actual Days / 365)

30-day month:
= ₹100 Cr × 9% × (30/365)
= ₹0.7397 Cr

Used for: UK bonds, some loans

30/360

Assumes 30 days/month, 360 day year

Interest = P × R × (30 × Months / 360)

1-month period:
= ₹100 Cr × 9% × (30/360)
= ₹0.75 Cr

Used for: US corporate bonds

📝 Worked Example

Impact of Day Count Conventions

Scenario: HDFC Bank issues a ₹5,000 Crore commercial paper (CP) at an interest rate of 7.50% for exactly 90 days. Assume a non-leap year.

Question: Calculate the interest cost under Actual/360 and Actual/365 conventions. What is the difference in borrowing cost?
1
Calculate Interest using Actual/360 Convention
Interest = Principal × Rate × (Days / 360)
Interest = ₹5,000 Cr × 7.50% × (90 / 360)
Interest = ₹5,000 Cr × 0.075 × 0.25 = ₹93.75 Crore
2
Calculate Interest using Actual/365 Convention
Interest = Principal × Rate × (Days / 365)
Interest = ₹5,000 Cr × 7.50% × (90 / 365)
Interest = ₹5,000 Cr × 0.075 × 0.246575 = ₹92.4657 Crore
3
Calculate the Difference
Difference = ₹93.75 Cr - ₹92.4657 Cr = ₹1.2843 Crore
Actual/360 costs ₹1.28 Crore more than Actual/365 for this 90-day period.
⚠️ Impact on Models

Actual/360 results in slightly higher interest than Actual/365 because dividing by a smaller number (360 vs 365) gives a larger result.

For a full year: ₹100 Cr × 9% × (365/360) = ₹9.125 Cr vs ₹9.0 Cr
Difference: ~1.4% higher interest expense

2.4

Excel Implementation

🔧 Building Interest Calculations in Excel

Basic Interest Formula (Annual): =C10*$D$5 where C10 = Beginning Balance, $D$5 = Annual Interest Rate With Day Count (Monthly Model): =C10*$D$5*(Days_in_Month/365) or use specific day count: =C10*$D$5*30/360 Floating Rate with Spread: =C10*(SOFR_Rate+Spread) where SOFR_Rate = 4.5%, Spread = 2.0% Multiple Debt Tranches: =SUMPRODUCT(Balance_Range,Rate_Range) Sums interest across all debt instruments

📊 Sample Interest Schedule Structure

Item Year 1 Year 2 Year 3 Formula
Beginning Balance $100.0 $80.0 $60.0 =Prior Year End
Interest Rate 6.0% 6.0% 6.0% Input assumption
Interest Expense $6.0 $4.8 $3.6 =Beg Bal × Rate
Mandatory Amort ($20.0) ($20.0) ($20.0) Input assumption
Ending Balance $80.0 $60.0 $40.0 =Beg - Amort
💡 Pro Tips

Excel Best Practices for Interest Schedules:
1. Use named ranges for key inputs (Interest_Rate, SOFR, Spread)
2. Put assumptions in a clearly labeled input section
3. Use absolute references ($D$5) for rates that don't change
4. Color code inputs (blue) vs calculations (black)
5. Add error checks: Total Interest should tie to IS

Excel Lab

Hands-On Exercise

🎯 Exercise: Calculate Interest for Multiple Debt Tranches

Given Data

Debt Type Balance Rate Type Rate/Spread
Revolver ₹50 Cr SOFR + Spread SOFR 4.5% + 1.5%
Term Loan A ₹150 Cr SOFR + Spread SOFR 4.5% + 2.0%
Senior Bonds ₹200 Cr Fixed 6.5%

Tasks

  1. Calculate total interest expense for each tranche
  2. Calculate weighted average interest rate
  3. Build a 3-year schedule with 5% annual amortization on Term Loan A
Debt Type Total Rate Annual Interest
Revolver 6.0% ₹3.0 Cr
Term Loan A 6.5% ₹9.75 Cr
Senior Bonds 6.5% ₹13.0 Cr
Total 6.39% (weighted avg) ₹25.75 Cr
Summary

Key Takeaways

  • Interest = Principal × Rate × Time - the fundamental formula
  • Beginning balance method avoids circular references
  • Floating rates = Benchmark (SOFR) + Spread
  • Day count conventions affect interest calculations
  • Use named ranges and clear input sections in Excel
← Previous: Debt Structure Next: Repayment Schedules →