Transposing data

S

Stclements1

Board Regular
Joined
Sep 15, 2018
Messages
158
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
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.
Sort by date Sort by votes
You must log in or register to reply here.

Similar threads

H
  • Question Question
Transposing Data - Evaluating formula to an array error.
Replies
1
Views
42
Skyybot
Skyybot
SanjayGMusafir
  • Solved
Text function not returning correct month
Replies
7
Views
159
Alex Blakenburg
A
B
  • Solved
Formula to identify date and location from a table and return value
Replies
3
Views
35
Fluff
Fluff
D
  • Question Question
If formula with date & VLOOKUP
Replies
1
Views
283
etaf
etaf
C
  • Question Question
Allocation Across Months
Replies
3
Views
442
NickFru
N

Forum statistics

Threads
1,223,892
Messages
6,175,236
Members
452,621
Latest member
Laura_PinksBTHFT

Share this page

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top