Two partners sharing revenue for rental property

Hi.

I was trying to make an excel sheet with these requirements with help from chatGPT but it was headache.
Suddenly it come in my mind that there are many people with this knowledge who might solve this quickly than me.

Case:
I am wondering if it is possible to make an excel sheet for a shared property investment for two persons.
Where total price of property is 575 000
Person A pays cash 200 000
Person B pays cash 75 000
Loan from bank will be: Total price of property - (Person A cash paid + Person B cash paid) = 300 000

earning from property is monthly 3000
Loan + rent to pay is with almost 5.75% interest to bank

Is it possible to make an excel sheet where earning can be divided by percentage paid of each persons paid part of investment.
And where also loan to pay is divided by who much loan each person owns the bank.

I​f person A and B agree they put all earnings in bank loan to pay loan but Excel should decrease loan for each person as their percentage of investment paid.
This means if person A has less to pay and no more cash is added by anyone, than person A should be able to cash out his part of earnings quickly after his part of loan is paid.
Same could be if Person B pays his part of loan with cash quickly and can cash out his earning.

I hope someone can help me out.

Kind regards

Sensemaker

Look at the file I have configured. I have run the scenario out 60 months.

Herer are first 5 months to see how it looks

Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F	G
1	Partner	Down Pmt	Pct	Loan	Monthly Income
2	A	200000	=B2/($B$2+$B$3)	=ROUND(C2*300000,2)	=ROUND(C2*3000,2)
3	B	75000	=B3/($B$2+$B$3)	=ROUND(C3*300000,2)	=ROUND(C3*3000,2)
4
5	Month	Balance A	Balance B	Partner A Interest	Partner B Interest	Avail for Princ A	Avail for Princ B
6	1	=D2	=D3	=ROUND(B6*0.0575/12,2)	=ROUND(C6*0.0575/12,2)	=$E$2-D6	=$E$3-E6
7	2	=B6-F6	=C6-G6	=ROUND(B7*0.0575/12,2)	=ROUND(C7*0.0575/12,2)	=$E$2-D7	=$E$3-E7
8	3	=B7-F7	=C7-G7	=ROUND(B8*0.0575/12,2)	=ROUND(C8*0.0575/12,2)	=$E$2-D8	=$E$3-E8
9	4	=B8-F8	=C8-G8	=ROUND(B9*0.0575/12,2)	=ROUND(C9*0.0575/12,2)	=$E$2-D9	=$E$3-E9
10	5	=B9-F9	=C9-G9	=ROUND(B10*0.0575/12,2)	=ROUND(C10*0.0575/12,2)	=$E$2-D10	=$E$3-E10

Sheet: Sheet1

Excel 2016 (Windows) 64 bit

	A	B	C	D	E	F	G
1	Partner	Down Pmt	Pct	Loan	Monthly Income
2	A	200000	0.727273	218181.82	2181.82
3	B	75000	0.272727	81818.18	818.18
4
5	Month	Balance A	Balance B	Partner A Interest	Partner B Interest	Avail for Princ A	Avail for Princ B
6	1	218181.8	81818.18	1045.45	392.05	1136.37	426.13
7	2	217045.5	81392.05	1040.01	390	1141.81	428.18
8	3	215903.6	80963.87	1034.54	387.95	1147.28	430.23
9	4	214756.4	80533.64	1029.04	385.89	1152.78	432.29
10	5	213603.6	80101.35	1023.52	383.82	1158.3	434.36

Sheet: Sheet1

Hi alansidman.


Many thanks.
Column B, Cell B2 + B3 is what is paid at the moment today in cash to buy property.
I maded change in B2 and B3 to get value from each partners in I and J.
This could be static in start but we need to know percent each have paid later to get out profit.


I have now added exact figure and some more date to get it flexible since expenses will be also added through the year.
But i am not sure how it will work. Since every time expenses will be added we do not want to change data that already have been paid down.
Because this is a long term investment but we also want to be flexible for which one of us want to pay down his loan early can pay and get his part of interest from income.

I have added following Fields.
Share to Pay in F2 for Person A
Share to Pay in F2 for Person B

Calculated rest percent of payment for Percent A in G2
Calculated rest percent of payment for Percent A in G3

Column I will show what is paid in cash by Person A. There will be possible to pay down in future
Column J will show what is paid in cash by Person B. There will be possible to pay down in future

Column K is Loan taken from bank at the date of buying. Which make the total sum of property purchase (1 350 000 + 775 000 + 445 000) = 2 575 000
Column L is sum of total expenses from Column M. I am not sure if we need to do it this way or just add price of propert and use
another column for Expenses which will be shared equal 50%.

Column N Description of expenses.
Column O is the Monthly rent/income from property. Property i at the moment rented out to two persons for 10000+9000.
Rent can be changed next year. or when person renting leaves and we need to find a new. Do we need to make two Cells for Rent to each renting person?



So from the loan we need to know how much percent Person A and B have in rest to pay from each part.
I suppose Column Row 5 need to be changed a bit since Balance will be changed when you now change the sheet.

We need to know is What is Rest balance Left to pay for Partner A and Partner B,
Interests to pay to bank from loan for each partner after their Rest PCT to pay calculated in G2 and G3

When Balance for one Partner is reached Zero or paid his part it should stay at Zero until new expenses occurs in form of repairs or something. Only if possible.

Interest will be paid out to Person A or B as how much they ha have paid of his part.
This means if Person A have paid down his part and have 36% rest loan as calculated in G2,
person A will take out 64% profit. Same will be calculated for Person B after value in G3.


This got a much more advanced but i hope you or someone could help me out.

I pasted same text in Excel sheet attached to work easy in one screen.