# Loans File; attempt to track a loan's balance at one year prior to another's commencement

1. ## Loans File; attempt to track a loan's balance at one year prior to another's commencement

I have a contingent notion to construct a loans File; this file would attempt to track a loan's balance (Loan Mark I) at one year prior to another, subsequent loan's commencement (Loan Mark II), as well as the loan's balance (Loan Mark I) when Loan Mark II commences. So, if Loan MK I commences on 3/5/2419, then Loan II commences on 5/7/2421, I want to present the balance of Loan MK I at both 5/7/2420 and 5/7/2421.

A sort of pyramiding effect could also if one consider further loans following Loan MK II. For example, Loan MK III commences on 9/22/2423. Now one wants to keep track of the balance of both Loan MK I and Loan MK II at their respective balances at both 9/22/2422 and 9/22/2423.

Anyone have any notion of what functions in Excel could easily present this situation?

I have a contingent notion to construct a loans File; this file would attempt to track a loan's balance (Loan Mark I) at one year prior to another, subsequent loan's commencement (Loan Mark II), as well as the loan's balance (Loan Mark I) when Loan Mark II commences. So, if Loan MK I commences on 3/5/2419, then Loan II commences on 5/7/2421, I want to present the balance of Loan MK I at both 5/7/2420 and 5/7/2421.

A sort of pyramiding effect could also if one consider further loans following Loan MK II. For example, Loan MK III commences on 9/22/2423. Now one wants to keep track of the balance of both Loan MK I and Loan MK II at their respective balances at both 9/22/2422 and 9/22/2423.

Anyone have any notion of what functions in Excel could easily present this situation?

The key data involved would entail:

Borrower Name
Loan Order (i.e. in what order did a person take this loan; as his or her first loan, second loan, etc.)
Date of Loan
Original Loan Amount
Payment Amount (amount of each individual loan payment for the loan; e.g. \$100 every two weeks)
Date of First Payment
Frequency of Loan (monthly, fortnightly, etc.)

So for a person who had three loans, one would keep track of:
Number of payments made on first loan when second loan commenced
Number of payments made on first loan when third loan commenced
Number of payments made on second loan when third loan commenced
Number of payments made on first loan one year prior to when second loan commenced
Number of payments made on first loan one year prior to when third loan commenced
Number of payments made on second loan one year prior to when third loan commenced

To present the situation, in a situation with a person with three loans:

I had an idea of using an Offset function, with reference to the first loan. To chart the case hypothetically noted above, the offset would entail:

0, 1, 2
1, 2
1

I also contemplated the Index and/or Match functions as helpful.

2. ## Re: Loans File; attempt to track a loan's balance at one year prior to another's commencem

An update of this situation. One finds the following response.

I had set up the file so that one column featured

Prior Loans

This section presented the number of loans preceding the loan logged in the present loan for the row. So, the first loan lacked preceding loans, so 0.

Another row featured the row value of the current row. So, the row containing the first loan featured a row value of 2 (this occurred as the first row in the sheet featured labels, hence displacing by one the data rows).

For this example, mentally construct five loans for Ms. Margo L--- and three loans for Ms. Myra R---

So, to present this situation visually:

Prior Loans Row Value Prior Loans-Row Value

0 2 2

1 3 2

2 4 2

3 5 2

4 6 2

0 7 7

1 8 7

2 9 7

3 10 7

Therefore, I determined that Prior Loans-Row Value will always equal the row value of the first row to feature a respective participant's first loan.

So, to assure that I will always arrive at the start of the loan range for the respective participant, one constructs the following formula:

Perhaps also:

One can use the Offset function to travel around using this formula. The second loan always occurs, perforce, in the second row of the respective participant's list of loans. The Offset functions allows one to retrieve adjacent data, horizontally (C) or vertically (R).

The Indirect function served quite helpfully in this situation, allowing one to retrieve data from the address function.

3. ## Re: Loans File; attempt to track a loan's balance at one year prior to another's commencem

I have an update on this situation.

I contemplated summing the balance left on all prior loans when a subsequent loan occurs. The columns of data appear as follows.

Balance left on first loan when second loan commenced
Balance left on first loan when third loan commenced; Set as First Column
Balance left on first loan when fourth loan commenced; Set as Second Column
Balance left on first loan when fifth loan commenced; Set as Third Column
Balance left on second loan when third loan commenced; Set as Fourth Column
Balance left on second loan when fourth loan commenced; Set as Fifth Column
Balance left on second loan when fifth loan commenced; Set as Sixth Column
Balance left on third loan when fourth loan commenced; Set as Seventh Column
Balance left on third loan when fifth loan commenced; Set as Eighth Column
Balance left on fourth loan when fifth loan commenced; Set as Ninth Column

Some guides:
The first loan precedes four loans
The second loan precedes three loans and follows one loan
The third loan precedes two loans and follows two loans
The fourth loan precedes one loan and follows three loans

One would add up data as follows:

Balance left on first loan when third loan commenced+Balance left on second loan when third loan commenced; so C1+C4

Balance left on first loan when fourth loan commenced+Balance left on second loan when fourth loan commenced+Balance left on third loan when fourth loan commenced; so as C2+C5+C7

Balance left on first loan when fifth loan commenced+Balance left on second loan when fifth loan commenced+ Balance left on third loan when fifth loan commenced+Balance left on fourth loan when fifth loan commenced; so as C3+C6+C8+C9

The number pattern seems as follows:
Offset 3
Offset 3, 2
Offset 3, 2, 1

3-0=3
3-1=2
2+1=3
3-1=2
2-1=1

I sought to try to find a way to automate the relations amongst numbers.

There are currently 1 users browsing this thread. (0 members and 1 guests)