Mortgage Calculator - Calculate Loan Amount from Monthly Payment

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

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?

.
This link : https://www.excelfunctions.net/excel...alculator.html

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.

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.

.

Please Login or Register  to view this content.

See attached.

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.

.
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.

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.

Originally Posted by vinnythering

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)

Please help me simplify and solve this absolute monster.

I have a database that can simulate a loan like a excel loan calculators.

Tested with:-
Version..............: Access 2010.
Regional settings.: English, French, German
References……..…: None.
Error handling…...: none.

if you have a comment please let me know.

Originally Posted by vinnythering

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)

Please help me simplify and solve this absolute monster.

I am looking for a very good mortgage calculator Excel sheet to use, in our country the interest rate can change any month, so I need to get one that can let me change the interest rate at any date, and keep the info before that at the old interest rate.