Sorry for the long post, I tried to give a little background but I believe the basic excel question boils down to how to do matrix multiplications.
I have 3 loans of $30 each, which started in different years. They all have a repayment schedule of 10 every year but every repayment has a different interest rates. On the first loan, the first repayment is calculated with an interest rate of 5%, the second repayment has 6%, etc. (the longer the tenor/maturity, the higher the interest rate).
Repayments:
<tbody>
</tbody>
Rate:
<tbody>
</tbody>
The interest payments calculate as follows:
<tbody>
</tbody>
Which formula would be best suited to give me in 1 cell the total interest payment by year, taking into account all tranches and different rates? I tried using an MMULT formula, but I can't seem to get it working, I could be doing something wrong on the number of array dimensions.
For 3 loans & 3 sets of rates I could do it manually, but in reality the number of tranches/maturities goes up to 60 months and there are 36 loans that were generated over 3 years. Then the array becomes interesting, 36 loans @ 60 maturities not one of them with a single similar interest rate.
I have 3 loans of $30 each, which started in different years. They all have a repayment schedule of 10 every year but every repayment has a different interest rates. On the first loan, the first repayment is calculated with an interest rate of 5%, the second repayment has 6%, etc. (the longer the tenor/maturity, the higher the interest rate).
Repayments:
| Loan 1 | Loan 2 | Loan 3 | |
| Year 1 | 10 | - | - |
| Year 2 | 10 | 10 | - |
| Year 3 | 10 | 10 | 10 |
| Year 4 | - | 10 | 10 |
| Year 5 | - | - | 10 |
<tbody>
</tbody>
Rate:
| Loan 1 | Loan 2 | Loan 3 | |
| Payment 1 | 5% | 3% | 1% |
| Payment 2 | 6% | 4% | 2% |
| Payment 3 | 7% | 5% | 3% |
<tbody>
</tbody>
The interest payments calculate as follows:
| Loan 1 | Loan 2 | Loan 3 | Total | |
| Year 1 | $0,50+$0,60+$0,70 | n.a. (does not yet exist) | n.a. (does not yet exist) | $1,80 |
| Year 2 | $0,60+$0,70 | $0,30+$0,40+$0,50 | n.a. (does not yet exist) | $2,50 |
| Year 3 | $0,70 | $0,40+$0,50 | $0,10+$0,20+$0,30 | $2,20 |
| Year 4 | n.a. (fully repaid) | $0,50 | $0,20+$0,30 | $1,00 |
| Year 5 | n.a. (fully repaid) | n.a. (fully repaid) | $0,30 | $0,30 |
<tbody>
</tbody>
Which formula would be best suited to give me in 1 cell the total interest payment by year, taking into account all tranches and different rates? I tried using an MMULT formula, but I can't seem to get it working, I could be doing something wrong on the number of array dimensions.
For 3 loans & 3 sets of rates I could do it manually, but in reality the number of tranches/maturities goes up to 60 months and there are 36 loans that were generated over 3 years. Then the array becomes interesting, 36 loans @ 60 maturities not one of them with a single similar interest rate.
