Myself and my partner are currently in the process of buying our first home and, to make things a little easier for ourselves, I created a spreadsheet to help track our affordability throughout the process. It’s been super helpful, so I thought I’d share for other people to use!
**To use the sheet, you’ll need to make your own copy. To do this, open the link and click *File > Make a Copy.**
The sheet is separated into 4 sections, and all you need to do to use it is enter your information wherever the bright yellow boxes tell you to. The rest of the sheet is auto-calculated based on different formulas.
## **Each Section Explained**
**The first section** calculates your initial costs with the purchase of the house. You enter the mortgage loan amount you’ve been approved for (if you’re not mortgage approved yet don’t worry – You can use the third section of the sheet to get an estimate and use that to continue with the sheet and prepare your budget), enter the Help to Buy amount (if applicable, otherwise leave blank), your current savings (which you can update as you go. Also, make sure to include any financial gift(s) in your savings amount). Next, choose whether it’s a new build or not (affects stamp duty calculation), and enter the price of the house. Finally at the bottom, enter any additional costs like your solicitor fees, broker fees etc. You can enter estimates if you want, to begin with, and update them as you go.
The “Left to Spend” value will auto-calculate how much money you have left over after all costs are deducted.
In **the second section**, enter any additional items you’re purchasing for the house. Your television, furniture etc. (You can even choose to add a Sorny TV, but I went for the superior Carnivalé due to it’s durable outer-casing to prevent fall apart). All of these items will automatically reduce your “Left to Spend” amount as you add them.
**The third section** can be used if you’re trying to see what mortgage loan amount you may need in order to afford your home. Enter your salary (or combined salary if you’re a couple), and the LTI (loan to income) rates will automatically be calculated. If you haven’t been approved for a mortgage yet, enter your salary to find out your 3.5 LTI amount, then enter that amount as your Loan Amount in the first section. *Update: I’ve added additional LTI rates starting from 2. Thank you to /u/The_Chaos_Causer for the suggestion!*
The **fourth section** is just to be used purely to see how you’re doing with your savings. Enter the total amount you saved each month and your average monthly savings will be calculated. This can be useful when chatting initially with banks or mortgage brokers.
Really hope someone finds this useful! If anyone has any suggestions for updates/fixes/changes, let me know!
This is freaking brilliant! Thank you. We’ve just put a deposit down for a new build. Starting to navigate all the finances next, this is gonna be a great help. Cheers.
Great work, thanks for sharing. I ll pass it to my friends looking right now for their home!
Just started this process with my partner aswell and I also made a spreadsheet with the most basic of calculations to help us narrow down the list of houses we have favourited (probably over 100 right now).
Good to see great minds think alike! Will be using, thank you!
I made a comment but deleted it. This is amazing I put together something similar but not nearly as user friendly.
I made some small, and some large changes to yours…
For me in my process the house price was key so I moved that section to the top and changed things to work off of *that* value.
The next step for me was that I then applied for mortgages up to 90% of that amount and saved my 10% deposit. So I added a checkbox utility beside the LTI values so the user can “check” their max approved limit. This now feeds into the Mortgage section automatically via an IFS formula.
I also was able to (figured out a small bug just now that had stopped it working) add a conditional format to highlight the closest possible mortgage amount that matches 90% of the house value ~~(needs some work it looks like as works fine for some values but not others!). ~~
Idea being it gives a quick look at the difference in expected mortgage (LTI 3.5) approved mortgage and the maximum mortgage possible.
With some further work I could probably set it so the user selects how much of a % of deposit they intend to save and have the highlight change to suit that (i.e. they will actually save 20% deposit so they only need a mortgage of 80% of house value.
The last step I might add is to have a warning if users “selected” mortgage is higher than 90% of house price when deposit is not equal to the minimum 10%. Or something like that.
Finally I separated the buying costs and furnishing costs. So added a new balance under Things to Buy which works off of the remainder from Initial Costs and Additional Initial Costs!
My “Left to Spend” is negative €45,000 🙁
Is a 3.5 LTI mortgage common/normal?
I was under the impression that it’s the max they can offer you but typically offer a smaller LTI (unless you’re lucky enough to get one of the 4.5 exemptions).
7 comments
Hey everyone!
Myself and my partner are currently in the process of buying our first home and, to make things a little easier for ourselves, I created a spreadsheet to help track our affordability throughout the process. It’s been super helpful, so I thought I’d share for other people to use!
[**Link to sheet**](https://docs.google.com/spreadsheets/d/1SuBAJStlrmknxw5f2z2yxS8mo8MpwZCwBV9T3pIsDfM/edit#gid=0)
**To use the sheet, you’ll need to make your own copy. To do this, open the link and click *File > Make a Copy.**
The sheet is separated into 4 sections, and all you need to do to use it is enter your information wherever the bright yellow boxes tell you to. The rest of the sheet is auto-calculated based on different formulas.
## **Each Section Explained**
**The first section** calculates your initial costs with the purchase of the house. You enter the mortgage loan amount you’ve been approved for (if you’re not mortgage approved yet don’t worry – You can use the third section of the sheet to get an estimate and use that to continue with the sheet and prepare your budget), enter the Help to Buy amount (if applicable, otherwise leave blank), your current savings (which you can update as you go. Also, make sure to include any financial gift(s) in your savings amount). Next, choose whether it’s a new build or not (affects stamp duty calculation), and enter the price of the house. Finally at the bottom, enter any additional costs like your solicitor fees, broker fees etc. You can enter estimates if you want, to begin with, and update them as you go.
The “Left to Spend” value will auto-calculate how much money you have left over after all costs are deducted.
In **the second section**, enter any additional items you’re purchasing for the house. Your television, furniture etc. (You can even choose to add a Sorny TV, but I went for the superior Carnivalé due to it’s durable outer-casing to prevent fall apart). All of these items will automatically reduce your “Left to Spend” amount as you add them.
**The third section** can be used if you’re trying to see what mortgage loan amount you may need in order to afford your home. Enter your salary (or combined salary if you’re a couple), and the LTI (loan to income) rates will automatically be calculated. If you haven’t been approved for a mortgage yet, enter your salary to find out your 3.5 LTI amount, then enter that amount as your Loan Amount in the first section. *Update: I’ve added additional LTI rates starting from 2. Thank you to /u/The_Chaos_Causer for the suggestion!*
The **fourth section** is just to be used purely to see how you’re doing with your savings. Enter the total amount you saved each month and your average monthly savings will be calculated. This can be useful when chatting initially with banks or mortgage brokers.
Really hope someone finds this useful! If anyone has any suggestions for updates/fixes/changes, let me know!
This is freaking brilliant! Thank you. We’ve just put a deposit down for a new build. Starting to navigate all the finances next, this is gonna be a great help. Cheers.
Great work, thanks for sharing. I ll pass it to my friends looking right now for their home!
Just started this process with my partner aswell and I also made a spreadsheet with the most basic of calculations to help us narrow down the list of houses we have favourited (probably over 100 right now).
Good to see great minds think alike! Will be using, thank you!
I made a comment but deleted it. This is amazing I put together something similar but not nearly as user friendly.
I made some small, and some large changes to yours…
For me in my process the house price was key so I moved that section to the top and changed things to work off of *that* value.
The next step for me was that I then applied for mortgages up to 90% of that amount and saved my 10% deposit. So I added a checkbox utility beside the LTI values so the user can “check” their max approved limit. This now feeds into the Mortgage section automatically via an IFS formula.
I also was able to (figured out a small bug just now that had stopped it working) add a conditional format to highlight the closest possible mortgage amount that matches 90% of the house value ~~(needs some work it looks like as works fine for some values but not others!). ~~
Idea being it gives a quick look at the difference in expected mortgage (LTI 3.5) approved mortgage and the maximum mortgage possible.
With some further work I could probably set it so the user selects how much of a % of deposit they intend to save and have the highlight change to suit that (i.e. they will actually save 20% deposit so they only need a mortgage of 80% of house value.
The last step I might add is to have a warning if users “selected” mortgage is higher than 90% of house price when deposit is not equal to the minimum 10%. Or something like that.
Finally I separated the buying costs and furnishing costs. So added a new balance under Things to Buy which works off of the remainder from Initial Costs and Additional Initial Costs!
My “Left to Spend” is negative €45,000 🙁
Is a 3.5 LTI mortgage common/normal?
I was under the impression that it’s the max they can offer you but typically offer a smaller LTI (unless you’re lucky enough to get one of the 4.5 exemptions).