Adding values in multiple arrays

Stclements1

Board Regular
Joined
Sep 15, 2018
Messages
120
Office Version
  1. 365
Platform
  1. Windows
Loan Sheet.xlsx
ABCDEFGHIJKLMNOPQR
1MonthCapital InputFund Value% Return on CapitalFund Plus InterestLoan PeriodInterest RateInterest PaymentCapital PaymentTotal Monthly Interest PaymentsTotal Monthly Capital PaymentsFund PaymentFund Balance
20,04
311000010 000,00400,0010 400,0010,0070070,000,0010 330,00Term
4210 330,00413,2010 743,20 00,0010 743,201
538000090 743,203 629,7394 372,9320,00975780,001 000,000,0092 592,932
6492 592,933 703,7296 296,65 00,0096 296,653
7515000111 296,654 451,87115 748,5130,00750112,508 000,000,00107 636,01Repayments
86107 636,014 305,44111 941,45200,00111 941,4510
9712000123 941,454 957,66128 899,1120,0072587,001 500,000,00127 312,1122
108127 312,115 092,48132 404,5900,00132 404,5934
119132 404,595 296,18137 700,7801 200,000,00136 500,78
121010000146 500,785 860,03152 360,8130,0075075,000,00152 285,81%
1311152 285,816 091,43158 377,2400,00158 377,240,70%
1412158 377,246 335,09164 712,3301 000,000,00163 712,330,8250%
151313000176 712,337 068,49183 780,8210,0070091,000,00183 689,820,95%
1614183 689,827 347,59191 037,4200,00191 037,42
1715191 037,427 641,50198 678,9101 300,000,00197 378,91%
181640000237 378,919 495,16246 874,0710,00825330,000,00246 544,070,00%
1917246 544,079 861,76256 405,8300,00256 405,830,0250%
2018256 405,8310 256,23266 662,0704 000,000,00262 662,070,050%
211925000287 662,0711 506,48299 168,5530,00750187,500,00298 981,05
2220298 981,0511 959,24310 940,2900,00310 940,29Value
2321310 940,2912 437,61323 377,9002 500,000,00320 877,900
242218000338 877,9013 555,12352 433,0220,00725130,500,00352 302,5230000
2523352 302,5214 092,10366 394,620,00,00366 394,6275000
262415000381 394,6215 255,78396 650,4030,00750112,501 800,000,00394 737,90
270,01
282380000,02
290,03
300,04
310,05
320,06
330,07
340,08
350,09
360,1
37
38
Sheet2
Cell Formulas
RangeFormula
C3C3=B3
D3:D26D3=C3*$D$2
E3:E26E3=C3+D3
C4:C26C4=B4+N3
I5,I26,I23,I20,I17,I14,I11,I9,I7I5=B3/INDEX($R$7:$R$9,MATCH($F$3,$R$3:$R$5))
G3G3=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,G9G4=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)),"")
H3H3=IFERROR((B3*G3),"")
H4:H26H4=IFERROR((B4*G4),"0")
N3:N26N3=E3-(H3+I3+L3)
B28B28=SUM(B3:B27)
Cells with Data Validation
CellAllowCriteria
F3:F26List=$R$4:$R$6
D2List=$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.
 

Some videos you may like

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.

Watch MrExcel Video

Forum statistics

Threads
1,114,071
Messages
5,545,817
Members
410,708
Latest member
SanTrapGamer
Top