I don’t recall it being shared if this is what you’re referring to?
That’s the one!!
@Lewis_P any chance of sharing it or making it public? I would find it very useful!
I made a mortgage overpayment calculator myself recently too.
If anyone’s interested I can put it into Google Sheets?
Hopefully you can save a copy of that?
It’s doing two things - 1 will show you the difference if you overpay each month in your total paid / when you pay off the mortgage (first sheet has a comparison graph of Total £, second sheet just has data with headings that might be useful.
2nd thing - is - assuming most people will re-mortgage at the end of a fixed rate period - you can enter some (hypothetical) rates for your future mortgages, and it will adjust the rates etc as you enter each mortgage period.
If you just want a comparison of a single mortgage, enter two rows in the main table - 1 with the fixed rate period / %, the 2nd with the total remaining term in Months, with the standard rate.
The charts went a bit wonky with an upload to Google but hopefully that can be useful to someone? Wasn’t really intended for sharing so please excuse the slightly dodgy set up and formatting!
The “no overpayment” tab needs no input - it will take it all from the first sheet
Shout if any of it doesn’t make sense and I can try and explain inputs better!
Edit: Hmm the main graph broke, will see if I can fix but it’s currently missing the mortgage total value as it decreases over the term, which was on the 2nd axis in Excel. Happy to share the excel file direct if anyone wants that.
Oooo thanks @BritishLibrary I’ll take a proper look tomorrow when I’m on the PC but it sounds spot on
Just out of interest, has anybody found/got anything similar to the mortgage calculator but for a PCP?
Beyond the headline figures, the way the numbers work has always been a mystery to me. I’d love to be able to track the status over time, calculate a settlement figure and see the impact of overpayments in my own Google Sheet.
I can probably make something, I like a challenge!
Are you happy to share the numbers you do have? Or real world ones that I can use to make something?
Thanks! I can dig out the paperwork and get the numbers I’m sure, but broadly speaking the variables you’re looking at are:
Vehicle Value/Amount Financed (without interest)
Guaranteed Future Value/Balloon
And that should give you the:
Total Amount Financed
I’ve given this a go in the past but where I’ve fallen down previously is calculating interest. Looking at the documentation available for Google Sheets, there are a tonne of formulas you can use to calculate interest. Working out the right one to be using has been the difficult bit.
What’s a PCP, please?
Edit - I had to go to Google as I couldn’t remember what it stood for. I could remember it’s for purchasing a car 🤦
Hi all - have been having a little look around and it doesn’t look like the data point exists to me but thought I’d better check and see if one of our resident data geniuses could confirm/help please.
Has anyone found a way to pull whether a transaction is excluded from summary or not out of the sheets export?
I don’t think it does.
If you always exclude the same category, you could fudge it yourself with a helper column.
=IF(B1=“Expenses”,1,0) and then with a SUMIF you’d sum anything with a 0.
Thanks for confirming.
Not always the same category unfortunately but might think about how I do that.
Or alternatively, you could use the comments / add a hashtag to transactions that are/are not in summary and achieve a similar goal?
Is it always the same few? So you might exclude rent/bills/savings or something? But you don’t exclude food etc?
Or does it vary month to month?
This is what I’m having a think about right now actually.
Last month I had a big dentist spend, which I excluded because didn’t want to mess up summary, but I’ve kept it in my health category for visibility.
Occurrences like that aren’t that common thankfully, but there are a few things every now and then that I want to keep in category but exclude from summary because they really are so unusual and will just mess my budgeting up.
So…I probably could put them all in expenses but think I would prefer the visibility within category history.
So maybe this is my solution! Given they aren’t too common, wouldn’t be too much hassle to manually update the sheet. Thanks.
Assuming you’re consistent with how you add comments - like if you add “#exclude” to every relevant transaction, you should be able to make a one-off mechanism in your sheet to filter it out.
Not sure how you’d intend to present the data, but a similar formula to what Revels posted but on the notes field should do it.
All that said though this just reminds me that the data in this export could have so much more utility - if they included some of this “meta” data about the transaction relevant to your budget.
This is what I do. Works a dream.
Another possibility is to make an “excluded” category.