Mortgage interest spreadsheet

VXman

Not sure where to put this post so thought I'd try here.

I have loaned/bought a flat for a my son. He gives me a 'gift' equivilet to rent to us every month ~(legally he owns the flat).

We do try to keep the arrangement like a proper bank loan/mortgage though so I do the mortgae calcs each month so we can see how much is left 'owing'.

Is there a spread sheet out there to help me track/calculate this. i.e. opening balance, payments in, interest calulated monthly based on a given rate, intersted added each month etc.

Newbie_John

Not per month, but this shows remaining balance per year:
https://www.moneysavingexpert.com/mortgages/mortgage-rate-calculator/

there's also a version with overpayments if he ever would like to do so:
https://www.moneysavingexpert.com/mortgages/mortgage-overpayment-calculator/

I guess if you want it to be more granular you can divide balance by 12.. 

Or for more details:
https://forums.moneysavingexpert.com/discussion/1157173/my-excel-mortgage-spreadsheet

silvercar

VXman said:

Not sure where to put this post so thought I'd try here.

I have loaned/bought a flat for a my son. He gives me a 'gift' equivilet to rent to us every month ~(legally he owns the flat).

We do try to keep the arrangement like a proper bank loan/mortgage though so I do the mortgae calcs each month so we can see how much is left 'owing'.

Is there a spread sheet out there to help me track/calculate this. i.e. opening balance, payments in, interest calulated monthly based on a given rate, intersted added each month etc.

More correctly, and I hope you both understand this for the future:

I have gifted my son money that he has used to buy a flat for a my son. He gives me a 'gift' equivilet to rent to us every month ~(legally he owns the flat).

We do try to keep the arrangement like a proper bank loan/mortgage though so I do the mortgae calcs each month so we can see how much is left 'owing'.  He gifts me money each month that will stop eventually.

You could clearly afford this, so why do you need to charge interest? Especially as it is your son? What does your son gain from this, he would be paying the same if it was a mortgage with a commercial lender. There is a question on whether you should be declaring the interest you are charging and paying tax on it. To me it looks like a loan, both parties are treating it like a loan, so hmrc may also do so. 

VXman

silvercar said:

VXman said:

Not sure where to put this post so thought I'd try here.

I have loaned/bought a flat for a my son. He gives me a 'gift' equivilet to rent to us every month ~(legally he owns the flat).

We do try to keep the arrangement like a proper bank loan/mortgage though so I do the mortgae calcs each month so we can see how much is left 'owing'.

Is there a spread sheet out there to help me track/calculate this. i.e. opening balance, payments in, interest calulated monthly based on a given rate, intersted added each month etc.

More correctly, and I hope you both understand this for the future:

I have gifted my son money that he has used to buy a flat for a my son. He gives me a 'gift' equivilet to rent to us every month ~(legally he owns the flat).

We do try to keep the arrangement like a proper bank loan/mortgage though so I do the mortgae calcs each month so we can see how much is left 'owing'.  He gifts me money each month that will stop eventually.

You could clearly afford this, so why do you need to charge interest? Especially as it is your son? What does your son gain from this, he would be paying the same if it was a mortgage with a commercial lender. There is a question on whether you should be declaring the interest you are charging and paying tax on it. To me it looks like a loan, both parties are treating it like a loan, so hmrc may also do so. 

Not that its really any of your business but I have bitten the bait....  legally we gave him the money to buy the house. His name is on the deeds. If he wanted to he could walk away and never give me another penny.

He can afford to pay rent/mortgage but due to the nature of his job (freelance/unpredictable) and where he lives (highest prices in the UK) he was never going to be able get a mortgage or a deposit big enough I didn't want to see him spending his life wasting his money renting.

I had the capitial but I cannot afford to just 'give it away' as I'm retired. I need the income from the funds. Actually by giving it to him I'm making a loss as it could be invested somewhere making a lot more and be mine if  I wanted to blow it all on a Ferrari. In practice he's never going to pay it off before  I die anyway and then it will come to him. So what ever 'interest' is added it doesn't really make any difference it's just a theroetical exercise on a piece of paper.

Why bother with the calculations? - well I have another child who has bought her house off her own means and I want it to be fair so I'm trying to treat him like a bank would. Ihave thought through the inheritance side of things and both children understand what will happen to make it fair and equal when that happens. 'The  'interest' aspect of this actually has an impact in this area.

I never got given any help from my parents and I want my kids to make their own way in life without freebies. This is my way of helping without it being free money for him. It works for him and works for me.

silvercar

I wasn't having a go.

Our way of helping our eldest on to the property ladder (he could never have obtained the mortgage he needs buying on his own where he wants to live) was to gift him the deposit and for my OH to go on the mortgage with him. So the property is in his name, the mortgage is in joint names. This had 2 advantages, obviously it meant he could buy rather than renting forever, it also meant that he has a decent size 2 bed flat and (at least until he can afford the mortgage on his own) he can rent out the second bedroom to increase his income to afford the mortgage.

When our other son wants to buy, we will gift him a similar deposit, though he will buy with his wife so won't need a 'sole proprietor, joint borrower mortgage'.

stuartp2000

I use a mortgage spreadsheet from Vertex42