PMT Function

[jmattingly85]

Hi. Looking for some help with the PMT function and not sure if what I'm looking to do is possible.

I'm looking to calculate what the max rate on a loan can be, using known values of the nper (length of loan) and pmt (NOI/DSCR).

Is this possible?

 

[Cubist]

Your question lacks a lot of details so cannot be answered.

 

[StephenCrump]

I agree that the question is vague. Did you perhaps mean something along these lines?

	A	B	C
1	Hypothetical property
2	Property value	500,000
3	NOI	25,000	p.a.
4	The amount you'd like to borrow	300,000
5
6	Hypothetical finance provider requirement
7	Required DSCR	1.2	say
8
9	Calculations
10	Your debt capacity	20,833	p.a.
11		1,736	p.m.
12
13	Interest only loan
14	Maximum interest rate possible	6.94%
15
16	Principal and Interest repayments
17	Term	240	months
18	Max interest rate	3.5%	p.a.
19	Monthly repayment	1,736
Sheet1

Cell Formulas
Range	Formula
B3	B3	=5%*B2
B10	B10	=B3/B7
B11	B11	=B10/12
B14	B14	=B10/B4
B19	B19	=PMT(B18/12,B17,-B4)

So for this hypothetical example, the maximum interest rate possible is ~3.5% p.a.

If that's not achievable, you could solve for the finance amount using some higher interest rate.

 

[jmattingly85]

I agree that the question is vague. Did you perhaps mean something along these lines?

	A	B	C
1	Hypothetical property
2	Property value	500,000
3	NOI	25,000	p.a.
4	The amount you'd like to borrow	300,000
5
6	Hypothetical finance provider requirement
7	Required DSCR	1.2	say
8
9	Calculations
10	Your debt capacity	20,833	p.a.
11		1,736	p.m.
12
13	Interest only loan
14	Maximum interest rate possible	6.94%
15
16	Principal and Interest repayments
17	Term	240	months
18	Max interest rate	3.5%	p.a.
19	Monthly repayment	1,736
Sheet1

Cell Formulas
Range	Formula
B3	B3	=5%*B2
B10	B10	=B3/B7
B11	B11	=B10/12
B14	B14	=B10/B4
B19	B19	=PMT(B18/12,B17,-B4)

View attachment 111854
So for this hypothetical example, the maximum interest rate possible is ~3.5% p.a.

If that's not achievable, you could solve for the finance amount using some higher interest rate.

Apologies for the vagueness. If these are my inputs:

NOI	$81,133​
DSCR	1.35​
Amortization	360​	months
Max Rate	?

I'd like so solve for the maximum rate that can be used assuming these requirement. Is that possible? Maybe PMT function isn't the correct formula.

 

[jmattingly85]

Apologies for the vagueness. If these are my inputs:

NOI	$81,133​
DSCR	1.35​
Amortization	360​	months
Max Rate	?

I'd like so solve for the maximum rate that can be used assuming these requirement. Is that possible? Maybe PMT function isn't the correct formula.

Missing the top row of inputs. Please see below:

Mortgage Amount	$482,079​
NOI	$81,133.00​
Required DSCR	1.35​
Amortization	360​	months
Max Rate	???​

 

[StephenCrump]

Did you see Post #3?

If you update for your assumptions, you should solve for an interest rate around 12.1% p.a.?