Calculate Loan Affordability

[KdilagSB]

Hi all,

I know the PMT formula can calculate the payment of a principal loan, if given the interest rate, number of payments and principal. However, what is the formula to calculate the principal loan if given the payment, interest rate and number of payments? In other words, based on my income, and terms, what is the loan I can afford?

Thanks so much! 

[#2]

@KdilagSB 

See the PV function.

[#3]

@KdilagSB

as @Joe User suggested, the formula to calculate the principal loan if given the payment, interest rate and number of payments is called the present value formula.
The present value formula is as follows:

PV = PMT / ((1 + r)^n - 1) / (1 + r)^n

where:
PV = present value (i.e., the principal loan amount)
PMT = payment per period
r = interest rate per period
n = total number of payment periods

To use this formula to determine the loan amount you can afford, you would first need to determine the payment you can afford based on your income and expenses. Then, you would need to choose an interest rate and a number of payment periods (such as years) that would work for your budget.

Once you have determined these variables, you can plug them into the formula to calculate the maximum principal loan amount that you can afford based on the terms of the loan.

[#4 · Accepted solution]

@Rr_  wrote:  ``as @Joe User suggested, the [....] present value formula is as follows``

No.  As I suggested, use the PV function (click here).  That is:

PV(rate, nper, pmt, fv, type)

I don't know why anyone would use a complicated math formula when such a simple function is readily available.

[#5]

Thanks @Joe User  and @Rr_ !

Knowing both the function and the math is helpful.

[#6]

@KdilagSB  wrote:  ``Knowing [...] the math is helpful``

Then note that @Rr_'s math is wrong.

The PV support page (click here) shows the math formula

Solving for PV, the math formula is:

pv = -(pmt*(1 + rate*type)*((1+rate)^nper - 1) / rate + fv) / (1+rate)^nper

@Rr_  makes the simplifying assumptions that fv=0 and type=0.  But even then, the formula is:

pv = -pmt*((1+rate)^nper - 1) / rate / (1+rate)^nper

Recall that pv and pmt are signed cash flows, and for loans, they should be oppositely signed.  Thus, if pv is positive, pmt is negative.

But even if we make the simplifying assumption that pv and pmt are both positive, but pmt should be interpreted as an opposite cash flow, the correct formula is:

pv = pmt*((1+rate)^nper - 1) / rate / (1+rate)^nper