+ Reply to Thread
Results 1 to 10 of 10

Mortgage Calculator - Calculate Loan Amount from Monthly Payment

  1. #1
    Registered User
    Join Date
    06-21-2019
    Location
    Claremont, CA
    MS-Off Ver
    2010
    Posts
    5

    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)

    Please help me simplify and solve this absolute monster.
    Attached Files Attached Files
    Last edited by vinnythering; 08-29-2020 at 07:49 PM. Reason: Updating formulas for planned attachment.

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    5,772

    Re: Mortgage Calculator - Calculate Loan Amount from Monthly Payment

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

  3. #3
    Registered User
    Join Date
    06-21-2019
    Location
    Claremont, CA
    MS-Off Ver
    2010
    Posts
    5

    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?

  4. #4
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    5,772

    Re: Mortgage Calculator - Calculate Loan Amount from Monthly Payment

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

  5. #5
    Registered User
    Join Date
    06-21-2019
    Location
    Claremont, CA
    MS-Off Ver
    2010
    Posts
    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.

  6. #6
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    5,772

    Re: Mortgage Calculator - Calculate Loan Amount from Monthly Payment

    .
    Please Login or Register  to view this content.
    See attached.
    Attached Files Attached Files

  7. #7
    Registered User
    Join Date
    06-21-2019
    Location
    Claremont, CA
    MS-Off Ver
    2010
    Posts
    5

    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.

  8. #8
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    5,772

    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.

  9. #9
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    12,559

    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?
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  10. #10
    Registered User
    Join Date
    06-21-2019
    Location
    Claremont, CA
    MS-Off Ver
    2010
    Posts
    5

    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.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 3
    Last Post: 07-11-2017, 03:49 PM
  2. Need function to calculate monthly payment of mortgage?
    By mixhi4ever in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 03-11-2014, 04:43 AM
  3. Replies: 3
    Last Post: 07-29-2013, 11:31 AM
  4. Loan schedule when I know the monthly payment amount?
    By Jo-Anne in forum Excel General
    Replies: 6
    Last Post: 02-02-2011, 11:12 AM
  5. Solve for Monthly Loan Payment Amount
    By ShaneBell in forum Excel General
    Replies: 1
    Last Post: 12-13-2006, 06:09 PM
  6. monthly mortgage payment calculator
    By Raza in forum Excel General
    Replies: 2
    Last Post: 10-17-2005, 09:05 AM
  7. I need a loan calculator that you can plug in the payment amount
    By payment amount loan calculator in forum Excel General
    Replies: 5
    Last Post: 05-30-2005, 05:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1