aurelius33
New Member
- Joined
- May 28, 2024
- Messages
- 3
- Office Version
- 365
- Platform
- Windows
So I'm constructing what will be a large spreadsheet with something like 10k to 12k rows at 15 to 17 columns wide in a single table. I am attempting to keep the formulas simple and not run deep nested IFs and the like. The terms for a loan are defined in what I'm calling the blue zero line. Under this line would be the first payment for the loan defined above in the blue line. With new loans being added every other week, keeping the formulas contained within a line and/or using simple directions is key. Resorting this thing and having to redefine all the fixed cells ($) would be a pain. The terms in the blue line are layed out as:
In between blue term lines are:
My issues are:
D2 : beginning date of the loan
E2 : the expected payment
F2 : the numbers of expected payments
H2 : the rate (%)
I2 : the beginning balance
In between blue term lines are:
Column A : the who
Column B : the payment numbers (shortened for this example)
Column C : the status of the payment (blank = not time yet, 1 = payment made, 2 = partial or special circumstances, 3 = not made)
Column D : dates of the remaining payments
Column E : the expected payment I manually copy from the expected payment calculation. A formula could be used once I see how this sheet handles loaded full of data.
Column F : any additional payments
Column G : the principal for that row's payment
Column H : the interest for that row's payment
Column I : the balance after the payment
Column J : any and all cumulative missed payments and/or shortages on payments.
My issues are:
- The prior column needs to hold a running total of missed payments. I have the interest applying to the balance in the event of a missed payment(s).
- I have conditional formatting hiding the expected payment in column E so one can tell at a glance where someone is on the loan. I could also use the status column but it just doesn't seem that intuitive to me. Is there a better approach someone knows of?
Cell Formulas | ||
---|---|---|
Range | Formula | |
G3:G13,G15:G23 | G3 | =[@[L1 Pmt]]-[@[L1 Int]] |
H3:H13 | H3 | =ROUND(I2*0.165*([@[L1 Date]]-D2)/365,2) |
I3:I13,I15:I23 | I3 | =IF([@Status]=3,[@[L1 Int]]+I2,I2-[@[L1 Prin]]) |
J3:J13,J15:J23 | J3 | =I2-[@[L1 Balance]]-[@[L1 Prin]]+J2+[@[L1 Add]] |
D4:D7,D15:D23,D9:D13 | D4 | =D3+7 |
E2,E14 | E2 | =ROUND(([@[L1 Int]]/52+[@[L1 Int]]/52/((1+[@[L1 Int]]/52)^[@[L1 Add]]-1))*[@[L1 Balance]],2) |
E3:E13 | E3 | =IF([@Status]>=3,0,IF(872.93+[@[L1 Add]]<=I2,872.93+[@[L1 Add]],I2+[@[L1 Int]]+[@[L1 Add]])) |
E15:E23 | E15 | =IF([@Status]>=3,0,IF(738.67+[@[L1 Add]]<=I14,738.67+[@[L1 Add]],I14+[@[L1 Int]]+[@[L1 Add]])) |
H15:H23 | H15 | =ROUND(I14*0.125*([@[L1 Date]]-D14)/365,2) |
Cells with Conditional Formatting | ||||
---|---|---|---|---|
Cell | Condition | Cell Format | Stop If True | |
E2:E23 | Expression | =(C2="") | text | NO |