Stclements1
Board Regular
- Joined
- Sep 15, 2018
- Messages
- 150
- Office Version
- 365
- Platform
- Windows
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| C3 | C3 | =B3 |
| D3:D26 | D3 | =C3*$D$2 |
| E3:E26 | E3 | =C3+D3 |
| C4:C26 | C4 | =B4+N3 |
| I5,I26,I23,I20,I17,I14,I11,I9,I7 | I5 | =B3/INDEX($R$7:$R$9,MATCH($F$3,$R$3:$R$5)) |
| G3 | G3 | =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,G9 | G4 | =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)),"") |
| H3 | H3 | =IFERROR((B3*G3),"") |
| H4:H26 | H4 | =IFERROR((B4*G4),"0") |
| N3:N26 | N3 | =E3-(H3+I3+L3) |
| B28 | B28 | =SUM(B3:B27) |
| Cells with Data Validation | ||
|---|---|---|
| Cell | Allow | Criteria |
| F3:F26 | List | =$R$4:$R$6 |
| D2 | List | =$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.
