# Matrix multiplication on loans & repayments with different interest rates (MMULT?)

#### Galileo

##### New Member
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:
 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.

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### RonB1111

##### Well-known Member
Assume:
Repayment numbers are in B2:D6
Int Rates are in B10:D12

Enter this formula in B15 and copy down and across to D19:
=IF(B2=0,0,IF(COUNTIF(B\$2:B2,">"&0)=1,SUM(B\$10:B\$12)*B2,IF(COUNTIF(B\$2:B2,">"&0)=2,SUM(B\$11:B\$12)*B2,B\$12*B2)))

Replies
8
Views
720
Replies
4
Views
880
Replies
0
Views
280
Replies
0
Views
833
Replies
9
Views
221

1,195,848
Messages
6,011,946
Members
441,657
Latest member
Diupsy

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back