1
$\begingroup$

I would like to know what's the way on how to calculate the weighted average interest rate for a loan portfolio properly, especially when looking at periods shorter than a year. The basic definition is, that the Weighted Average Interest Rate = Interest Paid in Period / Total Debt.

I think the question becomes a bit clearer, when I use an example. Please assume a loan of 10000 and an annual interest rate of 5%, which should be paid back in 12 monthly installments. Based on this loan I would like to know the average weighted interest rate in each month. Based on the simplest calculation the annual weighted interest rate would be 5%. But I thought in case of a monthly perspective it is necessary to look at the monthly interest expense relative to the debt outstanding. That would mean the interest part of the repayment divided by the debt outstanding.

Period  Payment  Interest   Repayment   Debt Outstanding
   1    856.07        41.67   814.41    9185.59
   2    856.07        38.27   817.80    8367.79
   3    856.07        34.87   821.21    7546.58
   4    856.07        31.44   824.63    6721.95
   5    856.07        28.01   828.07    5893.88
   6    856.07        24.56   831.52    5062.37
   7    856.07        21.09   834.98    4227.39
   8    856.07        17.61   838.46    3388.93
   9    856.07        14.12   841.95    2546.97
  10    856.07        10.61   845.46    1701.51
  11    856.07        7.09    848.99    852.52
  12    856.07        3.55    852.52    0.00

That would for example mean that my weighted interest rate in Period 8 would be 17.61 divided by 3388.93, which would result in a monthly average interest rate of 0.0052.

Which is the correct way of calculating a weighted average interest rate, based on fixed-rate mortgages.

$\endgroup$
0
$\begingroup$

In first place you need to fix the outstanding debt, because in period 12, you are actually calculating interest over period 11 debt. In second place, you are right about using the interest part of the repayment divided by the debt outstanding. In the specific case of Period 8 and forward, assuming the other payments have already been payed, you only need to focus on the remaining payments. That said, your Weighted Average Interest Rate would be your monthly average interest rate annualized:

  • Interest in Period 8 = ((17.61 + 14.12 + 10.61 + 7.09 + 3.55) / 4227.39)*360/150
  • Interest in Period 9 = ((14.12 + 10.61 + 7.09 + 3.55) / 3388.93)*360/120
  • Interest in Period 10 = ((10.61 + 7.09 + 3.55) / 2546.97)*360/90
  • Interest in Period 11 = ((7.09 + 3.55) / 1701.51)*360/60
  • Interest in Period 12 = (3.55) / 852.52)*360/30
$\endgroup$
  • $\begingroup$ Thanks for this, I don't really understand what you mean with your first sentence. So I basically calculate ratio of outstanding interest rate and outstanding debt and annualize it over the number of periods to maturity. $\endgroup$ – hannes101 Dec 5 '16 at 8:38
  • 1
    $\begingroup$ In most spreadsheets you want to calculate the interest over the debt in the same row. So what i meant was that the outstanding debt for a certain period is actually the one from the prior one. $\endgroup$ – MattR Dec 5 '16 at 17:14

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.