Solving for Loan amount based on PMT

  • Thread starter Thread starter bkiesz
  • Start date Start date
B

bkiesz

I'm having a heck of a time trying to find a formula to actually solve
for the loan amount based on Payment, term, and Interest. Maybe I'm
looking at it too closely (tunnel vision). Any help would be
appreciated.

What I'm trying to accomplish is trying to determine the max loan
amount based on a Debt Coverage Ratio..

Example would be:

You're NOI (Net Operating Income) on a property is $4195
Debt Service is $3336

DCR $4195/$3336 = 1.25
which mean for every $1.00 of debt there is $1.25 of income to satisfy
debt and expenses

Basically I need to take 1.25 and my NOI to solve for the Debt service,
divide it by 12 (mo. PMT) and solve for my loan based on the loan
payment.

Thanks in advance

Barry
 
One way:

=PV(mRate,nPer,-(NOI/DCR)/12,0)

Where mRate is your monthly interest rate, nPer is the number of
periods in the loan, and NOI and DCR are named ranges containing
their respective values.

For instance:

mRate = 7%/12, nPer = 120, NOI=4195 and DCR =1.25 (though in
your example, DCR = 4195/3336 = 1.257494005. NOI/1.25 = 3356)
returns a loan amount of

$24,086.67
 
Thanks for the quick reply... I will plug it in the spreadsheet tonight
and work with it.


Thanks again!!!!


Barry
 
Back
Top