# Help w/ formula for variable loan amount

• Jan 15th 2010, 10:22 AM
TripleC
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?
• Jan 15th 2010, 07:08 PM
Wilmer
Well, put up an example...else, I don't think anyone (me anyway!) will
• Jan 16th 2010, 11:48 AM
TripleC
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.