Stclements1
Board Regular
- Joined
- Sep 15, 2018
- Messages
- 158
- 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,D2-1)*1,1)) |
F2 | F2 | =(B3*(B5*12)/365)*(E2-B2) |
F3:F37 | F3 | =IFERROR(($B$3*($B$5*12)/365)*(E3-E2),"") |
H2 | H2 | =IF(E2>0,F2) |
I2 | I2 | =H2/$B$3 |
J2:J3 | J2 | =G2 |
K2 | K2 | =B3-G2 |
H3:H37 | H3 | =IFERROR(IF(E3>0,F3+H2),"") |
I3:I37 | I3 | =IFERROR(IF(E3>0,H3/$B$3),"") |
K3 | K3 | =K2-G3 |
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,K3-G4),"") |
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 | 15-Jan-21 | Payment Date | 01-Feb-21 | 01-Mar-21 | 01-Apr-21 | 03-May-21 | 01-Jun-21 | 01-Jul-21 | 02-Aug-21 | 01-Sep-21 | 01-Oct-21 | 01-Nov-21 | 01-Dec-21 | 03-Jan-22 | 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 | 01-Feb-22 | 01-Mar-22 | 01-Apr-22 | 02-May-22 | 01-Jun-22 | 01-Jul-22 | 01-Aug-22 | 01-Sep-22 | 03-Oct-22 | 01-Nov-22 | 01-Dec-22 | 02-Jan-23 | 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 | 01-Feb-23 | 01-Mar-23 | 03-Apr-23 | 01-May-23 | 01-Jun-23 | 03-Jul-23 | 01-Aug-23 | 01-Sep-23 | 02-Oct-23 | 01-Nov-23 | 01-Dec-23 | 01-Jan-24 | 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,E1-1)*1,1)) |
F2,F14 | F2 | =IF(ROWS(F$2:F2)>($B$4*12),"",WORKDAY(EOMONTH(B$2,F1-1)*1,1)) |
E3 | E3 | =(B3*(B5*12)/365)*(E2-B2) |
F15:P15,F9:P9,F3:P3 | F3 | =IFERROR(($B$3*($B$5*12)/365)*(F2-E2),"") |
G2:P2,G14:P14 | G2 | =IF(ROWS(G$2:G2)>($B$4*12),"",WORKDAY(EOMONTH($B$2,G1-1)*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,E7-1)*1,1)) |
E9,E15 | E9 | =IFERROR(($B$3*($B$5*12)/365)*(E8-P2),"") |
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 |