Apply matrix to adjust numbers and due dates on loans

[brandonmcg]

This is for loans on our system that have the wrong due date.

I need a formula to adjust due dates using the rules below and the attached matrix.

I will use a spreadsheet with loan numbers, number of months (+/-) the due date is off and the loans current due date.

I will paste the data on one tab and then need to have the loan number, adjusted number using the matrix and new date on another tab There can be a third tab with the matrix.

Loan # Number of Months Due Date Difference Current Due Date which will be different for each loan
1234567890 -108 06/01/22
2345678901 -096 01/01/22

Using the new data I need to see on the new tab
Loan # Adjusted using the matrix New Due Date
1234567890 10Q 06/01/13
2345678901 09O 01/01/14

What the guide says

(+/-) DUE DATE ADJ The number of periods to increase or decrease the due date. The system adds the number to the existing next payment due date.
To change the due date to an earlier date, type two digits and a negative replacement character in the last position. Refer to the "Positive and Negative Values" topic for additional information.

Example: The date is 12/01/18 and the loan's period is 12 (monthly). You want the new payment due date of 10/01/18, which is two months earlier (002). Type 00 followed by the replacement character for 2.

Positive and Negative Values
To specify a positive amount or quantity, type the actual number or a positive replacement character in the field's last entry position.
To specify a negative amount or quantity, type a negative replacement character in the field's last entry position.
The system rejects free-form transactions that include minus (-) signs.

See the attached matrix

Attachment 785019


Final Positive Negative
Digit of Replacement Replacement
Number Character Character
1 A J
2 B K
3 C L
4 D M
5 E N
6 F O
7 G P
8 H Q
9 I R
0 { }