I've got a spreadsheet I'm using to track a mortgage for a property that is jointly owned by the partners in an LLC. I'd like to track extra principal payments from each partner and somehow figure out how much they affect the ownership ratio. For example, if one partner decides to pay off an extra $500 one month, they don't just own $500 more of the property than the other partner; they should also gain whatever additional saved interest accrues as the loan matures. I'm struggling to program this into a spreadsheet, because each new payment changes the basis for all future payments.
First, don't think that $500 paid should equal $500 of ownership. The "real" value of the property may not equal the sale price you paid which may not equal the mortgage amount may not equal the total eventual payments on the mortgage. Ownership should be calculated in percentage, as 100% remains 100% over time.
Keep it simple: Outstanding Balance and Outstanding Equity.
Each time a payment is made, find it's percentage of the outstanding balance. Take that percentage of the outstanding equity and award it to the payer.
Here's an example with exaggerated payments to demonstrate the effect:
Outstanding balance: $1,000,000.00
Outstanding equity: 100.0 %
> Partner A pays $100,000.00 (10% of outstanding balance)
Outstanding balance: $900,000.00
Partner A equity: 10.0% (100.0% x 10.0%)
Outstanding equity: 90.0%
> After some time, 5% interest is added ($45,000.00)
Outstanding balance: $945,000.00
> Partner B pays $100,000.00 (10.582% of outstanding balance)
Outstanding balance: $845,000.00
Partner A equity: 10.0% (unchanged)
Partner B equity: 9.524% (90.0% x 10.582%)
Outstanding equity: 80.476%
As you can see, Partner A and Partner B both paid the same amount, but at different times. Partner A got more equity for the same price because they paid earlier, before some interest was added.
To put an analogy to it, each partner is buying equity from the remaining shares of the property, where the total combined price of all remaining shares is equal to the outstanding balance.
The formulas used when a payment is made are:
Outstanding Share Paid = Payment Amount / Outstanding Balance
Payer Equity Gain = Outstanding Share Paid x Outstanding Equity
Outstanding Balance (new) = Outstanding Balance - Payment Amount
Outstanding Equity (new) = Outstanding Equity - Payer Equity Gain
Payer Equity (new) = Payer Equity + Payer Equity Gain
Interest or other adjustments to the balance are made independently of these formulas. That means you will need to know what the real outstanding balance is at the actual time of payment for each payment recorded.