Thread: Help w/ formula for variable loan amount

I need to calculate a loan amount that can be reduced by a portion of funds from a separate account.

I have a known maximum loan amount (say $14MM) and a pool of funds from which funds will be applied to effectively reduce the amount funded (say $5MM). The problem is that, from the pool, I need to set aside an amount that will equal a set number of pmts of the resulting pmt in reserve (say 12 months pmts).

So, it looks like this:
Max. Amt - (Pool - Reserve) = Adjusted max; where Reserve = 12* PMT(Adjusted Max)

The Max Amt, Pool, and Months Reserve (as well as the variables req'd to calc a pmt) are all variable but known prior to this calculation. Is there a way to solve this non-recursively?

Well, put up an example...else, I don't think anyone (me anyway!) will
understand what you're asking...

Hmm, I thought that I had included an example. Let me try a different way.

Max Loan: $14MM
Pool: $ 5MM
Mo's Reserve: 12 (# of pmts set aside; based on new loan amt.)
Pmt. Terms: Can be anything but say 20 years, 8% interest

Problem restated: The actual loan made shall be reduced by the net Pool funds after deducting the Required Reserve funds from the Pool.

I need to be able to determine the proper reserve amount on a loan balance that is not known until I know how much is available from the pool. However, I can't determine that until I know what the new loan balance is.

I hope that I have made this a bit more clear. I can get "close enough" in my spreadsheet, but I am really very curious if there is a way to solve this without a circular reference. It seems to me that it may be possible.

I appreciate any thoughts on this. Take care.