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 { }

[AliGW]

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, worked examples where required, relevant cells highlighted and a few explanatory notes.

Administrative Note:

Members will tailor the solutions they offer to the version (NOT release number) of Office (Excel, NOT Windows) that you have. Please check that your forum profile is up-to-date in this respect. If you aren't sure, in Excel go to File | Account and report what it says below the MS logo at the top of that page. If your version is for Mac, please also state this. Thanks.