Transposing data

Stclements1

Board Regular
Joined
Sep 15, 2018
Messages
120
Office Version
  1. 365
Platform
  1. 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.



Temp Loan Sheet.xlsx
ABCDEFGHIJKLMNO
1Month NoPayment DateMonthly Interest PaymentMonthly Capital RepaymentAccumulated Interest Earned% Accumulated Interest EarnedCapital RepaidOutstanding Capital
2Loan DateFriday, 15 January 20211Monday, 01 February 2021244,520244,520,49%050000,00Term
3Loan Amount500002Monday, 01 March 2021402,740647,261,29%050000,001
4Loan Term (Years)33Thursday, 01 April 2021445,891470,591093,152,19%1470,5948529,412
5% Monthly Interest Rate0,008754Monday, 03 May 2021460,271470,591553,423,11%2941,1847058,823
6% Annual Interest Rate0,1055Tuesday, 01 June 2021417,121470,591970,553,94%4411,7645588,24Repayments
7Average Monthly Interest Payment431,916Thursday, 01 July 2021431,511470,592402,054,80%5882,3544117,6510
8Monthly Capital Payment*1470,597Monday, 02 August 2021460,271470,592862,335,72%7352,9442647,0622
9Total Interest15548,638Wednesday, 01 September 2021431,511470,593293,846,59%8823,5341176,4734
10Total Capital500009Friday, 01 October 2021431,511470,593725,347,45%10294,1239705,88
11Total Return65548,6310Monday, 01 November 2021445,891470,594171,238,34%11764,7138235,29%
12No of Interest Payments3611Wednesday, 01 December 2021431,511470,594602,749,21%13235,2936764,710,70%
13No Capital Payments3412Monday, 03 January 2022474,661470,595077,4010,15%14705,8835294,120,8250%
14% Overall Annual Interest Rate0,103713Tuesday, 01 February 2022417,121470,595494,5210,99%16176,4733823,530,95%
1514Tuesday, 01 March 2022402,741470,595897,2611,79%17647,0632352,94
1615Friday, 01 April 2022445,891470,596343,1512,69%19117,6530882,35%
1716Monday, 02 May 2022445,891470,596789,0413,58%20588,2429411,760,00%
1817Wednesday, 01 June 2022431,511470,597220,5514,44%22058,8227941,180,0250%
1918Friday, 01 July 2022431,511470,597652,0515,30%23529,4126470,590,050%
2019Monday, 01 August 2022445,891470,598097,9516,20%25000,0025000,00
2120Thursday, 01 September 2022445,891470,598543,8417,09%26470,5923529,41Value
2221Monday, 03 October 2022460,271470,599004,1118,01%27941,1822058,820
2322Tuesday, 01 November 2022417,121470,599421,2318,84%29411,7620588,2430000
2423Thursday, 01 December 2022431,511470,599852,7419,71%30882,3519117,6575000
2524Monday, 02 January 2023460,271470,5910313,0120,63%32352,9417647,06
2625Wednesday, 01 February 2023431,511470,5910744,5221,49%33823,5316176,47
2726Wednesday, 01 March 2023402,741470,5911147,2622,29%35294,1214705,88
2827Monday, 03 April 2023474,661470,5911621,9223,24%36764,7113235,29
2928Monday, 01 May 2023402,741470,5912024,6624,05%38235,2911764,71
3029Thursday, 01 June 2023445,891470,5912470,5524,94%39705,8810294,12
3130Monday, 03 July 2023460,271470,5912930,8225,86%41176,478823,53
3231Tuesday, 01 August 2023417,121470,5913347,9526,70%42647,067352,94
3332Friday, 01 September 2023445,891470,5913793,8427,59%44117,655882,35
3433Monday, 02 October 2023445,891470,5914239,7328,48%45588,244411,76
3534Wednesday, 01 November 2023431,511470,5914671,2329,34%47058,822941,18
3635Friday, 01 December 2023431,511470,5915102,7430,21%48529,411470,59
3736Monday, 01 January 2024445,891470,5915548,6331,10%50000,000,00
38
Sheet1
Cell Formulas
RangeFormula
E2:E37E2=IF(ROWS(E$2:E2)>($B$4*12),"",WORKDAY(EOMONTH(B$2,D2-1)*1,1))
F2F2=(B3*(B5*12)/365)*(E2-B2)
F3:F37F3=IFERROR(($B$3*($B$5*12)/365)*(E3-E2),"")
H2H2=IF(E2>0,F2)
I2I2=H2/$B$3
J2:J3J2=G2
K2K2=B3-G2
H3:H37H3=IFERROR(IF(E3>0,F3+H2),"")
I3:I37I3=IFERROR(IF(E3>0,H3/$B$3),"")
K3K3=K2-G3
G4:G37G4=IFERROR(IF(E4="","",$B$3/$B$13),"")
J4:J37J4=IFERROR(IF(E4>1,G4+J3),"")
K4:K37K4=IFERROR(IF(E4>0,K3-G4),"")
B5B5=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))
B6B6=B5*12
B7B7=B9/(B4*12)
B8B8=B3/INDEX($M$7:$M$9,MATCH($B$4,$M$3:$M$5))
B9B9=SUM(F2:INDEX(F:F,B4*12+1))
B10B10=B8*INDEX($M$7:$M$9,MATCH($B$4,$M$3:$M$5))
B11B11=B9+B10
B12B12=B4*12
B13B13=INDEX($M$7:$M$9,MATCH(B4,$M$3:$M$5))
B14B14=(B9/B3)/B4
Cells with Data Validation
CellAllowCriteria
B13List=$M$7:$M$9
B4:C4List=$M$3:$M$5
C13:C38List=$M$3:$M$5



Temp Loan Sheet.xlsx
ABCDEFGHIJKLMNOPQRST
2Loan Date15-Jan-21Payment Date01-Feb-2101-Mar-2101-Apr-2103-May-2101-Jun-2101-Jul-2102-Aug-2101-Sep-2101-Oct-2101-Nov-2101-Dec-2103-Jan-22Term
3Loan Amount50000Monthly Interest Payment237,53391,23433,15447,12405,21419,18447,12419,18419,18433,15419,18461,101
4Loan Term (Years)2Monthly Capital Repayment002272,732272,732272,732272,732272,732272,732272,732272,732272,732272,732
5% Monthly Interest Rate0,008503
6% Annual Interest Rate0,102Repayments
7Average Monthly Interest Payment5817,5913141516171819202122232410
8Monthly Capital Payment*2272,73Payment Date01-Feb-2201-Mar-2201-Apr-2202-May-2201-Jun-2201-Jul-2201-Aug-2201-Sep-2203-Oct-2201-Nov-2201-Dec-2202-Jan-2322
9Total Interest139622,15Monthly Interest Payment405,21391,23433,15433,15419,18419,18433,15433,15447,12405,21419,18447,1234
10Total Capital50000Monthly Capital Repayment2272,732272,732272,732272,732272,732272,732272,732272,732272,732272,732272,732272,73
11Total Return189622,15%
12No of Interest Payments240,70%
13No Capital Payments222526272829303132333435360,8250%
14% Overall Annual Interest Rate1,3962Payment Date01-Feb-2301-Mar-2303-Apr-2301-May-2301-Jun-2303-Jul-2301-Aug-2301-Sep-2302-Oct-2301-Nov-2301-Dec-2301-Jan-240,95%
15Monthly Interest Payment419,18391,23461,10391,23433,15447,12405,21433,15433,15419,18419,18433,15
16Monthly Capital Repayment2272,732272,732272,732272,732272,732272,732272,732272,732272,732272,732272,732272,73%
170,00%
180,0250%
190,050%
20
21Value
220
2330000
2475000
25
Sheet2
Cell Formulas
RangeFormula
E2,E14E2=IF(ROWS(E$2:E2)>($B$4*12),"",WORKDAY(EOMONTH(B$2,E1-1)*1,1))
F2,F14F2=IF(ROWS(F$2:F2)>($B$4*12),"",WORKDAY(EOMONTH(B$2,F1-1)*1,1))
E3E3=(B3*(B5*12)/365)*(E2-B2)
F15:P15,F9:P9,F3:P3F3=IFERROR(($B$3*($B$5*12)/365)*(F2-E2),"")
G2:P2,G14:P14G2=IF(ROWS(G$2:G2)>($B$4*12),"",WORKDAY(EOMONTH($B$2,G1-1)*1,1))
G4:P4,E16:P16,E10:P10G4=IFERROR(IF(G2="","",$B$3/$B$13),"")
E8:P8E8=IF(ROWS(E$8:E8)>($B$4*12),"",WORKDAY(EOMONTH($B$2,E7-1)*1,1))
E9,E15E9=IFERROR(($B$3*($B$5*12)/365)*(E8-P2),"")
B5B5=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))
B6B6=B5*12
B7B7=B9/(B4*12)
B8B8=B3/INDEX($S$7:$S$9,MATCH($B$4,$S$3:$S$5))
B9B9=SUM(F2:INDEX(F:F,B4*12+1))
B10B10=B8*INDEX($S$7:$S$9,MATCH($B$4,$S$3:$S$5))
B11B11=B9+B10
B12B12=B4*12
B13B13=INDEX($S$7:$S$9,MATCH(B4,$S$3:$S$5))
B14B14=(B9/B3)/B4
Cells with Data Validation
CellAllowCriteria
B4List=$S$3:$S$5
B13List=$S$7:$S$9
 

Some videos you may like

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Stclements1

Board Regular
Joined
Sep 15, 2018
Messages
120
Office Version
  1. 365
Platform
  1. Windows
Thank you for your help although I have solved it now
 

Watch MrExcel Video

Forum statistics

Threads
1,114,098
Messages
5,545,955
Members
410,714
Latest member
T_Bos
Top