Why Your Loan’s Interest Rate Isn’t What It Seems: Flat Rate vs. IRR Demystified

How Lenders Trick You with Low Interest Rates – The Truth About EIR and Excel Calculations

When borrowing money, many consumers focus on the advertised interest rate, often overlooking the Effective Interest Rate (EIR), which more accurately reflects the true cost of borrowing. Lenders may use marketing tactics to make loans appear more attractive by quoting nominal or flat rates instead of the actual EIR. This allows them to present lower interest rates, making the loan seem more affordable to potential borrowers. By doing so, lenders can attract more customers while obscuring the true cost of borrowing.

Borrowers who do not carefully examine the EIR might unknowingly commit to a loan with a significantly higher real interest cost than expected. For example, a lender might advertise a low flat rate of 2% per annum, which appears cheaper than a competitor’s 4% EIR, but in reality, the effective cost of borrowing could be significantly higher due to compounding interest and additional fees. Similarly, some lenders emphasize low monthly installments without highlighting the longer loan tenure, which results in higher overall interest payments.

This blog investigates the impact of EIR through four different loan scenarios.

Loan Examples, Cash Flows, and Calculation of Flat Rate vs. EIR

Example 1: Borrowing $10,000 for 1 year, Monthly Repayment of $854

• Cash Flow: -$10,000 (initial) | +$854 (monthly for 12 months)
• Total Repayment = $854 * 12 = $10,248
• Interest Paid = $10,248 - $10,000 = $248
• Flat Rate Interest = ($248 / $10,000) * 100% = 2.48%
• Effective Interest Rate (EIR) computed using IRR formula: 4.64%

Example 2: Borrowing $10,000 for 3 months, Monthly Repayment of $3,374

• Cash Flow: -$10,000 (initial) | +$3,374 (monthly for 3 months)
• Total Repayment = $3,374 * 3 = $10,122
• Interest Paid = $10,122 - $10,000 = $122
• Flat Rate Interest = ($122 / $10,000) * 100% = 1.22%
• Effective Interest Rate (EIR) computed using IRR formula: 7.55%

Example 3: Borrowing $10,000 for 3 years, Monthly Repayment of $302

• Cash Flow: -$10,000 (initial) | +$302 (monthly for 36 months)
• Total Repayment = $302 * 36 = $10,872
• Interest Paid = $10,872 - $10,000 = $872
• Flat Rate Interest = ($872 / $10,000) * 100% = 8.72%
• Effective Interest Rate (EIR) computed using IRR formula: 5.65%

Example 4: Borrowing $10,000 for 1 year, Monthly Repayment of $881 with One-Time Processing Fee (Included in Initial Loan Amount for Accuracy)

• Cash Flow: -$10,200 (initial, including $200 processing fee) | +$881 (monthly for 12 months)
• Total Repayment = $881 * 12 = $10,572
• Interest Paid = $10,572 - $10,000 = $572
• If a one-time processing fee of $200 is included, total cost = $572 + $200 = $772
• Flat Rate Interest = ($772 / $10,000) * 100% = 7.72%
• Effective Interest Rate (EIR) computed using IRR formula: 6.87% (Processing fee included in initial loan amount for accurate calculation)

Key Insights

1. EIR is Always Higher than the Advertised Flat Rate: Lenders often quote flat interest rates, but EIR accounts for the time value of money and is always higher than the stated rate. Borrowers should always request the EIR to understand the actual cost.

2. Shorter Loan Terms Can Significantly Increase EIR: Although the total interest amount paid might seem low in short-term loans, the high monthly repayments can lead to an EIR much higher than the stated flat rate.

3. Fees and Charges Increase the Real Cost of Borrowing: Example 4 highlights how a processing fee significantly inflates the true cost of borrowing, pushing the EIR higher than expected.

How to Compute EIR and Monthly Interest Rate Using Excel for Each Example

When deciding between the IRR and RATE functions, borrowers should consider their specific loan conditions:

• Choose IRR if the loan includes irregular payments or additional fees, as it dynamically accounts for the time value of money.
• Choose RATE if the loan follows a fixed payment structure without additional charges, making it a simpler option for standard loans.

To calculate the Effective Interest Rate (EIR) in Excel, follow these steps for each loan example:

Choosing Between IRR and RATE Function

• IRR Function: Best used when the cash flows include irregular payments or additional fees, as it accounts for the time value of money dynamically.
• RATE Function: Ideal for fixed-payment loans with no additional fees, as it directly calculates the periodic interest rate based on constant cash flows.

Example 1: Borrowing $10,000 for 1 year, Monthly Repayment of $854

1. Enter the following values into Excel:

A1: -10000
A2: 854
A3: 854
...
A13: 854

2. Use the formula =IRR(A1:A13) to get the monthly IRR.
3. Convert it to an annual rate using (1+IRR result)^12 - 1.

Example 2: Borrowing $10,000 for 3 months, Monthly Repayment of $3,374

1. Enter the values:

A1: -10000
A2: 3374
A3: 3374
A4: 3374

2. Use =IRR(A1:A4) to get the monthly IRR.
3. Convert to an annual rate with (1+IRR result)^12 - 1.

Example 3: Borrowing $10,000 for 3 years, Monthly Repayment of $302

1. Enter the values:

A1: -10000
A2: 302
A3: 302
...
A37: 302

2. Use =IRR(A1:A37) to get the monthly IRR.
3. Convert to an annual rate with (1+IRR result)^12 - 1.

Example 4: Borrowing $10,000 for 1 year, Monthly Repayment of $881 with One-Time Processing Fee

1. Enter the values (including processing fee in initial loan disbursement):

A1: -10200
A2: 881
A3: 881
...
A13: 881

2. Use =IRR(A1:A13) to get the monthly IRR.
3. Convert to an annual rate with (1+IRR result)^12 - 1.

How to Compute Monthly Interest Rate Using Excel RATE Function

In addition to the IRR function, the Excel RATE function can also be used to compute the monthly interest rate and derive the Effective Interest Rate (EIR).

Example 1: Borrowing $10,000 for 1 year, Monthly Repayment of $854

1. Use the formula: =RATE(12, -854, 10000)
2. Convert to an annual rate: (1+RATE result)^12 - 1

Example 2: Borrowing $10,000 for 3 months, Monthly Repayment of $3,374

1. Use the formula: =RATE(3, -3374, 10000)
2. Convert to an annual rate: (1+RATE result)^12 - 1

Example 3: Borrowing $10,000 for 3 years, Monthly Repayment of $302

1. Use the formula: =RATE(36, -302, 10000)
2. Convert to an annual rate: (1+RATE result)^12 - 1

Example 4: Borrowing $10,000 for 1 year, Monthly Repayment of $881 with One-Time Processing Fee

1. Use the formula: =RATE(12, -881, 10200)
2. Convert to an annual rate: (1+RATE result)^12 - 1

Final Thoughts

To calculate the Effective Interest Rate (EIR) in Excel, follow these steps:

1. List the cash flows: Enter the initial loan disbursement as a negative value (e.g., -10,000) in one cell and the periodic repayments as positive values in the subsequent cells.
2. Use the IRR function: Select an empty cell and type =IRR(range_of_cash_flows), replacing range_of_cash_flows with the actual range of the cash flows.
3. Convert to an annual rate: Since IRR returns a monthly rate in this context, convert it to an annual rate using the formula: (1 + IRR result)^12 - 1.

Example:

A1: -10000
A2: 854
A3: 854
...
A13: 854

Formula: `=IRR(A1:A13)` then `(1+IRR result)^12 - 1`

Conclusion

Understanding the true cost of borrowing goes beyond just looking at the nominal interest rate. The EIR provides a clearer picture by considering loan tenure, payment structure, and additional fees. Before signing a loan agreement, always ask for the EIR and calculate the actual repayment amount to avoid falling into marketing traps.

By making informed financial decisions, borrowers can avoid costly mistakes and select loan options that align with their financial health and objectives.

Key Takeaways for Borrowers

Understanding the difference between reducing balance and flat interest rates is crucial. A flat interest rate charges interest on the original loan amount throughout the loan term, making it appear lower but often resulting in a higher effective cost. A reducing balance interest rate, however, applies interest on the remaining principal, leading to lower interest payments over time. Always clarify which method a lender is using before committing to a loan.

1. Always Compare EIR, Not Just Advertised Rates – Ensure you understand the actual cost of borrowing.
2. Consider the Impact of Fees – Processing fees and other charges can significantly increase the cost of borrowing.
3. Loan Term Matters – Shorter loan durations often lead to higher EIRs.
4. Use Financial Tools – Excel's IRR or RATE functions can help you assess loan costs accurately.
5. Negotiate Terms – If possible, negotiate better terms, especially for fees and interest structures.