Formula for maximum Mortgage Loan Amount

**FinGhost** · 12-22-2015, 09:17 PM

I don't know if I missed the day in class to figure out how to get this to calculate. But I have spent all afternoon in excel and am lost.
My variables would be a maximum monthly payment allowed (income-liabilities)max DTI

What I need help is finding a formula for maximum loan amount when taken into account Principal and Interest (calculated with interest rate and loan amount), monthly mortgage insurance (a small percentage of loan amount), the value for monthly Homeowners insurance, the value for monthly tax dues.

http://www.calcunation.com/calculator/piti-payment.php
I'm not sure if this is clear at all but this website has the information on how the payment is calculated without the monthly mortgage insurance premium added but I need a formula on how to calculate the maximum loan amount based off of:
calculated max payment amount minus the set tax and insurance values, interest rate, term, mortgage insurance premium

**tony h** · 12-23-2015, 05:22 AM

The calculation for the repayment is :
repayment=Mortgage/((1-((1+monthlyInterest)^-yearsX12))/monthlyInterest)

or
So the formula for the mortgage is:
Mortgage = repayment*((1-((1+monthlyInterest)^-yearsX12))/monthlyInterest)

Just maximize the term and the repayment value (minus tax, insurance etc)

**joeu2004** · 12-23-2015, 06:16 AM

Originally Posted by FinGhost

I need a formula on how to calculate the maximum loan amount based off of:
calculated max payment amount minus the set tax and insurance values, interest rate, term, mortgage insurance premium

If the additional monthly amounts were constant (like tax and insurance), the max loan could be calculated simply by (referencing the table below):

=ROUNDDOWN(-PV(C3,C4,C2-C6-C7),0)

The problem is: in the US, the PMI rate is a percentage of the loan [1]. Consequently, we must solve for PV in the following formula, simplified and modified from the Excel PV help page:

PV*(1+r)^n - (PMT - tax - insurance - PV*i)*((1+r)^n - 1)/r = 0

where r is the monthly interest rate, i is the monthly PMI rate, PV and PMT are positive (not the usual signed amounts), and FV is zero.

The algebraic solution for PV results in the formula for B8 in the table below. I have not been able to simplify it.

Please Login or Register  to view this content.

-----
[1] According to the PMI wiki page, it is actually a percentage of the outstanding balance at the beginning of each year, resulting in a declining PMI each year. Nevertheless, the max PMI (ergo, the max total monthly payment) would be the percentage of the loan.

**joeu2004** · 12-23-2015, 01:15 PM

Originally Posted by joeu2004

The algebraic solution for PV results in the formula for B8 in the table below. I have not been able to simplify it.
[....]
B8: =ROUNDDOWN((C2-C6-C7)*((1+C3)^C4-1)/((C3+C5)*(1+C3)^C4-C5),0)

A little simpler:

Please Login or Register  to view this content.

Recall:
C2: max periodic payment
C3: periodic interest rate
C4: number of payment periods (loan term)
C5: periodic PMI rate
C6: periodic (property) tax
C7: periodic (home) insurance

**FinGhost** · 12-23-2015, 02:49 PM

I am punching in these things now

**FinGhost** · 12-23-2015, 03:14 PM

Im trying to follow this the best I can but I am still getting an issue with the maximum loan value.
Here is my sheetMax Loan Calc.xlsx

EDIT: typo in cell c2 but max loan amount is still off

**joeu2004** · 12-23-2015, 05:29 PM

Originally Posted by FinGhost

Im trying to follow this the best I can but I am still getting an issue with the maximum loan value.

You did pretty well. Your primary mistake: you entered the PMI rate in B5 as 0.46 (= 46%!) instead 0.46%.

However, I believe you also calculated the max payment in C2 incorrectly. See the explanation below of my formula in C2.

Finally, a minor design change: the inputs in B15:B17 should be in C15:C17 for clarity, since they are monthly amounts, not annual (the column heading for column B).

See the "Corrected" worksheet in the attached file. Or refer to the table below.

Please Login or Register  to view this content.

The formula in C2 is derived as follows.

Please Login or Register  to view this content.

**FinGhost** · 12-23-2015, 05:47 PM

I see what I did in C2, your formula is correct I was painting a different picture in my head when I keyed that formula. PMI rate in B5 makes sense now. Looks like everything is as it should. Thank you so much. I wish I could rep you again