# Mortgage Calculator - Calculate Loan Amount from Monthly Payment

1. ## Mortgage Calculator - Calculate Loan Amount from Monthly Payment

Hello,

I'm trying to build a mortgage calculator to determine how much of a loan someone can afford based on their payments. The payment would include homeowner's insurance and property tax, but not mortgage insurance as this is for a VA loan.

I've determined an equation and used online resources to solve for the desired value, but Excel returns #NUM and #DIV/0 errors depending on the structure of the formula. The cause is the large exponent but I cannot figure out how to circumvent that.

The formula is below:

=(((C17*C6)^(1+(C5*C6)))*(((C4/C6)+1)^(C5*C6))-((C17*C6)^(1+(C5*C6))))/((C11*(C4+C6))^(C5*C6))-((C11*C6)^(C5*C6))+((C14*(C4+C6))^(C5*C6))+(C2*C4*(C4+C6)^(C5*C6))+(C4*(C4+C6)^(C5*C6))

C1 = Home Value (Formula Entry)
C2 = VA Funding Rate (2.3%)
C3 = VA Funding Fee (Amount of Loan)
C4 = Interest Rate (Variable, ~3%)
C5 = Life Loan in years (30)
C6 = Payments Per Year (12)
C7 = Total Number of Payments (360)
C11 = Property Tax Rate (Variable, ~1.25%)
C14 = Home Insurance Rate (Variable, ~0.3%)
C17 = Desired Monthly Payment (Input Variable, ~\$3500)  Register To Reply

2. ## Re: Mortgage Calculator - Calculate Loan Amount from Monthly Payment

.
https://exceljet.net/formula/calcula...al-loan-amount  Register To Reply

3. ## Re: Mortgage Calculator - Calculate Loan Amount from Monthly Payment

I got a lot of good information there, but that's not quite what I'm looking for. I'm trying to plug a desired monthly mortgage payment into a formula that results in the original loan amount, thereby allowing someone to see how much of a loan they need to get.

Example: I want to pay \$3,000 per month. How big of a mortgage can that get me? I know that to be \$535,268, but how can I have the formula provide that number automatically?  Register To Reply

4. ## Re: Mortgage Calculator - Calculate Loan Amount from Monthly Payment

.

Provides a download Excel file. You will need to fill in ALL of fields though.

There simply is no means of entering only your monthly payment and determining what the total mortgage amount is. There are other variables
that are always included such as interest rate, length of note, etc. Were you to leave those aspects out of the formulation you would only be
kidding yourself what you can truly afford.  Register To Reply

5. ## Re: Mortgage Calculator - Calculate Loan Amount from Monthly Payment

That data is included in my spreadsheet. The only part not included is the original loan amount. The interest rates and terms are all known and included in the formula. I'm having difficulty reversing the formula.  Register To Reply

6. ## Re: Mortgage Calculator - Calculate Loan Amount from Monthly Payment

. ``Please Login or Register  to view this content.``
See attached.  Register To Reply

7. ## Re: Mortgage Calculator - Calculate Loan Amount from Monthly Payment

I'm not sure this is going to work. I appreciate your time but this is not what I'm looking for. You omitted a large part of my data and if I were to factor that back in I'm left with a circular formula error.  Register To Reply

8. ## Re: Mortgage Calculator - Calculate Loan Amount from Monthly Payment

.
I'm most likely not the person that will be able to assist.

If you were to post your workbook and your code / formulas for others to review, someone on this forum should be able to assist.
Without having your existing construct (not fully knowing what you are working with and what the ultimate goal is) it will be very
difficult for someone to assist you.

If there is confidential information contained within your existing project .... remove it. Provide a culled down version of what you are
working with. Just enough information to make sense of your project.  Register To Reply

9. ## Re: Mortgage Calculator - Calculate Loan Amount from Monthly Payment

I got a different, simpler equation that seemed to solve just fine (within rounding errors, anyway). Most of the work is algebra, and it isn't trivial, but it is straightforward if you keep track of parentheses and signs.

You used the PMT() function in B8, so I'm assuming that the entire thing fits into a PV() type of model. I start with the basic equation for present value (see the PV() function help file: https://support.microsoft.com/en-us/...rs=en-us&ad=us

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

type is 0, so that part of the pmt term drops out. fv is also 0 so fv drops out, leaving:
pv*(1+rate)^nper+pmt*((1+rate)^nper)-1)/rate=0
You seem to want pv and pmt to both be positive, so I switch the sign on the pmt term:
pv*(1+rate)^nper+pmt*(1-(1+rate)^nper)/rate=0

One issue -- the pv in the PMT() portion of this is not the loan amount, but rather, the VA amount VA=loan*(1+VArate), so substitute that into the main pv formula
loan*(1+VArate)*(1+rate)^nper+pmt*(1-(1+rate)^nper)/rate=0

The spreadsheet solves this equation for pmt in B8 to get the loan payment part. You then add the contributions for taxes (loan*monthlytaxrate) and insurance (loan*monthlyinsrate) to get the total payment in B17.
totalpmt=pmt+loan*taxrate+loan*insrate (where taxrate and insrate are monthly rates).
solve for pmt pmt=totalpmt-loan*taxrate-loan*insrate and substitute into the pv formula

loan*(1+VArate)*(1+rate)^nper+(totalpmt-loan*taxrate-loan*insrate)*(1-(1+rate)^nper)/rate)=0

From there, it's a "simple" matter of solving for loan. Then you will have a formula for C1 that should work.

Can you manage the algebra from there?  Register To Reply

10. ## Re: Mortgage Calculator - Calculate Loan Amount from Monthly Payment

Thank you for your in-depth analysis of my problem. I'll give it a shot! Algebra is how I ended up in this situation in the first place but I may have made some mistakes along the way. I'll update after I try your solution.  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 