Lookup remaining balance by month and payee

[csenor]

Hello. I have a table that keeps track of my debt payments. 3 of the columns are: Date, Payee, and Remaining Balance. I want to create another formula table or pivot table to see the Remaining Balance of each Payee by Month. I thought the following formula would work, but it's not recognizing the payee. It is only looking at the last date in the month and returning the remaining balance.

I tried to do this with a pivot table, but it doesn't look like it will find the last value of that month. It only lets you show the value as the min or max, which necessarily might not work if the credit card ends up going up by making a big purchase and making just the minimum monthly payment.

Formula: =LOOKUP(EOMONTH($a2,0)&B$1,Date&Payee,Remaining_Balance)

Formula Table:

	A	B	C	D	E
1		CREDIT CARD 1	CREDIT CARD 2	COLLEGE LOAN	CAR LOAN
2	3/1/14
3	4/1/14
4	5/1/14

<tbody>
</tbody>

 

[cyrilbrd]

Hi, not sure to understand your query "last value of that month", however shouldn't you add a column with payee name?
Your data is not optimal for pivot. better to minimise number of columns such as:

name	date	type	amount
A	1/1/14	COLLEGE LOAN	627
B	1/2/14	CREDIT CARD 1	132
A	1/3/14	CREDIT CARD 2	120
E	1/4/14	COLLEGE LOAN	549
B	1/1/14	CAR LOAN	173
C	1/2/14	CREDIT CARD 2	230
D	1/3/14	CAR LOAN	189
A	1/4/14	CREDIT CARD 1	895

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

 

[csenor]

My debt payment table is set up like your example. What I mean by last value of that month is I want to locate the last date a payment was made by each payee made and return the remaining balance. I want to be able to create a chart to show the remaining balances by month. There may be times when multiple payments are made in a month. I need to find the last one and see the remaining balance.

 

[csenor]

I've also tried to do this using the following formula. Column 7 is the remaining balance column in my debt payment table. This also does not recognize the Payee. It only recognizes the date and returns the remaining balance.

=INDEX(Table1,MATCH($a2&b$1,Date&Payee,1),7)

 

[cyrilbrd]

given in A1 raw as follows:

name	date	type	amount
A	1/1/14	COLLEGE LOAN	627
B	1/2/14	CREDIT CARD 1	132
A	1/3/14	CREDIT CARD 2	120
E	1/4/14	COLLEGE LOAN	549
B	1/1/14	CAR LOAN	173
C	1/2/14	CREDIT CARD 2	230
D	1/3/14	CAR LOAN	189
A	1/4/14	CREDIT CARD 1	895

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

results in A15 as follows:

A	1/4/14	CREDIT CARD 1	895
B	1/2/14	CREDIT CARD 1	132
C	1/2/14	CREDIT CARD 2	230
D	1/3/14	CAR LOAN	189
E	1/4/14	COLLEGE LOAN	549

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Formula in B15 is =MAX(IF($A$2:$A$9=A15,$B$2:$B$9)) Ctrl + Shift + Enter not just enter on a pC or Command + Return on a MAC. Copied down till needed.
Formula in C15 is =IFERROR(INDEX(C$2:C$9,MATCH(1,($A15=$A$2:$A$9)*($B15=$B$2:$B$9),0)),"") Ctrl + Shift + Enter not just enter on a pC or Command + Return on a MAC.
Copied to the right and down till needed.

 

[csenor]

Payment Table:

	A	B	C	D	E
1	Date	Payee	Payment	Current Balance	Remaining Balance
2	3/3/14	Credit Card	50	1500	1450
3	3/15/14	Car Loan	250	12000	11750
4	3/18/14	College Loan	75	5000	4925
5	3/20/14	Credit Card	75	1450	1375
6	4/2/14	Credit Card	50	1375	1325
7	4/15/14	Car Loan	250	11750	11500
8	4/25/14	College Loan	75	4925	4850
9	4/28/14	Credit Card	100	1500	1400

<tbody>
</tbody>

Formula Table to find remaining balance at the end of each month

	A	B	D	E
1		CREDIT CARD	COLLEGE LOAN	CAR LOAN
2	3/1/14	1375	4925	11750
3	4/1/14	1400	4850	11500
4	5/1/14

<tbody>
</tbody>

 

[cyrilbrd]

Given your raw data in A1.
And given in A16:

	CREDIT CARD	COLLEGE LOAN	CAR LOAN
3/1/14	1375	4925	11750
4/1/14	1400	4850	11500
5/1/14

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

Formula in B17 is =IFERROR(INDEX($E$2:$E$9,SUMPRODUCT(MAX((MONTH($A$2:$A$9)=MONTH($A17))*($B$2:$B$9=B$16)*(ROW($A$2:$A$9))))-1,0),"") Ctrl + Shift + Enter not just Enter on a pC or Command + Return on a MAC.

 

[Aladin Akyurek]

Sheet1, A:E, houses the data ('Payment Table').

A2:A9 is named as Date via Name Box, B2:B9 as Payee, and E2:E9 as RBalance.

Sheet2, A:E, houses the processing ('Formula Table')

Sheet2, B2, copied across and down:

=IFERROR(LOOKUP(9.99999999999999E+307,1/((Date-DAY(Date)+1=$A2)*(Payee=B$1)),RBalance),"")

 

[csenor]

Thanks Aladin. I fine tuned your formula and I got it to work. This is what I did:

=IFERROR(LOOKUP(200,1/((Date>=$A2)*(Date<=EOMONTH($A2,0))*(Payee=B$1)),Remaining_Balance),"")

Thanks for answering my post Aladin and Cryilbrd.

 

[Aladin Akyurek]

Thanks Aladin. I fine tuned your formula and I got it to work. This is what I did:

=IFERROR(LOOKUP(200,1/((Date>=$A2)*(Date<=EOMONTH($A2,0))*(Payee=B$1)),Remaining_Balance),"")

Thanks for answering my post Aladin and Cryilbrd.

Fine-tuned?

[a] 200 in lieu of 9.99999999999999E+307? See http://www.mrexcel.com/forum/excel-questions/102091-9-9999999-a.html



((Date>=$A2)*(Date<=EOMONTH($A2,0))

is exactly the same thing as:

(Date-DAY(Date)+1=$A2)

and less efficient.

[c] See the workbook that implements the suggestion as I made it:
https://dl.dropboxusercontent.com/u/65698317/csenor%20Lookup%20remaining%20balance%20by%20month%20and%20payee.xlsx