RatedrillHow much can I borrow
The amount you can borrow for a given payment is P = PMT x (1 - (1 + r)^-n) / r, the present value of the payments at the loan rate. At 6% over 30 years, each 1,000 of monthly payment supports about 166,790 of loan. In Excel: =PV(rate/12, months, -payment).
Syntax
P = PMT x (1 - (1 + r)^-n) / r| Part | What it is |
|---|---|
PMT | Monthly payment you can afford (principal and interest only) |
r | Monthly rate (annual rate / 12) |
n | Term in months |
Examples
Payment 1,500 per month, 6%, 30 years
P = 1,500 x (1 - 1.005^-360) / 0.005Result: P = 250,187.42
Excel: =PV(6%/12, 360, -1500)
Payment 2,000 per month, 5%, 30 years
P = 2,000 x (1 - (1 + 0.05/12)^-360) / (0.05/12)Result: P = 372,563.23
Car: 400 per month, 7%, 60 months
P = 400 x (1 - (1 + 0.07/12)^-60) / (0.07/12)Result: P = 20,200.80
Excel: =PV(7%/12, 60, -400)
Common mistakes
- Multiplying the payment by the number of months (1,500 x 360 = 540,000) ignores interest and wildly overstates the loan.
- Budgeting the full payment for principal and interest and forgetting property tax, insurance and fees, which lenders include in the affordability test.
- Using the annual rate without dividing by 12.
Practice How much can I borrow
These drills check your answer on live data, free, no account needed.
- How much you can borrowYour budget allows a monthly mortgage payment of $800. At 4.25% APR for 30 years, what is the largest loan you can take?
- Present value of a payment streamA legal settlement pays $1,000 at the end of each year for 25 years. At a discount rate of 3.25% per year, what is the payment stream worth today?
Questions
How do lenders decide how much I can borrow?
Usually by a debt-to-income ratio: total monthly debt payments (including the new loan) are capped at around 36% to 43% of gross monthly income. The affordable payment from that cap is then converted to a loan amount with this formula.
How much does a higher rate reduce my borrowing power?
A lot. At 1,500 per month over 30 years you can borrow 250,187 at 6%, but only 225,461 at 7% and 204,425 at 8%.
Related
- Mortgage payment formulaHow to calculate a monthly mortgage payment with M = P x r / (1 - (1 + r)^-n). Examples for 200,000 at 6% and 300,000 at 5%, Excel PMT and common mistakes.
- Present value of an annuityHow to find the present value of equal payments with PV = PMT x (1 - (1 + r)^-n) / r. Worked examples, Excel PV and why this is the loan formula in disguise.
- Car loan paymentHow to calculate a car loan payment with PMT = P x r / (1 - (1 + r)^-n). Examples for 20,000 at 7% over 60 months and 15,000 at 5%, total interest, Excel.
- Total interest paid on a loanTotal interest on an amortizing loan is the payment times the number of payments minus the principal. Examples for 30-year and 15-year loans, Excel CUMIPMT.