1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Mortgage Loan Constant Formula

Discussion in 'Business Applications' started by edbyrd, Mar 17, 2004.

Thread Status:
Not open for further replies.
Advertisement
  1. edbyrd

    edbyrd Thread Starter

    Joined:
    Jul 12, 2003
    Messages:
    19
    Can someone help with with an Excel formula for a mortgage constant with the following rules for each cell: Loan Amount W77 ($100,000), Interest rate W78 (5.125%), Amortization W81 (240 months).
     
  2. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    Interest rate W78 (5.125%)
    Amortization W81 (240 months)
    Loan Amount W77 ($100,000)


    the MC is:

    MC=(W78/100/12)/(1-(1/(POWER((1+(W78/100/12)),W81))))


    MC * $100,000 = $666.80 per month (for your example) So let's say this formula is in W80....

    W80*W77 is the payment

    The interest (5.125%) was divided by 100 and the 12 months
    The maturity (240) is in months

    - Castleheart :cool:




    hope I did that right. try some examples and see...
     
  3. AbvAvgUser

    AbvAvgUser

    Joined:
    Oct 3, 2002
    Messages:
    2,281
    I have a whole lot of Per Lakh (100,000) tables with me. That will make calculation of Rate of Interest from EMI (Equalted Monthly Installment) very very easy. You can use them the other way round as well. All you need is just a calculator or Pen/Paper.

    If you need amortisation statements, I have those too. Give me your email address in a private message and I will mail them all to you. :)
     
  4. AbvAvgUser

    AbvAvgUser

    Joined:
    Oct 3, 2002
    Messages:
    2,281
    BTW, just forgot to mention. They are all in Excel 2000 format. Some have been converted in HTML format but they exist in excel also
     
  5. CastleHeart

    CastleHeart

    Joined:
    May 4, 2002
    Messages:
    743
    FYI:

    Looking back at my post I realized that the formula I submitted just looks like a bunch of numbers to most readers, so for those who are interested in knowing it, the basic formula is:

    MC=i/{1-[1/(1+i)ª]}


    Where:
    MC is the mortgage constant
    i is the interest
    a is the period in months
    ( I used "a" in this post because I didn't know how to display "m" here as an exponent!)

    You then multiply MC times the loan amount for a monthly payment.

    - C :cool:
     
  6. AbvAvgUser

    AbvAvgUser

    Joined:
    Oct 3, 2002
    Messages:
    2,281
    Will have a look at your formula and will also test it for its accuracy. Thanks for it.
     
  7. analyst1

    analyst1

    Joined:
    Sep 15, 2008
    Messages:
    1
    What is that formula factored out to solve for i? I have the loan constant and the amortization and need to know the interest rate. Thanks!
     
  8. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/212705