Maximum Allowable Offer Math Problem

[Christopher Freeman]

Okay, here's a doozey.

Given: Rate and term on a first lien mortgage, rate and term on a seller second, composition of capital structure (e.g. 75% commercial mortgage, 15% seller note, 10% buyer's purchase equity), net operating income, and a maximum debt service coverage ratio...

Calculate: The amount of debt that will cause the DSCR to equal the max DSCR. In other words, calculate the maximum allowable offer based upon a specified DSCR constraint.

The first part is easy. If you know NOI and you know your max DSCR, you just do NOI/mDSCR = Maximum Debt Service.

If you only had one amortizing loan, the calculation is easy. If:

L= Loan Principal

P= Payment

c= Interest Rate

n = Number of periods

and

P= L*[(c(1+c)^n)/((1+c)^n)-1)]

then

L= (P(1+c)^n-P)/(c(1+c)^n)

Where it gets tricky is when your payment is the result of more than one amortization. If you had two loans with the same rate and term, then you could calculate it as though it were one loan with the same rate and number of compounding periods. I **think** (but am not 100% certain) that if you had two loans with the same term but different rates, that you could get the correct answer by using the weighted average cost of debt and leaving the number of compounding periods unchanged.

I'm not sure how to structure the calculation if both rate and term are different. In theory L = L1 + L2, where L-1 is the loan principal amount for the first lien mortgage and L-2 is the principal amount for the seller second. You could then setup the formula such that L= (P1(1+c)^n-P1)/(c(1+c)^n) + L= (P2(1+c)^n-P2)/(c(1+c)^n). However, you would not know or be able to calculate the value of P1 (payment of the first lien) and P2 (payment of the seller second) without knowing or solving for L1 and L2, which puts you in a circular logic loop.

Is there a way to get here without cheating and using goal seek in excel?