Lookup remaining balance by month and payee

C

csenor

Board Regular
Joined
Apr 10, 2013
Messages
169
Office Version
  1. 365
Platform
  1. Windows
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:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]CREDIT CARD 1[/TD]
[TD]CREDIT CARD 2[/TD]
[TD]COLLEGE LOAN[/TD]
[TD]CAR LOAN[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3/1/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4/1/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5/1/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Last edited:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Sort by date Sort by votes
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:
[TABLE="width: 324"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]name[/TD]
[TD]date[/TD]
[TD]type[/TD]
[TD]amount[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1/1/14[/TD]
[TD]COLLEGE LOAN[/TD]
[TD]627[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1/2/14[/TD]
[TD]CREDIT CARD 1[/TD]
[TD]132[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1/3/14[/TD]
[TD]CREDIT CARD 2[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]1/4/14[/TD]
[TD]COLLEGE LOAN[/TD]
[TD]549[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1/1/14[/TD]
[TD]CAR LOAN[/TD]
[TD]173[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1/2/14[/TD]
[TD]CREDIT CARD 2[/TD]
[TD]230[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]1/3/14[/TD]
[TD]CAR LOAN[/TD]
[TD]189[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1/4/14[/TD]
[TD]CREDIT CARD 1[/TD]
[TD]895[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.
 
Upvote 0
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)
 
Upvote 0
given in A1 raw as follows:

[TABLE="width: 324"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]name[/TD]
[TD]date[/TD]
[TD]type[/TD]
[TD]amount[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1/1/14[/TD]
[TD]COLLEGE LOAN[/TD]
[TD]627[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1/2/14[/TD]
[TD]CREDIT CARD 1[/TD]
[TD]132[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1/3/14[/TD]
[TD]CREDIT CARD 2[/TD]
[TD]120[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD]1/4/14[/TD]
[TD]COLLEGE LOAN[/TD]
[TD]549[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]1/1/14[/TD]
[TD]CAR LOAN[/TD]
[TD]173[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]1/2/14[/TD]
[TD]CREDIT CARD 2[/TD]
[TD]230[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]1/3/14[/TD]
[TD]CAR LOAN[/TD]
[TD]189[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1/4/14[/TD]
[TD]CREDIT CARD 1[/TD]
[TD]895[/TD]
[/TR]
</tbody>[/TABLE]


results in A15 as follows:
[TABLE="width: 324"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]A[/TD]
[TD="align: right"]1/4/14[/TD]
[TD]CREDIT CARD 1[/TD]
[TD="align: right"]895[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]1/2/14[/TD]
[TD]CREDIT CARD 1[/TD]
[TD="align: right"]132[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD="align: right"]1/2/14[/TD]
[TD]CREDIT CARD 2[/TD]
[TD="align: right"]230[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD="align: right"]1/3/14[/TD]
[TD]CAR LOAN[/TD]
[TD="align: right"]189[/TD]
[/TR]
[TR]
[TD]E[/TD]
[TD="align: right"]1/4/14[/TD]
[TD]COLLEGE LOAN[/TD]
[TD="align: right"]549[/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Upvote 0
Payment Table:
[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]Date[/TD]
[TD]Payee[/TD]
[TD]Payment[/TD]
[TD]Current Balance[/TD]
[TD]Remaining Balance[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3/3/14[/TD]
[TD]Credit Card[/TD]
[TD]50[/TD]
[TD]1500[/TD]
[TD]1450[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3/15/14[/TD]
[TD]Car Loan[/TD]
[TD]250[/TD]
[TD]12000[/TD]
[TD]11750[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]3/18/14[/TD]
[TD]College Loan[/TD]
[TD]75[/TD]
[TD]5000[/TD]
[TD]4925[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]3/20/14[/TD]
[TD]Credit Card[/TD]
[TD]75[/TD]
[TD]1450[/TD]
[TD]1375[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]4/2/14[/TD]
[TD]Credit Card[/TD]
[TD]50[/TD]
[TD]1375[/TD]
[TD]1325[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]4/15/14[/TD]
[TD]Car Loan[/TD]
[TD]250[/TD]
[TD]11750[/TD]
[TD]11500[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]4/25/14[/TD]
[TD]College Loan[/TD]
[TD]75[/TD]
[TD]4925[/TD]
[TD]4850[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD]4/28/14[/TD]
[TD]Credit Card[/TD]
[TD]100[/TD]
[TD]1500[/TD]
[TD]1400[/TD]
[/TR]
</tbody>[/TABLE]




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

[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]CREDIT CARD[/TD]
[TD]COLLEGE LOAN[/TD]
[TD]CAR LOAN[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3/1/14[/TD]
[TD]1375[/TD]
[TD]4925[/TD]
[TD]11750[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]4/1/14[/TD]
[TD]1400[/TD]
[TD]4850[/TD]
[TD]11500[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]5/1/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Given your raw data in A1.
And given in A16:
[TABLE="width: 295"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD]CREDIT CARD[/TD]
[TD]COLLEGE LOAN[/TD]
[TD]CAR LOAN[/TD]
[/TR]
[TR]
[TD="align: right"]3/1/14[/TD]
[TD="align: right"]1375[/TD]
[TD="align: right"]4925[/TD]
[TD="align: right"]11750[/TD]
[/TR]
[TR]
[TD="align: right"]4/1/14[/TD]
[TD="align: right"]1400[/TD]
[TD="align: right"]4850[/TD]
[TD="align: right"]11500[/TD]
[/TR]
[TR]
[TD="align: right"]5/1/14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


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.
 
Upvote 0
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),"")
 
Upvote 0
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.
 
Upvote 0
csenor said:
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.
Click to expand...

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
 
Upvote 0
You must log in or register to reply here.

Similar threads

trishcollins
  • Question Question
Looking for formula/VBA to determine where to best put extra funds
Replies
1
Views
225
trishcollins
trishcollins
S
  • Solved
Extract Balance
Replies
6
Views
199
Steve 1962
S
J
  • Question Question
How to create monthly report for activities based on created date, closed date, and due date, for past year?
Replies
1
Views
430
josevaldesv1
J
S
  • Solved
Conditional formatting - Automatic banding of months
Replies
4
Views
130
etaf
etaf
A
  • Question Question
Daily Interest Loan Scheduler | Missed Payments
Replies
0
Views
220
aurelius33
A

Forum statistics

Threads
1,223,250
Messages
6,171,036
Members
452,374
Latest member
keccles

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top