Stclements1
Board Regular
 Joined
 Sep 15, 2018
 Messages
 120
 Office Version

 365
 Platform

 Windows
Cell Formulas  

Range  Formula  
C3  C3  =B3 
D3:D26  D3  =C3*$D$2 
E3:E26  E3  =C3+D3 
C4:C26  C4  =B4+N3 
I5,I26,I23,I20,I17,I14,I11,I9,I7  I5  =B3/INDEX($R$7:$R$9,MATCH($F$3,$R$3:$R$5)) 
G3  G3  =INDEX($R$13:$R$15,MATCH(B3,$R$23:$R$25,1))+INDEX($R$18:$R$20,MATCH(F3,$R$4:$R$6,1)) 
G4:G7,G26,G24,G21,G18,G15,G12,G9  G4  =IFERROR(INDEX($R$13:$R$15,MATCH(B4,$R$23:$R$25,1))+INDEX($R$18:$R$20,MATCH(F4,$R$4:$R$6,1)),"") 
H3  H3  =IFERROR((B3*G3),"") 
H4:H26  H4  =IFERROR((B4*G4),"0") 
N3:N26  N3  =E3(H3+I3+L3) 
B28  B28  =SUM(B3:B27) 
Cells with Data Validation  

Cell  Allow  Criteria 
F3:F26  List  =$R$4:$R$6 
D2  List  =$R$27:$R$36 
In column B I have a list of values which represents Loans
In Column F I have the term (years) of each loan
In Column G I have the interest rate for each loan which is determined by the value of the loan and the term of the loan (details in column R)
In column I is the capital payments of each loan this is determined by the term of the loan and the first payments are made on the third month of the loan and every month thereafter for the duration of the loan. Example 1 = 10 payments, 2 =22 payments and 3 =34 payments. These are incorrect in this column as the first payments should be on the 3 month after the loan commences,
What I am attempting to evaluate in columns J and K is the ongoing accumulated monthly interest payments and capital payments which means adding each one as it falls due and then as the term of the loan is completed based on its corresponding value in column F so there are no further payments from that specific loan calculated.
Hopefully I have given enough clarity but will happily provide further data if needed.