Sorry, I should have said, I’m only really interested in what’s in Monzo. My mortgage I know I owe loads on!
@BritishLibrary I tried some variation of sumifs but couldn’t get it quite right.
I don’t have Plus anymore, but just thinking off the top of my head you should be able to accomplish this with a pivot table. Just uncheck the fields you don’t want it to display.
Ah so it’s not quite as trivial as I thought - and I didn’t quite set up my dasboard to do this.
I think it is doable, because your Transaction list has a history of everything, you can determine your total funds in All Pots with a SUMIF [Payment Type Column] = “Pot Transfer”.
Or sum everything except pot transfers… and that should give you your true actual balance.
You do the same monthly to see your variance month to month.
Will come back with a suggested route.,
1 Like
This is where I’ve stumbled.
If you exclude pot transfers then you don’t get to count what’s in a pot, if you do include them it seems like you’ve spent a lot more.
I might just have to separate it out and see how each transaction type does per month.
So here’s how I’d do it.
I’ve got a tab in my sheet with a “Category Spend by Month” - has some date cells in it which the formula is based on - other ways will
This is the table structure I have. Start date is important.
I normally use this to track each months category spending, but Row 3 is all you need.
Then, in your target month cell of choice, use:
=sumifs('Monzo Transactions'!$H:$H,'Monzo Transactions'!$B:$B,"<"&AL$3,'Monzo Transactions'!$D:$D,"<>"&"Pot Transfer")
Where “AL$3” is your “Start Date” cell.
That will give you your Net Worth up until the last day of each month.
In this view, you can then just add a simple This Month - Last Month and see the difference.
So it’s counter intuitive, but by excluding it as a spend, you’re effectively “keeping” it in your total balance amount - because the Transaction list knows every move in and out of your main balance - it’s as if you never “moved it” out of your main balance in the first place.
Here’s some data from when I first started using Monzo:
Cell I10 has my forumula as above.
Cell I13/14/15 - are counting the total Pot Transfers, then All Transactions, and adding one to the other - they should match.
The one thing that does mess this up though, is Interest Bearing pots - there’s no history of interest payments to those anywhere in the Sheet. So you’ll be off by some number, unfortunately.
2 Likes
Sorry, I missed this post somehow!
Thanks Ben, I’ll see what I can add but that looks like it makes sense.
1 Like
So after the help from Ben, I have this. Basically shows what I wanted it to, that at the end of each pay period, I had more than the time before.
I’ve taken out the y axis for values and I think I’ll take a bit of a battering this month (car insurance + Christmas coming out of pots) but overall I’m happy that it’s a positive increase most months.
6 Likes
#megaPulse
6 Likes
Looks great!
(Orange is 2020, grey is 2019, dotted grey is 2018)
You could add months to a pivot table then add that column as a label to the graph. That will make the months a little clearer on your graph.
3 Likes
#megamegaPulse
3 Likes
Right! That is a thing of absolute beauty. Need to absolutely steal that idea
4 Likes
That’s a good idea, I might have to do that!
1 Like
Its not entirely related to monzo, but I recently added my mortgage to my finance spreadsheet dashboard and replicated the mortgage overpayment calculator in google sheets;
It helped me come up with an overpayment game plan to cut 15 years of the mortgage term and save £30k. I can now see exactly how much interest my mortgage is gaining every day (~£10!!!), how much of my mortgage payment actually reduces my mortgage balance, and I can see the exact impact of overpaying today versus any other day.
It was a massive pain to set up and has slowed down my spreadsheets significantly as it has to calculate compound interest everyday for decades. But the added visibility and understanding of my mortgage is invaluable.
8 Likes
You’re my hero and idol all wrapped in one.
But I don’t want that because it will scare me into never spending another penny.
When @BritishLibrary sees this he’s going to have to explain to his partner why he isn’t coming to bed tonight.
4 Likes
Bravo 
- outstanding work to bring an instant visual 
moment to your financial outlook 
5 Likes
I mean I was literally just saying “I think I’m going to have an early night, I just need to check on my 3d printer…” But now THIS.
I have a similar tool in Excel - but designed for comparing the costs of comparing Mortgages and “stacking” mortgage deals back to back - In our current house search I wanted to not only see what the best deal now was, but get a measure of some typical expected costs if we, say, remortgaged every 2 years vs 5. A proper fun sheet.
Out of interest @Lewis_P - is this using some form of the PMT formula to calculate or are you doing compound interest differently?
I went about it the long way. The mortgage data sits alongside all of my other metrics (bank balances, health stats, car stats, utilities etc).
So there’s a row per date, with calculations for interest accrued for that date etc. Its definitely not the smartest or fastest way to do it, but it keeps everything in line and comparable. So I can see what impact each mortgage payment makes on the interest per day for example.
I try to keep all of my key metrics in one sheet to allow me to do comparisons across categories. For example I can compare miles driven versus electricity use per day, or number of steps versus bank balance, or the amount of solar exported versus interest accrued (they’re not all useful comparisons haha).
Another screenshot showing the impact of overpayments;
2 Likes
Would anyone be interested in contributing to a library of sheets that can be easily copied and pointed at your own data?
I could set up and help curate a topic on that if there’s interest? I’d have to look for others for the templates, though!
6 Likes
There’s some super experts here but I’m happy to help where I can.