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

 365
 Platform

 Windows
I have this spreadsheet which works perfectly, but instead of all the dates being in a linear order, I want to display them in an annual row for as in the second spreadsheet attached. The problem I am having is that when I change the value in B4 in sheet 2 it is still giving values where the dates are displayed and it should only give values based on the value in B4 being either 1,2 or 3. All the calculations work perfectly apart from displaying dates and values when they shouldn't be shown.
Cell Formulas  

Range  Formula  
E2:E37  E2  =IF(ROWS(E$2:E2)>($B$4*12),"",WORKDAY(EOMONTH(B$2,D21)*1,1)) 
F2  F2  =(B3*(B5*12)/365)*(E2B2) 
F3:F37  F3  =IFERROR(($B$3*($B$5*12)/365)*(E3E2),"") 
H2  H2  =IF(E2>0,F2) 
I2  I2  =H2/$B$3 
J2:J3  J2  =G2 
K2  K2  =B3G2 
H3:H37  H3  =IFERROR(IF(E3>0,F3+H2),"") 
I3:I37  I3  =IFERROR(IF(E3>0,H3/$B$3),"") 
K3  K3  =K2G3 
G4:G37  G4  =IFERROR(IF(E4="","",$B$3/$B$13),"") 
J4:J37  J4  =IFERROR(IF(E4>1,G4+J3),"") 
K4:K37  K4  =IFERROR(IF(E4>0,K3G4),"") 
B5  B5  =INDEX($M$12:$M$14,MATCH($B$3,$M$22:$M$24,1))+INDEX($M$17:$M$19,MATCH($B$4,$M$3:$M$5,1)) 
B6  B6  =B5*12 
B7  B7  =B9/(B4*12) 
B8  B8  =B3/INDEX($M$7:$M$9,MATCH($B$4,$M$3:$M$5)) 
B9  B9  =SUM(F2:INDEX(F:F,B4*12+1)) 
B10  B10  =B8*INDEX($M$7:$M$9,MATCH($B$4,$M$3:$M$5)) 
B11  B11  =B9+B10 
B12  B12  =B4*12 
B13  B13  =INDEX($M$7:$M$9,MATCH(B4,$M$3:$M$5)) 
B14  B14  =(B9/B3)/B4 
Cells with Data Validation  

Cell  Allow  Criteria 
B13  List  =$M$7:$M$9 
B4:C4  List  =$M$3:$M$5 
C13:C38  List  =$M$3:$M$5 
Temp Loan Sheet.xlsx  

A  B  C  D  E  F  G  H  I  J  K  L  M  N  O  P  Q  R  S  T  
2  Loan Date  15Jan21  Payment Date  01Feb21  01Mar21  01Apr21  03May21  01Jun21  01Jul21  02Aug21  01Sep21  01Oct21  01Nov21  01Dec21  03Jan22  Term  
3  Loan Amount  50000  Monthly Interest Payment  237,53  391,23  433,15  447,12  405,21  419,18  447,12  419,18  419,18  433,15  419,18  461,10  1  
4  Loan Term (Years)  2  Monthly Capital Repayment  0  0  2272,73  2272,73  2272,73  2272,73  2272,73  2272,73  2272,73  2272,73  2272,73  2272,73  2  
5  % Monthly Interest Rate  0,00850  3  
6  % Annual Interest Rate  0,102  Repayments  
7  Average Monthly Interest Payment  5817,59  13  14  15  16  17  18  19  20  21  22  23  24  10  
8  Monthly Capital Payment*  2272,73  Payment Date  01Feb22  01Mar22  01Apr22  02May22  01Jun22  01Jul22  01Aug22  01Sep22  03Oct22  01Nov22  01Dec22  02Jan23  22  
9  Total Interest  139622,15  Monthly Interest Payment  405,21  391,23  433,15  433,15  419,18  419,18  433,15  433,15  447,12  405,21  419,18  447,12  34  
10  Total Capital  50000  Monthly Capital Repayment  2272,73  2272,73  2272,73  2272,73  2272,73  2272,73  2272,73  2272,73  2272,73  2272,73  2272,73  2272,73  
11  Total Return  189622,15  %  
12  No of Interest Payments  24  0,70%  
13  No Capital Payments  22  25  26  27  28  29  30  31  32  33  34  35  36  0,8250%  
14  % Overall Annual Interest Rate  1,3962  Payment Date  01Feb23  01Mar23  03Apr23  01May23  01Jun23  03Jul23  01Aug23  01Sep23  02Oct23  01Nov23  01Dec23  01Jan24  0,95%  
15  Monthly Interest Payment  419,18  391,23  461,10  391,23  433,15  447,12  405,21  433,15  433,15  419,18  419,18  433,15  
16  Monthly Capital Repayment  2272,73  2272,73  2272,73  2272,73  2272,73  2272,73  2272,73  2272,73  2272,73  2272,73  2272,73  2272,73  %  
17  0,00%  
18  0,0250%  
19  0,050%  
20  
21  Value  
22  0  
23  30000  
24  75000  
25  
Sheet2 
Cell Formulas  

Range  Formula  
E2,E14  E2  =IF(ROWS(E$2:E2)>($B$4*12),"",WORKDAY(EOMONTH(B$2,E11)*1,1)) 
F2,F14  F2  =IF(ROWS(F$2:F2)>($B$4*12),"",WORKDAY(EOMONTH(B$2,F11)*1,1)) 
E3  E3  =(B3*(B5*12)/365)*(E2B2) 
F15:P15,F9:P9,F3:P3  F3  =IFERROR(($B$3*($B$5*12)/365)*(F2E2),"") 
G2:P2,G14:P14  G2  =IF(ROWS(G$2:G2)>($B$4*12),"",WORKDAY(EOMONTH($B$2,G11)*1,1)) 
G4:P4,E16:P16,E10:P10  G4  =IFERROR(IF(G2="","",$B$3/$B$13),"") 
E8:P8  E8  =IF(ROWS(E$8:E8)>($B$4*12),"",WORKDAY(EOMONTH($B$2,E71)*1,1)) 
E9,E15  E9  =IFERROR(($B$3*($B$5*12)/365)*(E8P2),"") 
B5  B5  =INDEX($S$12:$S$14,MATCH($B$3,$S$22:$S$24,1))+INDEX($S$17:$S$19,MATCH($B$4,$S$3:$S$5,1)) 
B6  B6  =B5*12 
B7  B7  =B9/(B4*12) 
B8  B8  =B3/INDEX($S$7:$S$9,MATCH($B$4,$S$3:$S$5)) 
B9  B9  =SUM(F2:INDEX(F:F,B4*12+1)) 
B10  B10  =B8*INDEX($S$7:$S$9,MATCH($B$4,$S$3:$S$5)) 
B11  B11  =B9+B10 
B12  B12  =B4*12 
B13  B13  =INDEX($S$7:$S$9,MATCH(B4,$S$3:$S$5)) 
B14  B14  =(B9/B3)/B4 
Cells with Data Validation  

Cell  Allow  Criteria 
B4  List  =$S$3:$S$5 
B13  List  =$S$7:$S$9 